Edit G/L Budgets in a Spreadsheet inside Business Central (Advanced Spreadsheets and Reporting)
Editing Business Central G/L budgets in a spreadsheet using the Advanced Spreadsheets and Reporting app: budget regions, formulas that update budget ledger entries, comparison periods, dimension details and access control. Demoed in a sandbox; the app is on AppSource and free to try in sandbox.
- Introduction and Overview
- Creating and Configuring a Budget Region
- Setting Up Sales Budget Parameters
- Editing Budget Data with Formulas
- Working with Multiple Regions and Compare Periods
- Configuring Comparison Columns and Formatting
- Adding Dimension Details to Budgets
- Access Control and AppSource Availability
Overview
Erik Hougaard shows how to edit G/L budgets in a spreadsheet inside Business Central. A budget region is an area of the spreadsheet linked to Business Central data, so Excel habits such as formulas carry over. He creates a region, sets up sales budget parameters, and enters formulas in budget cells.
He then works with multiple regions and a comparison column that shows actuals from the same period one year before, formatted through date formulas. He adds dimension details so the budget gains lines per dimension, and closes with access control for delegating budget entry and AppSource availability.
Key points
- A budget region is an area of the spreadsheet linked into Business Central. When you create one, you choose the G/L budget, company, period (e.g. month), date range, accounts, an optional compare column and sign reversal.
- Typing a value in a budget region cell updates the budget. Ctrl-clicking a region cell opens the data source, the budget ledger entries.
- Formulas typed into budget cells stay in the spreadsheet, while the calculated result is written to the budget.
- A compare column can show actuals from a compare period. By default it shows the same period 1 year before, and a custom compare date formula (e.g. -2Y) can be set.
- The comparison column can be given its own color (e.g. gray) to tell it apart from budget values.
- Budget regions can add details for a specific dimension, which adds budget lines per dimension. After changing settings, a recalculation is needed.
- Access to a specific spreadsheet can be granted to users, e.g. so someone in sales can fill in the budget.
Features
| Feature | Status | At |
|---|---|---|
| Budget regions in spreadsheets | status not stated, demoed | 0:48 |
| Budget formulas in spreadsheets | status not stated, demoed | 3:15 |
| Comparison periods for budgets | status not stated, demoed | 5:08 |
| Dimension support in budget regions | status not stated, demoed | 7:04 |
| Access control for spreadsheets | status not stated | 8:35 |
AL objects mentioned
As heard in the captions. A name that matches one object page by exact type and name links to it; the others stay as named.
- table “Budget Ledger Entries” at 3:09
Not found in BC28-30: table “Budget Ledger Entries”.
Quotes
- 0:25 “all your muscle memory from Excel is is working”
- 0:48 “region is an area of the spreadsheet that is linked into Business Central”
- 3:31 “the formula now lives in the spreadsheet, but the result lives in the budget”
- 5:44 “we’ll show the uh the month uh the same period 1 year before”
- 7:30 “the budget has gained lines from the dimensions”
Disclaimers in the video
- 0:14 preview: Free to try in the sandbox
- 9:01 other: The app is available in AppSource. Uh it’s free to use in sandbox.
Presenters (as heard): Eric.