Let’s be honest. None of us are fortune tellers. However, there’s no need to dust off the old crystal ball in order to predict the future when we’ve got Microsoft Excel. The software supports a scenario analysis — a process that examines different variables to anticipate subsequent outcomes. It’s particularly useful for businesses, as it can help you make better decisions and plan your strategy based on facts and figures rather than hunches.
While specific scenario analysis apps exist, they’re usually built for certain types of events, whereas, with Excel, you can create your own bespoke system. This is because good scenario modeling relies on complex logic and frequent updating of data, two of the software’s strong suits. What’s more, you already know how to use it, which makes everything even simpler. But how is it done? Follow our guide to understand the process.
First of all, you’ll need to decide what you want the scenario analysis to achieve. But that’s not all. We can all agree that ‘the future’ is a pretty vague term, and nothing about scenario analysis should be unspecified. In order to make it useful, you need to figure out what timeframe you want your projections to look at. Are you trying to foresee your profits for the next quarter? The next year? The next ten years? This will impact every part of your data collection and scenario building, so it needs to be defined clearly before you start the process.
Now that you know what question you want to answer with your analysis, it’s time to examine the factors that may impact this. If we still use our profit example, then consider what data you’d need to predict this. Number of production units, direct costs, value per unit, overhead costs, number of sales — all are important elements that will influence your future dividends.
It’s also vital that you take into account different market trends that might affect this. If you’re a leather jacket company, you can’t expect to sell as much in July as you do in October, for instance. What’s more, maybe an increasing number of people are turning vegetarian and moving away from owning animal products? Or, are certain planned legal changes going to impact your production or distribution at some point in the future? Context is extremely important to an accurate scenario analysis, so take a look at social, political, legal, and economic elements.
However, remember that Excel only allows for up to 32 variables in a scenario analysis, so don’t go overboard.
Armed with your list of variables, you now need to fill them in. Consider the level of certainty regarding each one. For example, the fact that more people will buy a leather jacket in Fall rather than Summer is pretty much a given. But has the law that’s going to change your production plans passed or is it still being discussed? Are fashionistas singing the praises of leather jackets already, or is it unclear whether your products will be in vogue this year?
Starting with your top uncertain factor, think up a moderately good and bad outcome. Now, create a scenario based on each, filling up the rest of the variables based on your more certain predictions. Continue doing this for the rest of your uncertain variables, while sketching out the likely chain of cause and effect.
You have all of your scenarios in front of you with the projections of what might happen should any uncertainty manifest. Now it’s time to utilize this information to your benefit. Think about whether any of the scenarios are more probable than others. Are there any contingency plans you need to have in place? How likely is it that you’d need them? Plan your strategy in accordance with the data that you’ve gathered, now presented beautifully and clearly in your spreadsheet.
While Excel is a great tool for scenario analyses, problems start to arise when more than one person has to use the spreadsheet. Collaboration is not Excel’s strong-suit, as every change is done by saving a new copy. This means that you’ll require a master copy, with a single person controlling it, which reduces productivity and ease of use. What’s more, many different versions of the same spreadsheet cause confusion, with staff often using or sending the wrong file on. And, if one of the people using the sheet accidentally fills it out the wrong way, it can introduce a chain-reaction of errors that will affect your business strategy.
Another major issue is security. You don’t want your projections to be seen by unauthorized people, namely competitors. Whether it’s intellectual property in the form of the formulae you used to create the scenarios in the first place, or even the hard numbers that can give a rival company an advantage, you want to protect your data. Unfortunately, Excel is not hard to hack, especially considering the fact that most businesses will use emails to share the files, causing extra risks.
But, Excel is still one of the best types of software to conduct scenario analyses. So what do you do? That’s where EASA comes in. Unlike other low-code applications that require the formulas to be moved from a spreadsheet onto a new platform, EASA re-uses the existing logic already in place within the Excel file.
This means that anything in the file — including features that are impossible to replicate otherwise, such as VBA and macros — can be used in the web app. It also dodges the often complex and time-consuming component of creating a low-code program, and enables easier maintenance as changes to data or formulas are done within the Excel file. As a result, you can still operate in a familiar setting, taking advantage of all of Excel’s scenario modeling benefits and capabilities, while avoiding the inherent problems of it.
The EASA app will be restricted only to authorised users, preventing the risk of hacking and IP leakage. At the same time, it keeps a record of everyone using the software, and puts everything in one place; completely averting version confusion, the need for a master copy working process, and the grave consequences of human error. Using EASA, you can enjoy the best of both worlds — the fantastic functionality of Excel when it comes to scenario analyses, without the damage its disadvantages can have for a business.
If the EASA custom application sounds like the right solution for you, don’t hesitate to contact us.