When importing budgets into Calxa from a spreadsheet your columns need to identify the Business Units, Accounts and Periods you wish to update. The purpose of this help article is to identify the spreadsheet options as well as required and optional fields for a successful budget import.
Remember you can always Export your Budgets to download a template that can be used to import.
TABLE OF CONTENTS
- Monthly Amount Spreadsheet
- Annual Amounts Spreadsheet Columns
Monthly Amount Spreadsheet
The Monthly Amounts format allows you to import monthly values where there are columns to match Business Units, Accounts and then monthly Period Amounts.
Business Unit Columns
MYOB and Unplugged Organisations
Business Unit Number or Business Unit Name (Optional): When no Business Unit columns are provided Calxa will assume the import is for the Organisation or Overall budget. For best results when mapping a Business Unit use the Business Unit Number as this is unique. You can however map either the Business Unit Number or Business Unit Name or both. When both columns are included Calxa will match on both Business Unit Number and Business Unit Name, so you may use this as an extra check that you're importing to the correct Business Unit.
Business Unit Name (Optional): When no Business Unit Name is provided Calxa will assume the import is for the Organisation or Overall budget. If your import is to be mapped to a Business Unit then a Business Unit Name column is required. Note if you export your budget from Xero Tracking Categories you'll need to add this column to your exported spreadsheet.
Business Unit Path (Optional): If your Tracking Category options are not unique across both Xero Tracking Categories then you'll need to include a column to uniquely identify the Business Unit. The best way to get the format for this column is to Export your Budgets first. If there are non-unique Business Units names the Business Unit Path values will be included in the exported template.
MYOB, Xero and Unplugged Organisations
Account Number (Required): Since the Account Number is a unique identifier for these organisations it is the only required column to identify the account.
Account Name (Optional): You can optionally include and map an Account Name during import. In this case Calxa will match on both the Account Number and Account Name, so you may use this as an extra check that you're importing to the correct account.
QuickBooks Online Organisations
Account Number or Account Name (Required): Since Account Numbers are optional in QuickBooks you can map either an Account Number or Account Name column. If using name alone the names must be unique across the entire chart of accounts for the import to succeed.
Account Path (Optional): If your underlying accounting does not have account numbers and the names are also not unique you can provide an Account Path column to identify them during import. The easiest way to get the correct format on this column would be to Export your Budgets first. If there are non-unique accounts the Account Path column will be included in the exported template.
Combined Account Number and Name (All Organisations)
The default behaviour to identify accounts is with a separate Number and Name column as described above. There is however an option to map a single column that combines the Account Number and Name. See Import Budgets and the Match fields to column headings step for details. This option is particularly useful if you are exporting budgets from Xero where the Account column is combined in the exported format.
Account (Required): When this combined option is enabled the Account column is required. The following formats for combing Number and name are supported.
- Name - Number
- Number - Name
- Name (Number)
- (Number) Name
Period Amount Columns
Period Amounts (Required): At least one Period Amount column is required. These column headings represent the Month and Year you would like to import budget values into. The following column heading formats are supported.
- (YYYY MMM) e.g. 2021 Jan
- (MMM YYYY) e.g. Jan 2021
- (YYYY-MMM) e.g. 2021-Jan
- (MMM-YYYY) e.g. Jan-2021
- (YYYY/MM) e.g. 2021/01
- (MM/YYYY) e.g. 01/2021
- (YYYY-MM) e.g. 2021-01
- (MM-YYYY) e.g. 01-2021
Budget Comments can optionally be imported along with the Budget Amounts. During the import wizard check the Import comments checkbox and Calxa will also update the Budget Comments along with the Budget Values.
The exact way to add comments in your spreadsheet varies for Excel and CSV file formats.
Add Excel Notes to the Budget value cells. Please note this is not Excel Comments which is used for conversations. You can Right Click on the cell in Excel and select New Note or select Edit Note if a note already exists on that cell.
In the cell that contains the budget value you can add a Comment by inserting a pipe separator and then adding your comment before adding the comma separator for the next value. Your row of data in raw text will look something like the following. In this example there are 6 monthly values mostly zeros where the 3rd value contains the comment One off payment.
0,0,150 | One off payment,0,0,0
Annual Amounts Spreadsheet Columns
The Annual Amounts format allows you to import annual values where each column represents a different Business Unit. This is useful if you have a very large number of Business Units and wish to quickly enter a Full Financial Year Budget against each Business Unit without breaking it down to a monthly amount for import. Calxa will split the annual value equally across the 12 months.
Accounts have the same requirements as the Monthly Amounts format discussed above. Refer to the Accounts section above for details.
Financial Year Column
Financial Year (Required): the financial year column is required. Then enter a 4 digit year which represent to year ending period. For example 2021 means Financial Year ending 2021. The budget values entered under the Business Unit columns will be equally split by 12 months in this financial year.
Business Unit Columns
Business Units (Required): at least one business unit column heading is required. The values entered in this column are the full year budget figures for that business unit. The column headings should be formatted as follows.
- BusinessUnitNumber · BusinessUnitName
or when no number exists
Once again the easiest way to get this formatting correct is to Export your Budgets first in the Annual format.