March 2021 – M.L. Humphrey

Focusing On the Right Goal

This is a writing-related post, so if you’re not interested in that, now’s the time to bail.

When I first started self-publishing there was a lot of talk about two things: how many copies someone had sold (a million copies!) and whether they’d hit six-figures.

Which are both great and wonderful things to talk about, but as I discuss in detail in Data Analysis for Self-Publishers, can be the wrong metrics to focus on. You can sell a million copies and not make a living from your writing. You can also have six-figures a year in revenue and not making a living from your writing.

The number of copies sold ignores what those copies were sold for, and selling a million copies at 99 cents is going to have a vastly different outcome from selling them at $4.99. And, of course, if you pay too much for advertising then all the sales in the world aren’t going to make a difference if you lose money on each one.

(Which is not to say I don’t believe in advertising. I absolutely do. I just believe in advertising for a profit. If you can’t advertise and be profitable then you need to change your ad strategy, change up the cover etc on what you’re advertising to make it more desirable, or write something new that you will be able to advertise at a profit.)

But that’s not what prompted me to write this post today. I’m currently in a Facebook group that is focused on selling books everywhere, not just Amazon. And there was a recent product release of a sales tracker that provides these pretty little circular graphs with each store in a different color. Members of this group LOVE to post those photos and talk about how “isn’t it great that Amazon is only 40% of my sales?”

Every single time I see one of those posts, I wince. Because it’s focusing on the wrong metric. One of the ones I saw this week, the person was proud to have Amazon as only 50% of their revenue but they’d also only made $70 on Amazon. You can’t live on $140 a month.

To be clear, I am a strong advocate for being wide because as I told someone a few months ago I have no desire to contribute to the collapse of the self-publishing ecosystem by placing all of the power in Amazon’s hands. As soon as they can they will fuck us all over. So if I want to be able to self-publish five years from now or ten and make any sort of money from it, I need to be part of there being viable competition for Amazon.

But the reality is that Amazon, especially in the U.S., is the single biggest sales platform for self-published authors. We don’t get into bookstores that often. So our sales are coming from Amazon, Kobo, Nook, Google, and Apple primarily. And for U.S. sales, Amazon is probably 75% or more of that market.

So even being wide, I expect Amazon to be a big chunk of my revenue. That’s why every single time I see one of those posts in that group I wince. Because the goal should not be to replace Amazon with the other stores. It should be to keep or grow your Amazon revenue while ADDING the other stores on top of that.

What I want to see is bar graphs or line graphs of increasing revenue. “I went wide and my income went from $1,000 a month to $1,500 a month.” That’s being successful wide.

(And, let me tell you, that’s probably not what most could say. Again, I am all for being wide, but the reality is that Amazon is set up to advantage exclusive authors in about a dozen ways so most authors who go wide take a financial hit. And those who start wide have no idea what they’ve sacrificed by being wide.)

Look, this is a hard business. You have to take every win you can find to keep yourself going. I celebrate the units sold. I celebrate the revenue. I celebrate the good months on each platform. (Thank you, Apple, for what I suspect was a recent feature of a free run I did on my fantasy series.)

But at the end of the day what I have to ultimately focus on is the things that pay my rent. That bottom line number of profit and loss. That’s what will let me keep doing this long-term. None of the rest of it will. It’s part of it, but if the bottom line isn’t good enough? The rest doesn’t matter.

So, please, don’t let yourself be distracted by pretty graphs.

And 2 More New Releases

Yet again I am a little behind on announcing new releases. (I impulsively decided to sell my house this month and it required this thing called cleaning that it turns out takes time to do.)

Anyway. The 2019 versions of the Access books are now out, Access 2019 Beginner and Access 2019 Intermediate.

I prioritized these over the PowerPoint updates because it turns out they made one “major” change in Access 2019 compared to Access 2013 that throws me every time I have to use the program and that’s in the import section for External Data.

Now when I want to import an Excel file I actually have to go to the New Data Source dropdown menu, then choose From File, and then I can finally choose Excel from the seconday dropdown. For a new user to Access I thought that was a big enough change that I wanted to get the updated guides out sooner rather than later.

Other than that, though, as with the other guides you’re pretty much fine using either Access for Beginners and Intermediate Access or Access 2019 Beginner and Access 2019 Intermediate. The base knowledge on all of these programs doesn’t change so drastically between versions that you need to relearn it.

(At least not since that lovely 2007 release.)

An Interesting Excel Question

Today I received an interesting Excel question from a user and thought I’d share the solution I came up with here.

Here was the question: In excel, what is the formula for taking a cost times a 30% margin then rounding it up to the next $x.x5 or $x.x9 (example: $3.45 x 30% margin = $4.93, roundup to $4.95) or (example: $3.48 x 30% margin = $4.97, roundup to $4.99)

At first, I thought, there’s no easy way to do this. And there isn’t. A quick search for functions that would do this specific task resulted in nothing.

But then I remembered that I’d covered the CHOOSE function in Excel 2019 Formulas & Functions and that it could be used for this. As it turns out, it still wasn’t easy to do, but it is doable.

(And there may very well be a much simpler solution to the problem, but this seemed to work for me, so it’s what I went with.)

Here’s what I ultimately came up with:

=ROUND(A1/0.7,2)+IF(TRUNC(A1/0.7)-A1/0.7=0,0.05,IF(ROUND(A1/0.7,1)-ROUND(A1/0.7,2)=0,0.05,CHOOSE(RIGHT(ROUND(A1/0.7,2)),0.04,0.03,0.02,0.01,0,0.03,0.02,0.01,0)))

The first part of that formula, =ROUND(A1/0.7,2) is calculating the marked-up price on a value in Cell A1. That gives the $4.93 or the $4.97 value.

The second part of that formula is where it gets interesting. Let’s drop out the IF functions for now and look at the CHOOSE function:

CHOOSE(RIGHT(ROUND(A1/0.7,2)),0.04,0.03,0.02,0.01,0,0.03,0.02,0.01,0)))

It’s best to work from the inside out.

So we start with the (ROUND(A1/0.7,2) portion which is just giving us the number we’re working with.

Next is the RIGHT function. What that’s doing is taking the right-most digit of our number. So in the examples above that would either be the 3 of $4.93 or the 7 of $4.97.

Finally, we have the CHOOSE function which basically provides a different result depending on the value you give it. So in this case a 1 returns a value of .04. A 2 returns a value of .03, etc. Once we cross the 5 mark we have to switch things up a bit so that 6, 7, 8, and 9 will result in values that end in a 9 instead of values that end in a 5.

That value gets added to our original calculated value to create a result that ends in either a 5 or a 9.

But because I used the RIGHT function we have a problem. Any value that’s a whole number, like $9.00, isn’t going to calculate properly because RIGHT will pull in a value of 9 not 0. And any value like $7.30, $8.20, etc. will also not work properly because RIGHT will pull a 3 or a 2 or whatever the next actual digit is instead of the zero.

We can solve that, though, with IF functions. Namely,

IF(TRUNC(A1/0.7)-A1/0.7=0,0.05,

and

IF(ROUND(A1/0.7,1)-ROUND(A1/0.7,2)=0,0.05,

IF(TRUNC(A1/0.7)-A1/0.7=0,0.05 basically says that if it’s a whole number then add .05 to it. And IF(ROUND(A1/0.7,1)-ROUND(A1/0.7,2)=0,0.05 says that if the rounded value with one digit is the same as the rounded value with two digits, then also add .05.

A little messy, but it solves those two issues.

And I think all of it taken together answers solves the issue.