Budget Writeback: create your budget in Excel, upload it back to Acumatica or MYOB Advanced in one click!

This is the 4th instalment of our articles explaining what lies ahead for Velixo. We finished the year with a look back at 2018 for Velixo and our popular Acumatica / MYOB Advanced financial and project reporting tool, and continued with detailed articles on new features in our upcoming release, Velixo Reports 4.0:

• GL Writeback, a new feature that enables you to create allocations, accruals and any type of Acumatica or MYOB Advanced GL entry right from Excel.
• Distribution Lists, a new feature that allows you to quickly distribute reports to a large number of users in a single click.

Today, I’d like to introduce another major enhancement in our product, Budget Writeback. Just like everything else in the product, Budget Writeback was built from the ground up with Acumatica and MYOB Advanced users in mind.

With Velixo Reports 4.0 and Budget Writeback, you can:

• Create a new budget from scratch using zero-based budgeting and push the results back to your ERP.
• Retrieve existing budgets and update them from Excel — budgets can be set by branch and by period. You can also set a single amount that you distribute to the whole year.
• Have your executives send Excel spreadsheets with the budgets items they manage and upload the final budget to your ERP.
• Create a sophisticated budgeting and forecasting model using Excel formulas and the power of the 45+ custom functions added to Excel by Velixo Reports to work with Acumatica and MYOB Advanced data.

Budgets in Acumatica and MYOB Advanced are managed using ledgers, and the system allows you to create as many ledgers (or versions of a budget) as you need. After the release of the budget, the budget amounts are available for reports and for comparison with actual figures or other budgets, whether it’s from Excel or from your ERP.

How to use the new Budget Writeback feature

Easy! From any spreadsheet, first, start by creating a blank budget entry worksheet. We take care of that process for you when you click on Budget Writeback -> New Budget Worksheet

Budget Writeback Menu

The Budget Writeback menu allows you to create a new worksheet with all the fields you need.

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

New Budget Worksheet

We need this information to determine the number of financial periods (and columns) needed for your budget. What you will then see is a spreadsheet that looks (a lot) like the Budget entry screen in Acumatica and MYOB Advanced:

A Blank Budget Worksheet

As you may have guessed, all you have to do at this point is 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.

An Excel workbook can contain multiple budget worksheets, so 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.

Putting it all together

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.

To import the budget, click on “Import Budget Worksheet” in the Excel toolbar. If you only want to import specific rows, select them and use the “Import Budget Worksheet (Selected Rows Only)” option.