How to use Budget Writeback
From any spreadsheet, first, start by creating a blank budget worksheet. Click on Budget Writeback -> New Budget Worksheet
You will be asked to select the connection (tenant) 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 (a lot) like the familiar Budget entry screen:
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.
- 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.
Then, click on Budget Writeback -> Import Current Worksheet
If you only want to import specific rows, first select them, then click on Budget Writeback -> Import Budget Worksheet (Selected Rows Only) option
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 that shows us the forecasted revenue and gross margin was added at the bottom of the sheet.
See it in action: