In this example, we are testing the Proportion of Guitar #1, which is B2. It doesn’t matter what you write for “Scenario name” or “Comment,” but it is important that under “Changing cells” you put the cell(s) that you are testing. What would the Gross Sales be if the Proportion of Guitar #1 was 25%? Or 75%? You can find out on Excel by selecting “Scenario Manager” under “What-If Analysis.”Īfter selecting scenario manager, you should end up on this screen: In the example, Gross Sales is connected to Proportion of Guitar #1 because the formula for C10 is (B6*C6) + (B7*C7), the formula for C6 is B2*A2, and the formula for C7 is A2-C6 therefore, the Gross Sales formula indirectly includes B2. To determine how Gross Sales are affected by Proportion of Guitar #1 using Excel’s what-if analysis tools, the formula for Gross Sales (C10) must be connected, either directly or indirectly, to Proportion of Guitar #1 (B2). When the proportion of Guitar #1 in stock is 50%, your gross sales are $10,625, assuming that all guitars in stock are sold.

You can do a what-if analysis to figure out how the number of each guitar you keep in stock affects your store’s gross sales. You realize that you have to cap the number of guitars in stock to 50. Suppose you start your own music store, and you sell two types of guitars: guitar #1 which sells for $125 and guitar #2 which sells for $300.

Let’s do a very basic example using the what-if analysis tools.

How to get data analysis on excel lab