How Not to Build a Financial Model – Part 1

PLANNING

Planning is simply for babies who can’t work models out in their heads or others who have too much time on their hands. Do you think a bank is going to lend you financing if you can’t figure it all out in your head? Details are for lawyers, accountants and losers, baby. All you have to do is head off to the bank, provide your best surly look and tell them what they must give you. It’s a guaranteed outcome.

Yes, good luck with that mindset and approach. You might laugh and think, “I’d never do that” but many, many companies do this – but perhaps not quite to that extreme. Take a long hard look at the last Excel model you developed. Did you draw out the processes / operations on a piece of paper before proceeding? Of course you did. Did you identify what the key drivers for both inputs and outputs would be before proceeding? Of course you did. Did you construct a scoping document to determine exactly what should be included and what should be excluded before you even opened Excel? Of course you did. Did you identify the key decisions that need to be taken so that they may be represented in the model? Of course you did. Did you talk to all users about their required needs before starting? Of course you did. Are you a pathological liar? Of course you are.

Bearing in mind that 57.4% of all statistics are made up on the spot, a sample survey of one revealed that virtually 100% of all modellers just take proverbial pen to Excel paper and commence modelling when they have a job to do (it’s “virtual” as we didn’t even ask one person). There’s a good reason for this: we are all under time pressure and it is neither an effective nor an efficient use of our time to plan out what is required. Bosses need to see we’re underway and that the model will be finished by Friday.

The beauty of this approach is that a financial model will be built by Friday. But is it the model that should be built? And why does it need to be built by Friday anyway? This is the problem. It takes a brave analyst to sit down and think about the modelling issues and risk being taken to task that they are “not being productive”.

This is a cultural problem the world over. No one has time to stop and smell the roses. Huge errors are made in a model as a consequence. You need to plan how your model will fit together. Key items to consider include (but are not limited) to the following:

• What is it you have actually been asked to model? What are the rules for determining what should be included and what shouldn’t?
• Who is the model for? Are there any other key stakeholders? Will they receive different reports? How should these reports reconcile if so?
• What is the model intended to do? Is it a strategic, tactical, operational or planning model? These have different ramifications and require particular alternate attributes.
• Why have you been asked to build the model? If the model is to identify cost savings it will differ from one seeking to optimise profitability, determine strategies or obtain financing. These models will require a different focus and allow for the ability to flex important inputs.
• Do you understand precisely what you are modelling? Try sketching a flowchart. How do variables interrelate? For example, if you buy raw materials less regularly, unit prices may be cheaper, but storage costs may be higher, as might wastage costs and risk of obsolescence. What’s the optimum quantity to purchase and how frequently?
• Should financial data be modelled in real or nominal terms? Real numbers make it easier to disregard the effects of volatile inflationary regimes but may make for misleading Balance Sheets (e.g. straight line depreciation is nothing of the sort).
• What are the key inputs and outputs? Should inputs all be on one worksheet so that end users can modify easily or should inputs be separated for distribution to various parties and for possible confidentiality reasons?
• Where will inputs come from? One frustration of end users time and time again is that models are built requiring data to be in a different format from how they receive it. This leads to user-built risky interim models to convert the data accordingly. This is crazy!
• What is the evaluation horizon and periodicity? If your company has cashflow problems, you are not looking to build an annual forecast model: you may require cashflow projections weekly or even daily. Models with shorter reporting periods tend to be operational in nature and therefore have only short forecasting periods (e.g. three to five years rather than 10 for a strategic model).
• What level of granularity is required? Do you need to be able to consider geographic locations, product mixes, leasing versus purchasing or is a high level strategic overview required? Don’t ever build more than you require.
• Should forecasting be developed on a top down or bottom up basis? Too many so-called experts advocate a financial model by definition should always be constructed from a bottom-up perspective, but this may lead to unwieldly, goal-incongruent unfocused models. What needs to be modelled in detail? What can be higher level (e.g. tax may be modelled higher level in many models). Why?
• Will there be much duplication in the model? This may affect model structure drastically. If you have many similar business units, you may create a template worksheet which can be readily replicated. These types of sheets work better if range names are kept to a minimum.
• Does the model need to be reconciled? It may need to balance with existing budgets, financial systems and / or statutory accounts. Have you allowed for sufficient granularity and outputs to be able to check the required outputs as needed?
• How do you intend to check irreconcilable items in the model? How can the assumptions be verified? Who will be responsible? How can internal consistency be retained?

The point of this blog is merely to get you thinking. We run >courses such as Strategic Budgeting, Planning and Forecasting that explore many of the issues above in much more detail and make non-generic recommendations. Avoid templates unless you like square pegs in your round holes. All models should be fit for purpose – so do you know the purpose?

Take a good hard look at your next model build. Feel free to drop us a line at either contact@sumproduct.com or training@sumproduct.com if you need to.