SI-GL-BT6 Balance Sheet Budget Writeback LY Actuals

Applies to

• Product version: Velixo NX, Velixo Classic
• ERP: Sage Intacct
• Functional area: Financials, Budgeting, Other
• Plan: Free, Essentials, Professional, Advanced
• 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
  • Reviewing the Options sheet
• Version history

Description

The SI-GL-BT6 Balance Sheet Budget Writeback LY Actuals optimizes the budgeting procedure for the Sage Intacct ERP. This solution offers a streamlined and effective approach for generating and uploading budgetary data directly into the Sage Intacct platform, ensuring a cohesive and efficient budgeting process. 

Coverage

This workbook includes the following sample reports:

Typical audience

The typical users of this type of report are Finance professionals: Accountants, CFOs, Controllers 

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

Preview

Download this template

Download

Documentation

Navigating and utilizing the SI-GL-BT6 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:

• Location: lists all entities present in Sage Instance (excluding groups) including any custom group defined in the options sheet. For ease of use, Velixo shows you the Entity id that you may be familiar with along with the entity name. This Display name is configurable in the Options sheet.
• Department: lists all departments present in Sage Instance (excluding groups) including any custom group defined in the options sheet. For ease of use, Velixo shows you the Department id that you may be familiar with along with the department name. This Display name is configurable in the Options sheet. 
• Source books: lists every type of books defined in your Sage Instance in a dropdown list. The report defaults to the Accrual book if none-selected.
• 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

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 Sage Intacct 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 A).
• 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.
• 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 Sage Intacct 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 H (calculated from Prior Year Actuals, including the Increase % specified in column E).
• 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 H) * 15%.

Equal allocation:

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

Manual allocation:

• Lets users enter their own budget amount in column V to column AG
• 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:
  • Location: select an item from the dropdown list
  • Department: select an item from the dropdown list 
  • Source book:  select an item from the dropdown list, or enter a formula such as ACCRUAL;GAAP (to combine amounts from the Accrual and GAAP books)
  • Budget ID: any "Budget ID" text that exists or should be created in Sage Intacct
  • Description: any Budget description to upload to Sage Intacct
  • Budget year: the fiscal year of the budget

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

• 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:
  • Ensuring that for each account, the Amount value equals the Distributed amount value (Except for Manual Allocation type)

• Reviewing the Forecast amounts for the new year in range C15:C21 and comparing with the Actuals amounts in range D15:D21

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

Customizing the template

Updating the Account ranges

• Expand column D to reveal the two columns, B and C.

• On columns B and C, specify the Account category or the Account code/range for each of the account type (Revenue, COGS, Expenses). Please note that as a default, Velixo set the QuickStart categories on Column B.

• The inputs specified on Columns B and C will be the basis of the Account column on the Budget upload table.

Reviewing the Options sheet

The Options tab comprises Sage Intacct 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.

• Location
• Department
• Budget ID
• Books

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

To do so:

1. collapse the column groups for either the Department or Location. 
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