How to use Budget Writeback
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.
Fill in the blanks and enter your budget. Here’s a description of all the different settings:
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 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 budget article amount.
Distributed Amount: the amount distributed over the periods. For the budget article to be released, the distributed amount should be equal to the value in the Amount column.
Period 01 through Period N: columns reserved for distributing the article amount over the periods of the year. Each column contains the budget article’s amount for the respective period in the budget year.
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.
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:
Velixo version 6.0.410 and higher support Budget Writeback feature when connected to your ERP using single sign-on
Note: Using Budget Writeback with your ERP's multi-factor authentication requires that the ERP's single sign-on ("OAuth2") feature also be enabled.