Planning for Uncertain Times

​We are living in uncertain times. That’s always true to a certain extent, but right now with the Corona virus pandemic it’s more extensive than we’re used to. Governors are running scenarios to determine what is the best way to slow the spread of the disease while not choking off all economic activity. All businesses are under strain as people stay at home and non-essential businesses are closed. What should you do for your business?
 
I’d suggest that you copy what the Governors are doing. Run scenarios to determine what the dangers are. Use a model and adjust that to fit events as they happen. I’m going to give you some ideas on how to set that up. It’s a bit of work to set up, but once it’s in place it will be easy to update for changes.
 
You don’t have time to install planning software and you certainly don’t have time to learn how to use a new system. I say use your current spreadsheet software, Excel or something similar. I’ll be using Excel when I talk about things you can do in your spreadsheet.
 
Your first step is to create a baseline projection. Take your last twelve months so you get annual numbers. We’ll use that as your base. Get those numbers into a spreadsheet. You can put them in as the line items that show up on your financial statements or you can go detailed and put them in from your trial balance that lists all your general ledger accounts. Now take those numbers and spread them out over the number of periods you want to project. If you want a one-year projection by month you can divide everything by 12 and spread it out over 12 columns in the spreadsheet. This can be your baseline. In fact, to make it easier you can call this tab on your spreadsheet “Baseline”.
 
Now you’ll want to set up some tabs on your spreadsheet that refer to your baseline numbers to build the reports you want to see. You can name these tabs with the report name but be sure to also include “Baseline” in the name.
 
Next, you’ll want an area where you’ll be able to make changes and play with different scenarios. You’ll want this to be in the same format as your baseline tab. The easiest way to do that is to simply copy your baseline tab and rename it. In Excel you can hold down the Ctrl key and then click and hold your mouse on the baseline tab. Now still holding the mouse button down drag to where you want the new tab to go then let go with your mouse. A copy of the baseline tab should appear. Rename it as “Scenarios” Then go into that tab and erase all your baseline numbers leaving the titles in place. You can now put in different scenarios, or what if’s if you prefer and it will be in the same format as your baseline data.
 
My suggestion for how to use the Scenarios tab is that you set up separate tabs where you calculate the different impacts of your scenario and have the Scenario tab pull in the numbers from your calculation tabs. The numbers you put in here are the change from the baseline. So, for example, if your sales were $100,000 and the scenario had them fall to $75,000 the entry in the scenario spreadsheet would be for ($25,000) and not $75,000.
 
Once you have this built out, you’ll need a way to get your final numbers. That is fairly simple to do. Using the same technique as before, create a copy of the Baseline tab and change the name of this one to “Combined”. Once again, erase all the data leaving the titles in place. Now choose your first cell that would have numbers in it. Set up a formula to add the same cell from the Baseline tab and the Scenario tab. This will give you the combined number of your two tabs. You can copy this formula to all your cells with data and suddenly you have all your numbers. You can now create reports to show the results of your scenarios.
 
That sounds like a lot of work to create those reports again doesn’t it. There is a way to make it easier. For the reports you set up earlier with the baseline copy each tab and rename it. Now you can use the replace function to get this new report to point at the Combined tab instead of the Baseline tab. In Excel you can find this function on the Home ribbon under Find & Select. When the function opens up, you’ll be asked to put in “Find what:” and “Replace with:”. You’ll want to replace all the references to the Baseline tab to the Combined tab. In formulas that refer to other tabs the tab name is in single quotes. We’ll use that, so you can put ‘Baseline’ in the Find what box and ‘Combined’ in the Replace with box. Then click the Replace All button at the bottom. Presto, your report now pulls all it’s numbers from the Combined tab. You can now see the results from your scenario.
 
You can also use this technique to generate reports that show just the changes made from your scenarios by changing the references to ‘Scenarios’. This would help you to be able to explain the changes from the scenarios.
 
Hopefully, this brief overview gives you enough of a picture to develop your own tool to be able to try to figure out some of the uncertainties facing you. Stay healthy.