One of the often overlooked features of Excel is the what-if analysis tool, which lets you explore different scenarios without changing the values ??in the spreadsheet. This feature is especially useful for economics and management professionals as well as business owners because you can see how different values ??affect models, formulas, and projected profit.
Excel What-If Analysis Tool
Let’s say you have a small business selling widgets and you determine that you will make a profit of $ 10 for each widget sold. To calculate the profit from the sale of these widgets, you create a table in Excel that looks like this:
Note that the number in cell A3 represents the number of widgets sold, and the number in B3 represents the profit per widget sold. The figure in C3 was calculated using the following formula:
= A3 * B3
and represents the profit from the sale of 1 widget with a profit of $ 10.
However, you may be interested in the total profit for a different number of widgets sold. For example, suppose you want to know what your profit would be if you sold 100, 200, or 300 widgets.
– / –
One way to do this is to change the number in A3 for each of the scenarios we have defined above. In other words, you can change A3 to 100 and mark the change in profit at C3. Then you can do the same for 200 and 300. However, Excel’s What-If Analysis function was designed for just such cases.
To use the What-If Analysis tool in Excel, first click the Data tab on the Ribbon and find the What If Analysis button under Data Tools. Click the What-If button and select Script Manager from the list of options.
You should now see the Script Manager window. Click the Add button and Excel will ask you to name the first script. You can choose any name you like, but it would be wise to choose one that describes the script you are creating. In our example, name the scenario 100 Widgets.
In the Modify Cells box, enter A3 and click OK.
In the Script Values ??window that opens, enter 100 and click OK. Basically, you’ve created a new “what if” scenario that will calculate the total profit if the number of widgets sold in A3 is 100.
Follow the steps above to create two more scenarios, one for 200 widgets and one for 300 widgets. When finished, the Script Manager window should look like this:
Click the script labeled 100 Widgets, and then click the Show button. Note that cell A3 changes to 100 and cell C3 changes to $ 1000. Now click on the script labeled 200 Widgets and click the Show button. Note that cell A3 will change to 200 and cell C3 will change to $ 2,000. Do the same for the 300 widget scenario.
The “What If” scenario also allows you to simultaneously see a summary of all the scenarios that you created in the previous steps. Click the Summary button and enter C3 in the Result Cell.
Click OK and Excel will create a new worksheet that presents all of your scenarios in one convenient summary view.
This simple example shows how powerful Excel’s what-if analysis can be. Using a worksheet filled with data and formulas, you can easily create useful what-if scenarios in Excel to save you the trouble of editing individual cells to see how the changes affect values ??in other cells. Enjoy!