Case: automation of budgeting by PL items.
Case description:
- monthly preparation of stand-alone budgets for over 50 departments and manual consolidation to a single file led to high labor costs for budgeting.
- manual calculations of amounts for all items made the budgeting time-consuming.
- missing opportunity of centralized update of rates and other reference data, common for all departments, and missing clear mechanism of controlling the source data relevance for the reporting period.
- missing planned-actual figures for comparison at hand, which affect the quality of budget decision-making, forced the management to manually extract figures of prior periods, in order to review and validate budgets for current period.
- missing user documentation.
End-to-end budgeting process took at least 3 working days of management staff.
Implementation results (obtained value):
|
Automation enabled significant cut of labor costs for the budgets’ initial preparation and update upon each iteration of approval. End-to-end budgeting process now takes only 1 day. |
|
Calculations made flexible and adaptable to the addition of new departments. |
|
Centralized control over the general indicators by means of directory tables enhanced and simplified the procedure. |
|
Enabled access to planned-actual data for comparison facilitated and increased the accuracy of management budgeting decisions. |
|
Enabled manual edits for manual entry in special fields leave a clear audit trail. |
Transparent and structured calculations, accompanied by respective “step-by-step” instructions, allow easy and efficient transfer of the functional responsibilities to a new user without significant costs. |
Suggested technical solution:
The budgeting process structure is as shown below:
- Automated template generation for each department using a custom “FIN.by Tools” add-in.
At this stage the following automated calculations are performed:- the amounts for items with clear calculation methodology, based on rates, depending on the department type, specified in the template;
- planned / actual figures from prior periods for comparison purposes.
- Separate areas for manual entries allow to:
- insert the amounts, based on unclear methodology;
- adjust the automated calculations results, if required in case of unforeseen situations.
- control over manual entries.
- Separate department budgets are automatically consolidated into a united table by clicking specific buttons on the custom Excel add-in “FIN.by Tools” (the consolidation is designed using Power Query).
Separate department budgets are automatically consolidated into a united table by clicking specific buttons on the custom Excel add-in “FIN.by Tools” (the consolidation is designed using Power Query).
- Implemented system of directories and mapping tables allows flexible setup of the indicators, applicable for all departments, before generation of the templates.
Area of responsibility for the update of such indicators is shifted from the department directors to financial department specialists.
- Implemented complex system of checks and controls, hinting on how to eliminate the occurring errors.
- Developed a detailed user documentation.
Calendar time for the development and implementation – 1,5 – 2 months.