AC-GL-BT5 - Operating Income Budget Writeback LY Actuals

Applies to

• Product version: Velixo NX, Velixo Classic
• ERP: Acumatica
• Functional area: Financials, Budgeting, Other
• Template type: Production Report template

TABLE OF CONTENTS

• Applies to
• Description
• Coverage
• Typical audience
• Features
• Preview
• Download this template
• Documentation
  • Filters
  • Budget Writeback Template structure
  • Allocation type
• User Guide
  • Using the report
• Customizing the template
  • Reviewing the Options sheet
• Version history

Description

The Velixo AC-GL-BT5 Budget Writeback Template optimizes the budgeting procedure for the Acumatica ERP. This solution offers a streamlined and effective approach for generating and uploading budgetary data directly into Acumatica, ensuring a cohesive and efficient budgeting process.

Please see Introduction to Report Templates for information about Samples, Demos, and Production Templates and the differences between them.  You many also want to refer to How to Modify Velixo Sample Reports.

Coverage

This workbook includes the following sheets:

• Budget Writeback: the template to be used for data writeback at account level. Offers multiple allocation type and highly customizable to cater multiple ERP set ups.
• Options: the template’s settings sheet that used for data validation lists and various lookups and configurations.
• Information: the information sheet of the report, used for storing useful details about the report such as: code, version, article link in order to keep track of the origin of the report.

Typical audience

The typical users of this type of report are Finance professionals: Accountants, CFOs, Controllers and also Directors.

Features

The following Velixo features are used in this report. To use this report, please ensure that your Velixo license includes all of them (or contact Support or Sales if not):

• Financial functions
• List functions
• Writeback functions

Preview

Download this template

Download

💡Having trouble downloading the file?

Simply right-click the link and select Save link as

Documentation

Navigating and utilizing the Velixo AC-GL-BT5 Budget Writeback Template is an intuitive process, designed for simplicity, genericity and efficiency.

Filters

Located in the upper left of the sheet, the filters include:

• Branch: lists all branches setup in Acumatica instance including any custom group defined in the options sheet. For ease of use, Velixo shows you the Branch ID that you may be familiar with along with the Branch name. This Display name is configurable in the Options sheet.
• Ledger: as default, this is set to BUDGET where all budget data is uploaded
• Financial Year: year to upload the budget

These filters are seamlessly linked to the data on the Options tab.

Budget Writeback Template structure

Velixo’s Budget Writeback template used the common information required for Acumatica Budget upload. The following information are used by default:

• Account Code: the GL account code to upload the budget figures into. Derived from the range or account class set on user customization columns (Col B or C).
• Subaccount: this is only applicable if the subaccount feature on Acumatica is turned on. 
• Description: name of the corresponding GL code
• Prior year actuals: full year balance from the prior year
• Increase %: percentage of increase from prior year to be applied to the current year’s budget upload.
• Allocation type: drives the calculation of the amount allocation per period.
• Amount: prior year actuals amount plus the increase percentage set (will be the basis of the allocation).
• Distributed amount: total amount distributed for all periods.
• Period 1-12 amount allocation: amount allocation per period

 Writeback Status: this will be the reference status of users when uploading budget figures directly to the Acumatica ERP:

• Pending: the Writeback process has not started yet, and the figures are not yet uploaded to the ERP.
• Line Upload: budget figures were successfully uploaded to the ERP.
• Failed: the writeback process failed and further template modification might be required.

Chart: Velixo added a Bar chart for users to easily visualize the difference between the Prior Year Actuals and the Current Year Budget. Highly customizable as users can change the chart settings to whatever they prefer.

Allocation type

As mentioned, Allocation type drives the distribution of the amount for each period. Based on the selected allocation type, the amounts will be distributed differently. The logic for each allocation type is as follows:

Weighted allocation:

• Takes the calculated Amount of column K (calculated from Prior Year Actuals, including the Increase % specified in column H).
• Allocates the Amount value across all 12 periods in a weighted way based on the percentage of Actuals turnover in the prior year for this period.
• For example: If the total turnover in the prior year is 10,000 and the turnover for January of that year is 1,500, then the applied weight to the January column is: 1,500/10,000 = 15%. The Period 1 figure is then: Amount (column K) * 15%.

Equal allocation:

• Takes the calculated Amount of column K (calculated from Prior Year Actuals, including the Increase % specified in column H)
• Then equally distribute the Amount (column H) across the 12 periods.

Manual allocation:

• Lets users enter their own budget amount in column Y to column AJ
• Note: the red background color applied to cells denote that those cells should not be used 

Multiple allocation types can be used in one upload, across accounts.

User Guide

Using the report

Now that we've covered the report overview, let's dive into a step-by-step guide on effectively utilizing the report.

• Set the Connection Name on the Options Sheet - crucial for all processes. Set the Connection Name on the Budget Writeback sheet by updating cell A2. Ensure it matches the name used during Velixo application login.

• Update the Options Sheet: As the foundation for filters on the Profit and Loss report sheet, keep the Options sheet up to date. It should always and automatically capture the latest dimensions and structures from your Acumatica instance; however, you can also customize it. 

• Choose Desired Filters - After updating the Options sheet, select desired filters for the report:
  • Branch
  • Ledger
  • Financial year

• Automatic Report Update - Selecting any filter will automatically update report balances. Begin analyzing the report with the latest data.

• Input the account code/range or account class for each of the account types (Revenue / COGS / Expenses) on Column B or C. Doing so will automatically update the information on the main Writeback section.

• For each account, enter a percentage value in the Increase % column. If left blank or if the value is 0, the Prior year actuals will be distributed across all periods.

• Select the desired allocation type from the dropdown list.

• Review the generated Forecast figures  by:
  • By ensuring that for each account, the Amount value equals the Distributed amount value

• By reviewing the Forecast amounts for the new year in range E13:E19 and comparing with the Actuals amounts in range F13:F19

• Once you are happy with the values in the Forecast column, you are ready to write this data back to your ERP by clicking the Perform Writeback button and selecting Current Worksheet. 

Note: this process may take several seconds, you can review the status of the operation by clicking the Status button which will open the Status pane.

• After the successful uploaded, go to Acumatica ERP to release the budget uploaded. 
• Once released, run the Velixo NX Refresh to extract the new budget numbers which will then reflect on Column G.

Additionally, users can still go directly to the ERP to verify the success of the upload.

Kindly be informed that all budgets uploaded are currently in an unreleased status upon upload. The release of budgets should be executed directly within the Acumatica platform.

Customizing the template

Reviewing the Options sheet

The Options tab comprises Acumatica dimensions, and other attributes and structures used in the report. This information updates in real-time with Velixo app refresh, facilitating immediate integration of newly added ERP data into the report as filters.

This includes:

• Branch
• Account Class ID and name
• Writeback Status
• Fiscal Year configuration

Users can customize Branch dimension by adding their preferred custom values, using a list and ranges as described in this article.

To do so:

1. collapse the column group for the Branch (Col H)
2. then simply input the desired values in the Group formula and Custom group name columns, highlighted in light blue for quick identification.

Version history