Enhanced Excel Financial Reporting Template (Offline Use)

This page covers:

The Excel Budget Reporting 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 Reporting Template is used to report on actual revenue and expenditure (Financial records) for the Budgets by time period.

Excel Budget Reporting Template

Download & Complete the Budget Reporting Template

1. To download a Budget Reporting Template, click on the “Download Financial Report” button on the relevant Reporting Period record.

   Open

   

2. When opening the downloaded Excel file, there are three tabs: “Instructions”, “Reference List” and “Reporting Period Name”.

Open

a. Instructions: This tab outlines how to enter data in a compatible format to upload the data onto the Reporting Period record.

b. Reference List: This tab shows allowed values for picklist/relationship fields in the "Reporting Period Name" tab. Get options for lists like "Activity", "Type", "Category", "Subcategory", and custom fields.

c. “Reporting Period name”: This tab displays a template with column fields from the Financial object. If there is existing data in the Budget record, it will display here; otherwise, this will download as a blank template. If the Reporting Period 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 (Reporting Period Name) displays a header with the name of the Project and the name of the Reporting Period. 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 3 types of columns:

• Attribute Columns: These are intended to capture information about financial line items under a budget linked to the Reporting Period, such as a description or category.

• Budget Column: For existing financial records, the cells in this column appear in white and display the budgets that contain the financial items in the template. When adding a new financial record, select a value from the options listed in the “Reference List” tab for the “Budget” column. You should not delete this column, but you can hide it if you prefer.

• Actual Columns: These are designated by headers highlighted in dark grey, and are meant to capture information on the financial line items entered at the time of reporting for the chosen period, such as actual amounts.

The data available in the Excel template will depend on the initial data setup:

• If the Reporting Period is linked to financial records and is added as a Budget Reporting Period, the linked financials will appear as line items in the downloaded Excel template.

• If the Reporting Period has no financial records linked to it, the template will show attribute information from the linked Budget's planned financials. This pre-populates relevant financials to report on. This applies only if the correct reporting period hierarchy has been set up and the parent Reporting Period record is a Budget Planning Period in the same budget where the selected Reporting Period is included as a Budget Reporting Period. (Refer to this section of this document for more information)

• If neither the selected Reporting Period nor its parent have any linked financial records or are not used for reporting and planning, the template will download as a blank file.

• Dark grey cells are data entry cells where financial reporting can occur.

• Blue cells contain existing financial information downloaded from Amp Impact and can be modified or additional information added. To add more input cells, drag the blue cells down a column, ensuring they follow the same format as the existing 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.

Upload the Budget Reporting Template

1. To upload a reported financials, navigate to the Reporting Period record and click on the “Upload Financial Report” button.

2. A popup with the option to attach a file will appear.

4. Click on the “Attach File” button to select a file from the system.

5. On the popup, browse and select the Excel Reported Financials file to be uploaded and click on the “Open” button to attach the file.

5. After attaching the file, click on the “Upload” button to upload the reported financials. You can click on “Cancel” at any time and return to the Reporting Period 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 Reporting Period 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.

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. 

Ensure that there no extra spaces or trailing spaces in cells to avoid validation errors.

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.

When a Budget is locked, users will get an error upon uploading in the error log.

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 that 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.