Skip to main content

What if Analysis



Sometimes, you want to see many different results from various combinations of inputs. Provided that you have only two input cells to change, the Data Table feature will do a sensitivity analysis.

Using the loan payment example, say that you want to calculate the price for a variety of principal balances and for a variety of terms.

You have a three-variable model for car payment. You want to do a sensitivity analysis with Principal from $21K to $30K and Term from 36 to 72 months.

Make sure that the formula you want to model is in the top-left corner of a range. Put various values for one variable down the left column and various values for another variable across the top.

The formula calculating payment has to be the top-left corner of the sensitivity analysis. Below that, enter 21000 to 30000 in 1000 unit increments. To the right of the top left cell, enter 36, 48, 54, 60, 66, 72.

From the Data tab, select What-If Analysis, Data Table....

On the Data tab, under What-If Analysis, choose Data Table.

You have values along the top row of the input table. You want Excel to plug those values into a certain input cell. Specify that input cell for Row Input Cell.

You have values along the left column. You want those plugged into another input cell. Specify that cell for the Column Input Cell.

The data table dialog asks for a Row Input Cell and a Column Input cell. Since you have monthly terms across the top row, those should get plugged in to B2. The loan principal along the left column should be plugged in to B1.

When you click OK, Excel repeats the formula in the top-left column for all combinations of the top row and left column. In the image below, you see 60 different loan payments, based on various inputs.

The results of the sensitivity analysis range from a low of $341 a month to $902 a month. In this screen shot, a color scale marks the highest numbers in red.

Note

I formatted the table results to have no decimals and used Home, Conditional Formatting, Color Scale to add the red/yellow/green shading.

Here is the great part: This table is “live.” If you change the input cells along the left column or top row, the values in the table recalculate. Below, the values along the left are focused on the 23K to 24K range.


Labels:

Comments

Post a Comment

Popular posts from this blog

Charts - Make your data presentable

One-click charts are easy: Select the data and press  Alt+F1 . What if you would rather create bar charts instead of the default clustered column chart? To make your life easier, you can change the default chart type. Store your favorite chart settings in a template and then teach Excel to produce your favorite chart in response to  Alt+F1 . Say that you want to clean up the chart above. All of those zeros on the left axis take up a lot of space without adding value. Double-click those numbers and change Display Units from None to Millions. To move the legend to the top, click the + sign next to the chart, choose the arrow to the right of Legend, and choose Top. Change the color scheme to something that works with your company colors. Right-click the chart and choose Save As Template. Then, give the template a name. (I called mine ClusteredColumn.) Select a chart. In the Design tab of the Ribbon, choose Change Chart Type. Click on the Templates folder to see the template that ...
Post a Comment
Read more

Data Analysis Tool Pack

  The  Analysis ToolPak  is an  Excel add-in  program that provides data analysis tools for financial, statistical and engineering data analysis. To load the Analysis ToolPak add-in, execute the following steps. 1. On the File tab, click Options. 2. Under Add-ins, select Analysis ToolPak and click on the Go button. 3. Check Analysis ToolPak and click on OK. 4. On the Data tab, in the Analysis group, you can now click on  Data Analysis . The following dialog box below appears. 5. For example, select Histogram and click OK to create a Histogram in Excel. Example Rank and Percentile The Rank and Percentile contained within the Analysis-ToolPak can be quickly used to find the rank of all the values in a list. The advantage of using the Rank and Percentile feature is that the percentile is also added to the output table. The percentile is a percentage that indicates the proportion of the list which is below a given number. Highlight the list (or the cells) which...
Post a Comment
Read more

20 Power BI Dax Measures

Power bi 20 dax measures 20 DAX (Data Analysis Expressions) measures in Power BI with examples: Total Sales: scss Copy code Total Sales = SUM (Sales[Amount]) Average Sales Price: scss Copy code Avg Sales Price = AVERAGE (Sales[Amount]) Total Units Sold: mathematica Copy code Total Units Sold = SUM ( Sales [ Quantity ] ) Total Customers: scss Copy code Total Customers = COUNTROWS (Customer) Total Products: mathematica Copy code Total Products = COUNTROWS ( Product ) Maximum Sales Amount: scss Copy code Max Sales Amount = MAX (Sales[Amount]) Minimum Sales Amount: scss Copy code Min Sales Amount = MIN (Sales[Amount]) Sales Growth Percentage: mathematica Copy code Sales Growth % = ( Total Sales - [ Total Sales Last Year ] ) / [ Total Sales Last Year ] Total Profit: scss Copy code Total Profit = SUM (Sales[Profit]) Total Orders: scss Copy code Total Orders = COUNTROWS (Orders) Total Customers with Sales: css Copy code Total Customers with Sales = COUNTROWS( FILTER ...
Post a Comment
Read more