Financial reports and consolidation
Budgets with write-back
Open a G/L budget in a sheet, start from last year's actuals, change it by a percentage, type amounts and formulas, then check and write it back to the budget.
updated: applies-to: 1.0.0.0
Build a G/L budget in Business Central the way you would in a spreadsheet, with accounts down and periods across, then write it straight back to the budget.
Open a budget in a sheet
- Search for G/L Budgets, select the budget and choose Open in Sheet.
- A workbook opens with the whole period: a line per account, a column per month and a total, with period and dimension slicers above it.
- In the budget's Settings, set the G/L accounts you budget, so every line is one you plan.
You can also insert a budget into any workbook: on the Insert tab, choose Budget.
The settings choose the budget, the period, the columns (day, week, month, quarter, year or fiscal period), the G/L accounts and an optional line per dimension value. Options show revenue as a positive number, show zero as blank, and lock closed periods.
Start from last year
- Open the menu on the budget's title bar and choose Copy or Change Budget.
- Choose Copy from Actuals, and a Round To value such as 100.
- Preview Changes lists every budget entry before anything is written. Choose Apply Changes.
Change by Percentage raises or lowers the selected lines by a percentage, with the same preview. Select the sales and cost lines together to keep the margin.
Type amounts and formulas
Type an amount in any month, or a formula such as =3150*1.04, and fill it across the year. Total lines are read-only sums of the accounts below them.
Review, check and apply
- Choose Review changes. The panel lists every changed amount.
- Choose Check changes. Business Central's own checks run without saving anything.
- Choose Apply to write the amounts to the budget.
When one budget entry matches the cell, its amount is changed. Otherwise an entry for the difference is added at the start of the period, with the dimension values you filtered on.
Compare with actuals
In the settings, turn on Compare with actuals. Actuals of a period you choose, last year by default, appear next to each period of the plan.
In Business Central
The G/L Budget Entries hold every amount you applied, with the description Entered in a sheet.
Department budgets
Let each department head enter their own budget, and change nothing else.
- Open the Sheet Template Gallery and choose Department Budget Entry.
- Pick the budget, check the period, and list the expense accounts your departments plan. Every account gets a line per department, with last year's actuals beside each period. Switch the columns to quarters to see the whole year on one screen.
- Select a department's line and choose Spread Budget Amount. Like Prior Year Actuals follows last year's pattern, and Evenly splits the amount over the periods. The preview shows every amount before you apply it.
Then share the workbook with the people who use Sheets, through their permission set, and choose Can edit budget amounts only. In Business Central, every amount lands on the budget with its department.
You can also set A line per value of to a dimension in any budget sheet. A dimension filter with a single value is stamped on the new budget entries.
Who can change the budget
Share a workbook with Can edit budget amounts only to let a person change budget amounts and nothing else. Budgets from another company are read-only.
What's next
Financial reports and consolidation: the rest of the chapter.