Royalty Cash Flow Forescasts

An important aspect of royalty accounting is forecasting future cash flow requirements. This includes forecasting when and how much cash is required to pay royalties due and royalty advances. These forecasts are revised each month, based on year to date results and updated information from stakeholders.

Book Publisher Example

For book publishers, the rapid growth in digital sales has led to dramatic changes in their royalty forecasting models where royalties on digital sales are twice as much; or more, as royalties on print sales.

Below is a simple model for forecasting royalties payable for a book publisher’s January through June period that are due for payment in September. This model accounts for differing royalty percentages by format and royalty period. Each month the model is updated with actual YTD payables and revised revenue projections.

Format

Royalty

Period

Actual   YTD Payable
Jan to Feb

Revenue   Forecast
Mar to June

Est.   Royalty

Mar   to June

Est.   Royalty Payment Due
Sept 2014

Print

10%

6

$20,000

$800,000

$80,000

$100,000

Print

10%

12

$17,000

$700,000

$70,000

n/a

Digital

25%

6

$15,000

$400,000

$100,000

$115,000

Digital

25%

12

$12,000

$160,000

$40,000

n/a

Total

$215,000

Forecasting royalty advance payments requires a different model as contracted advance payments are based on specific events; manuscript receipt and publication. Each month the acquisition editor updates the estimated dates for their titles’ royalty advance conditions. This updates the cash flow forecast.

Title

Advance
Liability

Condition

Estimate
Updated
2/24/2018

Apr

May

Jun

Blue Mountain

$5,000

Manuscript

4/01/2014

$5,000

Blue Mountain

$5,000

Published

6/01/2014

$5,000

Yellow River

$10,000

Manuscript

5/01/2014

$10,000

Yellowstone

$10,000

Published

6/20/2014

$10,000

Totals

$30,000

$5,000

$10,000

$15,000

To allocate the royalty advances to the correct column based on the date in an Excel worksheet use the Excel AND function.

Example:

=IF (AND (Condition 1, Condition 2, etc.), Value if all conditions are True, Value if 1 condition is False)

=IF(AND (MONTH($C2)=MONTH(G$1),YEAR($C2)=YEAR(G$1)),$D2,””) where row 1 refers to the column headers and column C refers to the projected event date. Thus if the projected MONTH and YEAR = Column header MONTH and YEAR, it enters the amount from column D (the amount column) into that month’s column. If the dates don’t match the cell for that column month is left empty.

The results from the royalties payable and royalty advance forecasts are combined to generate the cash requirements forecast.

Cash Requirements

April

May

June

Royalty Payable

$215,000

Royalty Advances

$5,000

$10,000

$15,000

Total

$5,000

$10,000

$230,000