Wednesday, October 7, 2015

Spreadsheets - Keeping Track of Sales

Okay, so there was a request to discuss my use of spreadsheets in this self-publishing endeavor.  For some of you, this might be a total snooze-fest.  Sorry about that.  Check back on Friday and I'll try to be more interesting.  ;o)

First, so we're all on the same page, this is Excel (2010, I think. Might be 2007.)  The files themselves are called workbooks and each individual tab within the workbook is a spreadsheet.

I have been using a separate workbook for each book.  They kinda look like this:

But opening a separate workbook for each book is kind of a pain in the ass, and it clutters the bottom of my screen. (Way worse than what you can see up there.)  Plus, toggling between all those files.  And then think about when I have 8 books out there.  Or twelve.  Ugh.

So, I made a new workbook to encompass all the books in one place.  It's my 2015 Book Sales workbook.  (The one called Sales Totals is what I refer to as the Master Plan. It's very pretty and has multiple charts & graphs.)

The 2015 Book Sales workbook keeps everything in one place. Now, instead of opening 4 separate files (one for each book and the Sales Totals), I will be able to open two files:

Isn't it pretty?
And the 2015 Book Sales will feed the Sales Totals, so I only have one place to plug in data to create pretty graphs like this:
My 'Pages Read' graph
Why bother? (Or as Hubs asked, 'why not just keep track of total book sales instead of individual books?')

Well, I'm hoping to track what works and what doesn't.  Find sales patterns and then find a way to recreate the spikes.  Look for trends and maximize them.  And I do the individual book thing because each book is different - different pricing at different times, different page counts, different genres, different marketing efforts.  :shrug:  Or, it could be that I like playing with data. 

Sales Totals, Daily Sales, color-coded for mktg efforts
As I've said before, some stuff works, other stuff doesn't - but how would I have an inkling of what worked if I didn't track it?  So I track it.  Does it help?  A little.  Down the road - perhaps more.  At least I'll have some idea of what to expect with each new release and with every subsequent year. 

I hope this helps.  I suspect at this point, it probably didn't answer the questions people might've had on exactly 'how' to do it.  That would take hours and I suspect I might not be the awesome software instructor I used to be.  Excel nowadays, though, does a lot of the work for you.  Autosum was a godsend, let me tell ya.  And being able to type the equal sign then click a cell in a whole other spreadsheet?  I could kiss someone for that.  Try typing out '[2015 Book Sales.xlsx]Monthly Totals'!$B$13 in one cell and then in the next cell that but now the B should be a C, or the 13 has to be a 14.  Ugh.  What a pain.

Now, it's not exactly what I would call easy-peasy, but it is a lot easier than it used to be.  Don't be afraid of it.  Play around.  Try some things.  But do what I didn't do and plan ahead.  Don't just think about your needs now, but what your needs might be a couple books or a year down the road.  Or you'll wind up like me - building a new workbook and then going back and populating it from the old workbooks.  It's a lot of work I could've saved myself if I'd planned ahead.  In the end, though, the work I put in now will save me time in the long run.

Any questions?  Do you keep spreadsheets?  What do use yours for?  (I also have one for expenses. I used to have a spreadsheet that listed all the books I owned - titles, authors, pub dates, etc. I still have one I use for quotes.)


  1. Very interesting. I'm mainly self-taught on excel and use it at work for various things. I have a couple of personal spreadsheets: monthly accounts & household bills; paperback listing (sadly out of date); books read 20**; etc.

    The one I appreciate the most in my accounts spreadsheet. It's just so easy to see all the monthly direct debits and once you've taken them out of the way, the rest of the money is mine to last the month. I (we) got into a mess back in the day by leaving the money side to my ex and burying my head in the sand. I clawed my way out of that with hard work, frugality & the use of my spreadsheet and have tried to teach fiscal responsibility to my kids so it never happens to them. The kids are doing OK with their money now and if I could only find a way to pay off my mortgage I'd be on cloud 9!

    1. I'm self-taught, too, Fran. We rock!

      Go you for getting yourself financially stable! Yeah, exes can really do damage to the old bank account. Ugh. I was reminiscing yesterday about a couple who left me in debt after racking up charges on my credit cards. I was happy to pay it if it meant they were gone. I'll keep my fingers crossed for your eventual mortgage payoff! =o)

  2. I love spreadsheets! I have one that keeps track of my sales & expenses (I use it for tax time). I have one that keeps track of my writing (words and scenes and characters and whatever else is writing related). For the sales spreadsheet, I have a column stating what book sold, how many copies, and the royalties. I can subtotal and it will tell me how each book did. If I want to track something, I use a spreadsheet. I don't bother with graphs though. They're pretty, but don't really tell me anything (plus, I suck at graphing!).

    I used spreadsheets a lot when I was a cost accountant. I don't use them so much now, but I do love them still. And I have Excel 2003. I like the simplicity of that one and hope I never have to upgrade (although I know that's unlikely).

    1. Yay for spreadsheets! I used to have a spreadsheet for wordcounts, but I got out of the habit. Now I only keep one of those if I'm doing NaNo, so I know what I've done and how much more I have to do to keep my avg at 1667 (or how far behind I am LOL.)

      Ugh, I hate upgrading, but yeah, sooner or later, you can't put it off any longer. =o\

  3. You are way more organized than I am. Good for you, bad for me. LOL And I actually understand Excel and have taught it. Ah well.

    1. LOL, I don't know about that. Sometimes I think I'm just using it as busy work or procrastination. I never taught Excel. The last spreadsheet I taught was Lotus 123 v3.1 - which tells you how long ago that was. ;o)

  4. Thanks for posting this. All I track is units sold. I need to get better organized.

    1. I think it helps me, Deb, that I have someone I report to - Hubs - so when he asks how sales are, or where I'm at on my goals, I can tell him easily (without trusting to my memory.) ;o)