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.

Elements of Book Design: The Grid

One element of good book design, or page layout for any print or digital application, is the grid. The grid could be compared to the rough framing of a building. In the building, the framing determines where the interior finishes will go and how they will be fastened to the structure. Similarly, a grid in graphic and book design determines the interior divisions upon which the elements, from type to visuals like illustrations and photographs, are laid out.

It is important to note that sometimes the grid is cast aside in favor of a more expressive approach versus the rationalism of the grid. However, in the instance of a traditional, trade book that presents a strong narrative into which the reader will lose themselves, I favor using a grid to create predictable and purposeful form that does not distract from the content the reader is after.

Grids can be designed around several styles. In this post, I am showing a proportional geometric grid that is based on subdivisions of the book dimensions. The German designer Paul Renner described a similar process in his book Die Kunst der Typographie, published in 1948. The grid here is 18 X 18 for each page, or 36 X 18 for a spread. Note that the diagonal lines intersect each division.

The document grid with horizontal lines intersecting the diagonal line.

Below is the text box stretched out over the grid. This particular book is over 400 pages long, so I wanted the text box not to get sucked into the gutter. There is also plenty of space at the top and bottom for headers and page numbers, and ample space on the outside edges for thumbs.

The text box with ample room along the margins for the gutter, headers, page numbers, and thumbs.

Next, I estimated the approximate leading and type size that I wanted to use. I then subdivided the grid units until I found the right match. In this instance, 2 units divided by 5 created the ideal leading for the type size. Digital typefaces allow this style of grid to really shine because the typefaces can be scaled to any size, into the thousandths if need be. Note that the baselines split every other grid line, adding a touch of the expressive to the geometric by both keeping with the grid and breaking it at set intervals.

The baselines, where the type will run, are built on the document grid.

Finally, the content is set into the text block, and headers and page numbers placed onto the grid.

The first lines of the paragraphs are indented 1 em, i.e. height of the leading. The block quotes are indented two vertical grid units into the text box.

New sections are conveyed with a line break, no indent, and small-caps. Having more than just a line break is important for when a new section starts at the top of a page.

Headers have a slight tint applied to fade them from the main text and thus be less distracting.

The reader sees the canvas of the typography stretched over the hidden frame of the grid.

Designing a book this way is very enjoyable. Some might argue that a grid will constrain the typography and other visual elements. But I think that the grid frees the designer to find expression through form.

The iterative process of book cover creation

How does one create a book cover? Does it materialize in the mind of the designer in a brilliant moment of inspiration? Or is it a formula in which the designer plugs in the genre of book and out pops a cover?

I liken it to an architect designing a building, though admittedly designing a book cover is a far simpler task.

There is a discovery phase in which the designer learns about the needs and wants of the client and about the restraints of the project. The drafts come next. These are conceptual ideas that can be presented to the client. Once a draft is chosen, multiple iterations are created within the style of the draft. Lastly, one of these iterations is chosen and refined, refined, and refined some more.

I am a visual learner, so here is the draft by draft process of creating a recent book cover.

The Initial Drafts

The book is a collection of the writings of Cindy Kamler, a community leader in wildlife rehabilitation. This background information established the theme of the book and directions for the cover.

We had several meetings after drafting these covers. We decided to create iterations based on the two covers with tracks. The vector-based cover with mountains was eliminated.

Note the aspen leaf background on the middle cover. This was a sample stock illustration. Using stock illustrations helps to speed the drafting process. Later, if custom art is needed, you will be further along in the creative process and thus have a better idea of what you need.

Also, note that we used two different subtitles. We did this to get a feel for how the different subtitles would interact with the layout of the cover.

Iterations of the Selected Drafts

Next, we created a different version of each draft. We decided that the human footprints were a digression from the book’s theme, centering the book on a human rather than wildlife. And the cross-hatching on the cover with the animal tracks also had to go. So here is where we arrived after that:

At this point, we needed to pick one or the other. The cover with the various types of animal tracks was selected. It speaks to the style of nature guides and books of the past, including some that the client admired.

Creating Iterations of the Final Draft

Once we picked a draft and its accompanying style, we were then ready to create numerous versions, or iterations, of that draft. Here three of those, but in fact, there were far more. Many of the iterations had a minor color or layout adjustment.

On this round, we decided on the cover with grass above both the title and the author’s name. With that decision, we were ready to create the back cover and refine it.

The Final Cover

Here is the (almost) final cover. I say “almost” because there will be a few inevitable minor changes to the back blurb copy, and the subtitle is still undergoing iterations.

Print Considerations

Once the cover is ready to submit for a printed draft, we will have to decide between a gloss finish and a matte finish. I have a suspicion that a matte cover will look best (I like glossy covers for photographs and matte covers for illustrations). But all of this remains to be seen …

The Basics of Book Binding Types

Perfect bound. Case bound. Soft cover. Hard cover. Sewn signatures. Glued signatures. Case wrap. Dusk Jacket.

Board book. Chap book. Saddle stitched.

What does it all mean? And what combination is best for your project? In this article, I will explain the basics of the most commonly used book binding types and the types of publications for which they are suited. In the next article, I will discuss book cover types.

First, what does it mean to bind a book? A book consists of the interior pages (called the book block), a binding that holds the block together, a binding that attaches the block to the cover, and a protective cover. Binding a book refers to the method used to hold the book block together and then attach a cover to the block. In sales and marketing, the book cover type is often used to describe the binding, but, as we will discuss, that can be misleading, for cover types and binding types can be mixed.

Important in the binding process are the signatures. A signature is a folded sheet of paper that makes multiple pages. A book block typically consists of multiple signatures. One signature can have 2, 4, 8, 16, 32, 64, or 128 pages.

Saddle-stitching

A saddle-stitched binding is what you will often find in magazines, notebooks, comic books, or small booklets. Staples go through the center fold of the publication. This is a fast and cheap binding type, and works best on thinner publications.

Board book

A board book is a special type of book in which the book block and binding are made from the same heavy duty paperboard. They are designed to be very durable, and therefore are used almost exclusively in children’s books. The pages and cover of a board book are typically two pieces of paperboard glued together, then the entire book is cut to shape (often with rounded corners for safety).

A board book.

Perfect bound

Perfect binding, or adhesive binding, is the most popular book binding used (it is also used in some magazines). It is also the fastest and cheapest method. Signatures are glued together to create the book block and the cover is glued to the spine of the block. In some production processes, the ends of the signatures at the spine are cut off before the glue is applied. While in other processes, the signatures are glued together intact. In the later case, sections of the signatures are milled to allow glue to penetrate into the spine of the block and thus adhere to each page. Fully cut signatures are what you will see in most Print-on-Demand solutions. They are less durable than the alternative, but cheap.

If the book block is going into a softcover (a.k.a. paperback), the cover and book block are trimmed to size once assembled.

It is important to note that hardcover books can have a perfect binding. Once again, this production method is used for cost and time. Print-on-Demand hardcover books are printed this way.

Perfect binding on a Print-on-Demand book. Note the trimmed signatures, making each leaf independent.
Perfect binding with intact signatures. Note the folded together signatures.

Sewn bindings

The terminology can get confusing here because the terms are often used interchangeably between the block binding and the cover type. Case-binding is a popular sewn binding for hardcover books, but can also be used for softcover books. Signatures are stitched together, then fabric is glued over the spine of the book block. The cover, or case, is then glued to the book block.

There are different types of sewn bindings, including oversewing and Smyth Sewing. Again, it is important to remember that sewn bindings can have either hardcovers or softcovers. Softcover books with durable sewn bindings include nature and outdoor guide books, which need to be both durable and portable.

Softcover with sewn binding. Signatures are sewn through the center fold and glued together. Softcover is attached to spine via glued endpapers.
A hardcover, case wraped, with sewn binding. Signatures are sewn at the center fold and the block glued together. Case wrapped hardcover is attached to block via glued endpapers.
Cloth hardcover with dust jacket and sewn binding.

Further Reading

Book Design: A comprehensive guide by Andrew Haslam is an excellent resource for anyone wanting to dive further into the art and engineering of good book design and production.

Excerpt from Haslam’s book.

Cover Types

But what about hardcover, softcover, case wrap, and dust jacket? I’ll discuss those in the next post.

Market Forces Affecting Publishers, Content Creators, and Marketing

In July, I presented a talk at the Eastern Sierra Book Festival. The organizer asked me to give an update on trends in the publishing industry. I decided to spend the allotted 30 minutes discussing current market forces that impact not only independent authors and small publishers but also impact any business competing for an audience’s valuable time.

The first two market forces are Our Competition and Our Content. These synergistic forces are evolving faster than many large businesses can adapt.

The third force is Amazon.com. In particular, I will emphasize the importance of diversification away from Amazon while continuing to use their platform.

Continue reading “Market Forces Affecting Publishers, Content Creators, and Marketing”

Creating Elegant eBooks for Academics and Students

The current trend in Higher Education is moving towards Open Educational Resources (OER). These resources are freely accessible course materials that bring down the costs of obtaining a degree and facilitate research. The materials include textbooks, workbooks, course outlines, and even videos of the courses.

However, there is no universal format being used in the OER movement. Some universities are using Markdown as the base format language, which then allows a book to be exported in ePub, HTML, PDF, or a Word file.

To create a resource using Markdown, you will need to get a Markdown editor like Atom. The challenge with Markdown, however, is that your textbook will come out looking pretty basic and even a little raw.

What if you want to create an eBook that is more elegant and openly accessible?

There are several resources available for creating digital books that look good. The two that I use are Sigil and iBooksAuthor. Sigil is the more basic application and is used for creating primarily text-based books with little media. It creates books using the open source ePub format. To use Sigil, you should know some HTML and CSS.  iBooksAuthor can create either ePubs, PDFs, or iBooks.

Since the goal of this post is to create a more elegant eBook without coding skills, let’s focus on iBooksAuthor. First, this application is made by Apple. This means it will only work on a Mac. However, if you choose an ePub format, you can create digital books on a Mac but that will work on any device.

Creating an ePub in iBooksAuthor is as easy as creating a document in Word. Import your text, and then start the fun part of making it look good. The key thing to remember with an ePub is that they are not fixed layout (and you don’t want them to be). The text will adjust based on the device and the display settings of the user.

Photos can be embedded into the text, and thus attached to the eBook. For videos, it is best to provide a link to an external website (this way the book file does not become a behemoth to large to download). iBooksAuthor is a What-You-See-Is-What-You-Get editor, so that will make many users lives much easier.

All of this said there is a confusing array of formatting options out there. It can certainly be frustrating to the author who just wants their books to be accessible. It can be doubly frustrating to anyone who wants their book to be modestly aesthetic.

If you need helping formating your eBook project, please reach out to me. Or learn about all of the services that I offer.

Quality Control for Books and Content Management

Creating quality control protocols is just as important for the book publisher as it is for the factory. At the end of the day, book publishers are producing a product that will be mass manufactured on an assembly line and, hopefully, consumed by many people. The implication here is that we need to take great care in producing the books we publish, from design and editing to marketing and distribution.

Continue reading “Quality Control for Books and Content Management”

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”