The Excel PRODUCT Function

I have to confess that the PRODUCT function in Excel is one I’ve generally considered pretty useless. SUMPRODUCT is much more exciting to me. But this morning I was trying to do some projections for sales of my new cozy mystery and I ended up using the PRODUCT function two different times.

And because I’m a nerd and Excel things excite me, I figured I’d share where and how I used it.

This will also be useful to any writers who write in a series or are thinking of doing so and want to extrapolate from sales of a first in series to sales of the whole series.

So here’s the thought process and how the PRODUCT function can come into play:

At its most basic, if I publish a single book then my earnings on that book are equal to the price of the book times the payout percentage. (In reality, if you’re in KU or have a print version it’s more complicated than that because each format has its own value but we’re going to ignore that for now.)

Now, there’s a temptation to say, “If I write ten books in this series, I’ll earn ten times as much.” But that’s not how it works.

Not everyone who reads the first book will go on to read the other books. I have a series of eight related short romance stories and I see a drop off from story 1 to story 2 and then from story 2 to story 3. But from story 4 onward it’s very close to a 100% readthrough.

If I want to calculate the value of a new customer I can’t just take the price of each story times the payout. I also have to factor in how many readers actually make it that far into the series.

So the value of book 8 to me is not price times payout. It’s price times payout times % of readers of book 1 who read it.

This is the first place you can use the PRODUCT function.

Because the calculation you’re doing here is: price times payout times % who read book 2 times % who then read book 3 times % who then read book 4 times % who then read book 5, etc.

(In Excel for Self-Publishers I did a similar sort of calculation but used a different approach that just looked at book 1 to that particular book in the series because I had real data at that point. But this is extrapolating when you don’t have any data yet.)

One way to write that calculation is: =A1*A2*A3*A4*… where each of those values is in a different cell in Column A.

Another way to write it, though, is using PRODUCT. You just write =PRODUCT(A1:A9) assuming your price is in A1, your payout is in A2, and your readthrough rates are in Cells A3 through A9.

Isn’t that nice and simple?

I know, it’s a little hard to visualize. And you’re probably not going to set your data up exactly that way.

But let’s look at a simpler example, which is the second way in which I used PRODUCT.

First I had to calculate the value above. I assumed I had a ten book series with book 1 priced at 99 cents and the rest at $3.99 with a fifty percent readthrough from book 1 to book 2 that then goes up to 80% and then 95%, I calculated that I would earn $7.54 for each new reader.

Currently book 1 is priced at $3.99.

So that’s an increase in overall revenue by a factor of 2.70 just based on having more books out. (7.54 divided by 3.99).

But we also have that price drop factor at play. How many more people will read book 1 if it’s priced at 99 cents than are currently reading it when it’s priced at $3.99? For my purposes I said three times as many.

(This is for cozy mystery. Other genres might see no increase or even a drop. Or readthrough might be severely impacted depending on book 1 and book 2 prices. You have to know what you’re selling or have a guess how it’ll behave to do this.)

Next, we have the series factor. Some people prefer to read series instead of standalones. And more books means more visibility. So how much will having ten, presumably well-reviewed, books in a series impact book 1 sales? In this case I assumed that would double sales although there’s a good chance it could do more than that.

So if I want to take current sales on book 1 and try to figure out what I might earn if I have ten books out in that series what I need to do is take the amount I’m earning each month on that book and multiply it by that readthrough factor and then multiply that by the price drop factor (assuming I’m dropping book 1’s price, otherwise it’s just 1), and then multiply that by the series increase factor.

Which is another place to use PRODUCT. Here’s the Excel worksheet so you can visualize this:

Series Value Estimation

I have the increase factors in Cells C34 through E34, so my formula in Cell F34 is =PRODUCT(C34:E34) to get my overall factor of 16.2.

I can then take that number and multiply it by the amount I expect to earn on the first cozy when things reach a steady state. Let’s say $250. (In the image above that calculation’s done in Cell G34.)

(You can use PRODUCT again here, which is what I did. In that case, the formula is =PRODUCT(B34:E34) because I have Book 1 sales in Cell B34. Or you can just do =B34*F34.)

Based on these calculations I can say that if I have a Book 1 in a series that is earning me $250 a month and I write nine more books in that series and discount the first book to 99 cents while selling all the other titles at $3.99 with my assumed readthrough rates and price drop and series increases that I will earn, on average, $4,000 a month from those ten books.

Understand, though, that there are many many assumptions at play here. If my Book 1 to Book 2 readthrough is 75% instead, that number goes up to $6,000 a month for a ten-book series. If I see a four-fold increase in sales from having a series rather than a two-fold increase then it’s $8,000. If I have 75% readthrough AND a four-fold increase, then it’s $12,000 a month.

On the flip side if there is no series increase and dropping the price only doubles sales then it’s just $2,000 a month for a ten-book series. And if people fall off of the series as it continues (say you have lower and lower readthrough rates after Book 6) then it’s just $1,445 a month for that ten-book series.

(Don’t continue a series like that. Wrap it up as soon as you can. Book 1 to Book 2 you should see drop off. Family and friends will buy Book 1 to support you but not read the whole series, some people will find that the book just isn’t for them, and some will buy and not get around to it for five years. Book 2 to Book 3 may have another drop off because people who were on the fence with Book 1 may try Book 2 and then decide not to continue. But from Book 3 onward, you should really have your core audience.)

(I should also add that there could be a time factor at play here. The longer the series the longer it takes someone to read through that series. On a recent podcast someone mentioned six months for how long it can take to read through a series. For a very long one, I’d agree that might be true. For a trilogy, if you’ve really hooked a reader, I’d say one month at the longest. Of course, that’s from when they start reading it. But once you reach steady state for a series that should disappear in the wash because people should always be reading through your series each month even if they’re at different points in doing so.)

Anyway. You can make all of this insanely complicated if you want, but this was just a basic calculation I wanted to share using PRODUCT which I had so unfairly misjudged. Once you set something like this up, it’s easy enough to play with the numbers and see your full range of values.

 

 

Know Your Timeframe…

My senior year of college I interviewed for a couple of jobs where we ended up discussing various business ideas. This was 1999, so about twenty years ago.

In the first instance I ended up interviewing with the business development group for a television channel. And they were very excited about online content.

My reaction? No, not yet. Here I was, a senior in college, and I’d never even had a computer in my room until that year and the computer I did have was certainly not a fancy one. I was not alone in this. And the internet connectivity to support that kind of thing just wasn’t there yet.

To me that was probably a good decade away from being significant and I said so. (Didn’t get the job. No surprise there.)

Now, did it take people having that belief in 1999 to get to the point where things started to happen in 2005 and really started to pick up in 2008? Probably. But that man I spoke to in 1999 did not think he was nine years from launching his project.

On the other side of the coin I had another job interview that involved a written exercise where we were supposed to evaluate four different business ideas related to plane tickets. I had just flown from San Francisco to Washington DC using an emailed reservation. (Two years prior to that I’d gone to Europe and the only option was a paper ticket and you were screwed if you lost it.)

One of the options in that article was a kiosk that would be placed in large office buildings where business travelers could print off their ticket as they left for their flight. Great idea five years before when the world revolved around having a physical, paper ticket. Obsolete by the time I was traveling for work a year after the interview. Everything was digital by then.

Both were good business ideas for a specific period of time. Someone could’ve made bank with those kiosks if they’d done it five years earlier and managed a rapid rollout. And online streaming content is certainly a money-maker today.

But oftentimes the key to making money in an industry is knowing what’s going to happen when. And how quickly you can react to a situation. And what your personal timeframe is.

Let’s take KU for an example. I know of authors who’ve made millions by being in KU. There’s an author who posted on a certain forum who is all-in with KU who makes six figures a month. For that author and their timeframe, which is short-term, KU is a perfect choice.

Now, the flip side of that is how long KU will last. If you want to be an author making a living at writing in ten years or twenty, then being all in with KU might be a horrible decision. Because KU may not exist by then and when it goes away you will be staring from scratch on all the wide platforms along with every other KU author. You’ll have a backlist but you’ll potentially drown in a sea of new content.

(I should add that part of what prompted this post is a post that KKR just wrote implying that KDP will not exist long-term. In other words, no Amazon sales. Something that has crossed my mind as well once or twice because self-publishers routinely make themselves a complete PITA for Amazon.)

So someone who’s in this for the twenty year timeframe may be best off going wide and staying wide even if that means earning far less money in the short-term.

It’s interesting to look at these things, because I will often miss a good profitable short-term opportunity because I see that long-term it won’t be sustainable.

For example, last spring someone advised me to lean hard into my book on AMS ads. They saw real potential there for an alternative to the data-heavy model advocated by Meeks. (And it’s true that what I preach is far less analytical and labor-intensive than everything I’ve heard about his approach.)

But I said no. Because to me AMS is just one form of advertising that will eventually get glutted by self-publishers and then I’m suddenly a snake oil salesperson either telling people about a method that once worked but no longer does or trying to tell them about a new ad option that I don’t do as well with.

I did not want to build a career on that.

But there’s no denying that Mark Dawson and Brian Meeks have both made significant amounts of money off of their advertising courses. Short-term, telling people how to use Facebook ads or AMS ads is a good play.

Long-term? Let me just say that understanding how one ad platform works does not mean you understand how the next one does and that it can get a little shaky when you’re still looked to as an expert on something you actually no longer have expertise in…

So not something I wanted to do. Because my timeframe was five-plus years. If I’m going to devote time and energy to something I want it to have value five years from now.

Another example of this is writing to trend. You can make a killing writing the latest greatest thing that ravenous readers want. People have done so in LitRPG and reverse harem and step-brother romance. And if you’re timeframe is short, that’s great. Get in, get the money, get out.

The problem arises if you’re thinking that your short-term play has long-term potential. Because when that goes away, you are broken. The guy who raised funds to put kiosks in office buildings to print plane tickets? Sitting there five years later wondering what he does now because those kiosks are worth nothing.

For me, there’s no wrong or right about which timeframe to choose. The key is knowing your own timeframe and then making decisions according to that timeframe. Also, listening to those who share your timeframe and not the others.

I’d also say that you need to pick the timeframe that works for what you are capable of accomplishing. So if you want to be all-in KU and write to trend, you need to be able to write fast. If you want to take advantage of a short-term opportunity that could be worth nothing a year from now, you need to be able to develop and roll out a “good enough” product NOW rather than a perfect product eighteen months from now.

I also do think it’s possible to “ride the waves” and adjust when things change. So you go all in with KU now and write to X trend for however long it lasts, and when that dies you find the next wave and ride it. And when that dies you find the next one and ride it. You may miss a few. You may have down times. You may go from $20,000 a month to $500. But that doesn’t mean it was a bad choice.

It’s just a different choice than the slow and steady build. Both can have the same long-term financial result. Which you choose is very much about what you’d rather have: slow, steady progression upward or peaks and valleys.

The key, though, is knowing the decision you’re making. Know how those patterns play out based on your timeframe. Seek out the opportunities that match that and ignore the rest.

CreateSpace to KDP Migration Additional Comments

So it’s been about three months since I migrated my books from CreateSpace to KDP at Amazon’s insistence.

A few thoughts.

First, if you still haven’t done so, just do it. It’s literally a button push and you’ll make sure that the books end up connected to the right account assuming you follow the pretty simple directions.

Second, if you have migrated and haven’t noticed this yet, you may want to check in with your CreateSpace account for a few months after the migration. I’m still getting expanded distribution sales reported through my CreateSpace account. (And then paid separately as a CreateSpace payment.)

Third, because CreateSpace didn’t have as many keyword slots and only allowed one category, when your books move over they move over with just those five keywords and one category. You can go in and update the book to add more keywords and categories, but you have to go through all three screens to do so. I hesitated to do that because I was worried something wonky would happen with my files (and some people had reported having that be an issue), but I think you’re okay as long as you don’t try to upload new files and just save through the second screen. I updated all of my books this last week and they went through without any issues. Only issue I had was an old title I renamed and the cover didn’t meet their specs when I changed just the title text and uploaded it again.

Fourth, the territories for books that migrate over are not worldwide. I think the boxes that are checked are the ones that Amazon/CreateSpace actually distribute to, but you can, while you’re in there, change it to worldwide availability. (Canada is one that isn’t be default checked, for example.)

Fifth, I will say that for some reason I lost all Euro sales after the move. I was making about 150 Euros per month in paperback sales and have had maybe 10 Euros in sales in the three months since. No idea why. Maybe that worldwide checkbox. We’ll see, but so far I’m not seeing any difference.

Sixth, I’ve uploaded new titles and it’s been pretty simple. A too-wide spine text and that one that didn’t meet their dimensions but in both cases the error message was very clear on what the issue was down to them telling me the exact inch dimensions that one cover needed to be.

And that’s about it. I took a few days’ hit when I first moved over because KDP Print reports when a book ships and I think CreateSpace reported when it was ordered, but now that I’m three months in that’s not an issue anymore. But do be aware that the print sales you see on your KDP dashboard on any given day may have been ordered up to 10 days before that date. (Generate the Excel file and look at the Order Date column to see when the books were actually ordered.)

That delay makes AMS tracking a nightmare for me. I suspect most people won’t have that same issue since most fiction titles sell far better in ebook than print.

Anyway. Yet another change. Wasn’t as bad as it could’ve been. Had some annoyances of missing books the first week or so, but that’s all sorted as this point. Onward and upward.

Excel Templates Now Available

I’m trying an experiment. We’ll see how it goes.

Basically, I’m making the Excel files I used for Excel for Writers, Excel for Self-Publishers, and Excel for Budgeting available for purchase via the Payhip store. These are for anyone who wants to perform the calculations in those books but would rather not go through the steps of recreating all of the worksheets themselves.

They are not ready to use out of the box. They will need to be tailored to each individual’s information, so there is definitely still Excel work that needs to be done to use them. But for those who aren’t as comfortable with Excel as I am or who just want to save a little time, they’re now available.

The Excel for Writers and Excel for Budgeting templates are 99 cents. The Excel for Self-Publishers template is $2.99.

Note that these templates are meant to be used in conjunction with their respective books so they do not come with detailed instructions for how to use each tab because that is covered in the books.

We’ll see how this works. Hopefully it’s useful for people but if it turns out to create more confusion than it’s worth, these won’t remain on sale for long.

Know Your Audience

I was just on Facebook and saw an author mentioning that they’re writing six short stories to serve as an introduction to their main series of novels.

My immediate thought was, that might be a waste of time.

I as a reader am a novel reader. I don’t seek out short stories or novellas. I did recently find myself reading novellas by two authors I like (Kristen Britain and Ilona Andrews) because that’s what they’d released recently and I am constantly starved for more material from what I consider top-tier authors.

They were fine, but if that’s all they ever published again, I’d stop reading them because they wouldn’t be meeting my needs as a reader.

And for a new author? Someone I’ve never read before? I’m not going to buy that 99 cent short story or novella. I won’t even look at it because I’m not a short story or novella reader. Give me a free or 99 cent novel of yours and I might check it out. (Might. I’m weird so rarely buy books on those kinds of sales and still read mostly in print.)

And, yes, there are readers who cross over between stories of all lengths, so writing a short story or novella lead-in to your world might be an effective strategy to bring in a certain percentage of readers. And if you have an opportunity to be in a box set or themed anthology it might make some sense to participate to expand your exposure to new readers.

But, honestly, I would say that if you’re going to commit yourself to writing 50,000 to 100,000 words in a world that you stick to the same general story length and type you’ve already written so that you can pull the readers you attract to one of your titles through your entire series.

As always, YMMV, but something to think about. At the end of the day it all depends on your audience and knowing what they will/will not buy from you.

Excel Essentials Quiz Books Now Available

I wrote Excel for Beginners, Intermediate Excel, and 50 Useful Excel Functions to standalone, but it occurred to me that some people will want to test their knowledge of the material covered in each book.

So to accommodate that I have just published three new titles: The Excel for Beginners Quiz Book, The Intermediate Excel Quiz Book, and The 50 Useful Excel Functions Quiz Book.

The-Excel-for-Beginners-Quiz-Book-Generic     The-Intermediate-Excel-Quiz-Book-Generic    The-50-Useful-Excel-Functions-Quiz-Generic

 

 

 

Each quiz book includes a series of questions that cover the material from the original titles as well as providing written answers to those questions that you can review. There are also five bonus exercises in each one that let you practice applying what you’ve learned with real-world scenarios.

The books are available in ebook on Amazon, Kobo, Nook, and Apply. Print books are available on Amazon and Barnes & Noble and should be available on other retailers in the next few weeks.

Also, you can also always buy direct through Payhip using Paypal at https://payhip.com/mlhumphrey. And, in fact, if you think you’ll be buying more than one book and are comfortable buying that way, you can get some discounts that way. Just select your first book and look for the pop-up offer for other titles at 10% off.

Please to enjoy and for those in the U.S., Happy Thanksgiving.

Hitting Publish

I’m working on the final files for three new titles today and it has me thinking about what it feels like when you’re about to hit publish on a new title. By my count these will be my 136th, 137th, and 138th titles I’ve published.

Now, before you get all impressed with that number a lot of those early titles were short stories and I’ve published collections of other works as well as spun off portions of books into their own series, so on one level that number is not as impressive as it sounds.

But regardless of how long the title is or how much new material it includes the publication stage is always the same for me. There’s a lot of second-guessing. Did I spellcheck? Did I scan through for formatting issues? Am I presenting the information in a way that readers expect and that will work well for them for that format?

And that is, of course, on top of the thoughts about “is this something of value” to my readers? Are they going to get valid information and enough of it out of this non-fiction title? Are they going to enjoy this story or novel? Is what I’m putting out there worth someone paying money for it?

It can be easy to get stuck at this point. Because you don’t want to mess it up. You want to put out a good product that people will like. But I can tell you, no matter how careful you are, no matter how many people look at it, no matter how many times you look at it, things will slip through here or there. Hopefully not big things, but years later you’ll find a paragraph that should be indented here or a missing comma there. It happens.

It annoys me no end every time I later see one of those, but I figure you can’t let pursuit of perfect get in the way of completing a project. In my opinion, perfect isn’t actually possible, so I aim for pretty damned good. (My A- Student Philosophy of Life.)

It works for me. But I’m still going to do one last scan of all these files. Just in case…