Using Budget Writeback

Created by Harry Lewis, Modified on Fri, 05 Apr 2024 at 07:09 PM by Harry Lewis

Applies to:

Velixo NX
Velixo Classic
Acumatica, MYOB Advanced, Cegid XRP Flex

TABLE OF CONTENTS

How to use Budget Writeback
Example
Troubleshooting
- Tip1
- Tip 2

How to use Budget Writeback

Step 1

Start by creating a blank budget worksheet. Select Budget from the New Sheet button pulldown:

You will be asked to select the (tenant-specific) connection and the financial year for which you want to create a budget worksheet:

This information is needed to determine the number of financial periods (and columns) required for your budget. You will then see a spreadsheet that looks similar to the familiar Budget entry screen in your ERP:

With Velixo versions prior to 6.0.410, you are required to use this grid. Later versions support both the grid format as well as the WRITEBACKBUDGET function with any format.

Note: While the default layout for the New Budget Writeback sheet is created for a single ledger / branch / year, the WRITEBACKBUDGET function supports multiple ledgers, branches, or years at once from a single worksheet.

Step 2

Fill in the blanks and enter your budget. Here’s a description of all the fields/columns:

Connection: the name of the connection, as configured in the Connection Manager. This is the instance/tenant where your budget will be imported, and the same name you use in your functions.
Branch: the branch whose budget you are creating. If you don’t use branches, leave this blank.
Ledger: the ledger of the Budget or Statistical type that stores the budget
Financial Year: the financial year of the budget.
Automatically Release: whether or not the budget should be released automatically after import.
Note: in order for budget values to be released for any financial year, all financial periods for that year must be defined and unlocked within the ERP (even if no budget changes are being made to the locked periods)
Account: the account of the budget article.
Subaccount: the subaccount of the budget article. If you don’t use subaccounts, leave this blank.
Amount: the amount to be distributed over the periods.
Distributed Amount: the amount that has been distributed over the periods.
Period 1 through Period N: columns reserved for distributing the amounts over the periods of the year. Each column contains the budget article’s amount for the respective period in the budget year.

Step 3

When you are ready to send your budget data to your ERP, click on Perform Writeback on the Velixo ribbon:

If you only want to select a single (or multiple) worksheets, ensure that worksheet is displayed and select Current Worksheet from the pulldown on the Perform Writeback button:

If you only want to import specific rows, first select them, then click Selected Cells or Worksheets from the Perform Writeback pulldown.

An Excel workbook can contain multiple budget worksheets. If you have multiple tenants, business units, or budgets versions, you’ll likely want to have different tabs with different names.

Your budget can leverage the functions that Velixo adds to Excel such as ACCOUNTTURNOVER(), ACCOUNTTOTALDEBITS(), and ACCOUNTTOTALCREDITS(). You can even use project-related functions, or any of the standard Excel functions.

Example

Here’s a completed, albeit quite simple, example:

A few columns and formulas were added to this budget:

2018 Actuals are retrieved using the ACCOUNTENDINGBALANCE() function.
A Description column was added to show the account name.
An Adjustment column was added to allow us to enter the expected % increase for 2019.
The Amount column is calculated automatically based on the 2018 Actuals and the expected increase and is distributed equally to all 12 periods.

Finally, a chart showing revenue and gross margin forecasts was added at the bottom of the sheet.

See it in action:

Notes: 

As of version 6.0.410, Velixo Classic supports the Budget Writeback feature when connected to your ERP using single sign-on.  This requires that the Velixo Acumatica Customization Project be published in the tenant being accessed.

Using Budget Writeback with your ERP's multi-factor authentication requires that the ERP's single sign-on ("OAuth2") feature also be enabled.  

Enabling OAuth2 does *not* require an SSO/Advanced Authentication License with Acumatica

Troubleshooting

Tip1

Please note that using functions to compute the values for the periods in the writeback worksheet can result in rounding issues. e.g., If the amount to be budgeted over three periods is 100 and each period contains a formula such as =total/3, the amount for each will be 33.3333333333. The total amount budgeted will be 99.9999999999 (which does NOT equal 100).

Tip 2

Blanks and zeros are NOT the same! If you enter a blank for a budget value, this can prevent the writeback from being able to process:

("One of the amounts has been omitted or left blank. Please double-check any empty cells or redundant commas in your function")