#Data analysis with excel for mac plus#
#Data analysis with excel for mac mac#

Step 5 - Click вЂAddвЂ™ to create your first experimental scenario. Step 4 - For the вЂњOriginalвЂќ scenario, do not adjust any values in the вЂScenario ValuesвЂ™ window. Step 3 - Name this scenario вЂњOriginalвЂќ and enter the cell references of all cells with constant values that you may consider changing in other scenarios (maximum 32 cells). Step 2 - Click вЂAddвЂ™ from the Scenario Manager pop-up window. Step 1 - Click вЂWhat If AnalysisвЂ™ from the Data tab and select Scenario Manager. Best practiceДЄs a best practice, the original worksheet data should be saved as a scenario so that you can revert to it after all the experiments have been completed. If we continue our bank loan example, we can determine our modelвЂ™s sensitivity to change by adjusting any or all of the values in cell B1, B2, or B3.

Only constant values should be changed within the Scenario Manager - cells with formulas should not be manually adjusted.

Scenarios may be named and edited, and a brief description provided.

Each scenario can be saved for comparative purposes.

The Scenario Manager allows the creation of an unlimited number of possible scenarios by changing up to 32 variables at a time.Some other noticeable differences between Goal Seek and Scenario Manager are listed below: This option is somewhat more advanced than Goal Seek in that it allows the adjustment of multiple variables at the same time.

#Data analysis with excel for mac mac#

Once вЂњOKвЂќ is selected from the Goal Seek Status window, the values on the worksheet are adjusted and are only retrievable by selecting the вЂUndoвЂ™ command (Ctrl+Z Windows shortcut/Cmd+Z Mac shortcut).ДЄnother what if analysis tool is the Scenario Manager.

The cell chosen in the вЂњBy changing cellвЂќ field must be a cell containing a constant.

The cell chosen in the вЂњSet cellвЂќ field must be a cell containing a formula.

Excel will change the principal (B1) based on the number we enter as the new value for cell B6.ДЄssuming that the interest amount (9%) and loan period (24 months) remain the same, the new principal amount is calculated and displayed in cell B1 if a valid solution exists. Our first inclination may be to find out how much we will be able to borrow if we pay $1750 per month and all other variables remain the same.

Practically speaking, we may not have much control over the interest rate, so it is more likely that we have the option of adjusting the amount we borrow, or the repayment period. Excel can work backwards to change either cell B1, B2, or B3 to reach that goal. Therefore, if we determine that the monthly repayment amount quoted is higher than desired, we can use Goal Seek to set the monthly amount to $1750.

In the example above, cell B5 is dependent on the variables in cells B1, B2, and B3. The amount to be paid each month is also calculated and shown in cell B6.Д«y using the Goal Seek command, we can indicate a desired outcome and Excel will determine the adjustment we need to make to a single variable.

#Data analysis with excel for mac plus#

Using the above information, the bank calculates that the amount borrowed plus interest over the loan period will be $47,200, as shown in cell B5. To illustrate how it works, imagine that the bank is offering an interest rate of 9% per annum on personal loans with 24 months to repay, and that you would like to borrow $40,000. Assuming that you know the single outcome you would like to achieve, the Goal Seek feature in Excel allows you to arrive at that goal by mathematically adjusting a single variable within the equation. The simplest sensitivity analysis tool in Excel is Goal Seek.