What if projects in excel

For more information about the Solver add-in, see the section Prepare forecasts and advanced business models , and follow the links in the See Also section. If you have a formula that uses one or two variables, or multiple formulas that all use one common variable, you can use a Data Table to see all the outcomes in one place. Using Data Tables makes it easy to examine a range of possibilities at a glance.

Because you focus on only one or two variables, results are easy to read and share in tabular form. If automatic recalculation is enabled for the workbook, the data in Data Tables immediately recalculates; as a result, you always have fresh data. Cell B3 contains the input value.

A Data Table cannot accommodate more than two variables. If you want to analyze more than two variables, you can use Scenarios. Although it is limited to only one or two variables, a Data Table can use as many different variable values as you want. A Scenario can have a maximum of 32 different values, but you can create as many scenarios as you want. If you want to prepare forecasts, you can use Excel to automatically generate future values that are based on existing data, or to automatically generate extrapolated values that are based on linear trend or growth trend calculations.

You can fill in a series of values that fit a simple linear trend or an exponential growth trend by using the fill handle or the Series command. To extend complex and nonlinear data, you can use worksheet functions or the regression analysis tool in the Analysis ToolPak Add-in. Although Goal Seek can accommodate only one variable, you can project backward for more variables by using the Solver add-in.

By using Solver, you can find an optimal value for a formula in one cell—called the target cell—on a worksheet. Solver works with a group of cells that are related to the formula in the target cell.

Solver adjusts the values in the changing cells that you specify—called the adjustable cells—to produce the result that you specify from the target cell formula. You can apply constraints to restrict the values that Solver can use in the model, and the constraints can refer to other cells that affect the target cell formula. You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Goal Seek. Data Tables. Using Solver for capital budgeting. In this lesson, you'll learn how to use a what-if analysis tool called Goal Seek. Optional: You can download this example for extra practice. When you create a formula or function in Excel, you put various parts together to calculate a result.

Goal Seek works in the opposite way: It lets you start with the desired result , and it calculates the input value that will give you that result. We'll use a few examples to show how to use Goal Seek. Let's say you're enrolled in a class. You currently have a grade of 65, and you need at least a 70 to pass the class. Luckily, you have one final assignment that might be able to raise your average. You can use Goal Seek to find out what grade you need on the final assignment to pass the class.

In the image below, you can see that the grades on the first four assignments are 58 , 70 , 72 , and Even though we don't know what the fifth grade will be, we can go ahead and write a formula or function that calculates the final grade. Once we use Goal Seek, cell B6 will show us the minimum grade we'll need to make on that assignment. We can use Goal Seek to figure out how many people to invite. As you can see in the example above, some situations will require the answer to be a whole number.

If Goal Seek gives you a decimal, you'll need to round up or down , depending on the situation. For more advanced projects, you may want to consider the other types of what-if analysis: scenarios and data tables.

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. It will ask you to mention the new values, mention new values as listed in scenario 1. It will save this scenario for you.