What follows is an easy and zero-cost way for book publishers to generate book royalty statements for copyright holders in a matter of minutes. In particular, this royalty management method is ideal for publishers using Lightning Source, Ingram Spark, Amazon KDP, or any other wholesaler who sends sales reports in CSV or Excel format. You don’t need expensive software to do this, just a copy of Microsoft Excel or access to Google Sheets (I use Google Sheets and will explain the process from this perspective).
There is one caveat, a large book publisher (1,000+ book titles) might need something more robust. This method is ideal for small to medium sized publishers, and it would also work for any other organization managing and paying out royalties.
Once the spreadsheet is set up, generating multiple royalty reports is as simple as copy, paste, print. It’s a massive time saver!
The Process
First, you need to learn how to use Pivot Tables. These will allow you to sort, calculate, and analyze data across columns and rows. These external links will tell you how to do it:
How to make Pivot Tables in Google Sheets.
How to make Pivot Tables in Excel.
Once you ready to begin, create a new spreadsheet in Google Sheets. Name it for the royalty period that you want to report on (in the future, you will just duplicate this spreadsheet for new royalty periods).
The first sheet we make is called “Currencies.” This sheet contains the exchange rate for the various international markets that you work in and collect royalties from. It is also a good place to write down any instructions for using the spreadsheet or reminders.
Next, create unique sheets for each source from which you, the publisher, receive checks, and thus sales statements. For clarity, the above screenshot has three sales sources: eBook (Amazon KDP), Paperback (Amazon KDP), and LSI (Lightning Source Inc.). If you use other wholesalers or distributors, you will need to create unique sheets for them as well. If you as a book publisher handle direct retail and wholesale orders as well, then create the appropriate sheets for those.
The next sheet we create will contain the sales statement issued by Amazon KDP for all eBooks sold in all markets. This sheet is called eBook Creating sheets for other sales channels mirrors this process.
Download the CSV or Excel version of your sales statement, and open it. Copy the column headers into your new spreadsheet. Don’t delete any of them, even if the data they contain is useless to you.
Add a column at the end called “True Royalty.” This column will contain the total amount you, the publisher, received for the sale adjusted to the appropriate exchange rate (home currency won’t change, i.e. if you are in the United States, then the amount will stay the same). Note: we will get to the Copyright Holder’s Royalty later.
Next, copy and paste your sales statement into the new sheet. To do this, highlight everything in your sales statement expect the headers and hit copy. Then, click on Row 2, Column A of the new sheet and hit paste.
Now, set the value for the “True Royalty” column. For every row that has sales data, make sure this is the value. This is the Google Sheets version:
=IF(J2="USD",O2,IF(J2="EUR",O2*Currencies!$B$2,IF(J2="JPY",O2*Currencies!$B$5,IF(J2="CAD",O2*Currencies!$B$3,IF(J2="GBP",O2*Currencies!$B$4,IF(J2="AUD",O2*Currencies!$B$6,IF(J2="BRL",O2*Currencies!$B$7)))))))If you need more or fewer currencies, then adjust appropriately.
If you did everything right, the values in the “True Royalty” column will reflect the total amount that you received in your home currency. Run a couple of manual calculations to verify that you did it right.
Note: this formula uses Net Sales for royalty calculations. If your organization uses List Price or Gross Sales as the basis, then adjust the formula.
Next, we will make the first Pivot Tables. These will be used on the sheet called Totals, which displays the Units Sold and True Royalty for every book in every sales channel. I have mine set up to combine all markets (USA, UK, EU, etc). So in this example the Pivot Tables displayed on the Google sheet “Totals” are: all Amazon eBook sales, all Amazon paperback sales, and all Lightning Source Inc. paperback sales.
Here is a snippet of that sheet:
You can edit the Pivot Table from the Pivot Table Editor on the right of the dashboard. Here it is:
This particular Pivot Table grabs all of the data from the eBook sheet (the sheet containing the sales statement for all Amazon KDP eBook for the period). In the Editor, you can adjust how the data is sorted and what is displayed, calculated, and filtered. The Filters will be used on the Royalty Statements for individual rights holders.
Finally, the Royalty Statement:
Above is the Royalty Statement for the Copyright Holder of one or more titles. On this statement, there are 3 Sales Channels, and each of those is created using a Pivot Table similar to the “Totals” sheet Pivot Tables. The only difference is that these Pivot Tables have filters applied to the book title data.
The rest of the sheet reflects the Copyright Holder’s royalty rate and the total they will receive. There are also places for deductions to cover Author purchases or other expenses that get applied to the account. You can customize these calculations.
Concluding Thoughts
Once these sheets are set up, they really are a massive time and money saver for book publishers. This is especially so for small and medium sized publishers who have better things to do than sort data and run reports all day long. Remember, we got into publishing to be creators, so let’s spend as much time creating as possible.
Enjoy.
