Need to Create an Assortment of Financial Scenarios? It’s a Snap With Excel
In my business I have to assemble a wide range of scenarios so we can prepare negotiations on pricing certain projects, and since the scenarios usually contain many variables, that job can take hours. Any ideas for speeding the process?
Sure enough, Excel has a perfect tool for that job, and you probably won’t be surprised to learn it’s called Scenario. It lets you create an ensemble of basic circumstances and then, as you make adjustments in any of the parameters, you can save the calculated scenarios and call them up from a menu in your toolbar—just perfect to provide the key numbers at your fingertips during presentations.
But before I walk you through the setup steps, I suggest you add Scenario to your toolbar. To do that, go to Tools, Customize, click the Commands tab and under Categories, go to Tools, and under the Commands tab, go to the Scenario: icon that contains the dropdown menu (see screenshot below) and drag it into your toolbar.
Now we’ll create a short scenario, from which I’ll show you how to easily build a menu of variations that you can then access with a single click. Open a new Excel spreadsheet and, using the text in the screenshot below, fill in the information from A2 to A8. In B6, enter =B2*B3; in B7 enter =B4*B5; and in B8 enter B7-B6.
For this example, we’ll create a menu of scenarios in which the Number of attendees will rise from a minimum of 12 to a maximum of 100, and the Scenario will display the financial impact of those changes in Total profit.
To do that, highlight cells B2 to B5 (the variables in this scenario) and go up to the Scenario: icon you just created in your toolbar, typing in Minimum attendees and press Enter (see screenshot below).
Now we’ll change one variable, increasing Number of attendees to 100 and again select B2:B5, and in the Scenario: box this time type Maximum attendees and press Enter (see screenshot below).
Clicking on the down arrow in toolbar’s Scenario lets you select which scenario you wish to view. And if you go to Tools, Scenarios (see screenshot below), you can edit your selection.
