Add KPI/Target accounts

You have three options for adding your own KPI/Target accounts to Spotlight:
‍

• Import KPI accounts via Excel
• Create a new account
• Create your own formula to calculate KPI
• Codes and formulae

‍

Import KPI accounts via Excel

1. From the Import tab, click the Excel option and download the import template.
2. Open the template and in the KPI Actuals tab, enter the name and account type of each account.
3. Account name - Column A - This is the account name you want to appear on your Spotlight reports.
4. Account type - Column B - This should always be KPI.
5. In column C onwards, enter the required dates in the format mm-yy.
6. Enter your actual values into the corresponding months from column C onwards.
   ‍
   Note: You should enter the month-end value.
   ‍
7. In the KPI Targets tab, enter the same account names and type you used in step 2.
8. Enter the required dates in the format mmm-yy into column C onwards
9. Enter your target values into the corresponding months from column C onwards.
   
   Note: If required, you can enter future targets. These will appear in Spotlight in yellow, as with financial budgets.
   ‍‍
10. Save the Excel workbook on your computer.
11. Open Spotlight, then from the Import tab select the Excel data source then click Choose file.
12. Browse to and select the saved file, then click Open.
13. Once the file uploads, to complete the process click Import.
    ‍

Note: You will need to import updated values each time you run the report.

If any accounts have not been mapped correctly, to correct this you can change the report code in the Customise Data tab.

Create a new account

‍

1. From the Customise Data tab click KPIs/Targets.
   
   ‍Note: Spotlight already contains some default accounts. These are already set up for you, and appear on some default charts.
   ‍
2. Click Add Account, enter a name for the new KPI.
3. Select one of the following options:
   
   Manually enter actuals and targets
   Calculate actuals from [data source] data
   ‍
4. To create the account, click Save.
5. Find the new account within the data grid and enter the actual and target values for the account.
6. If you chose to manually enter actuals and targets, you can now enter the values directly to the data grid.  If you chose to calculate the actuals, you can enter the targets and then build a formula to calculate the KPI figures. For information about how to set up a formula, please refer to the following section.
   
   ‍Note: You will need to update the values each time you run your report.

Create your own formula to calculate KPI actuals

When you create a formula in Spotlight, you need to use report codes to choose the accounts you want to use.  For example, a formula to show wages as a percentage of trading revenue would be EXP.WAG / REV.TRA * 100.  You can find the report code for each account alongside the account name in Customise Data.
‍

1. In the Customise Data tab, click KPIs/Targets.
2. Locate or create the account you want to use for the formula.  For information about how to create an account, please refer to the previous section.
3. Click the name of the account, then select the Calculate actuals from [data source] data option.
4. Enter the formula using the report codes for the data you want, then click Save. For details of the options available please refer to the following section.
   ‍

‍

Codes and formulae

When you create a formula, you can use the following options:
‍

• You can add (+), subtract (-), multiply (*) and divide (/) accounts by using the report codes.
  ‍

You can also use additional codes to specify particular accounts and periods for the formula, rather than using the month end value.
‍

• Display a particular account:
  If an account shares the same report code with other accounts, you can use the account name to select the particular account you want.
  Code: [@ACCOUNT#NAME='xxxxx']
  Example: REV.TRA[@ACCOUNT#NAME='Project Revenue’]

• Display grouped accounts:
  This code will quickly display all accounts that are grouped together using a Spotlight Display Name.
  Code: [@DISPLAY='xxxxx']
  Example: EXP[@DISPLAY='Financial Expenses’]

• Display YTD or cumulative results:
  This code will show the year to date total or cumulative result of accounts.
  Code: [fromDate='SOFY']
  Example: REV.TRA[fromDate='SOFY']

• Display previous year results:
  This code will display the previous year’s data as a separate series line in your chart.
  Code: [date='DATE­1Y’]
  Example: EXP[date='DATE­1Y’]