Choose the Total Sales Value, cell D8, as thatвЂ™s what you want to compare. You will now be asked for вЂResult cellsвЂ™. In the Scenario Manager window, click on Summary. Now that your scenarios are created, letвЂ™s compare them. You can create many more scenarios like this. I have put in the below values for Worst case. The changing cells will ofcourse remain the same. Similarly add 1 more Scenario and name it as вЂWorst CaseвЂ™. I have entered values as above, you can enter whatever you like. of cars that you will be able to sell, basically the variable cells Changing Cells вЂ“ select cells C2:C6 as these are the No.Under What-if analysis, go to scenario manager. You make a model as below and then want to create multiple scenarios based on number of cars that you will be able to sell for each of the cars. You must build multiple scenarios and prepare a comparison of all the scenarios. You have been given the task to plan the sales for the next quarter. LetвЂ™s say you are working in a Car Showroom in the Sales department. You will get the EMI amount for each combination in no time, without much effort or any complicated formulas. of payments in monthsвЂ™ and Column input cell as вЂPrice of carвЂ™ Go to Data Table and select Row input cell as вЂNo. So now you think about what if I change the duration of the loan, and compare for all these 5 cars? Similarly, you can have 2 inputs varying and still get the respective outputs. Select cell B1 to inform Excel that the 5 values are Price of Car values.Д®xcel has calculated for you, the EMI for each change in Price of Car. Since our options of Prices of Cars are put vertically in a column, we will use Column input cell. Now select this structure you have created and go to вЂData tableвЂ™ under what-if analysis in Data tab. Thus, here you have informed Excel that you want to calculate the resulting EMI for each value, using the formula in B9. Basically, you are pointing to the formula that is used to calculate the EMI. In D3, you can write anything you want, doesnвЂ™t matter. Make this structure in your Excel sheet next to your model. Since only 1 input is changing, that is, Price of Car, we will use 1-input data table. Use what if analysis data table to find this. You find out 5 cars that you like, you want to know what would be the EMI amount for each of the car? Then you go to the Car showrooms and research on more cars available. Now, after applying the Goal Seek, you know you can afford to buy a car worth Rs. LetвЂ™s take the same Car loan example as earlier. What this means is basically, either 1 or 2 of the inputs in your model are changing, you want to know output based on each change. Data Table in What-If analysisДata Table is used for Sensitivity analysis. This was calculations at multiple levels that Goal Seek in What-if analysis did, as it had to consider Available funds, ROI, Number of payments to reverse calculate and give you the answer. Put the Goal Seek values as above and you will know the Price of Car that you can afford. So, how much can you afford as вЂPrice of CarвЂ™?

The value is negative as money is going out of your pocket.Д«ut, you have a budget of only Rs. You can use Goal Seek even for more complex models, letвЂ™s take an example of a Car loan model. This was a very simple example of using Goal Seek in what-if analysis. Thus cell B1 is selected.Д®xcel will reverse calculate and immediately give you the value Rs. In вЂBy changing cellвЂ™, choose the value that needs to be changed, invoice amount in this case.In вЂTo valueвЂ™, enter the desired value, 11000 in this case.

In вЂSet CellвЂ™, B4 will automatically be selected as you had kept your cursor on it.

Under the вЂDataвЂ™ tab, click on вЂWhat-If-AnalysisвЂ™, then on вЂGoal SeekвЂ™.

To find out, how much + 18% = 11000, we will use Goal Seek in What if analysis. Now, the equation in simple terms is, X + 18% = 11000, where X is the invoice amount, 18% is the total GST. By clicking the above button, you agree to our terms and conditions and our privacy policy.

What if analysis excel 2010 goal seek