Budget Spreadsheets are Complex!
Even as a professional accountant, I find other people’s budget spreadsheet models complex and difficult to follow. This article is about simple steps that budget preparers can do to help other people understand how their spreadsheets interact with one another.
A good budget model captures the relationships between the different financial drivers affecting the organization. For example, every additional client service hour affects both revenues and expenses. Every new hire increases employee benefits, statutory deductions and recruiting costs, as well as salaries. Remembering which spreadsheets are affected by which formulas quickly becomes impossible.
Draw a Diagram
Microsoft Excel has a simple tool to insert diagrams into a spreadsheet. In this example, I created an Overview tab with a hierarchy to document the Budget workbook. The brown boxes represent spreadsheets requiring input from the operational staff. The blue boxes are the budget outputs. The grey box houses the formulas used to allocate Revenues, Payroll expenses and Administration charges to the programs.
The beauty of this system is that it allows you to colour code the spreadsheet tabs to match the diagram, making it easier for others to understand how the tabs work together. The text in the smaller boxes is the name of the tabs, so there is no confusion. You are also storing the budget documentation within the budget model itself, so there is no danger of it getting lost.
When a client asked me to prepare their budget, I created a diagram like this to make sure that I understood the requirements. It helped us ensure we were both on the same page. It’s easy to make structural changes to a model in the planning stages. Trying to do it once the spreadsheets have been created and the formulas set is time consuming, and the risk of making errors is significantly higher.
Put All the Drivers Together
Create an Options tab in your spreadsheet and put all of the assumptions and constraints in it. Examples of assumptions:
- Inflation will increase by 2%,
- Salaries will increase by 1.5% and
- Government funding will increase by 0%
Examples of constraints:
- Core administration cannot exceed 15% of Total Revenues
- The organization cannot transfer more than $10,000 from Reserves to the Operating Fund.
- Each program cannot have a surplus or deficit more than $500
The advantage of this approach is that it becomes easy to see the impact of changing the assumptions on the overall budget. You also don’t have to search through the spreadsheets to find which drivers have been incorporated into the model.
Document the Questions, Suggestions and Issues
Budgeting often involves negotiations, particularly when there are cost reductions in the plan. As the new year progresses, different people may disagree on just what was decided. It’s a good practice to have a spreadsheet that documents how budget issues were resolved, any outstanding questions or issues, and suggestions for the next version or next year.
The best budgets are the ones prepared (and understood) by the whole team, rather than being a mysterious document created by the accounting department. As the regulatory environment and funders’ requirements become more complex, budget models become more complicated. We all need to work to bring clarity and understanding to both the process and the results.
What has the experience been in your organization?
What to said is exactly correct? We can color the activities. But instead of making chart, we can give separate sheets with coloring in excel. Why cant you can go through general ledgerwise?
I have different method in which we are plotting activity / process wise. Just visit my blog for the same
Thank you for sharing excellent information. Your website is very cool.
Major thanks for the post. Much obliged.