Easy and free Royalty Management for Book Publishers (And Anyone Else)

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.

A Google spreadsheet that contains sheets within it.

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.

This sheet contains Amazon KDP’s statement for eBooks sold in all markets. Note that one title can have many sales listed separately or combined.

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.

The True Royalty column adjusts the Royalty to the appropriate exchange rate. The highlighted row changes the Royalty from Canadian dollars to USA dollars. Note: if the book publisher uses the list price as the basis for the Royalty, then the Avg. List Price column would be used.

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:

A Pivot Table displaying total unit sales and exchange rate adjusted royalties for all currency markets for all Amazon KDP eBooks sold for the given period.

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:

Example of an auto-generated Royalty Statement using Google Sheets and Pivot Tables. Hit Command+P to print this to a PDF.

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.

Book Printing: Print Only What You Can Sell

Note: As of mid-2019 this post is 5 years old. Some aspects may be dated. However, the core lessons hold true for the new author looking to self-publish or an entrepreneur looking to start a small publishing business.

When a new publisher is looking at printing options, there is a temptation to print too many copies. The more books the publisher orders, the bigger the discount the printer will give. It is very easy to fall into the trap of asking for a quote for 1,000 copies and ordering 5,000. The discount of 25% to 50%, or even more, that the publisher would get by ordering 5,000 copies causes the publisher to forget everything discussed in the book pricing chapter.

I was guilty of this mistake, once. Then I vowed to never again forget one simple rule: print only what I have sold. Today it is easy for a publisher of any size to use offset printing, Print-on-Demand, or electronic formats to publish their books. These options allow the publisher to develop a game plan that will stick to this simple rule.

Continue reading “Book Printing: Print Only What You Can Sell”

Book Submissions and Acquisitions

Here is how it is going to happen.

  1. The new publisher establishes an imprint with one or more books that will sell.
  2. A website is built for the imprint. This includes a book submissions page with information on how to submit book proposals.
  3. The initial books are successful and penetrate the market, the publisher gains new contacts in the industry, and the imprint becomes known.
  4. Authors and others learn of the imprint and the submissions roll in.
  5. An undisciplined, new publisher sees these submissions and gets excited. This enthusiasm leads to taking on projects that the publisher doesn’t know how to market.
  6. Alternatively, the disciplined publisher browses the submissions, finds no leads and thus rejects all submissions, and then continues with the original business plan.
Continue reading “Book Submissions and Acquisitions”

Book pricing—Solving the Great Mystery

Book pricing is difficult and a bit like cold fusion—many people claim to have discovered the key to success, but none can prove it. That being said, there are steps that publishers can take to eliminate some of the magical guesswork from the process of setting prices for books.

Publishers must always remember why prices exist. Prices exist to create profit. The publisher should strive to generate their desired profit from the primary audience. Understanding the primary audience and the price elasticity of the book within the primary audience is critical to designing success and achieving profit goals. What follows is the method that I use to decide prices for books.

Continue reading “Book pricing—Solving the Great Mystery”