Importing budget data from MS Excel can be especially cumbersome when you already have existing, or partially existing data, in your budget. With several enhancements to the data import process from Excel, Sage Intacct Planning (SIP) provides you with options to mass update your budget by either adding data to new budget lines (like today), overwriting data in detected existing budget lines, or canceling the import for selected budget lines. The decision of how to import your data is entirely up to you!

To begin the import of data from Excel, choose and set up the Excel file from which you want to import budget data. SIP automatically detects which lines from Excel already exist in your budget. Existing budget lines are highlighted in the sheet with a message indicating that the highlighted lines already exist in your budget.

While previously in SIP you couldn't update existing budget lines using the import from Excel process, now when SIP recognizes identical budget line names, you can choose which of the existing lines in your budget you want to update with new data.

When you select to import at least one of the lines from Excel already existing in your budget, matching budget line names are identified in SIP and the budget line types of the existing budget lines in your budget are automatically assigned to the imported lines. However, you can change the existing budget line type to a different type from the dropdown. If you change the existing budget line type, the updated budget line type appears in italics.

You can switch back to the default assigned budget line type by either clicking on Choose original value or on the budget line type that appears in the regular text.

📝 During the Data row selection, you may find that you have rows that are unavailable for selection or rows that are available, but the details of the entire line are grayed out. This may happen for a number of reasons according to the background logic in SIP. For example, if you have 2 or more existing budget lines with the same name in your budget, the checkbox to select a new line to import from Excel with the same name as your existing budget lines is disabled. Or, say you select to import a line with the Simple Subscription budget line type. While you can select to import this budget line type as a new line in your budget, the accompanying budget data is grayed out and the accompanying data can’t be imported. However, if you change the assigned budget line type of a budget line that’s been grayed out to Expense, for example, the new data is displayed and you can overwrite the existing data during import.

💡 Keep an eye out for more details on this topic in the future.

As you move to the next step of the Excel import wizard, choose the columns from which you want to import data and how to map the data to your budget line in SIP.

Here, too, the budget lines selected for importing that already exist in your budget are highlighted. Click Next when you’re done.

At this point, once you’ve chosen to import data from Excel to already existing budget lines in your budget, you can now decide how you want to import the data, or you can decide not to import data for specific lines. Your existing data and new data are displayed side-by-side for comparison.

📝 Keep in mind, this step is only displayed if you’ve chosen to import data to already existing budget lines. All of the new budget lines (or lines not recognized as having matching existing budget lines) are imported, but not displayed in this step.

On the left part of the sheet, you can see the existing budget lines in comparison to the new data from the lines that you’ve selected for import. When you select to import data from lines in Excel with matching names to the existing budget lines in your budget, the default action is Overwrite. However, you can change the default action from the Action column.

You can choose which action to perform on each of the individual budget lines from the Action dropdown:

You can enable these actions on multiple selected lines when you select Enable Bulk Action from the vertical ellipsis:

The Budget line type column displays the budget line type of the existing budget line in comparison to what the budget line type will be after the import. If the budget line action is set to overwrite, but the field is not supported by the overwrite process, then although the column might contain values and be mapped, the value is still crossed out and not imported. Additionally, if the budget line action is set to Don’t Import, the new values appear crossed out.

📝 Strikethrough or crossed-out text indicates that these data values aren’t imported when the Overwrite or Don’t Import action is chosen.

According to your previous column selections, additional columns display the values from your existing budget lines in comparison to the values from the new lines you selected to import to your budget.

The columns are displayed in the order they’re imported. The right part of the table displays the fields that haven’t been imported and mapped but do add context to your existing budget lines. These fields include currency, dimensions, comments, tags, start and end dates, and actuals account data. Overwriting or adding new values to the actuals account, dimensions, comments, and tag fields in existing budget lines is not yet supported.

To see the difference between the new data being imported and your existing data, click on Show Differences from the vertical ellipsis. The differences between the existing data and the data selected for import are highlighted.

If there is more than one line in Excel with matching names to the existing budget lines in your budget, the lines are recognized as duplicates and displayed together in the table.

The default action for these lines is Don’t Import and the new values appear with a strikethrough to indicate that the crossed-out values aren’t imported. You can change the action to either overwrite the existing data from one of the new lines (only), or you can add one or more of the lines to your budget as new lines.

📝 If you select to import the Accounts data and map the column to Actuals account, the data from multiple lines that have the same actuals accounts and dimensions is aggregated to one line.

If you select to enable bulk actions, keep in mind that overwriting data in the existing budget line from more than one line is disabled.

If, however, you choose to change the default Don’t import action and select more than one line with the same name to overwrite existing data in your budget, an error message is displayed and you are prompted to select only one line to use in the overwrite process.

When you choose to import data from Excel to existing budget lines, SIP detects the budget line type of the existing budget line and automatically assigns a budget line type to the new line with the overwrite data. As long as you don’t manually change the budget line type, the import process works according to the following overwrite rules:

When you overwrite existing budget lines with the Revenue or Expense budget line type, the amounts in the existing P&L preview are pasted as the base values and converted to the booking or monthly amounts without any payment terms (payment after) that were defined before the import. The values in the Cash preview are recalculated based on the values from the import and the new base amounts of the bookings or monthly amounts. Any advanced settings, such as Payment after, Depreciate expense (for expense budget lines), and Advanced cash and recognition options fields for both Revenue recognition and Expense recognition are zeroed out.

📝 Keep in mind, if you import a monthly value with a negative amount to a Revenue or Expense budget line, the budget line type is automatically changed to Revenue adjustment or Expense adjustment, as applicable.

As with the Revenue and Expense budget line types, the same rules apply to Revenue adjustment and Expense adjustment budget line types when you overwrite existing budget lines.

When you overwrite a budget line with a Revenue from model or Expense from model budget line type, the budget line type is automatically changed to Revenue or Expense, accordingly. In this case, the Model line field becomes irrelevant and the same rules for overwriting existing budget lines that are used in Revenue and Expense budget line types apply to the remainder of the fields.

Let’s say you have an Expense from model budget line type for Benefits - Health Insurance (6000) in your budget that’s based on a model line for HMO online sales with a delayed payment of 3 months. Your Cash preview indicates the first payment in April 2020.

Now, let’s say that you import new data from Excel for the same existing Benefits - Health Insurance budget line. SIP automatically shows the Overwrite action indicating that the existing budget line values are to be replaced by new values. If the budget line action is set to overwrite, but the field is not supported by the overwrite process, then the value is crossed out and not imported. Note, as previously described in the overwrite rules above, the Expense from model budget line type is overwritten by the Expense budget line type. (See Overwriting a Revenue from model or Expense from model budget line type.)

Once you complete the import process, go back to the Benefits - Health Insurance (6000) budget line in your budget. Here you can see your data before the import from Excel and after the import from Excel.

When you import lines from Excel to existing budget lines in your budget, the assigned currency of the budget line might be influenced or updated by the currency of the imported line.

When you import lines from Excel to existing budget lines in your budget, the Date range remains as it appears in the existing budget lines. However, if you do have different data in the Start and End budget date range columns and the columns are mapped to the Date range fields of your existing budget line, then the date range is overwritten with the new data.

📝 Keep in mind, if the amount data from the imported lines is outside the defined date range, the amounts aren’t displayed in the budget line in the Inputs > Main view. To display the amounts data, you can extend the date range of the budget line.

Regardless of how you decide to import (or not to import) new data, we recommend that you choose the Actuals accounts column when you import data from Excel. The Actuals account column connects your budget lines from Excel to an actual account in your budget. So, when you have multiple lines with the same actuals accounts and dimensions, the data is aggregated to one line. Otherwise, the new lines are added as separate budget lines in your budget without aggregating the data.