Spreadsheets — The Bad and Ugly (and the Good)


So, this is the last post I have planned on spreadsheets.  First, I wrote about my simple earnings spreadsheet.  Then, I wrote about my tax deductions spreadsheet.  My goal was to make spreadsheets seem simple and easy and valuable for authors at all stages of our careers.

Today’s post is a little different.  Today’s post is where I tell you about the Spreadsheet That Failed.

The STF is the first spreadsheet I built for my writing career.  I intended to capture all pertinent earnings information over my entire writing career in one document.  “All pertinent earnings information” meant all annual income, and all royalty statements per book.

The first page of the STF is a number of columns that represent years:

2000 — 2001 — 2002 — etc.

The entries in those columns represent, in dollars, income received.  There’s no explanatory data about whether the income was from an advance or a royalty; rather the raw numbers just get tallied by year.  On the first page, I calculated a Grand Total, the sum of income from every year.

Every other page of the STF is devoted to a specific book.  For my first novel, THE GLASSWRIGHTS’ APPRENTICE, I created a complicated header that listed the book title, the ISBN, the publisher, the date of the contract, the editor’s name, my submission deadlines, and the publication date.  You’ll note that every item in this header is text — there are no numbers that need to be tallied, calculated, or manipulated.  In other words, I violated one of my key rules about spreadsheets.

That’s okay, though.  I filled the rest of the ‘sheet with calculations.  I created five columns:


DATE is self-explanatory.

ADVANCES let me record the advance payments for each book.  My early contracts had two advance payments — one on signing, and one on delivery.  Some of my later contracts introduced a third advance payment on acceptance of an outline.

POSITIVE ROYALTIES  is self-explanatory.

NEGATIVE ROYALTIES gave me a place to record money that the publisher deducted from the total profitability of the book.  In each case the negative royalties were for returns of merchandise, after the publisher disbursed all of its reserve against returns.  (You know what that means, right?  If you don’t, let me know in comments, and I’ll elaborate.)  I did not actually have to pay this money back to the publisher; nevertheless, these “negative royalties” were deducted from the overall profitability of my book.

NOTES gave me a place to record the royalty period I was detailing.

For each book, I used the spreadsheet to calculate the Grand Total of earnings:  Advances plus Positive Royalties minus Negative Royalties.

In theory, the Grand Total from the first page (all the years I had written) would equal the Grand Total of all the other pages combined (all the books that I had written). 

In practice, this spreadsheet was a mess.  I had to list the Negative Royalties is I wanted my book summary to match the publisher’s summary for each book.  But because I never paid back the Negative Royalties, my book Grand Total never matched my years Grand Total.  I created an Adjusted Book Grand Total (Advances plus Positive Royalties), and I used that to match up with my years Grand Total. 

But a lot of the time, my totals were off.  Usually, the discrepancy was a few pennies — transpositions of numbers, or incorrect spreadsheet usage, when I entered numbers in a row that was inadvertently mis-tallied.  I spent a *lot* of time reconciling these data.

But about three years ago, I finally decided to quit.

I already had a system for tracking my annual income.  My publishers *gave* me the other data on their royalty reports — sometimes those reports were hard to read, but they were always complete.  My spreadsheet had become an exercise solely for the sake of an exercise.

And I haven’t updated it in three years.

I do keep one last spreadsheet — a tally of sales for each of my self-published books, pulling together the sales numbers reported by the various vendors.  I won’t detail this one for you, because of our MW focus on traditional publishing.  Nevertheless, I mention this “Good” spreadsheet both to get rid of the bad taste of the Bad and the Ugly STF and because I use the Good spreadsheet to determine which new projects most deserve my time and energy.

So.  That’s what I’ve got on spreadsheets.  Some users have already mentioned other tools I could be using, such as Pivot Tables, and I intend to look into that.  Anyone have questions?  Comments?  Anything else to say on topic?


13 comments to Spreadsheets — The Bad and Ugly (and the Good)

  • Mindy, I had lunch this week with someone who has terrific organizational skills. Not as great as yours, but terrific ones nonetheless. And I am just in awe of that gift/skill. I think when the genetics fairy handed out gifts, he left a blank hole in my brain right where numbers go. Maybe the fairies are cruel, or something. But I just don’t have that brain stuff because what you have described (including the failed one) seems like magic. Pure magic. Great series of posts. Maybe when I grow up someday.

  • Thanks for the spreadsheet posts! I’m going to figure out how to merge your use of spreadsheets with my own current spreadsheets and see if I can come up with a better (and by better I mean simpler) system form to keep track of my incoming and outgoing money.

  • So, I’m wondering to what extent (if any) you use spreadsheets in a creative capacity, for keeping track of plot lines, characters, worldbuilding, etc. You mention them very briefly in the first post of this series, but I would love to hear more about how you use them in your writing. Not that this other stuff isn’t interesting (and at some point I’m going to hit you up for info on the “good” spreadsheets you mention at the end of this post, since I plan to release some of my work later this year), but I think that we would all benefit from your spreadsheet expertise on the creative side, too. Maybe one more post?

  • Faith — I think the fairies take all their gifts and distribute them so that we all end up with the same total. (Me, I missed out on the wildly inventive ideas distribution. I have to *fight* for my creativity, which you seem to have in spades!) That’s what I love about being here — learning from everyone about our fairy deficits!

    SiSi — Thanks for the kind words! I see all of these posts as “blueprint” or “guidepost” writings, to get people thinking about heading down the road and giving them a way to start exploring what works best for them. Good luck simplifying!

    David — Let me know when you want to chat about the self-pub spreadsheet! As for creative tracking, I don’t use spreadsheets at all for that. Rather, I rely on Scrivener, and its various features to track the data that others spreadsheet. The information can be translated, though, between programs. So now I know what I’ll write about next time 🙂

  • David — Er, I meant that the “ideas” can be translated — or maybe a better analogy is “transposed” — between programs, not a literal transfer of the same information!

  • Vyton

    Mindy, I’ve enjoyed this series. I’m not at the point where I need business-side spreadsheets. On my first work, I used a spreadsheet to track characters by chapter and date, but now I’m working in Scrivener. I like the features, including the cork board where you can play mix and match. Or more accurately, 52-card pickup.

  • Mindy, this series has convinced me of two things. One, that tracking these numbers is really important. And two, that when I sell a book I’m going to need a talented CPA. I am good at basic spreadsheet making and I’ve used them for years on various jobs. But I’m terrible about personal bookkeeping and filing. Before electronic banking came along I was always wrestling with my badly kept check register. Since one of the books I want to sell is co-written that’s going to add another layer of complexity to matters like royalty statements and tax payments. I’m definitely going to need an expert for the financial side of things.

  • Gypsyharper

    I’m reasonably competent at creating simple spreadsheets – I use them at home for my check register and budget, as well as my word count goals, and also at work for a variety of things – but taxes boggle my mind. (I just read your previous post, as well – I’m a little behind on my blog reading). I don’t have any writing related income yet, but I will definitely be keeping these posts bookmarked for future reference.

    I’m also interested in the creative tracking side – whether with spreadsheets or Scrivener. I’ve been using Scrivener for a while now, but I’m always looking for tips on how to make the best use of its many features.

  • Hi, it’s me, the local spreadsheet junkie. I’ve loved this series, Mindy, although each of your spreadsheets, including the failed one have me itching to get my fingers on them to mush them all into one fully functional on-stop-does-everything ‘sheet. But that’s just me. 😀

  • Vyton – You made me laugh with the “52 Pickup” comment — I feel like I’m playing that game a lot of the time, when I use flashcards (which I don’t use all that often…)

    Sarah – It’s infinitely easier to *start* with the professionals, than to clean up years of mistakes! You’re write that co-writing causes problems, especially if you intend to self-publish. (Most vendors will only accept one author’s name, which can have major adverse tax consequences for the named author.) If you’re interested in the self-pub angle on co-authoring, track down the article in this month’s Romance Writers Report that discusses the topic in detail!

    Gypsyharper – I get *tremendously* frustrated with taxes. I repeatedly say that I am an intelligent person, and I can’t believe that I have so much trouble with X and Y and Z…

    Lyn – It’s always interesting to discuss these things with real experts, as you are. Sometimes, the truly professional solutions overwhelm us amateurs. (And sometimes, we can master them with a bit of coaching and feel quite proud of ourselves 🙂 )

  • Hi Mindy,

    I just wanted to mention another spreadsheet, increasingly referred to as the “Magic Spreadsheet,” by Tony Pisculli, in that it helps writers get into the habit of writing daily. It’s also part motivation based on a group, public effort. I’ve seen writers start using it, and then their commitment to daily writing explodes, like, well, magic. Hence, the name, The Magic Spreadsheet.

    Here’s a brief explanation and link to The Magic Spreadsheet, by Mur Lafferty: http://murverse.com/at-long-last-the-magic-spreadsheet/.


  • Mindy, I’ve really enjoyed this series. I’m going to bookmark it for when I’m actually making money from writing.

    I’d love to hear more about how you use Scrivener. I’ve considered buying it, but haven’t yet.