Tag: Excel spreadsheets

The spreadsheets that help me tame the minutia of indie publishing

Lynn

I’ve been reading a lot lately about spreadsheets and inventories on another site.

Reading about the time involved in such a massive project has made me very happy that I’ve been tracking my stuff from the very beginning.

My main “publish-list” spreadsheet is up to ten tabs of data these days, and even includes all the sales links for my books on all the retailers I upload to. It’s been a handy thing to have.

I include so much in this spreadsheet that it’s difficult to think of something I can’t find there.

Screenshot

Screenshot

Screenshot

Within those tabs, I have a sheet called “File Updates” that tells me how long it’s been since I updated a file on any particular distributor site. It was a wake up call when I added that one because I ended up with a lot of titles in the “2000+ days” category, meaning all the back matter was way out of date.

I recently decided to tackle those old files and the way I’m doing it is to have a goal of updating at least one old file each week on an ongoing basis. That’s 52 files updated in a year, so that means I will have caught up within a year or so without adding in a lot of extra work or losing a lot of writing time.

(I have 34 titles at this point. Someday it will become more than a year’s worth of weeks of updates but I’ll worry about that when it happens.)

I also have a pretty streamlined EPUB generation system these days so this first year of updates will go a long way to making future updates take minutes instead of the hour or so it will take for some of these books now and will mean I should be able to easily update multiple books at a time instead of keeping it to one a week after this first round.

I update my publish-list spreadsheet every time I publish something or make a change that needs to be recorded. And keeping this thing updated is something I force myself to do every time I do anything that affects the data I keep up with. I don’t let myself off the hook on this, ever. It’s just too probable that something will slip through the cracks if I do.

I wrote -96 words yesterday

Lynn

How is it even possible to write negative words? In case you’re new here, let me explain my tracking sheet.

I put in my current doc’s word count, and it tells me how many words I’ve written today. As you can see, today’s total is zero at the moment. (The 3,333 below today’s count is the goal and it is a formula that will tell me how many words I have to go to get to that goal.)

Starting at row 10 is the list of all my works in progress and below that all my completed works, with word counts noted. That’s where I update my word counts to get an updated cumulative word count. The previous total number is manually adjusted each day so that the spreadsheet will calculate an accurate number of words for today. This lets me work on as many stories as I want in one day and still have a central place to track that word count.

I’m sure some people would like to have individual spreadsheets for each book or story, but I really don’t want or need that much granular detail. I tried adding another step into my tracking process for a while, but keeping up with one more sheet was just too much of a time waster for me.

Anyway, my point is that I have negative words because I obviously deleted more than I added, so at the end of the day my word count in the doc for my current book was lower than it was at the beginning of the day. For my sheet to be accurate, I have to record my doc’s actual word count. I like it that way even if it does leave me in the hole some days.

Sadly, -96 words is nowhere near the 1,500 word goal I set myself last night. I fell down hard on that. My only excuse is, well, an excuse. I’ll take a pass on making it.

Revisiting the cumulative title sales spreadsheet

Lynn

I had a thought that maybe there was a way to make updating my cumulative title sales spreadsheet easy enough that it would be worth keeping it up to date so I revisited the spreadsheet today for about an hour or so.

As you can see in the image above, I have several sheets in the workbook. The thing is, I know just enough Excel to know how much I don’t know.

But I had a thought and I followed up on it and that thought led to another and suddenly I’d found a way to make the currency conversions for the KDP sheet easily and quickly—and in a way that would work for future additions to the KDP sheet without requiring any additional effort on my part.

You see, if you’ve seen the KDP reports from Amazon, you’ve probably noticed they don’t include the currency conversion for the payments the way the reports from everywhere else does. It’s an inconvenience, but it is what it is.

My challenge was to find a way to prorate each month’s payment to each month’s sales. The way I chose to do it a few days ago was inelegant and not wholly accurate for each book. That bugged me, but it was a time versus accuracy dilemma, and my need to save time and effort won out.

But now I’ve figured out how to do it with zero additional upkeep.

#1

I created a column for a conversion factor in my book sales report where I track royalties earned and royalties paid by month for each vendor.
Sample row data (extraneous columns deleted):

Oct 2016 Amazon.de EUR 36.87 38.43 1.0423108

The conversion factor is simply the payment amount, which I have a column for ÷ earnings in the original currency, which I also already have a column for.

#2

I multiplied the foreign currency royalties in the KDP sheet by the conversion factor and got the USD royalties for each item (which I’d consolidated from the monthly KDP reports). I did this for all the rows in the KDP sheet.

If I’d had to do it manually, it would have taken forever.

The trick with the formula was to use the SUMIFS function because my royalty payments spreadsheet has only one row for every date and marketplace combination. That means the sum is always going to return only the conversion factor that matches the date and marketplace that the formula needs to calculate the USD royalty. It was just good luck that since KDP reports don’t show transaction dates, I had set up all my rows in the KDP sheet to contain only the period end date, which is also how I’d set up my royalty payments sheet (Oct 2016 is actually 10/31/2016 but set to the MMMYYYY format).

#3

I created a table so I could use the VLOOKUP function to solve the problem of having different names for the marketplaces in the two sheets (Amazon Kindle US Store vs. Amazon.com) but that was actually the easy part. :)

Anyway, what this means is that my brain spent a lot of time working on this while I wasn’t actively working on it—even after I’d decided it was too much trouble to keep up. But that happens sometimes.

I’m happy to say the sheet works well with the new formulas, even if my formulas are a bit of a hack. Updating the cumulative title sales will be a simple process that shouldn’t take more than half an hour every few months, and it’ll have the actual payment amounts from KDP applied to the correct months’ foreign sales.

I’m glad I was able to come up with a solution, and I swear, it feels like it’s taken me longer to write this post than it took me to fix the spreadsheet.

I had considered the idea of one massive spreadsheet that I could put into a PivotTable, but I didn’t consider that a good long term solution. The data between reports just isn’t standardized enough and I sure didn’t want to spend a bunch of time standardizing it each time I decided to update the cumulative title sales report. And I’m pretty happy with the solution I’ve come up with, because it means I can just copy and paste the new data into the appropriate sheets and I’ll be good to go.

Smashwords might be tricky, but my current plan is to just delete and re-add all the data from the updated reports, instead of append it. This is necessary because Smashwords throws in new transactions for some very old periods sometimes and trying to pick those transactions out of the whole would be a waste of time.

I don’t figure I’ll update this spreadsheet more than once a quarter anyway, but I’m tickled that it’ll be maintainable without a lot of effort. :D

Now, off to do some other stuff before I call it a night. It’s 12:03 a.m. and I swore I wasn’t going to bed late again! :o