On the Data ribbon in Microsoft Excel is the What-If Analysis collection of tools, which include Scenario Manager, Goal Seek, and Data Table. Each of these tools offers a unique way to approach data analysis that includes unknown variables or where you want to see a number of different outcomes.
Scenario Manager allows you to compare a number of different sets of variables to see how the result of a calculation changes. The example below shows how a person might compare the revenue resulting from two different pairs of ticket prices and corresponding ticket sales. In these scenarios any tickets sold ahead of time can be sold at full price, but tickets sold the day of the event only sell for half price. There are 10,000 total tickets available, and cell D7 is calculated as =10,000 – D5 – D6. Revenue in cell D9 is =D4*D5 + 0.5*D4*D6.
To create the first scenario, go to What-If Analysis > Scenario Manager. Enter $50 Ticket Price as the name of this scenario, and click the select button next to Changing cells, and select cells D4:D6, which represent the ticket price, full price tickets sold, and half price tickets sold. Then click OK.
Next you are prompted for the values you want to enter for each of the cells. In this case, you project that at a $50 ticket price, there will be 5000 tickets sold at full price and 3000 at half price. When you click OK, you’ll see the $50 Ticket Price scenario under the scenario list.
If you click on Show, the values you entered for this scenario will be plugged into the appropriate cells, and you can see both the tickets remaining unsold and the total revenue. In this scenario, the total revenue is $325,000.
Now that you’ve seen the results of the $50 scenario, you can compare a $40 scenario in which you project that 7000 tickets will be sold at full price and 2000 will be sold at half price. You can do this by clicking Add in the Scenario Manager, and creating a new $40 Ticket Price scenario with values of 40, 7000, and 2000. Once you’ve created the scenario, you can click Show to see the results of the $40 scenario, which is a total revenue of $320,000.
From the Scenario Manager, you can switch back and forth between the two scenarios, and add additional ones if you’d like. While it’s fairly easy to compare two different scenarios, in some cases you may have more complex equations or multiple scenarios that you want to compare. For such cases, Scenario Manager can provide you a scenario summary which shows all of the input combinations and the resulting values in a separate sheet. While in the Scenario Manager window, click on Summary, and then select the results cell(s) you’re interested in. In the example below, cell D9, the total revenue was selected, so you can easily see the resulting revenue from the two scenarios.