Whether you’re producing a three-year financial plan to present to an equity investor, a 12 month budget for the board or a short term cash model for the bank to secure that overdraft extension, building a financial model is not a task for the inexperienced.The decisions that are dependent on such models are often business changing, or the difference between business and no business, so make sure you get the right help to do the job well. If nothing else, ensure you avoid the following schoolboy errors in your modelling:
1. Too simpleWorkings should be laid out clearly so it’s always possible to trace the numbers back to their original drivers. Models should also include workings so the user or reader doesn’t have to refer to numerous different sheets or rely on knowledge in someone’s head.
2. Too complexA good model should be relatively easily interpreted and managed by someone else. Long formulas can be extremely difficult and time consuming to follow if you haven’t written them yourself. No formula should be longer than half the length of the formula bar!
3. Not flexible enoughYou should be able to see the impact a five per cent increase in the price of product B has on the profitability and cash requirements for the whole period of the model; or the award of a three per cent pay rise or improvement in debtor days etc. If you have to make lots of manual adjustments in the model to reflect changes like these then you need to start again with a sheet that lays out all those key drivers and enables you to run easy sensitivity analysis on different scenarios.
4. No logical structureMost models will have numerous sheets but they can either be too long (how many times do you have to hit the page down button?) or arranged in a haphazard way. For example, staff costs will always be key so have them on a separate page, built up by individual headcount/department where practical. Order the sheets logically so the sheet at one end of the file has the greatest level of detail flowing through to the sheet at the other end with the highest level summary. It can also help to have a contents sheet that you can hyperlink and easily navigate to the individual sheet for that cost category or income stream.
5. No fully integrated balance sheet and cashflowDon’t think these aren’t important. It’s the balance sheet that drives the cashflow of the business so don’t overlook your creditor days, stock turnover, debtor days etc. Without modelling these correctly, you’ll never really be able to forecast your cash position with confidence.
6. No executive summaryIt speaks for itself, but set this sheet up to print the right area correctly and include the P&L, cash and balance sheet and some of the key assumptions/drivers. Also think about how you could illustrate revenue, costs, profit and cash balances in a chart to make it easy to understand at a glance.
7. Formula errorsThe easiest errors to make and sometimes the hardest to find. You can easily miss pieces of data because of incomplete ranges or formulas not copied across correctly after adding new information. Be especially careful when inserting rows or columns near the edge of ranges and where you’ve fixed references in cells.
8. No error or sanity checksWith the right thought it’s quite easy to build in automatic error checks, and don’t be afraid to get familiar with the audit functions in excel, they can be really helpful. Sanity checks are equally important and can be applied to such things as average revenue per sales person; can you really quadruple your sales by only adding one sales person to your team of five?
9. Hard coded numbersA classic error but repeated time after time. Never hard code, or at least if you do then insert a comment against the cell explaining what you’ve done and where the number has come from. You may remember it at the time but if you come back to it later you’ll probably have forgotten and the model may not work properly.
10. Consistent formatsFor both presentation and usability, it helps to have nicely presented and consistent formats throughout the model. Many of the sheets will have the same month by month, qtr by qtr or year by year profile, so make all these column headings identical on each sheet. Always start them from the same column, use the same bordering, fonts, shading, labels, descriptions and style wherever possible. Not accurately describing the rows and columns can leave the reader easily confused.
Chris Chapman is managing director of My Business FD, which offers high-calibre finance directors to ambitious smaller and growing companies on a part time, flexible and affordable basis. Tel: 0207 717 5254 or enquiries@MyBusinessFD.com.
Share this story