What if analysis template xls

Forgot Password? Free Excel Course. Login details for this Free course will be emailed to you. Article by Jeevan A Y. What is a What-If Analysis in Excel? We have 3 parts of What-If Analysis in Excel. It uses a set of ranges that have an effect on a certain output and can be used to generate different scenarios such as bad and medium depending on the values. Once you click on Scenario Manager, it will show you below the dialogue box. Click on ADD. Give Scenario a name. In changing cells, select the first scenario changes you have listed out.

The changes are Project cost cell C10 at 15 million, Raw material cost cell C7 at 11 million, and other expenses cell C11 at 4. Mention these 3 cells here. Click on, OK. In one more cell, I gave the formula for the additional price which we want to sell.

In the set, the cell gives the cell position where we are going to give the output value here the additional price amount we know which we are giving in cell C4. The profit percentage is 30, and the selling price should be Similarly, we can check for different targeted values. This goal seeks to help to find the EMI calculations etc. Now we will see the Data table. We will consider a very small example to understand better.

We have to get the percentages in each combination. In these situations, the Data table will help to find the output for a different combination of inputs. Now we will see how to achieve this. First, create a formula to perform this. If we observe the above screenshot, the part marked with a box is the example. In A3, we have the formula to find the percentage from A1 and A2.

Now below are the following steps for setting up the initial values for Scenarios:. Step 3: Now a scenario manager dialog box appears, click on the Add button to create a scenario. Step 4: Create the scenario, name the scenario, enter the value for each changing input cell for that scenario, and then click the Ok button. Step 6: Now, change the value of B3to and click the Add button. Step 7: After clicking on the Add button, the add scenario dialog box appears again.

Step 9: Change the value of B5 to and click the Ok button. Step Similarly, create Scenario 3 and click the Ok button. Step Again, appears scenario values box with a changed value of the B5 cell. Step Change the value of B7 to and click the Ok button.

The Scenario Manager Dialog box appears. In the box under Scenarios, You will find the names of all the scenarios that you have created. Step Now, click on the Summary button. The Scenario Summary dialog box appears. Step Select Scenario summary under Report type and click Ok. Scenario Summary report appears in a new worksheet. You will get the following Scenario summary report. Goal Seek is a What-If Analysis tool that helps you to find the input value that results in a target value that you want.

Goal Seek requires a formula that uses the input value to give the result in the target value. Then, by varying the formula's input value, Goal Seek tries to solve the input value.

Goal Seek works only with one variable input value. If you have more than one input value to be determined, you have to use the Solver add-in. Below are the following steps to use the Goal Seek feature in Excel. Step 3: Type C9 in the Set cell box. This box is the reference for the cell that contains the formula that you want to resolve. Step 4: Type in the To value box.

Here, you get the formula result. Step 5: Type B9 in the By changing cell box. This box has the reference of the cell that contains the value you want to adjust. Step 6: This cell that the formula must reference goal Seek changes in the cell that you specified in the Set cell box. Click Ok. As you can observe, Goal Seek found the solution using B9, and it returns 0 in the B9 cell because the target value and current value are the same.

With a Data Table in Excel, you can easily vary one or two inputs and perform a What-if analysis. There are two types of Data Tables, such as:. If you have more than two variables in your analysis problem, you need to use the Excel Scenario Manager Tool.

A one-variable Data Table can be used to see how different values of one variable in one or more formulas will change those formulas' results. In other words, with a one-variable Data Table, you can determine how changing one input changes any number of outputs. Below is an example of creating a one-variable data table. A good example of a data table employs the PMT function with different loan amounts and interest rates to calculate the loan.

There is a loan of 1 00, for a tenure of 5 years. You want to know the monthly payments EMI for varied interest rates. You also want to know the amount of interest and Principal that is paid in the second year. Scenarios are probably the most common for individual users. A scenario takes data from your Excel spreadsheets and displays results based on the scenario factor.

Scenarios are useful if you want to know results based on different data sets. For instance, you might want to know how much revenue you make next year based on prices of products. You can create scenarios with product different pricing data sets. In some cases, you want to know how to get to a different goal. The Goal Seek tool helps determine what values are required to get a specific result. For instance, suppose that you have a budget each month. You don't want to go over a specific amount when you buy a new car, so you use Goal Seek to find an interest rate that matches the monthly payment that you can afford.

For each of your analysis spreadsheets, you have formulas that use data and calculate results. Formulas use variables as input and calculate based on changes in these variables. Using a Data Table, you can see the result of a formula calculation by changing one or two variables. Data Tables are useful when you want to see how data changes based on just a one-factor change in the calculation. The examples in this overview are just a few ways that you can use a What-If Analysis spreadsheet, but these tools are used much more frequently in everyday business.

Understanding these tools will help you make better decisions with finances and future purchases. Suppose that you want to figure out how much revenue is needed to make a specific gross profit. You can use scenarios to use different data sets.

Instead of manually changing data sets or making multiple spreadsheets based on "what if" factors, you can use Excel's scenario tool. To test this application, you first need some test data.

The following image shows the test data we're using. Note that the "Profit" row is a formula that subtracts operation costs from gross revenue. The formula is shown in the content text box. Highlight all of these cells and go to the Data tab. This tab has the What-If Analysis tools that you'll need.