Enhanced Excel Budget Planning Templates (Offline Use)
- Wilmarié Moreland
- Harshula Jhala
This page covers:
The Enhanced Excel Budget Planning Template
The Excel Upload/Download feature allows you to download Budget and Financial Line Item records to an Excel sheet, make changes offline, and re-upload the templates.
The Budget Planning Template is used to plan revenue and expenditure (Financial records) for the Budgets by time period. It allows users to plan for multiple time periods at once.
Remember to first create a Budget record and assign the planning periods to the Budget in Amp Impact before proceeding with this step.
The Enhanced Budget Planning Template is only available in Jarvis (v1.32) or higher. Contact your System Administrator for more details.
Download & Complete the Budget Planning Template
To download a Budget Planning Template, click on the “Download Budget” button on the relevant Budget record.
Note: Double-check that “Budget Planning Periods” have been filled out before attempting to download the template.
2. When opening the downloaded Excel file, there are three tabs: “Instructions”, “Reference List” and “Budget name”.
a. Instructions: This tab outlines how to enter data in a compatible format to upload the data onto the Budget page.
Note: Instructions reflect any customizations made by the System Administrator using Custom Labels and Translation Workbench.
b. Reference List: This tab shows allowed values for picklist/relationship fields in the "Budget Name" tab. Get options for lists like "Activity", "Type", "Category", "Subcategory", and custom fields.
c. Budget name: This tab displays a template with fields from the Financial object as columns. If there are existing financial records linked to the budget and belonging to multiple reporting periods, they will be displayed here; otherwise, this will download as a blank template. If the Budget name exceeds 80 characters, it will be truncated to 27 characters followed by 3 dots/dashes, and displayed as the tab name.
Opening Tab 3 (Budget Name) displays a header with the name of the Project and the name of the Budget. The row just below this header displays the date of download and the name of the user who downloaded the file. This is how the template looks with 2 types of columns:
Attribute Columns: These columns are different from the rest because they don't belong to a specific timeframe or Reporting Period. Instead, they are used to capture general, static information about your financials, such as the Description or Category. To make sure your budget data is accurate and complete, please enter this information in the designated attribute columns.
Reporting Period Columns: These columns are used to capture specific information about your financial data that changes with each Reporting Period, such as planning costs, planning quantities, and other relevant data. Each Reporting Period has its own set of columns to track the data for that period. These Reporting Periods are derived from the information selected in the “Budget Planning Periods” field in this section.
If no financials exist with your budget, you will see an empty file as seen in the screenshot below.
However, if financials exist, a populated template with the financial records will be grouped in the following way is seen:
Financial records with the same general information (like Type or Category) but different periodic information (like Amount Planned) will appear on a single line. The periodic information will be shown in the appropriate reporting period column, while the general information will be displayed only once.
Financial records with different attribute information will appear on separate lines in the Excel table, with their periodic information under the relevant reporting period column.
Blue cells are data entry cells. Additional input cells can be added by dragging the blue cells. Users also have the ability to:
Add additional worksheets to segment and streamline data analysis;
Employ macros and save the template as a macro-enabled Excel file to automate any workflows;
Hide irrelevant out-of-the-box columns in the template;
Apply custom formulas to perform specific calculations on data;
Add custom columns to the Budget tab with information for analysis that won't be uploaded to Salesforce
All numeric data will display in US English format. Regardless of the currency locale enabled in Salesforce settings, all downloaded budgets will display in this format. Guidelines are provided in the Instructions tab to edit the Excel settings to view numeric data in the format of preference.
Upload the Budget Planning Template
To upload a planned budget, navigate to the Budget record and click on the “Upload Budget” button.
A popup with the option to attach a file will appear.
Click on the “Attach File” button to select a file from the system.
On the popup, browse and select the Excel Planned Budget file to be uploaded and click on the “Open” button to attach the file.
After attaching the file, click on the “Upload” button to upload the planned budget. You can click on “Cancel” at any time and return to the Budget record page, with no records being updated/uploaded.
And there you go! It’s done!
Click the “View Successes” button to view the successful uploads.
Click the “View Errors” button to view the failed uploads.
Click the “Close” button to return to the Budget record page and navigate to the “Financials” tab and refresh the page to view the newly updated or uploaded financial records. If you cannot see the new Financial records, try refreshing the page.
Check if a Budget is Approved/Locked
When a Budget is locked, users will be prevented from uploading it. When users click on the “Upload Budget” button they will get the following message.
When Budgets(s) are submitted and locked via the “Submit and Lock Wizard” users will see the same message shown above. This Wizard is available from Mira Release (v1.37) onwards
Troubleshooting Common Issues
Validation
While processing the Excel file during upload, validation is built-in to check for:
invalid file (e.g. budget file for the wrong record is selected),
data entry (e.g. currency code entered in a number field),
metadata (e.g. user renames a column header).
Users are provided with the full list of validation checks in the “Instructions” tab.
Deleting vs Clearing Record Data
If a user downloads a Budget Excel file and clears out existing data for a financial line item, then the upload process will clear information from the existing record. However, it will not delete the record.
No Financial Records After Uploading?
No new financial record will be created if the columns intended to capture specific information about your financial data, such as planned quantity and amounts, for the reporting period (with headers highlighted in dark grey) are left blank.
Make sure there are no blank lines in the Excel file. Any line item after a blank line will not be uploaded.
Data Quality
Make sure that the column headers for the planning periods in the prepared budget align with the periods selected in the budget setup step.
Ensure that a valid tab for financials exists which is named after the corresponding Budget and the header of this tab has not been altered.
Ensure any custom columns added to the template have unique headers.
Review Success/Error Logs
Downloaded success/error logs are stored as ContentVersion files on the Budget record's Files list and attached to a Chatter Post on the record detail page. They're updated with the last download date and owner's details, and previous versions are available.
Limitations
The enhanced budget and financial report templates allow for seamless management of budgets planned for and reported at different frequencies and are compatible with multiple budgets within a single project. It is important to note, however, that this feature has limitations in accommodating multiple budgets planned for different periods but reported at the same period, as of Jarvis(1.32). In such scenarios, the full benefits of the feature may not be realized.
To provide an example, imagine a scenario where costs are planned annually and reported monthly as part of an Annual Budget, along with a Quarterly budget in the same project where financials are planned quarterly but reported monthly. In this case, downloading a financial report during a monthly reporting period will not have prefilled financials from both budgets. This is because, at any given time, a reporting period can only look up to a single reporting period record, which can either be the quarter or the year in this situation.