Make it Your Business — Spreadsheets I


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 “ 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? 


21 comments to Make it Your Business — Spreadsheets I

  • Vyton

    Mindy, this is a good introduction. I don’t have anything to enter into this spreadsheet. You brought up something I hadn’t considered: quarterly tax estimates. There’s always something else, isn’t there? Thank you.

  • Mikaela

    Thank you for this, Mindy. I don’t need it for my writing ( yet), but I *do* need it for getting more structured with the used books I am selling online. Until now it has been a bit willy nilly and really small scale ( I think I earned 50 dollar last year :)). But I would like to get better at tracking my expenses and incomes.

    But the main reason is that I want to get used to tracking my expensens and income, to a create routine of entering numbers in a spreadsheet.

  • Oy, oy, oy. I should have done this years ago, because now I have a system for doing my taxes that totally sucks, but that I am too lazy and too stuck in a rut to change. But with not only taxes but also college financial aid forms looming, I could have really used this technique for last year’s income.

    Mindy, I don’t suppose you want to come over and do my taxes for me, do you . . .?

    Cracked me up with the Battleship line, btw.

  • Hepseba ALHH

    I love spreadsheets! (heads-up: in your example, A2 would be the first column in the second row, and “columns are designated by letters”) I could not function without them, especially when it comes to basic money tracking. I have one that estimates monthly expenses with # of major grocery runs, weeks of daycare tuition, etc…

    As to a question: sort and subtotal are great – do you have to do these manually each time you look at them (the only way I know how to do it) or is there some way to automatically subtotal for each change in month, for example?

  • This was very useful, Mindy. Thank you. 🙂

    Do you keep a spreadsheet or other document for scheduling your time / allotting time for projects?

  • Vyton – Yep, if it’s not one thing, it’s another. I find that my quarterlies actually drive a *lot* of the bookkeeping/career stats that I do. Yuck.

    Mikaela – I’m glad that it can help in another aspect of your book-ish life. (And yes, getting into the habit of tracking is one of the most important things we can do!)

    David – I only started in 2010 – a full decade after my first book was out. I overhauled my record-keeping system in 2010 because I became serious about paying quarterlies (before that, I overpaid on my withholding at my day job and let it all come out in the wash…) BTW, I gave up calculating my own quarterlies a couple of years ago; I hire an accountant, and she’s well worth the relatively small amount of money… (We still do our own federal and state returns, though.)

    Hebseba – thanks for the typo corrections (although I think A2 really is correct — Row A (first row), Column 2 (second column). As for your question — I use Excel’s functions to do the sorting and subtotaling. I typically remove all subtotals when I add new entries to the sheet, then re-do the functions. (I add entries chronologically; therefore, on a practical basis, I don’t sort often, and I typically only subtotal at quarterly and end-of-year intervals.) Does that make sense?

    Laura – I use my calendar to track my time (and I don’t keep any record of actual words written/edited; rather, I measure my progress by complete chapters drafted and revised.) When I’m ready to go full steam ahead on a new work, I put in calendar entries for each day, saying what I intend to complete (e.g., “Draft Chapter 4”, “Revise Chapters 1-2”, “Readthrough Chapters 1-8”). I move them, as necessary, when Life intervenes. Does that make sense? I can explain it in a more complete post down the line…

  • Basically it makes sense, but I agree, a whole post on it would be great! Thanks!

  • Simple accounting speadsheets are wonderful things!

  • I confess I’ve been a bit afraid of spreadsheets. You make it look doable. I’m like David. My ‘system’ is pretty crappy. I think I’m going to give this a try. I’ll look forward to your next posts. I need some of this kind of education.

  • Laura – Will do, at some point in the future!

    Lyn – It’s pretty amazing how powerful even the simplest ‘sheets can be!

    Di – I was terrified of them for years. Believe me, I only do spreadsheets that make my life easier — I don’t mess with ones that are too complicated, too confusing, etc. If you try it and you have questions, let me know!

  • THank you Mindy and please do tell us more about how you use these. I love spreadsheets – in part, I think because I’m not good at sorting or remembering data and a spreadsheet gives me something to check back on. (Same reason I love my online calendar.) Right now I have a very simple spreadsheet for my agent querying process. Each agency gets its own row with columns for agent name, query letter, attachments/no attachments, how many pages to send, date I sent the query, etc. Then I color code it. White means I haven’t queried yet. Yellow means a query was sent. Grey means a rejection. Green means a request for partial or full manuscript. This way I never delete an agent I’ve queried so I don’t accidentally double query. I have a similar sheet for short stories to track what’s out where.

  • Mindy – I’ll admit I had to delete several draft posts as I wanted to answer Hepseba’s question about sorting and sub-totalling, and also offer a few suggestions on how to improve your speadsheet. But this is your post. 🙂

  • Tom G

    I can’t wait to see your expense spreadsheet. I track my indie sales meticulously, with a spreadsheet that projects monthly income and everything. But I have never tracked or otherwise my writing income. I just trust that the ebookstores will send the needed tax info at the end of the year. Last year it was fine, since I’d only been earning money a few months, but his year sales across most of my pen names have increased. I think I have enough saved to pay taxes, but I have to get serious about this if before I get myself into trouble.

    Right now, I’m trying to figure out how to get “receipts” for editor and cover artists (my only real expenses). I paid them all through paypal, but paypal doesn’t keep that information a full year. Doh!

    I definitely need income and expense spreadsheets. Like last year. :/

  • Tom G

    And I need a physical filing system for printed out receipts!

  • This is packed with a lot of useful information. The use of spreadsheet in the title actually got me more interested to read it:
    Thanks for all the tips. A whole post would be great.

  • quillet

    I know next to nothing about spreadsheets, so this is very, very helpful. Thank you. And you’re making it sound non-scary — that’s quite a feat!

  • Sarah – Your agent and story tracking sheets sound useful — alas, it’s a long time since I’ve needed to do either. (They sound like good examples of that category of spreadsheets that I describe early in my post — the ones that don’t actually need the spreadsheet formulas, but take advantage of the cell format.)

    Lyn – Feel free to elaborate! We’re here to learn from each other!

    Tom – If you’re trying to figure out how to generate receipts — I just do mine in Word, typing up “For services rendered” type language. As for tracking *what* you paid people, I can’t help you there, but I’ll show you how I track those types of expenses in my next post. (And I’ll detail how I maintain my receipts physically, too…)

    JQtrotter – Thanks for your interest! More spreadsheets to follow!

    Quillet – I think that’s my librarian training kicking in, trying to make the unfamiliar not-scary 🙂

  • I use a bunch of different spreadsheets – one to track my book sales and income, one to track my freelance billings and help remind me to bill folks, and another to track expenses. I’m probably going to splurge on NeatReceipts at some point this year to scan all my receipts in and automatically plug the data into the computer, but not right now.

  • I feel the same way about spreadsheets, though it started off rough. Over the last year, I have moved from expenses & income to keeping track of submissions and have started to use one for outlining my stories.

    You are spot on about starting early with these habits because it will help establish a behavior and you will find they are easier to use when you have more things to enter.

  • John – I recently heard from a writer friend who is using NeatReceipts and loves it. I haven’t checked it out yet…

    JMVanHorn – Starting is *always* the most challenging part for me — of writing, of editing, of tracking business data… Ah, inertia… (But the rewards can be so great, that I usually manage to get off my butt!)