Excel

If Spotlight does not have a direct integration to the software you’d like to report on, you can use the Excel Import function to import any financial or non-financial data. All you need to do is download and populate the Excel template with your own data.

Understanding the spreadsheet

There are seven tabs in the workbook - three of these tabs are used to import financial data and the remaining four are for non-financial figures. You don't need to use all tabs to import via Excel, instead you can simply include the ones for the data you want to import.

Financial tabs

Note: For profit and loss accounts, you should use the monthly values. For balance sheet accounts, you should use the cumulative values.

  • Actuals - Actual figures from the current financial year profit and loss and balance sheet reports.
  • Budget - Monthly profit and loss Budget figures for the current financial year.
  • Actuals LY - Actual figures from last financial year's profit and loss and balance sheet reports.
  • Cash - Cash movements in the current financial year for each profit and loss and balance sheet account.
  • Cash LY - Cash movements in the previous financial year for each profit and loss and balance sheet account.

Non-financial tabs

  • KPI Actuals - Month end results for your Key Performance Indicators (KPIs).
  • KPI Targets - Monthly targets for your KPIs.
  • Non Financial - Month end results for non-financial data.
  • Non Financial Targets - Monthly targets for non-financial data .

    Screen Shot 2017-01-13 at 3.50.56 PM.png

The information in each tab

Each tab should contain at least three fixed columns:

Note: If you use the sample import template, you should delete the sample text shown in row 2.

  • Column A - Account Name - The account name as shown in your chart of accounts.
  • Column B - Account Types - The type of account.  The following types are available:

    Revenue Fixed Asset
    Cost of Sales Current Liability
    Overheads Non-Current Liability
    Current Assets Equity
    Bank  KPI
    Non-Current Assets Non-Financial


  • Column C onwards - Monthly values - Enter the month and year in the column heading, and the values for each account below.

    Note: For profit and loss accounts, enter the monthly value.  For balance sheet accounts, enter the year to date value.

    excel import example template image2

Data to gather before import

You can gather these balances by downloading a trial balance or the monthly profit and loss, balance sheet and cash summary reports from any accounting system.

  • Monthly accrual balances - Profit and loss and balance sheet accounts.
  • Monthly cash balances - Profit and loss and balance sheet (optional).
  • Monthly profit and loss budget - Budget values for profit and loss accounts only (optional).

Note: Although Spotlight is designed to report monthly, you can import quarterly or annual balances. Just enter the figures available to the last month within the relevant quarter or year.

Importing financial data

You can also view this information in a quick video demo.

  1. From the Import tab, download the Spotlight import template.
  2. Open the appropriate worksheet tab that you would like to import data for.

    Note: If you wish to import KPI or Non-financial data, we have provided specific instructions further down in this article.

  3. In column C onwards, enter the required dates in the format mmm-yy.
  4. Complete the remaining data. All tabs use the following structure:

    Note: You should only import account balances. Do not include any total values in the import.

    • Column A - Account name - The account name you would like to appear within your Spotlight reports.
    • Column B - Account type - The type of account you're importing. For information about the account types available, please refer to the account type list.
    • Column C - Monthly values - The appropriate values for the tab type - actual values, cash movements, budget or target values.

  5. Save the spreadsheet on your computer, then from the Import tab in Spotlight, select the Excel data source and click Choose file.
  6. Browse to and select your saved spreadsheet, then click Open.
  7. Once the file uploads, to complete the process click Import.

Note: Once the import is complete, if any errors are reported please check the error message and correct any issues before re-importing the Excel file.

You will need to import updated values each time you run the report. If any accounts have not been mapped correctly, you can change the Report Code in the Customise Data tab to correct the mapping.

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.

    • Column A - Account name - The account name you want to appear on your Spotlight reports.
    • Column B - Account type - This should always be KPI.

  3. In column C onwards, enter the required dates in the format mmm-yy.
  4. Enter your actual values into the corresponding months from column C onwards. 

    Note: You should enter the month-end value. 

  5. In the KPI Targets tab, enter the same account names and type you used in step 2.
  6. Enter the required dates in the format mmm-yy into column C onwards
  7. 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.

    Screen Shot 2017-01-11 at 11.34.41 AM.png

  8. Save the Excel workbook on your computer.
  9. Open Spotlight, then from the Import tab select the Excel data source then click Choose file.
  10. Browse to and select the saved file, then click Open.
  11. Once the file uploads, to complete the process click Import.

Note: Once the import is complete, if any errors are reported please check the error message and correct any issues before re-importing the Excel file.

You will need to import updated values each time you run the report.  If any accounts have not been mapped correctly, you can change the Report Code in the Customise Data tab to correct the mapping.

Import Non-financial accounts via Excel

  1. From the Import tab, click the Excel option and download the import template.
  2. Open the template and in the Non Financial tab, enter the name and account type of each account.

    • Column A - Account name - This is the account name you want to appear on your Spotlight reports.
    • Column B - Account type - This should always be Non-Financial.

  3. Enter the required dates in the format mmm-yy into column C onwards.
  4. Enter your actual values into the corresponding months from column C onwards. 

    Note: You should enter the month-end value. 

  5. In the Non Financial Targets tab, enter the same account names and type you used in step 2.
  6. Enter the required dates in the format mmm-yy into column C onwards
  7. 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.

  8. Open the Non Financial tab, and enter the actual results as of each month.
  9. Save the Excel workbook on your computer.
  10. Open Spotlight, then from the Import tab click Choose file.
  11. Browse to and select the saved file, then click Open.
  12. Once the file uploads, to complete the process click Import.

Note: Once the import is complete, if any errors are reported please check the error message and correct any issues before re-importing the Excel file.

You will need to import updated values each time you run the report. If any accounts have not been mapped correctly, you can change the Report Code in the Customise Data tab to correct the mapping.

 

Back to top

Release Notes - Terms of Use - Privacy Policy - Launch Application - Copyright © Spotlight Reporting 2017