What-If Analysis
What-If Analysis in Excel allows you to try out different values (scenarios) for formulas. The following example helps you master what-if analysis quickly and easily.
Assume you own a book store and have 100 books in storage. You sell a certain % for the highest price of $50 and a certain % for the lower price of $20.
If you sell 60% for the highest price, cell D10 calculates a total profit of 60 * $50 + 40 * $20 = $3800.
Create Different Scenarios
But what if you sell 70% for the highest price? And what if you sell 80% for the highest price? Or 90%, or even 100%? Each different percentage is a different scenario. You can use the Scenario Manager to create these scenarios.
Note: You can simply type in a different percentage into cell C4 to see the corresponding result of a scenario in cell D10. However, what-if analysis enables you to easily compare the results of different scenarios. Read on.
1. On the Data tab, click What-If Analysis and select Scenario Manager from the list.
The Scenario Manager dialog box appears.
2. Add a scenario by clicking on Add.
3. Type a name (60% highest), select cell C4 (% sold for the highest price) for the Changing cells and click on OK.
4. Enter the corresponding value 0.6 and click on OK again.
5. Next, add 4 other scenarios (70%, 80%, 90% and 100%).
Finally, your Scenario Manager should be consistent with the picture below:
Note: to see the result of a scenario, select the scenario and click on the Show button. Excel will change the value of cell C4 accordingly for you to see the corresponding result on the sheet.
Scenario Summary
To easily compare the results of these scenarios, execute the following steps.
1. Click the Summary button in the Scenario Manager.
2. Next, select cell D10 (total profit) for the result cell and click on OK.
Result:
Conclusion: if you sell 70% for the highest price, you obtain a total profit of $4100, if you sell 80% for the highest price, you obtain a total profit of $4400, etc. That's how easy what-if analysis in Excel can be.
Goal Seek
What if you want to know how many books you need to sell for the highest price, to obtain a total profit of exactly $4700? You can use Excel's Goal Seek feature to find the answer.
1. On the Data tab, click What-If Analysis, Goal Seek.
The Goal Seek dialog box appears.
2. Select cell D10.
3. Click in the 'To value' box and type 4700.
4. Click in the 'By changing cell' box and select cell C4.
5. Click OK.
Result. You need to sell 90% of the books for the highest price to obtain a total profit of exactly $4700.
Data Tables
Instead of creating different scenarios, you can create a data table to quickly try out different values for formulas. You can create a one variable data table or a two variable data table.
Assume you own a book store and have 100 books in storage. You sell a certain % for the highest price of $50 and a certain % for the lower price of $20. If you sell 60% for the highest price, cell D10 below calculates a total profit of 60 * $50 + 40 * $20 = $3800.
One Variable Data Table
To create a one variable data table, execute the following steps.
1. Select cell B12 and type =D10 (refer to the total profit cell).
2. Type the different percentages in column A.
3. Select the range A12:B17.
We are going to calculate the total profit if you sell 60% for the highest price, 70% for the highest price, etc.
4. On the Data tab, click What-If Analysis and select Data Table from the list.
5. Click in the 'Column input cell' box (the percentages are in a column) and select cell C4.
We select cell C4 because the percentages refer to cell C4 (% sold for the highest price). Together with the formula in cell B12, Excel now knows that it should replace cell C4 with 60% to calculate the total profit, replace cell C4 with 70% to calculate the total profit, etc.
Note: this is a one variable data table so we leave the Row input cell blank.
6. Click OK.
Result.
Conclusion: if you sell 60% for the highest price, you obtain a total profit of $3800, if you sell 70% for the highest price, you obtain a total profit of $4100, etc.
Note: the formula bar indicates that the cells contain an array formula. Therefore, you cannot delete a single result. To delete the results, select the range B13:B17 and press Delete.
Two Variable Data Table
To create a two variable data table, execute the following steps.
1. Select cell A12 and type =D10 (refer to the total profit cell).
2. Type the different unit profits (highest price) in row 12.
3. Type the different percentages in column A.
4. Select the range A12:D17.
We are going to calculate the total profit for the different combinations of 'unit profit (highest price)' and '% sold for the highest price'.
5. On the Data tab, click What-If Analysis and select Data Table from the list.
6. Click in the 'Row input cell' box (the unit profits are in a row) and select cell D7.
7. Click in the 'Column input cell' box (the percentages are in a column) and select cell C4.
We select cell D7 because the unit profits refer to cell D7. We select cell C4 because the percentages refer to cell C4. Together with the formula in cell A12, Excel now knows that it should replace cell D7 with $50 and cell C4 with 60% to calculate the total profit, replace cell D7 with $50 and cell C4 with 70% to calculate the total profit, etc.
8. Click OK.
Result.
Conclusion: if you sell 60% for the highest price, at a unit profit of $50, you obtain a total profit of $3800, if you sell 80% for the highest price, at a unit profit of $60, you obtain a total profit of $5200, etc.
Note: the formula bar indicates that the cells contain an array formula. Therefore, you cannot delete a single result. To delete the results, select the range B13:D17 and press Delete.
Quadratic Equation
A quadratic equation is of the form ax2 + bx + c = 0 where a ≠ 0. A quadratic equation can be solved by using the quadratic formula. You can also use Excel's Goal Seek feature to solve a quadratic equation.
1. For example, we have the formula y = 3x2 - 12x + 9.5. It's easy to calculate y for any given x. For x = 1, y = 0.5
2. For x = 2, y = -2.5
3. But what if we want to know x for any given y? For example, y = 24.5. We need to solve 3x2 - 12x + 9.5 = 24.5. We can solve the quadratic equation 3x2 - 12x + 9.5 - 24.5 = 0 by using the quadratic formula.
3x2 - 12x -15 = 0
a = 3, b = -12, c = -15
D = b2- 4ac = (-12)2 - 4 * 3 * -15 = 144 + 180 = 324
a = 3, b = -12, c = -15
D = b2- 4ac = (-12)2 - 4 * 3 * -15 = 144 + 180 = 324
x =
|
-b + √D
|
or
|
x =
|
-b - √D
|
2a
|
2a
|
x =
|
12 + √324
|
or
|
x =
|
12 - √324
|
6
|
6
|
x =
|
12 + 18
|
or
|
x =
|
12 - 18
|
6
|
6
|
x =
|
5
|
or
|
x =
|
-1
|
4. You can use Excel's Goal Seek feature to obtain the exact same result. On the Data tab, in the Data Tools group, click What-If Analysis, Goal Seek.
The Goal Seek dialog box appears.
5. Select cell B2.
6. Click in the 'To value' box and type 24.5
7. Click in the 'By changing cell' box and select cell A2.
8. Click OK.
Result.
Note: Excel returns the solution x = 5. Excel finds the other solution if you start with a x-value closer to x = -1. For example, enter the value 0 into cell A2 and repeat steps 4 to 8. To find the roots, set y = 0 and solve the quadratic equation 3x2 - 12x + 9.5 = 0. In this case, set 'To value' to 0.
No comments:
Post a Comment