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

20 Time Intelligence Dax Measures

20 Time Intelligence DAX measures in Power BI with examples: Year-to-Date Sales: css Copy code YTD Sales = TOTALYTD( [Total Sales] , Calendar [Date] ) Month-to-Date Sales: css Copy code MTD Sales = TOTALMTD( [Total Sales] , Calendar [Date] ) Quarter-to-Date Sales: css Copy code QTD Sales = TOTALQTD( [Total Sales] , Calendar [Date] ) Previous Year Sales: mathematica Copy code Previous Year Sales = CALCULATE ( [ Total Sales ] , SAMEPERIODLASTYEAR ( Calendar [ Date ] ) ) Year-over-Year Growth: css Copy code YoY Growth = DIVIDE( [Total Sales] - [Previous Year Sales] , [Previous Year Sales] ) Rolling 3-Month Average Sales: sql Copy code 3 M Rolling Avg Sales = AVERAGEX(DATESINPERIOD(Calendar[ Date ], MAX (Calendar[ Date ]), -3 , MONTH ), [Total Sales]) Cumulative Sales: scss Copy code Cumulative Sales = SUMX (FILTER(ALL(Calendar), Calendar [Date] <= MAX (Calendar[Date])), [Total Sales] ) Running Total Sales: scss Copy code Running Total Sales = SUMX (FILTER(ALL(Calendar), Calen...
Post a Comment
Read more

40 Power Query Editor features in Power BI

40 Power Query Editor features in Power BI along with examples: 1. Filter Rows: Remove rows based on conditions. Example: Remove rows with a null value in the "CustomerName" column. 2. Remove Duplicates: Eliminate duplicate rows. Example: Remove duplicate entries based on the "OrderID" column. 3. Sort Rows: Arrange rows in ascending or descending order. Example: Sort data by "Date" column in descending order. 4. Replace Values: Substitute one value with another. Example: Replace "N/A" with "Unknown" in the "Status" column. 5. Split Columns: Divide a column into multiple columns. Example: Split "FullName" into "FirstName" and "LastName." 6. Merge Queries: Combine data from multiple sources. Example: Merge customer and order data based on the "CustomerID." 7. Group By: Aggregate data based on a specific column. Example: Group sales data by "ProductCategory" and calculate the sum ...
Post a Comment
Read more

Data Cleaning Functions in Excel

  The CLEAN function Using the CLEAN function removes nonprintable characters text. For example, if the text labels shown in a column are using crazy nonprintable characters that end up showing as solid blocks or goofy symbols, you can use the CLEAN function to clean up this text. The cleaned‐up text can be stored in another column. You can then work with the cleaned text column. The CLEAN function uses the following syntax: CLEAN(text) The text argument is the text string or a reference to the cell holding the text string that you want to clean. For example, to clean the text stored in Cell A1, use the following syntax: CLEAN(A1) The CONCATENATE function The CONCATENATE function combines, or joins, chunks of text into a single text string. The CONCATENATE function uses the following syntax: CONCATENATE(text1,text2,text3,...) The text1, text2, text3, and so on arguments are the chunks of text that you want to combine into a single string. For example, if the city, state, and ZIP co...
Post a Comment
Read more