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 — ADVANCES — POSITIVE ROYALTIES — NEGATIVE ROYALTIES — NOTES
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?