Wednesday, December 30, 2020

Spreadsheets for the New Year

As you all probably know, I'm a spreadsheet geek.  On the personal side, I have spreadsheets for the books I've acquired and for my weight and activity (where I also keep track of writerly things like word counts and pages edited).  I do spreadsheets for the payjob.  And I have spreadsheets to keep track of things for the writing business.  Those last ones are the ones I want to talk about today.

I have a whomping huge spreadsheet to keep track of my expenses and income.  All six years worth.  That one's particularly ugly this year, so we won't really talk about it much.  When I finish updating it, it will tell me how far in the hole I am and whether I made any progress anywhere.  The closest any of my books is to being out of the hole is Accidental Death.  It's only $102.51 in the negative.

My other two main spreadsheets are Book Sales Data and Sales Totals.  

Book Sales Data has a tab for each month of the year and on each tab, it has sales information for each book - color coded by book on the rows with columns for each possible sales item.  Each potential US price gets a column, other countries get columns (except the EU countries only have one column because they all use the euro), there are columns for KU and print and returns. This sheet is for quantities only.  I used to do quantities and amounts, but I deleted the amounts section several years ago because it wasn't necessary here.  I keep all that over in the Sales Totals spreadsheet.

The Sales Totals spreadsheet is another whomping huge one.  This sucker tells me everything - quantity, amounts, page reads, etc. on a daily, monthly, and yearly basis with comparisons and charts.  I use the Book Sales Data sheet to feed this one.  (I have to, or this one would be even huger and the weight of it would probably topple my computer.)

The first tab of the ST takes the data from the BSD.  It's a lot like the BSD in that there are color-coded lines and columns for each of the possible sales items. Rather than try to explain it, here's a screen capture of what I'm talking about.

Pretty, pretty rainbows.

I couldn't capture the whole sheet, so I just gave you a month and the quantity side.  The earnings side looks the same, except the numbers over there will have dollar signs, when I have numbers to put into it.  The colors are for more than just looks, too.  They help me keep track of things with a glance.  Dying Embers will always be that shade of blue.  If I'd thought this though way back when, series would all be the same color, but I didn't.  And it would've messed up my rainbows anyway.  

If you look beneath the rainbows, you can see the tabs to where all that information works.  The Daily is quantities of everything sold.  The Daily Pgs is for Kindle Unlimited.  Overall keeps track of everything over all the years.  Monthly breaks it down.  Pgs Read does the KU by month and book.  It's all pretty self-explanatory.  

Every time I publish another book, I have to update everything - adding rows and columns, etc. - but for the most part, this works for me all year long.  Sucking in data and presenting it in a format I can easily digest.  

Every month, I have to plug in formulas to make ST pull from BSD, but that's only because I'm too lazy to do it all ahead of time.  I used to populate the thing with formulas at the start of the year.  Then it got to be such a pain, I decided to do it month to month, as things sold.  Why put a formula into a cell if there will never be any information in that cell?  So, I stopped.

Anyway, you probably won't ever want or need anything like this.  It keeps me out of trouble and even when the sales are light, it gives me a clear outlook on where I've been and where I'm going. 

If you have any questions, I'll be happy to answer them as long as they aren't too involved like I'd be building your spreadsheet for you.  I've often thought about offering my services to make spreadsheets like these for other authors, but the sheer weight of these things and the data involved makes it less than ideal.  And the time it takes to create and maintain one would make the process costly for other writers. 

And besides, few writers are as geeky as I am.  I like knowing exactly what's going on with the business at any given time.  Even when the news is bad.  

Speaking of which, I will be doing at least one wrap-up post here sometime next week to talk about 2020 sales and junk.  See ya then.  I won't be here on Friday.


  1. You are so organized! I just sort of glance at sales figures, income amounts and sort of guesstimate it in my head. There are times when I wish I WAS as organized as you and then I realize that I'd never stick with it. Heck, I started a spread sheet of characters names this past year, kept it going for about 3 months and then...nada. Ah well. My brain just doesn't go there. LOLOL

    Good on you though! Oh, and I'd never ask you to make me a spreadsheet. I "get" Excel. For some weird reason. 🤣🤣🤣

  2. I'm not nearly as organized as you are!

    I keep track of each book's sales monthly, by K-reads (fractions), ebooks sold, and paperbacks sold -- I mostly sell paperbacks. I keep track of money in (and, rarely, out) on Quicken. I've had a decent but not great year number-wise, but I'm afraid to go look at $$. Since I just got my last payment for the year, I guess I should do that.

    May we all have a more prosperous 2021!