So let’s go.
Backwards or Forwards?
There are two ways you can build the spreadsheet:
You can build a simple linear sheet, where the second book is scheduled for the day after the first book is finished, takes what time it needs to be plotted and written, and the release date is set according to when the second book is finished + production time.
Constructing the sheet this way ensures you have adequate time to write and produce all your books. There won’t be any time crunches or overlaps.
The down side is that you won’t have a regular release schedule (which readers and Amazon likes).
If you are just learning to track your productivity and schedule your releases, this is the way to build your sheet for now. I used this version of the sheet to begin with.
For this style sheet, the columns would be:
| Title | Length |
These are possibly the most straightforward columns on the sheet. Title equals the title of your book, or the short code for the type of book you want to write . So for example, SF1 or HISROM1, or the actual title of the book, if you know it. You can also put series books in there, by their series order. So for my Once and Future Heart series, I would put: ONCE4 and ONCE5
The length is what you estimate the book will be. This is based upon genre expectations and also upon what you are in the habit of writing. Round your estimate up, if you are guessing. This helps build padding into the schedule.
If you are a pantser, you can skip this step.
For dedicated outliners, time to develop the story to the point where you can start writing gets built into the production schedule in these two columns.
The formula for the Start column is: last day of writing previous book + 1
This post is not the place to explain how formulas are built in Excel and other spreadsheets. If you are uncertain of the process, there are a ton of online tutorials which will walk you through it.
Very important: You must format the cells for dates. That allows calculations to be made on the actual date. It is key to how the spreadsheet works.
For the very first book on the spreadsheet, you won’t have a previous book to calculate from. In this case put today’s date, as you will be starting today, right? Or tomorrow’s, or Monday’s…
The second book on the list will have the formula in the cell. It will come up with an error right now, because you have not yet calculated when the previous book finishes.
The formula for the Finish column is: Start Date + (book word length / plotting-words-day)
Again, these cells should be formatted for dates.
- The Start date is the previous cell.
- Book word length is the figure you put into the second cell.
- Plotting words per day is the rough estimate you made last week, about how many “words per day” you could plot
The result produced by the formula will be a date that is somewhere ahead of the Start date. This is the date by which you should have finished plotting the book under normal circumstances.
The Writing Start date formula is: Plotting finish date + 1
The Writing Finish date formula is: Writing Start date + (length of book/writing-words-per-day)
- The writing start date is the figure in the column to the left.
- The length of the book is the figure you wrote in Column B.
- Writing-words-per-day is the number you estimated last week for how many words you write per day, averaged across the week.
Remember that the estimate of the number of words you write per day should be rounded down to pad the production shed door, which will actually make it more realistic and likely to be met.
The Weeks column is a number you plug in manually. It is how many weeks you have decided you need to take a book from finished first draft to published.
We discussed last week how you should arrive at this number. It should remain the same for all books. However, I put this number in the column instead of adding it into the formulas that follow, because I have found that sometimes this number changes. Instead of having to recalculate formulas, you can manually adjust the production times.
The Production Start date formula is: writing finish date + 1
The writing finish date is the date in the column two to the left of this one.
The Production Finish date formula is: production start date + (weeks of production x 7)
Check your formulas, then copy down.
I actually copy down to check my formulas. Then I tweak and recopy down.
Make sure your formulas are correct and that everything looks reasonable before building the rest of the sheet.
You don’t copy the contents of the cells, but the formulas. There is a difference. When you drag to copy formulas into cells beneath, the formulas are relative, so the dates change according to which cell they are in.
This is what makes the spreadsheet work.
Again, if you’re not familiar with spreadsheet formulas and copying and pasting formulas, and formatting cells, then you will need to do some training with online tutorials to get the hang of it. It’s worth the effort.
Once you have your formulas sorted out, then copy and paste everything down, and add in your books for the year, or two years, or more if you want.
Be aware that the further into the future your production schedule reaches, the less accurate it will be.
Consider the results
This is the magic of the production schedule. You now have a semi or fully realistic projection of the number of books you can write for the year ahead. You have release dates, which allow you to do some serious marketing. And you have deadlines you must hit in order to make all this happen.
Once you have finalized your spreadsheet, you can copy your plotting and writing and production deadlines into whatever time management tool you use.
You can build a sheet that calculates backwards from the release date:
This version of the sheet works backwards from your release dates. This is the version I use now.
Building a backwards sheet requires that you know how long, on average, it takes for you to produce a book and release it (which you have very roughly calculated, last week).
You spread your release dates as you would prefer them, then calculate your deadlines for the book, moving backwards.
For example, I released a book every four weeks. So the very first dates I plugged into the production schedule are the release dates, in the far right column.
Somewhere way back in time, I put in the first release date manually. The second release date, right beneath, was a formula. In my case, (previous books release date + 28).
Then I simply copied and dragged the formula to the bottom of the spreadsheet. Now my release dates are locked in.
If you think you have the capacity and the discipline to hit a regular release date in this way, you can also build your release date by using a formula.
However, you can fully control what each release that will be. Simply put those release dates in the column for each book that you want to publish.
Now you start moving backwards (that is, to the left), filling in the relative dates. In other words, you put in formulas that calculate from the release date.
The Production Start date formula is: Release date – (weeks x 7)
The Weeks date is static, as I discussed above. This is the figure that you estimated last week.
The Writing Finish date formula is: production start date – 1
The Writing Start date formula is: writing finish date – (book length / writing words per day)
As you have probably discerned, these are the same formulas, just in reverse.
The Plotting Finish date formula is: writing start date -1
The Plotting Start date formula is: plotting finish date – (book length / plotting words per day)
Complete your spreadsheet and analyse
Once you have the reverse formulas plugged in, copy and dragged them down, so you populate your spreadsheet.
Now you can check the spreadsheet to see how realistic it is.
Here is a high level glimpse of what mine looks like:
I have roughly grayed out the titles, because this is an older version of the production sheet, and because I don’t want my savvy readers to trip over this production schedule and raise their hopes about books that may not eventuate.
As you can see, I don’t work too far into the future. This particular sheet only runs into mid-2019. It’s sometimes fun to project years ahead, but usually everything changes by the time you get there, so I don’t bother unless I’m running a fantasy scenario to motivate me to get to more writing. 🙂
Unlike the linear, straightforward spreadsheet designs that I outlined above, this one has the possibility of presenting start dates for your book that begin before the end dates for the previous books. (There are a few on the example, above.)
If this happens, and it is more than one or two days overlap, then you need to adjust your release dates so the start dates are more reasonable.
What if your release dates overlap the start date of the next book by only a day or two?
This is where you will need to make a decision about how much slack is in your production sheet, and how much you can realistically get done.
For example, I quite often have start dates that are one or two days before the end dates of the previous book. I know that I can make up that time, more often than not, and prefer to have that pressure there. I don’t like to adjust my release dates!
I am quite militant about making sure I hit that 30 day algorithm on Amazon. It may not be as important to you, so you can adjust your release date so you have a production schedule you can live with.
This is where knowing yourself and knowing your work habits helps enormously.
If you create a spreadsheet that is completely unrealistic, you can’t stick with it. If you have published the release dates, you will also be letting down your readers.
So adjust your release dates as you need to. The more you work with a production schedule like this, the more accurate and realistic it will become.
Eventually you will reach the point where you feel comfortable advertising your release dates for intended books, so readers are aware of when the next book in their favourite series will be released.
I am still wary of doing this, however. Things change, so the series you thought would have eight books in it may end up only having four. Or you may find a series doesn’t sell at all, and have to start with a fresh one. You don’t know what’s going to happen in the future, even with the best built production schedule in the world. I recently had to rework mine to include more books in a series I thought I would never write again because of poor sales. Now, thanks to reader pressure, I’m back to writing more books in that series. You just never know.
As I mentioned in the very first part of this series, knowing your production dates allows you to set up long term marketing for those books, so you can take advantage of advertising opportunities that have very long lead times. It lets you set up preorders well ahead of time, which are a marketing ploy of their own.
A well-built production schedule is incredibly useful. It will inform most of your business decisions.
If you have been an indie writer for long, then you will have learned to enjoy the control that comes with being independent. A production schedule will increase your control to a degree that takes a lot of the stress out of your career.