Sensitivity analysis in Excel

Summary

This video explains how to perform sensitivity analysis in Excel, focusing on how changes in input variables (volume, price per unit, and variable cost per unit) impact the outcome variable, which is the operating margin. It walks through setting up both one-variable and two-variable data tables to analyze how different inputs affect profitability. The video also covers conditional formatting for better visualization of results and troubleshooting common errors in data tables. Additionally, it touches on more advanced options for multi-variable analysis using Excel's Scenario Manager.

Takeaways

• 🔍 Sensitivity analysis examines how outcome variables, like Operating Margin, change in relation to input variables, such as volume, price, and variable cost.
• 📊 A one-variable data table in Excel shows how different levels of volume impact Operating Margin, ranging from 80,000 to 120,000 units.
• 💰 The relationship between revenue and volume is calculated as revenue = volume × price, and variable cost = volume × variable cost per unit.
• 📈 Contribution margin is calculated by subtracting variable cost from revenue, while Operating Margin is contribution margin minus fixed cost.
• 🔗 The one-variable data table links volume levels (in B2) to Operating Margin (in B11) to track how changes in volume affect profitability.
• ✏️ In a two-variable data table, both price and volume are varied to see their combined effect on Operating Margin, providing deeper insights.
• 🟩 Conditional formatting highlights positive and negative changes in Operating Margin, with green for gains, red for losses, and bold red for negative margins.
• ⚠️ Moving South-East on the data table (higher volume and price) improves Operating Margin, while moving North-West (lower price and volume) worsens it.
• 🔄 A second two-variable data table analyzes how changes in price and variable cost per unit impact Operating Margin, with positive margins when price exceeds variable cost by $4 or more.
• 📌 Excel’s What-If Analysis tool is used for scenarios involving two input variables at a time. For more than two inputs, Scenario Manager can be used.

Q & A

What is the main goal of the sensitivity analysis performed in this Excel file?

The main goal of the sensitivity analysis is to see how changes in input variables (expected volume, selling price per unit, and variable cost per unit) affect the outcome variable, which is the Operating Margin.

How is Operating Margin calculated in this analysis?

Operating Margin is calculated by subtracting Fixed Costs from the Contribution Margin, which is determined as Revenue (Volume x Price) minus Variable Costs (Volume x Variable Cost per unit).

Which cells in the spreadsheet contain the input variables for sensitivity analysis?

The input variables are in cells B2 (volume), B3 (price per unit), and B4 (variable cost per unit).

What is the purpose of a one-variable data table in this context?

A one-variable data table is used to analyze the sensitivity of the Operating Margin to changes in just one input variable, such as volume, while keeping the other inputs constant.

How is the one-variable data table set up in this analysis?

The data table is set up by entering different levels of volume in column D (e.g., 80,000 to 120,000 units) and linking the outcome variable (Operating Margin) in cell E3. The table is populated by setting the column input cell to B2 (volume).

What insight does the one-variable data table provide about the relationship between volume and Operating Margin?

The one-variable data table shows that for every 10,000 additional units of volume, the Operating Margin increases by $40,000, demonstrating a linear relationship between volume and Operating Margin.

What is the advantage of using a two-variable data table in this sensitivity analysis?

A two-variable data table allows you to analyze the effect of changing two input variables simultaneously (e.g., volume and price) on the Operating Margin, providing a more comprehensive view of sensitivity.

How is the two-variable data table set up in this analysis?

The two-variable data table is set up by inputting different levels of price in row 3 (e.g., $8 to $12) and different levels of volume in column H (e.g., 80,000 to 120,000 units). The table is populated by linking the row input cell to B3 (price) and the column input cell to B2 (volume).

What does the conditional formatting in the data table represent?

The conditional formatting highlights cells where Operating Margin is above or below the base case of $200,000. Green cells represent values greater than $200,000, red cells represent values below it, and cells with a negative Operating Margin are formatted with bold black text and a dark red background.

What happens to Operating Margin when both price and volume increase?

When both price and volume increase, the Operating Margin improves, moving in the South-East direction in the table, showing higher profitability. Conversely, moving in the North-West direction (lower price and lower volume) reduces profitability.