BC-GL-BT9 Balance Sheet Budget Writeback LY Actuals

Applies to

• Product version: Velixo NX
• ERP: Dynamics 365 Business Central
• Functional area: Budgeting, Other
• Plan: All
• 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
  • Updating the account ranges
• Version History

Description

The BC-GL-BT9 Balance Sheet Budget Writeback LY Actuals optimizes the budgeting procedure for the Dynamics 365 Business Central. This solution offers a streamlined and effective approach for generating and uploading budgetary data directly into the Business Central platform, ensuring a cohesive and efficient budgeting process. 

Coverage

This workbook includes the following sample reports:

• 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 is 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, and the 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 features are used by this report. To use this report, please ensure that your Velixo license includes all of them, (or contact Support or Sales):

• Financial functions
• List functions
• Budget Writeback
• Query 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 BC-GL-BT9 Balance Sheet Budget Writeback LY Actuals is an intuitive process, designed for simplicity, genericness and efficiency.

Filters

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

• Budget Dimension Configuration: defines the set of filters that determine which Budget Dimensions will be used. Similar to Dynamics 365 Business Central, up to four dimensions can be specified.
• Budget Dimension Values: defines the values applied to the dimensions identified in the Budget Dimension Configuration. The Global Dimensions from the General Ledger Setup are included by default.
• Budget Writeback Values: contains the main header filters needed to create or update a Budget ID. Included filters are:
  • Budget ID: for usability purposes, Velixo lists all of your Budget Ids in a dropdown list. And since this is a Writeback template, user can add a new Budget ID and Velixo will upload it to the ERP.
  • Description: simply the description of the Budget ID upload
  • Budget year: year to upload the budget
  • Update mode: defines how the writeback behaves when the process is initiated. Two options are available:
    • by-natural-key (default): zero the budget entries with the same Account, Date, Dimensions then create new ones.
    • recreate: set to zero all budget entries for the budget, and create new ones.

For the update mode "by-natural-key":
 - if a single entry matching the natural key is found, it will be updated.
 - if multiple entries share the same natural key, one will be updated with the new value, and the others will be set to zero.
 - if no matching entry is found, a new one will be created with the specified value.

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 a Business Central 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 set on user customization column (Col D).
• Account 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.
• Line description: the note assigned to each of the budget entries.
• 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.
• Period 1-12 manual amount allocation: manual allocation per period and will be applied to the template if the selected Allocation type is Manual Allocation.

Writeback Status: this will be the reference status of users when uploading budget figures directly to the Business Central 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 L (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 L) * 15%.

Equal allocation:

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

Manual allocation:

• Lets users enter their own budget amount in column Z to column AK
• 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 Options sheet by updating cell A2. Ensure it matches the name used during Velixo application login.

• Choose Desired Filters:after updating the Options sheet, select desired filters for the report:
  • Set Dimensions on the Budget Dimension Configuration
  • Set Dimension values for the Global and Budget Dimensions
  • Populate the Budget Writeback Values:
    • Budget ID: any "Budget ID" text that exists or should be created in Business Central
    • Description: any Budget description to upload to Business Central
    • Budget year: the fiscal year of the budget

Note: Any dimensions and dimension values defined in the filter are applied globally across all accounts and dates under the Budget ID. To exclude dimensions, leave all dimension-related filters empty.

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

• Reviewing the Forecast amounts for the Budget Year in range F22:F30 and comparing with the Last Year Actuals amounts in range G22:D30

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

• Once successfully uploaded, run the Velixo NX Refresh to extract the new Budget numbers which will then reflect on Column H.

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

Customizing the template

Updating the account ranges

• Expand column E to reveal the column D

• On column D, specify the the Account code/range for each of the account type (Accounts Receivable, Accounts Payable, etc.). Please note that as a default, Velixo set the account codes based on the Business Central demo instance.

• The inputs specified on Column A will be the basis of the Account column on the Budget upload table.

Version History