Import data from Excel or a budget template

Importing data from Excel files is a fast way to get data into Planning. With one import process, you can add new and update existing budget lines by mapping your data from Excel to the budget groupings of your existing budget hierarchy in any combination (to one, to multiple, or all of your existing P&L budget groupings).

In Planning, you can initiate the import of data into your existing budget from either your root budget or from any of your company subsidiaries. The import process can also be initiated from individual budget groups of your budget structure (i.e. Revenue, Operational Expenses, Cost of Revenues, and Other income and expenses) or your own user-defined groups.

Download a Planning budget template

Download an Excel budget template from the Inputs>Main tab, the Sheets>Create report option, or the horizontal ellipsis icon that appears on the right side of the toolbar below your profile name. Use the budget template to assist you with importing, updating, and mapping data to your existing budget structure. With a budget template, you can easily export all of the existing data in your budget, update the data, and import it straight back into your budget.

Templates can be downloaded from the root of your budget tree or any budget grouping, user-defined group, or subsidiary in your budget tree. The templates are automatically customized based on the budget hierarchy, budget grouping, and dimensions of the location from where you download the template. This means that the columns you download maintain the budget hierarchy, groupings, and dimensions that exist in your budget structure, respective to where you download the template. Add new lines or update existing lines in the template spreadsheet. The data is then uploaded in the same hierarchical structure and imported from the same location where the template was originally downloaded.

To download a budget template:

1. Click on the vertical 3 dots icon that appears next to the root budget name of your budget tree, or from any budget grouping, group, or subsidiary.

2. Select Budget template and open the template from the containing folder.

   

If you download the template from a budget grouping, group, or subsidiary, only the columns that match the hierarchy from where the template was downloaded are included.

This greatly simplifies the import process. So if you want to add new lines to your file, copy the hierarchical structure in the spreadsheet and paste it into a new line. If you want to update existing lines, the changed values are recognized when you upload the file to Planning.

Additionally, if you add new lines to the template or decide to make changes to your budget hierarchy, the lines you select are imported according to the same logic as described in Import data from Excel to new and existing budget lines.

If you attempt to import empty lines from your Excel spreadsheet, these lines won’t be included in your budget.

Prepare an Excel file for import

Select the Excel file from which you want to import your budget data. In the Setup view, select the sheet containing the data you want to import into your budget. The header (columns) and header description rows are selected automatically by Planning, but you can change them manually.

Use Next and Back to move back and forth as necessary between the steps of the import process.

Select data columns

Map data to your budget structure

Mapping helps determine how the data from your Excel file is identified and validated against the budget line structure of the existing budget lines in your budget. If no matching budget line name and structure is found, the data is imported to a new budget line. If a match is found, the data is mapped to the existing budget line in Planning and the budget line can be overwritten if you choose to do so.

From the Data - Columns step, select the columns from which you want to upload and map data. As you select columns, the column header value is automatically detected and mapped for you to a selection in Planning. If you select a column and it isn’t automatically mapped, or if you want to manually change the mapping, click Map to and select one of the values available for mapping in your budget.

As you map columns from the Excel file to your budget hierarchy, each level of your budget group has its own sequence number, so be careful to choose the appropriate mapping option. For example, if you initiate the import process from the root level of your budget and you have a subsidiary company with its own subsidiaries, you would assign the parent-subsidiary (USA) as Subsidiary level 1 mapping and the child subsidiary (NYC) Subsidiary level 2, and so on (Bronx, Queens, and so on).

Select budget line data rows

The general behavior for selecting data lines to import remains unchanged from the previous improvements made to the import process from Excel. When you select data rows to import, you can choose to mass update your budget by either adding data to new budget lines, overwriting data in existing budget lines that are detected by Planning, or canceling the import for selected budget lines.

For more information, see Import data from Excel to new and existing budget lines.

Auto-assign budget groupings and budget line types

The validation of existing budget lines includes the budget line name, dimensions, actuals accounts, hierarchies, and budget grouping.

• When you import lines from Excel that already exist in your budget with matching budget line names and the budget line is partially mapped (Remember, the more you map the better!), all of the relevant mapped columns (such as dimensions, actuals accounts, and hierarchies) are validated against the existing budget lines. If there’s an exact match, the budget grouping and budget line type are inherited from the existing budget line and the line is updated.

• When you import lines from Excel that don’t match the budget line names of existing budget lines and there’s no mapped budget grouping, the columns that are mapped are validated against the hierarchical and dimensional intersections of the existing lines in your budget. If a match to your budget structure path is recognized, the imported line is automatically assigned the budget grouping that correlates to the P&L budget groupings (Revenue, Operational expenses, and so on) of your existing budget structure hierarchy.

• When you import lines from Excel with no matching budget line name and budget grouping, and more than one hierarchical and dimensional intersections are recognized, Planning uses certain keywords from the budget line name to assign the appropriate grouping and line type. For example, if the budget line name contains the word subscription, the budget line is recognized as Revenue with the Simple subscription budget line type. The keyword assignments work as follows:

Budget line keywords	Automatically assigned budget grouping
Revenue(s), income, subscription(s), service(s), license(s)	Revenue
Expense(s), expenditure, payment, cost(s)	Operational expenses
Other income and expense(s), other, equity, financing, income and expenses	Other income and expenses
Cost of revenue, revenue cost, cogs	Cost of revenues

Manually map budget groupings and budget line types

Again, while it’s best to map budget groupings and budget line types by uploading and automatically mapping them from Excel, you can manually change the automatically assigned budget line groupings and budget line types of new and existing budget lines. Use the budget grouping and budget line type dropdown menus that appear next to the respective budget line data.

Any time you change the budget grouping of a budget line, Planning validates if the budget line structure matches an existing line in your budget. If there’s a match, the line is highlighted in yellow. If there’s no match, the budget line appears as new and is added to your budget as a new line. However, if you leave the budget grouping as is and only change the budget line type, the budget line remains highlighted as an already existing line. The available budget line type options are inherited from the assigned budget group line. For example, available budget line types for the Revenue budget group may be Revenue, Revenue adjustment, Simple subscription, and so on.

You can change automatically assigned and manually mapped budget groupings at any time. Different budget groupings can be selected from the existing list of groupings, or you can select No grouping in which case the budget line type is blank.

Budget lines with no grouping

Budget lines with No grouping can be imported and moved to a budget group after the import process is completed. Simply move the lines to the desired location in your budget tree.

If you import budget lines assigned with No grouping, the budget lines are added to a No grouping subgroup of your budget tree and excluded from your budget data calculations and totals. These budget lines appear as read-only but can be updated once they’re moved to a relevant budget group (like Revenues, Cost of revenues, Operating expenses, or Other income and expenses).

For example, if you move the budget line to the Revenues budget group, the budget line is assigned with the Revenue budget line type and the amounts are imported according to the Different amounts each billing spread.

Troubleshooting

You may find instances where data import from Excel or a budget template is not as you expect.

Here are some common issues that may arise when you import data into your budget:

Issue	Solution
Your data doesn’t match the mapped hierarchy structure	If you want to import a new line into an existing budget, but the budget hierarchy and dimension mappings of the new line don’t match the budget hierarchy and dimensional intersection of the existing budget, then the new line isn’t imported. Conversely, if you want to update an existing budget line, but the data from Excel is mistakenly mapped incorrectly, as long as the mapping matches the budget hierarchy and dimensional intersection of the existing budget, the data is imported to a new budget line. Here again, if the mapping doesn’t match the budget hierarchy and dimensional intersection, then the data line is not imported.
You have two or more identical lines in your existing budget	If you have two or more identical budget lines in an existing budget, the budget line from Excel with identical data can’t be imported into your existing budget.
The budget line type doesn’t match the budget grouping of the data you uploaded	If you select to upload a budget line that is identical to an existing budget line with the correct dimension mapping, but the budget line type doesn’t correspond with the manual or auto-assigned budget grouping, the budget line type is automatically changed to match the budget line type of the existing line. If the uploaded budget line is new (and isn’t identical to an existing budget line), the budget line type is updated to match the default budget line type of the budget grouping.