No matter how good your budget plan might be, you can’t predict the future. Different circumstances, good and bad, can affect reality. Therefore, it’s a good idea to compare your actuals with your budget predictions on occasion throughout the budgeting period. Importing actuals to SIP enables you to do this.
Importing actuals using the wizard in SIP enables you to get a side-by-side comparison of your budget plan and your company’s performance. Based on this information, you can then decide to update your budget plan accordingly.
📝 Your actuals must be set up in an Excel sheet according to the SIP Excel template. To download a copy of the template, go to either the Actuals > Summary or the Actuals > Details tab, and click Add transactions > Download template. Note that this SIP template is only for Excel transactions or Excel balances templates depending on which type of transaction you want to import.
To import actuals:
1. From either the Actuals > Summary or the Actuals > Details tab, click Add transactions > Import from Excel. The wizard opens.
2. Drag your Excel file to the wizard screen, or browse to the file by clicking Click here to browse for one and select the file.
📝 As mentioned earlier, the Excel file should be set up according to the template.
The file is uploaded and you are guided through 5 steps.
3. In the Select step ①, fill in the following information and then click Next:
Select which Excel sheet you would like to import from the dropdown.
From the What type of template is this? dropdown, select either Excel transactions or Excel balances, depending on which type of transaction you want to import.
The Overwrite existing data option overwrites actuals on a monthly basis. All current actuals in the SIP from the months that are imported are deleted and replaced with the new imported actuals.
For example, if you select this Overwrite existing data option, and import actuals from Feb-22, all of your current actuals that exist in SIP from Feb-22 are deleted and replaced with the imported ones.
4. In the Setup step ②, select your header row and title column and click Next.
5. In the Data - Columns step ③, select the data columns you want to import, click on each dropdown to map to the correct column type, and click Next.
📝 You must select and map the account and currency columns, and at least one of the date columns. The other columns are optional.
To select a date column, select Amount from the dropdown, and then select the month from the calendar.
Some information about mapping dimensions...
When actuals are imported with dimensions, the following conditions must be met. If the conditions are not met, you need to manually select and map the actuals to be imported to the budget (in the following step).
Dimensions values must be identical: When importing dimensions with your actuals, the dimension values in your budget and the Excel sheet must be identical. For example, if a Location dimension value is USA in your budget, then it must be USA in the Excel sheet and not US.
Dimension mapping must be consistent: Actuals data must contain an exact combination of mapped dimension values that you have in your budget.
For example, let’s say you have an account for Travel - transportation (6380) that’s mapped to multiple budget lines in SIP by location and then by department. When you import actuals for this account, the transactions that have matching values are automatically mapped. If there is no match then you have to manually select the row and map in the following step.
6. From the Data - Rows screen ④:
Select the lines of actuals you want to import. Each row is automatically mapped to the Budget line and Type according to the mapping in your current budget. For accounts that are not already mapped in the budget, select the Budget line and Type from the dropdowns. In addition, you can update any lines that were automatically mapped.
If you select data rows with currencies that aren’t used in your budget, the rows are crossed out and not imported. If there is a row without a currency value, then it is imported as the budget’s default currency.
📝 To see the currencies used in the budget, look at the Exchange Rates tab in the Settings window. From the Inputs or Actuals tab, select the Settings ⚙ icon > Exchange Rates tab.
The currency values in your budget settings and the Excel sheet must be identical. For example, EUR in your settings must be EUR in the Excel sheet and not EURO. For more information, see Set currencies in SIP.
7. From the Preview screen ⑤ preview the data for import. Click <Back to make any relevant changes.
8. Click Import.