• Import contract royalty data such as royalty rates, payee splits, escalators and advances using the Consonance contract royalties spreadsheet templates.
• Review either the implementing royalties as a new publisher documentation or the implementing royalties with legacy data 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.
• Upload each completed template sheet separately into Consonance one at a time.
• 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.
• Do not change the column headers. They must be exactly the same as the template.
• Do not add any other columns.
• You may delete unnecessary columns, but you must have certain columns to identify the type of royalties spreadsheet. These are detailed below, in the sections for each of the templates.

There are four template spreadsheets for importing contract data:

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

To successfully run royalties for a product, you will need a royalty rate for at least one of the levels (product, masterchannel or channel) and the split of that rate between the applicable payees. So, each product will need to be present on at least one of the rates sheets (product rates, masterchannel rates or channel rates).

To determine which rate spreadsheets to use, consider the structure of the royalties and sales reporting. Most products can have the royalty rate at the product level, because the royalty rate for a product does not change between channels. However, for example, if quantity escalators only apply to certain sales, the channel structure needs to exclude sales that do not count towards the quantity escalator. See below for further explanation and examples about escalators and masterchannel/channel levels.

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 column for spreadsheet identification.
• If this column is blank, no royalty specifier will be created.
• 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.
• If a rate at this level already exists in the system, the import will overwrite that rate.

Net/Retail (price basis)

• 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
• The system will display Discount for net and List for retail.
• If a price basis at this level already exists in the system, the import will overwrite that rate.

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, as the step value is ADDED to the base rate. 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

Sale value escalators

• If the royalty is agreed as: 25% base rate up to £10,000 in sales, 35% up to £25,000 in sales and 50% thereafter you enter:
  • Step 1: from (sale value): 10000.01
  • Step 1: % pt increase: 10
  • Step 2: from (sale value): 25000.01
  • Step 2: % pt increase: 15 The system will deduct 0.01 to create the threshold at the agreed level.

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. The product may also have a default rate at product level.

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. The product may also have a default rate at product level and/or masterchannel level.

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 this is your first royalties import and 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 implementing royalties with legacy data documentation for guidance on these columns.

The payment column can also be useful to update Consonance after you have paid royalties on a statement batch. If you raise a support ticket, we will generate a spreadsheet with the details the royalty balance per payee in the whole statement batch, that can be checked over by you and then imported back in as payments, so the system knows those royalties have been paid.

General troubleshooting and tips

• Be sure to spot check the import by clicking on the import name to see the import detail view. You’ll see each row and the data that will be imported.
• Be particularly careful with decimal points. Sometimes Excel is tricky!
• If a column in the import detail view is empty, check if there is an ignored columns message on the import listing page. It may mean the column header in your uploaded spreadsheet is not exactly as it should be.