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.
![what if analysis excel 2010 goal seek what if analysis excel 2010 goal seek](https://www.timeatlas.com/wp-content/uploads/goal-seek-error.jpg)
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.
![what if analysis excel 2010 goal seek what if analysis excel 2010 goal seek](https://i.ytimg.com/vi/ExKqY2h2uQA/maxresdefault.jpg)
Supports all languages, and all new commands of 2007, 2010, 2013, 2016, 2019 and 365 have been added into the classic interface. You can use Office 2007/2010/2013/2016 immediately without any training.
![what if analysis excel 2010 goal seek what if analysis excel 2010 goal seek](https://pryormediacdn.azureedge.net/blog/2016/3/excelgoalseek03.png)
Click Data menu, move to What-If Analysis.With Classic Menu for Office 2007/2010/2013/2016/2019 installed, you can click Menus tab to get back the classic style interface.Classic Menu for Office Home and Business.
![what if analysis excel 2010 goal seek what if analysis excel 2010 goal seek](https://i.ytimg.com/vi/ZMe_1cMJ908/maxresdefault.jpg)
Classic Menu for Office 2007 Applications.