Yeah, I know. I’ve lost half of you by putting the word “spreadsheets” in the title of this post. The other half are ready to school me in the subject matter — because I really am not an expert in this stuff — at all! But I can tell you what works for me. And I can encourage you to at least try my system, making adjustments to suit your own needs.
First off — a bit of history. I loved spreadsheets before I knew what they were. You see, many, many years ago, when I was a child growing up in Dallas, Texas, my then-best-friend and I used to play “school”, teaching all of our stuffed animals many exciting classes. When we played, we graded our students, using amazing ledger notebooks that Pam’s father, a C.P.A., brought home from work. Each page was light green and marked with columns — some pages only had two columns; some had as many as a dozen. We *loved* those gradebooks.
Imagine my surprise when, twenty years later, I learned that those ledger notebooks had been turned into a computer program, a spreadsheet. The first spreadsheets I worked with were in Lotus 1-2-3; now, I regularly use Excel and (free) Google documents.
If you’ve never used a spreadsheet, don’t run screaming into the night. A spreadsheet is simply a specialized file that has rows (usually designated by numbers) and columns (usually designated by columns), resulting in a number of cells (usually designated by their location in the grid — A2, for example would be the cell in the second column of the first row. It might also be the location of your Battleship, but that’s something totally different.) Spreadsheets allow users to enter text or numbers in each cell. The program can perform a wide range of mathematical operations on cells that contain numbers.
A lot of people never enter numbers anywhere on their spreadsheets; rather, they use their spreadsheets to organize a lot of textual information. For example, they designate Column A to record the volumes of The Endless Sword Saga, Column B to record the chapters in each volume, Column C to record the scenes in each chapter, Column D to record the characters involved in each scene, Column E to record the actions of each character…. You get the idea. The end result resembles an outline; the spreadsheet is used as a formatting tool, with no hint of scary mathematical functions.
Those aren’t the type of spreadsheets I’m talking about. I’m talking about spreadsheets that manipulate numbers, allowing me to sort add, calculate, and display data in ways that help me track my career success.
Which (finally!) brings me to the simplest of my spreadsheets — Freelance Income which is, er, the spreadsheet I use to track my freelance income. It consists of multiple pages, one for each year since I started using this system. (I just created a new page, labeled 2013, to track my income for this year.) Each page has four largely self-explanatory columns:
Date – Amount - Source - Notes
Date is the date on which I received the income.
Amount is the amount, in US dollars, of the income.
Source is who paid me the income. My sources include my agent (for all traditional publishing contract moneys, including advances royalties, and subsidiary rights), Amazon U.S. (for self-published novels sold in the U.S.), Amazon International (for self-published novels sold outside the U.S.), Barnes & Noble (more self-publishing), and each of my freelance clients.
Notes is additional information, typically the term covered by the payment (e.g. “U.S. sales May 1 – May 30″ or “Audible.com advance for five Glasswrights novels”).
For the Amount column, I total the numbers using the Sum function, so that each time I add a row to the spreadsheet, my total income is updated at the bottom of the column.
That’s it. Boring. Simple. The most basic of spreadsheet creation skills.
And yet… This simple spreadsheet gives me a tremendous ability to analyze my writing business. Of course, it lets me see at a glance if I’m on track to meet my overall writing goal for this year’s Strategic Plan. I can also see if my income is consistent from year to year (page to page of my spreadsheet) and if it is (I hope!) increasing.
But the spreadsheet tells me a lot more than that. Using the “Sort” (Data -> Sort) and “Subtotal” (Data -> Subtotal) features, I can learn many things about my writing career. By sorting on date, then subtotaling at each change in source, I can learn:
- Which sources generate the most income?
- Did specific publishing events (e.g., a traditionally published novel) have an impact on other publishing venues (e.g., self-published novels)?
- Should I allocate my time and efforts in different ways to maximize my income?
- Are all of my freelance clients worth the time and effort it takes to meet their needs, when I study the cold, hard cash?
I can add a column to indicate the month in which income was received, and then I can glean more information. Which months are most profitable for me (sort by month, subtotal at each change in month)? If certain months have consistent payouts (e.g., a freelance project with regular deliverables), how can I schedule alternative work to fill lean months?
This spreadsheet is also invaluable when it comes to paying taxes. As a freelancer, I must pay quarterly estimated taxes. This spreadsheet allows my tax preparer to see, at a glance (and/or a quick subtotal), what my income is in each quarter. She can offset that with my deductions (listed in another spreadsheet, which I’ll describe in a couple of weeks.) Also, at year-end Amazon will send me tax documentation for my domestic sales, but they don’t do the same for international sales. I can use my spreadsheet (sorting and subtotaling by source) to isolate those international sales.
Writers’ careers are subject to thousands of variables. We can’t control a lot of what happens to us. But by tracking and analyzing our income, we can make better plans. We can diversify intelligently, developing protection against market vagaries. We can be in charge.
(As a side note: For purposes of this spreadsheet, I record my gross income rather than net. That way, I treat my agent’s commission as the business expense it is, just as my hired editors and cover designers are expenses for my self-published work.)
(As another side note: You might think that your career isn’t advanced enough to bother with this sort of tracking device. I encourage you, though, to set up your tools *now*. It’s far, far easier to maintain functioning systems than to build them from scratch when your career is taking off in ways that you never anticipated. And in the Brave New World of modern publishing, with its mingling of traditional and innovative publishing venues, you’re likely to need this sort of tracking far sooner than you might ever have expected.)
Okay – a lot of this post was Spreadsheets 101. I won’t go into as many details when I tell you about the other two ‘sheets I use regularly, and the one that I abandoned. What questions do you have for me? Was this a useful exercise?