• Import contract royalties such as royalty rates, payee splits, escalators and advances using the Consonance contract royalties spreadsheet template.
  • Review the getting started with royalties documentation first, to ensure your account is properly configured.
  • Be sure to raise a ticket with the Consonance team before starting for guidance and the pre-populated spreadsheets.
  • Currently, send your spreadsheet to the Consonance team for checking and import. Coming soon: uploading the spreadsheet directly into Consonance.
  • Uploading scans of existing contracts may be useful for archiving purposes, but please use the importer spreadsheets so the system can process the data.

Add data to the contract template spreadsheets

  • The pre-populated data is for reference and is not imported.
  • Certain column names must be present in the top row to identify the type of royalties spreadsheet. These are detailed below, in the sections for each of the templates. Other columns can be deleted, if that helps your workflow.

There are four template spreadsheets for importing contract data:

  • Product rates
  • Masterchannel rates
  • Channel rates
  • Royalty payees

2. Product rates template columns

Each row in the spreadsheet template represents one product.

Product ID (pre-populated)

  • Required for accurate data ingestion.

Total Paid Across All Payees %

  • Required for spreadsheet identification.
  • This is the combined total percentage of base rate royalties that will be paid out across all contributors. (The royalty payees template is where you designate which contributor gets what.)
  • If the royalty structure is 8% to the author and 7.5% to the illustrator, this field should be the number 15.5.
  • The number must be less than 100.

Net/Retail

  • Required for spreadsheet identification.
  • This field designates the total paid percentage as a percentage of the net receipts or retail price. Net is sometimes called discount, where royalties are calculated on the sales value you receive from customers, after discounts have been taken off. Retail is the same as list, where royalties are calculated as a percentage of the retail price.
  • Required if data is present in the total paid percentage field.
  • Valid values: net, retail, n, r

Escalators

  • The base rate step is included in the total paid. Do not include the base rate again; start from the first escalation.
  • Second and subsequent steps should be specified in relation to the previous step. See examples below.
  • Each step uses two columns – the esclation trigger and the change in rate.
  • Use negative numbers if the percentage diminishes.
  • Add more step columns, if needed.
  • Delete unnecessary columns if it helps your workflow.

Unit escalators

  • If the royalty is agreed as: 7.5% base rate, 10% after 10,000 units, 12.5% after 25,000 units you enter:
    • Step 1: from (units): 10000
    • Step 1: % pt increase: 2.5
    • Step 2: from (units): 25000
    • Step 2: % pt increase: 2.5

Date escalators

  • Dates must be in the format YYYY-MM-DD for absolute clarity.
  • Dates must be in chronological order.
  • If the royalty is agreed as: 7.5% base rate, 10% after 1 Jan 2020, 12.5% after 1 Jan 2021 you enter:
    • Step 1: from (date): 2019-12-31
    • Step 1: % pt increase: 2.5
    • Step 2: from (date): 2020-12-31
    • Step 2: % pt increase: 2.5

Discount escalators

  • If the royalty is agreed as: 7.5% base rate, 5% on discounts over 75%, 3.5% on discounts over 85% you enter:
    • Step 1: from (discount): 75
    • Step 1: % pt increase: -2.5
    • Step 2: from (date): 85
    • Step 2: % pt increase: -1.5

3. Masterchannel rates template columns

This spreadsheet template works in exactly the same way as the product rates spreadsheet, but is used to define royalty rates at the masterchannel level.

  • Important! If you do not specify a masterchannel code for a row, but include product rates data, those rates will be applied at the product level.

This sheet will typically be used for unit escalators that aggregate across channels. For example, you have:

  • a masterchannel called Home
  • two channels within that masterchannel called Retail and Libraries
  • a unit escalator that needs to aggregate across both channels

The escalator should not be specified at product level in the product rates template; it should be specified here using the product identifier and the masterchannel code.

4. Channel rates template columns

This spreadsheet template works in exactly the same way as the product rates spreadsheet, but is used to define royalty rates at the channel level.

  • Important! If you do not specify a channel code for a row, but include product rates data, those rates will be applied at the product level.

This sheet will typically be used for escalators that only apply to specific channels. For example, you have:

  • a masterchannel called Home
  • two channels within that masterchannel called Retail and Digital
  • a date escalator that must only apply to digital sales

The escalator should not be specified at product level in the product rates template; it should be specified here using the product identifier and the channel code.

5. Royalty payees template columns

Each row in the spreadsheet template represents one payee of one work.

Your pre-populated spreadsheet template will include all the contributors for the work, but there may be non-contributory payees, such as estates or agents. In that case, please duplicate a row for that work, replace the Contact ID with the Contact ID from Consonance and we will create a contract-payee relationship with the rate you specify.

Agents / Non-contributor payees

The system can’t present these payees as part of the pre-populated data, because there is no link between the work and the non-contributing payee until a payee split record is created. To handle non-contributing payees within this import:

  • The contact must already exist within Consonance. (There is a contact importer, if there are a lot to add at once.)
  • You need the Consonance Contact ID, which can be found either by visiting each contact and taking the ID number that’s in the URL of that contact’s web page, or by generating a custom report that includes the Consonance Contact ID field.
  • Duplicate the row in the payee splits sheet, so that you have the correct Work ID, then replace the Contact ID with the appropriate non-contributing payee. Specify the split, and the system will create that payee relationship.

Author + translator or other multi-payee situation

To handle differing royalty rates:

  • The product rates, channel rates and masterchannel rates spreadsheets “Total base rate percent” should be the total percent paid for all payees. So, if you are paying an author 10% and a translator 5%, you enter 15% in “Total base rate percent”.
  • Then in the payee splits spreadsheet you designated the portion that total to each of the payees: 66.66% to the author and 33.34% to the translator.

Work ID (pre populated)

  • Required for accurate data ingestion.

Contact ID (pre populated)

  • Required for accurate data ingestion.

Split % (optional)

  • This is the split of the total percentage specified in the product rates Total Paid Across All Payees % column, and all the rows for a work should add up to 100%, unless they all add up to 0%.
  • For example, an author and an illustrator on a work have an uneven split
    • The Total Paid Across All Payees % has been specified as 15% on the product rates sheet
    • In the row for the work and the author, specify 66.66%, in the row for the work and the illustrator, specify 33.34%. This will work out to a 10% and 5% split.
  • Blank cells will be ignored. If you mean 0, you must include 0.

Payment history columns (advances paid or opening balance)

If you have run royalties for this work outside of Consonance, to ensure escalators and unearned advances are accounted for, the system needs payment history.

Please refer to the getting started with royalties documentation for guidance on these columns