The period starting from August through to December is one every analyst coordinating an annual business plan or budget knows too well. After seven months of finalizing the current year’s plan, it can feel almost too soon for the next preparation to be due. Depending on the type of budget system your organization runs, you might have made several revisions during the course of the year already. Tightening the loose expenses or tweaking revenue projections are usually the common revisions to make.
I’ll start our conversation by saying that it is worth paying for an automated custom budgeting application than the traditional Microsoft Excel based templates we usually use. You can contact Nitrogen, a Ghanaian based custom software developer to convert your excel based budgeting system into a web based application. A web based solution makes it easier to deploy budgets to line and branch managers who are stationed offsite and always a better option to a spreadsheet based budget for many reasons. The most obvious reasons being the risk of data loss during consolidation, lengthy consolidation processes, and difficulty to adjust during revisions. For the purposes of this article, I will use plans and budgets interchangeably.
If you are using spreadsheets for your budget, it is advisable to start ground work very early. For organizations running a budget on the traditional calendar, the budget cycle may commence in August through until when the year-end statistics are drawn. Most often than not, the entire strategy for the year in question is handed down by executive management and this is the single most important information which determines the direction the planning process should take. It may be considered as the ‘mission statement for the next year’ as this is mostly informed by the outcome of the prior year strategy plus any significant skewing occurrences in the current year. In any case, as an analyst, you may have your own interpretation of the numbers which you should have delivered to the executives prior to the strategy being drawn. This is because strategies are often sanctioned by the board of directors. Getting an early start means you will finish with the numbers early and then have time to reflect if the outcome matches the strategy and so on.
Depending on the size of the organization, the planning process can be a nightmare or a stroll in the park. As with other reports, analysts should already know that a business plan is meant for execution by operational departments and also for financial controlling, thus the users’ needs will have to be paramount. The maturity level of the organization’s budget control function has an impact on the depth of comparative parameters. For instance, if you work in the financial industry, your plan may have to drill down to the most basic and detailed dimensions such as agents, officers, products and even projects. This will make comparison with actuals very easy and informative and may provide better answers to the success or failures or the current strategy. High level budgets tend to answer financial questions only and says very little on operations which is the real driver of revenues. Naturally, larger organizations tend to have more sections and units making a decentralized planning process quite cumbersome. This reiterates the earlier point of starting early. A web based application would make this easy since all managers or budget officers can login remotely to plan with instant consolidation achieved. Anyway, we will proceed on this discussion with the assumption that we will plan using a spreadsheet.
Designing your plan templates
It is a common practice for even multinational organizations to plan using spreadsheets. The real reason for this is quite difficult to discern but may be attributed to flexibility since third-party over the counter software are made in a generic way. Again these organizations can always make a request a quote from Nitrogen. Not to entirely discredit the use of spreadsheets, I must emphasize that with a really good excel programmer with adequate knowledge of visual-basic, you could get a very robust spreadsheet designed for your needs which would ensure data integrity.
If you are using spreadsheets for your plan, you need to firstly determine the type of budget you need to draw. This could be embedded in policy or otherwise. The type of budget will go a long way to inform your design structure. In broad terms, budgets can be traditional or zero-based.
Traditional budgets are plans that leverage on past trends and results to make an appropriate projection into the future considering all resource constraints and operational changes. Historical growth and seasonality patterns are common projection methodology employed. Zero-based budgets on the other is the production of a plan based on no history or on a completely new set of business constraints and decisions as if there is no history.
As can be seen from these definitions, the type of preparation that needs to be done depends on the type of budget. You will need the historical data for the immediate past year and maybe the year before that in order to make concrete decisions on the validity of your trends. You may need to request additional information from line managers on the reasons for certain dips and spikes on some trends and many others. These sort of information can be easily obtained if an actual versus planned exercises are carried out and documented on a regular basis. This will save a lot of time spent in the initial stages. Zero-based budgets as opposed to the literal meaning of the name, requires a lot more information than traditional budgets. This is because a lot more thought has to go into making assumptions about the ensuing period. This involves interviews of line managers regarding their plans and how they fit into the global organizational strategy and vision, obtaining external information on industry projections as well as macro-economic indicators. Information gathered here must be well indexed like puzzle pieces and fit into the big plan where required.
There is no straight jacket rule regarding creating your spreadsheet template. Your spreadsheet design template is entirely up to you. This is where you must employ all the guidelines discussed in my previous article on designing good excel reports. Grouping the various sections of the plan for instance is a good way to achieve good navigation of a rather elaborate workbook. The remainder of the template depends on the kind of budget you need to produce. For example, when designing a template for an operational plan, the dimensions chosen could depend on the line departments you have or even sales teams. Your choice of the plan dimensions should be made easier by the managers who are allowed to make input.
Make very good use of colors for distinguishing input cells, calculations cells and output cells. Add some validation rules to restrict changing formula and password protect the formula cells. For validation rules, add some input messages and error alerts for interactivity and pop-ups. These can provide useful tips for users without your phone buzzing every second for directions. Include a summary sheet with hyperlinks to all sections of the plan to allow for easy navigation especially when you have more than six worksheets. Keep worksheet names short and self-explanatory to avoid confusion.
I must emphasize that it is easy to be over elaborate with restrictions and colors. Ensure you do not put too many passwords to make planning cumbersome. Also make sure you use only subtle colors so as not to make numbers and text difficult to read. Use medium and tidy font types like Arial, Calibri or MS Sans Serif. I have a personal liking for the Windows 8 Segoe UI font.
Consolidating your plan is a critical part of the planning process. Errors in formula and data loss due to omission and commission is more likely to occur. To make consolidation easy, I personally find it easier to design a universal worksheet which can accommodate many functional lines. It may be useful to create different templates for different functional groups with many common planning lines. For instance, it may be easier to design a template for various sales teams promoting different products, A and B. When product A sales manager is planning, he will simply ignore all lines relating to product B. Using distinct colors to distinguish product A from product B could make it faster and less confusing provided it is well defined for the managers to understand the meaning of the colors. In consolidating the inputs, it is probably better to design a separate template and link the separate files to it. The reason for this is that it makes updates less clumsy and relatively easier to circulating the entire the budget to line managers for update. This maintains data integrity and keeps the functional goals intact. In this case, the budget coordinator keeps the master budget file.
To conclude, I must say that all my recommendations above are based on my experience with both local and international companies. Everyone may have different circumstances within which planning takes place but I believe in every case, the underlying principle applied is similar.
Let’s get planning!