FP&A (Financial Planning & Analysis) professionals and others who are responsible for developing financial projections are not in a one and done business. I can’t remember the last time I created a projection, forecast or anything else that wasn’t quickly the subject of some ‘what if’ conversations.
What if we increase prices by 10% across the board?
What if our biggest competitor cuts prices?
What impact would these things have on our projections?
If you’ve built your model properly, your life will be much easier when these questions arise, because not much in life doesn’t change. Anticipating likely scenarios and building your model in a way that provides quick answers to different changes will go a long way towards making your life in planning more successful.
Define Key Decision Factors
Every business has a small number of key decision factors. These are not necessarily the business drivers you identify when you look at an 80/20 analysis.
For example, you may have a certain product mix that is fairly consistent based on trend analysis. This helps you identify the 20% of the variables that are drivers of your business, so you can use this to build your model accordingly.
A key decision factor may be that when the product mix changes beyond some predetermined threshold some action will be taken. Maybe when sales of product X drop below $X volume the company wants to evaluate certain promotions or implement pre-determined promotions. This is a key decision factor. It’s not based strictly on historical analysis, but on actual or projected events or metrics.
Identify Areas of Uncertainty
Identifying areas of uncertainty with the biggest potential impact is one step that helps identify how to develop scenarios to model. Both uncertainty and potential impact should be considered, not just one or the other.
For example, if your business operates in an industry where your raw materials prices are highly volatile because of supply chain issues, it would be wise to build a variety of scenarios that allow you to see outcomes based on different supply assumptions.
Depending on the complexity of the scenarios, you may want to use Excel or you may want to use a different tool. There are lots of planning and analysis tools available that are much better suited to complex scenario analysis than Excel, so know its limitations and don’t let it hold you hostage.
Design the Model
You now have three key things identified that will help you build your model right. The desired end goal, the 20% of drivers that will drive 80% of results, and the key decision factors you need to take into consideration. Having these clearly identified will help you understand how you need to build your model.
Before you start your design, decide on the tool you will use. Will you build your model in Excel or some other tool? This is no small decision because while most of the planning and analysis tools import data from Excel, you’ll do unnecessary work if you start in Excel and later migrate to another tool.
If you choose a tool other than Excel, make sure you factor in any necessary training so you have the time and resources to learn the new tool in time to meet whatever deadlines you have. Scenario management is one of the big strengths of some of the software tools available today, so don’t overlook this option. Also, remember that the number and type of scenarios you will likely need to use may steer you towards a particular technology tool.
When you have the ability to answer questions driven by changing variables, you shorten the time between thinking of questions and answering them. Being in a position to answer questions on the spot instead of having to schedule a follow-up meeting is a great outcome derived from effectively developing a model that can deal with multiple scenarios effectively and efficiently.
