The Xero Budget Maker is designed to convert budgets for weekly monthly, quarterly annual payments into a budget which can be loaded into Xero. It uses power query and should function in any standard version of excel the intention is that the budget should be set at the item level, one level below the account. The Xero Budget Maker is free and can be downloaded and modified for your own use for the use of other uses.
How does it work? The workbook uses a combination of Excel tables to extend budgets using a calendar and the summarises them by account and month. You can see the tables by using the Excel Menu Formulas->Name Manager. You can see the Power Queries by using the Menu Data-> Queries and Connections. The Account tables have the expenditure Accounts from Xero; these should be checked against your Xero company before you start entering you own budgets. Also note that this data does not have to reside in the workbook, you could read them from an external data source, although care must be taken if an account name is added or changes.
The Budget master is where you enter your actual budget the frequencies that are available are weekly, monthly, quarterly and annual (W,M,Q,A). The Account names use a lookup to the Account Table and the dates are validated to be withing the data parameter table. The code has been tested for a July-June financial year. Againe this table could be external, for instance in a SharePoint list. If you wanted to create a shared budget process you can use Power Query to hook up your data sources. Note there is no fortnightly frequency, this was omitted in favour the only having a weekly frequency, but you could do a fortnightly frequency if you needed to.
The Calendar Master is a table that is not visible on the workbook sheets, but it is present in the Power Query side of the calculations. The Calendar is used to generate separate intermediate queries for each frequency. These intermediate tables are then combined and appear on the Budget sheet and its associated table. As you work on the BudgetMaster sheet, adding budgets, you can also review using the Summary by Account sheet, which has a pivot table. You will need to ‘Refresh All’ on the data tab to see the changes updated. There’s also a summary page with a budget pivot and a graph, have a play here to view your developing budget. When you have finished updating your budget you Xero budget is available on the XeroExpenditureBudget sheet. Now you are ready to transfer your budget to Xero. Grab your budget template from Xero in Accounting->Reports->Financial Performance->Budget Manager, make sure you select for the financial year then Export->Excel. Open the downloaded workbook, click on enable editing. Then cut the value from the XeroBudgetMaker->XeroExpeditureBudget pivot table and ‘Paste Values’ into the correct position on the template. Then import the template back into Xero.
My suggestion is that as you develop a budget, that you create a fresh budget in Xero and then think about overwriting the default budget when you are ready. Download the workbook XeroBudgetMaker Watch the YouTube video
