MSDyn365FO: Deep dive into Budget planning. Columns and Layouts
I have received a bunch of questions about Budget planning with its logic and I am planning to deep dive into one scenario with focusing on Layouts and Columns.
Business scenario:
We have just 4 departments as depicted below.
Finance Planning and Analysis Department being on top of all the other departments: Operations, IT Department, and HR
From a process perspective, following is what we have envisioned.
- Generate a Parent Budget Plan from 2017 transactional data for just 1 ledger Account (600150) + Departure financial dimension combination (104,105,106) in to Initial Baseline Column
- Submit the workflow and copy it to Estimated By FP&A Column.
- Adjust Estimated By FP&A Column by 1 %
- Distribute it to the Departments in to 3 separate Child Department Budget Plans. Display distributed amount in 4 different columns:
- Departure Forecast Q1,
- Departure Forecast Q2
- Departure Forecast Q3
- Departure Forecast Q4
- Departure Forecast Total
- Get Child budget plan adjusted by 1% by the Departments and then approved by the Departments
- Receive them back in to the Parent Plan in an Aggregated form. Display aggregated amounts in 4 different columns:
- Departure Estimate Q2
- Departure Estimate Q3
- Departure Estimate Q4
- Departure Estimate Q5
- Copy aggregated amount into Budget manager approved column to adjust and approve it by Budget Manager
- Copy aggregated amount into CFO approved column to adjust and approve it by CFO
- Have it Reviewed By the Budget Manager and adjusted if needed
- Have it Reviewed By the CFO and adjusted if needed
Let’s get the ball rolling…
Budget plan scenarios
Think of Budget plan scenarios as of containers with the data at the specific stage.
We will comprise of the following budget plan scenarios:
Previous year: in this container we will store amounts from previous year. In will be Initial baseline – starting point for the budgeting.
Estimated by HQ: in this container we will store amounts from previous year and this amount will be adjusted by Headquarter and later on distributed to the departments.
Departure forecast: in this container we will store amounts distributed from HQ.
Departure estimate: in this container we will store amounts adjusted by departments.
Approved by Budget manager: in this container we will store amounts to be reviewed by Budget manager.
Approved by CFO: in this container we will store amounts to be reviewed by CFO.
Budget planning stages
We came up with the following budget planning stages:
Initial baseline: at this stage we will receive amounts from previous year and it is the initial stage when parent budget plan is created.
Departure estimated: at this stage child budget plans are created.
Copied: at this stage we will copy previous year amounts into Estimated column to be adjusted by HQ.
Departure submitted: at this stage child budget plans adjusted and approved.
Aggregated: at this stage amounts aggregated into parent budget plan.
Reviewed by Budget manager: at this stage amounts will be reviewed by budget manager.
Reviewed by CFO: at this stage amounts will be reviewed by CFO.
Budget planning workflow stages
We will have 2 workflows:
- Department workflow (Dep-Budget-Plan)
- Headquarter workflow (FinanceV2)
Below you might find the stages for the Department workflow
And the stages for the Headquarter workflow
Stage allocations
Initial baseline stage – Copied Estimated allocation: we will copy previous year amounts into Estimated by HQ column.
Copied stage – Allocate across departments: we will copy data from the Estimated by HQ column and allocate them into 4 departure forecast columns. This information will be used as a base for the distribution to the department, but also will be visible on the parent budget plan.
Copied stage – Distribution: we will distribute previous year amounts from parent budget plan into child budget plan. If we don’t do it, Previous year column will be blank on the Child budget plan.
Copied stage – Distribution forecast: we will distribute estimated by HQ amount from parent budget plan into child budget plan. If we don’t do it, Estimated by HQ column will be blank on the Child budget plan.
Copied stage – Distribute departure forecast: we will distribute departure forecasted amounts from parent budget plan into child budget plan. If we don’t do it, 4 Forecasted columns will be blank on the Child budget plan.
Aggregated stage – Aggregated budget manager: we will aggregate amounts from child budgets to the parent budget into Approved budget manager column. This information will be used as a base for the adjustment by Budget manager.
Aggregated stage – Aggregated from Departments: we will aggregate amounts from child budgets to the parent budget into 4 department estimated columns. This needs to be done in order to see in the parent budget plan what was initially forecasted and what was estimated by departments.
Reviewed by Budget manager stage – Allocate across periods CFO: we will copy data from the Approved by Budget manager column and allocate it into CFO approved column. This information will be used as a base for the adjustment by CFO.
Allocation schedules
Copied Estimated allocation. This allocation schedule will be used at the Initial baseline stage and we will copy previous year amounts into Estimated by HQ column.
Period key 40 means that we will copy data without changes.
We will take the data from Previous year container and copy into Estimated by HQ container.
Allocate across departments. This allocation schedule will be used at the Copied stage and we will copy data from the Estimated by HQ column and allocate them into 4 departure forecast columns.
Distribution: we will distribute previous year amount from parent budget plan into child budget plan.
Distribution forecast: we will distribute estimated by HQ amount from parent budget plan into child budget plan.
Distribute departure forecast: we will distribute departure forecasted amounts from parent budget plan into child budget plan.
Aggregated budget manager: we will aggregate amounts from child budgets to the parent budget into Approved budget manager column.
Aggregated from Departments: we will aggregate amounts from child budgets to the parent budget into 4 department estimated columns. This needs to be done in order to see in the parent budget plan what was initially forecasted and what was estimated by departments.
Allocate across periods CFO: we will copy data from the Approved by Budget manager column and allocate it into CFO approved column. This information will be used as a base for the adjustment by CFO.
Columns
This setup is used to define the columns that will be available in the budget plans. In our scenario we will have the following columns:
For each column you have to define from which container (scenario) you would like to take and display the data in the column.
Initial baseline column will be populated once you generate parent budget plan. Upon budget plan generation you will specify the scenario where you would like to store the data and as the result here we will take that scenario to display amounts.
Once we submit Parent budget plan workflow, we will copy amounts from Initial baseline column into Estimated by FPY1 column. Later on HQ can adjust Estimated by FPY1 amounts and those amounts will be used for the distribution to the departments.
We will have 4 departure forecasted columns (Q1,Q2,Q3,Q4). These columns will be populated once HQ approve workflow after adjustments of the Estimated by FPY1 amounts.
Departure forecasted total column will display total amount of departure forecasted columns (Q1,Q2,Q3,Q4).
We will have 4 departure estimated columns (Q1,Q2,Q3,Q4). These columns will be populated once child budget plans have been approved. As the result we will see:
departure forecasted columns – what was initially forecasted
departure estimated columns – what was estimated by departments
Budget manager approved column will be used to adjust estimated amounts by department. This column will be populated once we approve child budget plan and those amounts in the aggregated form will be set in this column.
CFO approved column will be used to adjust department estimated amounts by CFO. This column will be populated once we approve child budget plan and those amounts in the aggregated form will be set in this column.
Layouts
This is a configuration of columns that will be displayed in the budget plan.
Continued…
Below you can find the budget planning process setup.
Budget plan workflows
In order to cover this scenario, two workflows have been created.
Headquarter workflow presented below
Department workflow presented below
We are done with the configuration. Let’s review the process itself.
Generate budget plan from general ledger:
Parent budget plan has been created.
Open parent budget plan and review the amounts transferred from 2017 year from 600150 account.
As you can see the list of columns based on the Layout.
Submit the workflow.
Notice that stage has been changed from Initial baseline to Copied. At this point we have copied amounts from Initial baseline into Estimated by FP column.
You might notice that child budget plans already created, but if you open child plan it will be blank. It’s because distribution of the amount didn’t happen yet.
Go back to the parent budget plan. Keep in mind that Refresh of the form will not refresh the data, you will have to reload the page.
You will notice that amounts were copied and you can adjust it.
We will slightly adjust it.
Click Workflow -> Complete.
Don’t forget to refresh the form / reload the page.
Once workflow task executed, Estimated by FP column allocated across periods and Forecasted columns are populated.
At this stage, amounts from parent budget plan will be distributed into child budget plans.
Adjust amounts in the Child budget plan 1.
Adjust amounts in the Child budget plan 2.
Adjust amounts in the Child budget plan 3.
Click Approve for each of 3 budget plans.
Wait until approval.
Review the status of the child budget plans. It should be approved.
Refresh the form / Reload the page.
Go to the Parent budget plan and Complete workflow. After that all amount from Child budget plan will be aggregated.
Review the parent budget parent plan.
As you can see the first 7 columns remain the same, but the last 6 columns display aggregated amounts from Child budget plans.
Adjust the CFO approved column.
Click Approve.
If you need additional approval step by Budget manager, you just simply need to add this task into Headquarter workflow.
Review the status of the Budget plans.
That’s it!
