Aug 02, 2016  Example of how to build a sensitivity analysis table in Excel 2016 to evaluate how changing two inputs simultaneously will affect an output (profit). Sep 28, 2010  Microsoft Excel for Mac 2011 represents the largest step forward for serious Mac spreadsheet jockeys in many years, more so than either Excel 2004 ( ) or Excel.

The tutorial explains how to use Goal Seek in Excel 2016, 3013 and 2010 to get the formula result you want by changing an input value.

What-If Analysis is one of the most powerful Excel features and one of the least understood. In most general terms, What-If Analysis allows you to test out various scenarios and determine a range of possible outcomes. In other words, it enables you to see the impact of making a certain change without changing the real data. In this particular tutorial, we will focus on one of Excel's What-If Analysis tools - Goal Seek.

What is Goal Seek in Excel?

Goal Seek is Excel's built-in What-If Analysis tool that shows how one value in a formula impacts another. More precisely, it determines what value you should enter in an input cell to get the desired result in a formula cell.

The best thing about Excel Goal Seek is that it performs all calculations behind the scenes, and you are only asked to specify these three parameters:

• Formula cell
• Target/desired value
• The cell to change in order to achieve the target

The Goal Seek tool is especially useful for doing sensitivity analysis in financial modeling and is widely used by management majors and business owner. But there are many other uses that may prove helpful to you.

For instance, Goal Seek can tell you how much sales you have to make in a certain period to reach $100,000 annual net profit (example 1). Or, what score you must achieve for your last exam to receive an overall passing score of 70% (example 2). Or, how many votes you need to get in order to win the election (example 3).

On the whole, whenever you want a formula to return a specific result but are not sure what input value within the formula to adjust to get that result, stop guessing and use the Excel Goal Seek function!

How to use Goal Seek in Excel

The purpose of this section is to walk you through how to use the Goal Seek function. So, we'll be working with a very simple data set:

The above table indicates that if you sell 100 items at $5 each, minus the 10% commission, you will make $450. The question is: How many items do you have to sell to make $1,000?

Let's see how to find the answer with Goal Seek:

1. Set up your data so that you have a formula cell and a changingcell dependent on the formula cell.
2. Go to the Data tab > Forecast group, click the What if Analysis button, and select Goal Seek…
   
3. In the Goal Seek dialog box, define the cells/values to test and click OK:
   • Set cell - the reference to the cell containing the formula (B5).
   • To value - the formula result you are trying to achieve (1000).
   • By changing cell - the reference for the input cell that you want to adjust (B3).
4. The Goal Seek Status dialog box will appear and let you know if a solution has been found. If it succeeded, the value in the 'changing cell' will be replaced with a new one. Click OK to keep the new value or Cancel to restore the original one.

   In this example, Goal Seek has found that 223 items (rounded up to the next integer) need to be sold to achieve a revenue of $1,000.

If you are not sure you will be able to sell that many items, then maybe you can reach the target revenue by changing the item price? To test this scenario, do Goal Seek analysis exactly as described above except that you specify a different Changing cell (B2):

As the result, you will find out that if you increase the unit price to $11, you can reach $1,000 revenue by selling only 100 items:

• Excel Goal Seek does not change the formula, it only changes the input value that you supply to the By changing cell box.
• If Goal Seek is not able to find the solution, it displays the closest value it has come up with.
• You can restore the original input value by clicking the Undo button or pressing the Undo shortcut (Ctrl + Z).

Examples of using Goal Seek in Excel

Below you will find a few more examples of using the Goal Seek function in Excel. The complexity of your business model does not really matter as long as your formula in the Set cell depends on the value in the Changing cell, directly or through intermediate formulas in other cells.

Example 1: Reach the profit goal

Problem: It is a typical business situation - you have the sales figures for the first 3 quarters and you want to know how much sales you have to make in the last quarter to achieve the target net profit for the year, say, $100,000.

Solution: With the source data organized like shown in the screenshot above, set up the following parameters for the Goal Seek function:

• Set cell - the formula that calculates the total net profit (D6).
• To value - the formula result you are looking for ($100,000).
• By changing cell - the cell to contain the gross revenue for quarter 4 (B5).

Result: The Goal Seek analysis shows that in order to obtain $100,000 annual net profit, your fourth-quarter revenue must be $185,714.

Example 2: Determine the exam passing score

Problem: At the end of the course, a student takes 3 exams. The passing score is 70%. All the exams have the same weight, so the overall score is calculated by averaging the 3 scores. The student has already taken 2 out of 3 exams. The question is: What score does the student need to get for the third exam to pass the entire course?

Solution: Let's do Goal Seek to determine the minimum score on exam 3:

Dark Souls 3 is an Action game and published by BANDAI NAMCO Entertainment released on 11 Apr, 2016 and designed for Microsoft Windows.As fires fade and the world falls into ruin, journey into a universe filled with more colossal enemies and environments. Download Dark Souls: Remastered (Mac OS) Torrent! (2018) Full PC game available for download direct link via torrent on this page. The Dark Souls 3 Mac gameplay has taken place as the fires have faded and the world falls into ruin. It is the conclusion of the trilogy and thus the story revolves around the Lord of Cinder. Dark Souls 3 for Mac game takes place in the set in the Kingdom of Lothric where you can play as the Ashen One. Dark souls mac torrent. Dark Souls III Mac OS X FULL GAME. Dark Souls III Mac OS X is an role-playing video-game from FromSoftware that can be played as single-player and multiplayer as well. Dark Souls III is similar with the previous two, and was released only in March 2016. From a third-person perspective, the player has to fight against enemies.

• Set cell - the formula that averages the scores of the 3 exams (B5).
• To value - the passing score (70%).
• By changing cell - the 3^rd exam score (B4).

Result: In order get the desired overall score, the student must achieve a minimum of 67% on the last exam:

Example 3: What-If analysis of the election

Problem: You are running for some elected position where a two-thirds majority (66.67% of votes) is required to win the election. Assuming there are 200 total voting members, how many votes do you have to secure?

Currently, you have 98 votes, which is quite good but not sufficient because it only makes 49% of the total voters:

Solution: Use Goal Seek to find out the minimum number of 'Yes' votes you need to get:

• Set cell - the formula that calculates the percentage of the current 'Yes' votes (C2).
• To value - the required percentage of 'Yes' votes (66.67%).
• By changing cell - the number of 'Yes' votes (B2).

Result: What-If analysis with Goal Seek shows that to achieve the two-thirds mark or 66.67%, you need 133 'Yes' votes:

Excel Goal Seek not working

Sometimes Goal Seek is not able to find a solution simply because it does not exist. In such situations, Excel will get the closest value and inform you that Goal Seeking may not have found a solution:

If you are certain that a solution to the formula you are trying to resolve does exist, check out the following troubleshooting tips.

1. Double check Goal Seek parameters

First off, make sure the Set cell refers to the cell containing a formula, and then, check if the formula cell depends, directly or indirectly, on the changing cell.

2. Adjust iteration settings

In your Excel, click File > Options > Formulas and change these options:

• Maximum Iterations - increase this number if you want Excel to test more possible solutions.
• Maximum Change - decrease this number if your formula requires more accuracy. For example, if you are testing a formula with an input cell equal to 0 but Goal Seek stops at 0.001, setting Maximum Change to 0.0001 should fix the issue.

The below screenshot shows the default iteration settings:

3. No circular references

For Goal Seek (or any Excel formula) to work properly, the involved formulas should not be co-dependent on each other, i.e. there should be no circular references.

That's how you perform What-If analysis in Excel with the Goal Seek tool. I thank you for reading and hope to see you on our blog next week!

You may also be interested in:

Data tables enable you to enter a series of possible values that Excel 2016 then plugs into a single formula. A one-variable data table substitutes a series of possible values for a single input value in a formula.

The following figure shows a 2017 sales projections spreadsheet for which a one-variable data table is to be created. In this worksheet, the projected sales amount in cell B5 is calculated by adding last year’s sales total in cell B2 to the amount that it is expected to grow in 2017 (calculated by multiplying last year’s total in cell B2 by the growth percentage in cell B3), giving you the formula

Because you clicked the Create From Selection command button on the Ribbon’s Formulas tab after making A2:B5 the selection and accepted the Left Column check box default, the formula uses the row headings in column A and reads:

As you can see in this figure, a column of possible growth rates ranging from 1% all the way to 5.0% have been entered down column B in the range B8:B24. Mac skype video greyed out for some contacts.

To create the one-variable data table shown that plugs each of these values into the sales growth formula, follow these simple steps:

1. Copy the original formula entered in cell B5 into cell C7 by typing = (equal to) and then clicking cell B5 to create the formula =Projected_Sales_2017.

   The copy of the original formula (to substitute the series of different growth rates in B8:B24 into) is now the column heading for the one-variable data table.

2. Select the cell range B7:C24.

   The range of the data table includes the formula along with the various growth rates.

3. Click Data→What-If Analysis→Data Table on the Ribbon.

   Excel opens the Data Table dialog box.

4. Click the Column Input Cell text box in the Data Table dialog box and then click cell B3, the Growth_2017 cell with the original percentage, in the worksheet.

   Excel inserts the absolute cell address, $B$3, into the Column Input Cell text box.

5. Click OK to close the Data Table dialog box.

   As soon as you click OK, Excel creates the data table in the range C8:C24 by entering a formula using its TABLE function into this range. Each copy of this formula in the data table uses the growth rate percentage in the same row in column B to determine the possible outcome.

6. Click cell C7, then click the Format Painter command button in the Clipboard group on the Home tab and drag through the cell range C8:C24.

   Excel copies the Accounting number format to the range of possible outcomes calculated by this data table.

A couple of important things to note about the one-variable data table created in this spreadsheet:

• If you modify any growth-rate percentages in the cell range B8:B24, Excel immediately updates the associated projected sales result in the data table. To prevent Excel from updating the data table until you click the Calculate Now (F9) or Calculate Sheet command button (Shift+F9) on the Formulas tab, click the Calculation Options button on the Formulas tab and then click the Automatic Except for Data Tables option (Alt+MXE).

• If you try to delete any single TABLE formula in the cell range C8:C24, Excel displays a Can’t Change Part of a Data Table alert. You must select the entire range of formulas (C8:C24 in this case) before you press Delete or click the Clear or Delete button on the Home tab.