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.

Creating an Oral History Program

I recently attended an oral history workshop sponsored by the California Conference of Historical Societies. The presentations from archivists at the Bancroft Library at the University of California, Berkeley and the Sacramento Public Library were excellent and highly informative.

The workshop focused on the process of developing and running an oral history program.

First, what is oral history?

Oral history is the collection and study of historical information about individuals, families, notable events, or everyday life using audiotapes, videotapes, or transcriptions.

https://en.wikipedia.org/wiki/Oral_history

I’ve been creating oral histories for some time, from interviewing members of the Missouri and Yosemite climbing communities to documenting the personal stories of monks. My work on biographies, family histories, and regional histories has also reminded me of the importance of oral histories.

Before I get into the how’s of creating an oral history, I want to highlight why oral history is essential to our communities.

A client recently remarked that they were unsure if anyone would find their story of interest because they had no particular claim to fame. I disagreed; their story is and will be important. This person’s account and experience of the world provides a glimpse into a cross-section of time and place. Readers today, particularly those living in the same location and engaging in similar activities, will learn from this individual’s life and insights. Readers and researchers in the future will be able to compare this person’s story with the stories of others who lived at the same time and experienced the same economic, political, and societal paradigms, though from perhaps different personal circumstances.

Oral histories are a reasonably easy way (far easier than, say, creating an entire biography) to document a perspective that might otherwise get lost to time. As such, a lot is possible. We can interview winners and losers. We can question the significant players in an event, as well as the folks who played a minor role. All of these perspectives create a multi-faceted story that can help those in the future have a more comprehensive understanding of history.

Whether you are interviewing a head of state, a coal miner, or a spiritual director, all oral history efforts will share certain elements. What are the parts of an oral history program? 

Preparation

First, the interviewer needs to research their interviewee and the topics they will discuss. Did the person write a book? If so, read the book. Were they in a war? Then read about the war. Familiarize yourself with the person and the things they have experienced. This preparation helps interviewers to ask the right questions and lead the conversation in a productive direction.

The copyright ownership of the interview also must be sorted. Will the interviewee have a chance to review the transcriptions of what was said? Can they keep the recording from being released? Does the recording belong to an institution? Who owns the copyright of the oral history? After you make these decisions, a contract must be drafted and provided to the subjects of the interview.

pre-interview questionnaire can be sent to the interviewee. The answers to these questions will help to guide the interviewer in developing questions and running the interview.

An alternative to the pre-interview questionnaire is a preliminary interview. This interview can be recorded or not. Similar to a questionnaire, it will help both the interviewer and the subject to create a high-quality final interview.

Finally, an oral history project needs a good interviewer. A good interviewer knows when to allow digressions and when to bring the conversation back on track. The interviewer can also be someone from the same community as the subject or have experienced similar things in life.

The interview

Once you get to the final interview, you should have put in enough preparation that the interview itself is fluid. You know what questions to ask, you have researched the subject and what they will talk about, and the paperwork is out of the way. Only two things remain: the setting and the equipment.

The setting for the interview is essential. More than once, I made the mistake of interviewing a subject in a loud, distracting environment. Poor surroundings affect the audio/video and prevent the interviewer and subject from focusing on the story. Find a quiet and comfortable place, and if you are using video, make sure that the lighting is right.

Know your equipment. You do not need the best camera or recorder. You need to know how to use what you have and its limitations. Make sure that you have plenty of batteries and storage space. And know what file formats you are creating.

After the interview

There is plenty of work that must be completed after the interview.

Someone must transcribe the audio; this can be done in house or outsourced to a contractor. If the interview was filmed, then it needs to be edited and formatted for distribution.

Once the post-production is complete, the content will need to be archived for future preservation and distributed to interested audiences.

For secure archiving, the use of both cloud back-ups and physical back-up drives is the best solution. When backing-up anything, redundancy is critical.

Distributing the oral history will depend on the copyright, funding, and goals of the project. A simple, cheap solution is uploading the audio or video to a site like YouTube. Another option is to host the material on the organization’s website. The transcribed audio can also be published as a book (or assembled with other oral histories into one book), which will require book design and printing (I could write an entire post on this topic). If the oral history is intended for private distribution within a family or organization, then the book option is probably the best way to share the content.

Concluding Thoughts

The process of creating oral histories is often exciting and rewarding; it does entail a lot of work, but the results are worth the effort. While creating this post, I have also been developing the outline of my next oral history project, I am thrilled with the prospect and know that this work will provide a service to the interested community.

Enjoy the process of building your next oral history project, and if you need help, please reach out to me.

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”

5 Tips for Editing Your Writing

Writing a rough manuscript or draft is often a solo endeavor. However, getting that draft into a polished piece ready for public distribution is a collaborative effort. Therefore, I do not recommend being both the author and sole editor on a writing project. However, sometimes it has to be done. Here are some basic tips that will help you edit your own writing. While it’s not easy, it’s possible.

1. Leave it.

  • Once you are finished with the first draft, set down the pen or close the document for at least a week and forget about it during this time. The longer you can leave it be, the better. This exercise will help to give you a fresh set of eyes when you go back to edit the writing.
Continue reading “5 Tips for Editing Your Writing”

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.

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”

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”