Course: Advanced Excel
Topic: Performing Sensitivity Analysis on Business Variables
Course Link: Advanced Excel Course – JustAcademy
Demo Link: Register for a Course Demo
Official Website: JustAcademy
Sensitivity analysis is a business analysis technique used to understand how changes in input variables affect a particular output or business result. It helps analysts identify which variables have the greatest influence on profitability, revenue, costs, cash flow, and other performance measures.
Businesses operate under changing conditions. Selling prices, customer demand, raw material costs, employee salaries, interest rates, marketing expenditure, and production quantities may all change over time. Sensitivity analysis allows decision-makers to examine the potential impact of these changes before making important business decisions.
Microsoft Excel is particularly useful for sensitivity analysis because it provides formulas, What-If Analysis tools, Data Tables, charts, and other features that help compare different assumptions and outcomes.
For example, a company may want to understand how its profit changes when selling prices decrease by 5%, production costs increase by 10%, or sales volume changes by 20%. By creating a sensitivity model in Excel, the company can evaluate these situations systematically.
After completing this topic, learners will be able to:
Sensitivity analysis examines how the output of a model changes when one or more input variables change. The output is recalculated using different assumptions while other variables are held constant or changed according to a defined scenario.
A business model usually contains three main components:
A business sells 1,000 products at ₹500 each. The variable cost is ₹300 per product, and fixed costs are ₹100,000.
Revenue = Selling Price × Units Sold
Total Variable Cost = Variable Cost per Unit × Units Sold
Profit = Revenue − Total Variable Cost − Fixed Costs
Revenue = 500 * 1000
Total Variable Cost = 300 * 1000
Profit = Revenue - Total Variable Cost - Fixed CostsUsing these assumptions, revenue is ₹500,000, total variable cost is ₹300,000, and profit is ₹100,000. Sensitivity analysis can reveal how the ₹100,000 profit changes when price, quantity, costs, or fixed expenses change.
| Business Area | Purpose | Example |
|---|---|---|
| Sales and Marketing | Evaluate pricing and demand changes. | Measure the effect of a 10% price reduction. |
| Finance | Understand financial risk. | Analyze profit under higher interest expenses. |
| Operations | Control operating and production costs. | Test the impact of rising raw material costs. |
| Budgeting | Prepare flexible budgets. | Compare expected, optimistic, and pessimistic expenses. |
| Investment | Evaluate project assumptions. | Test returns at different growth rates. |
| Human Resources | Evaluate staffing costs. | Estimate the effect of salary increases. |
| Supply Chain | Assess supplier and logistics risks. | Analyze the effect of higher shipping costs. |
Before performing sensitivity analysis, identify the variables that may influence the result.
Selling price is the amount charged to customers for a product or service. Changes in price can affect revenue, profit margins, and customer demand.
Sales volume is the number of products or services sold during a period. Increasing sales volume may increase revenue, but the actual profit impact depends on variable costs, capacity, and pricing.
Variable costs change with production or sales volume. Examples include raw materials, packaging, and sales commissions.
Fixed costs generally remain unchanged within a relevant operating range. Examples include monthly rent, insurance, and some salaries.
Marketing expenditure includes advertising, promotions, campaigns, and lead-generation costs. Sensitivity analysis can help evaluate how different spending levels affect expected sales and profit, provided the model includes a reasonable relationship between spending and results.
Interest rates can influence loan payments, financing costs, and investment valuations. A sensitivity model can compare outcomes at different rates.
A reliable sensitivity analysis starts with a correctly structured worksheet. The input cells should be clearly labeled, and the output formula should refer to those inputs.
Consider a company that sells products. Enter the following assumptions in Excel:
| Cell | Business Variable | Value |
|---|---|---|
| B2 | Selling Price per Unit | ₹500 |
| B3 | Units Sold | 1,000 |
| B4 | Variable Cost per Unit | ₹300 |
| B5 | Fixed Costs | ₹100,000 |
Next, create the following calculations:
| Cell | Metric | Excel Formula | Expected Result |
|---|---|---|---|
| B7 | Revenue | =B2*B3 | ₹500,000 |
| B8 | Total Variable Cost | =B4*B3 | ₹300,000 |
| B9 | Total Cost | =B8+B5 | ₹400,000 |
| B10 | Profit | =B7-B9 | ₹100,000 |
| B11 | Profit Margin | =IFERROR(B10/B7,0) | 20% |
The model is now ready for sensitivity analysis. When the input values in B2:B5 change, the formulas automatically recalculate according to the workbook's calculation settings.
One-variable sensitivity analysis changes one input at a time while keeping the other input assumptions constant. This helps isolate the impact of an individual business variable.
For example, a company can test how profit changes when the selling price moves from ₹400 to ₹600 while sales volume, variable cost, and fixed costs remain unchanged.
Use the original model with 1,000 units sold, variable cost of ₹300 per unit, and fixed costs of ₹100,000. Test the following selling prices:
| Selling Price | Revenue | Total Cost | Profit |
|---|---|---|---|
| ₹400 | ₹400,000 | ₹400,000 | ₹0 |
| ₹450 | ₹450,000 | ₹400,000 | ₹50,000 |
| ₹500 | ₹500,000 | ₹400,000 | ₹100,000 |
| ₹550 | ₹550,000 | ₹400,000 | ₹150,000 |
| ₹600 | ₹600,000 | ₹400,000 | ₹200,000 |
Interpretation: With all other assumptions fixed, increasing the selling price by ₹50 increases modeled profit by ₹50,000. This result follows from the assumed model and does not account for possible changes in customer demand caused by higher prices.
=B10, in E2, above the output column.$B$2 in the Column input cell field.The precise cell range can differ depending on your worksheet layout. The important points are that the formula reference is correctly positioned and the input-cell field matches the orientation of the scenario values.
Two-variable sensitivity analysis changes two input variables simultaneously. It shows how combinations of two assumptions affect one formula result.
For example, a company may want to understand how profit changes when both selling price and sales volume vary. A two-variable Data Table displays the resulting profit for every combination of the selected prices and sales quantities.
Assume the variable cost is ₹300 per unit and fixed costs are ₹100,000. Test selling prices of ₹450, ₹500, ₹550, and ₹600 alongside sales quantities of 800, 1,000, 1,200, and 1,400 units.
The profit formula is:
Profit = (Selling Price * Units Sold)
- (Variable Cost per Unit * Units Sold)
- Fixed CostsFor example, at a selling price of ₹500 and 1,000 units sold:
Profit = (500 * 1000) - (300 * 1000) - 100000
Profit = 100000| Units Sold / Price | ₹450 | ₹500 | ₹550 | ₹600 |
|---|---|---|---|---|
| 800 | ₹20,000 | ₹60,000 | ₹100,000 | ₹140,000 |
| 1,000 | ₹50,000 | ₹100,000 | ₹150,000 | ₹200,000 |
| 1,200 | ₹80,000 | ₹140,000 | ₹200,000 | ₹260,000 |
| 1,400 | ₹110,000 | ₹180,000 | ₹250,000 | ₹320,000 |
Interpretation: The table shows that both selling price and sales volume influence profit. Under these assumptions, the highest modeled profit is ₹320,000 at a selling price of ₹600 and sales volume of 1,400 units. In an actual business, the company should also consider whether customers would purchase that quantity at the higher price and whether it has sufficient production capacity.
=B10.$B$2, in the Row input cell field because prices are arranged horizontally.$B$3, in the Column input cell field because quantities are arranged vertically.Percentage-based sensitivity analysis evaluates how an output changes when an input increases or decreases by a percentage. It is useful when business variables have different units or scales.
Use the following formula to calculate the percentage change in an output:
Percentage Change = (New Value - Original Value) / Original ValueIn Excel, if the original profit is in B10 and a scenario profit is in E3, use:
=IFERROR((E3-$B$10)/$B$10,0)Format the result as a percentage. This formula measures the change relative to the original profit. If the original value is zero, the percentage change is not meaningfully defined; the IFERROR formula returns zero as a fallback, so check the underlying values before interpreting it.
| Scenario | Original Profit | New Profit | Change in Profit | Percentage Change |
|---|---|---|---|---|
| Price decreases | ₹100,000 | ₹50,000 | -₹50,000 | -50% |
| Original scenario | ₹100,000 | ₹100,000 | ₹0 | 0% |
| Price increases | ₹100,000 | ₹150,000 | ₹50,000 | 50% |
Percentage changes help explain the relative size of a result, but they must be interpreted carefully when the original value is zero, negative, or very small.
Excel Data Tables are convenient for testing one or two input variables. For straightforward scenarios, you can also create separate calculations using formulas.
Suppose the original variable cost is ₹300 per unit. Test a 5%, 10%, and 15% increase in cost.
| Scenario | Formula for New Unit Cost | New Unit Cost |
|---|---|---|
| 5% increase | =300*(1+5%) | ₹315 |
| 10% increase | =300*(1+10%) | ₹330 |
| 15% increase | =300*(1+15%) | ₹345 |
To calculate profit using a scenario cost stored in C2, use:
=(B2*B3)-(C2*B3)-B5Adjust the cell references to match your workbook. This approach is helpful when you want a clearly labeled scenario worksheet or need to show calculations explicitly.
Charts make it easier to communicate how changing business variables affect an output.
Use a line chart to show how profit changes as selling price or cost increases. This is particularly useful when one variable is tested across a sequence of values.
Use a column chart to compare profit across discrete scenarios, such as low, medium, and high sales volumes.
A two-variable Data Table can be formatted as a heat map to show low and high outcomes quickly.
Use clear chart titles, readable axis labels, and consistent currency or percentage formats. A chart should support the analysis rather than replace the underlying figures and assumptions.
After calculating the scenarios, interpret the results in terms of business risk, opportunities, and practical constraints.
A variable that changes profit substantially may deserve closer monitoring. However, sensitivity alone does not measure the likelihood of a change occurring. A complete risk assessment should also consider how plausible each scenario is.
| Feature | Sensitivity Analysis | Scenario Analysis |
|---|---|---|
| Main purpose | Measures how changes in inputs affect an output. | Evaluates a defined set of combined business assumptions. |
| Variables changed | Often one or two variables at a time. | Can change several variables together. |
| Typical example | Testing profit at different prices. | Comparing optimistic, expected, and pessimistic business conditions. |
| Excel tools | Data Tables and formulas. | Scenario Manager and scenario worksheets. |
| Primary benefit | Identifies variables that influence the result. | Shows the combined effect of a set of assumptions. |
| Problem | Possible Cause | Solution |
|---|---|---|
| All Data Table results are identical | The wrong input cell was selected or the formula does not depend on that input. | Check the formula references and verify the selected input cell. |
| Incorrect results in a two-variable table | The row and column input cells were assigned incorrectly. | Use the horizontal scenario variable as the Row input cell and the vertical scenario variable as the Column input cell. |
| Data Table does not calculate | The selected range is incomplete or the formula reference is missing. | Select the full table, including the formula cell and scenario values. |
| Profit appears too high or too low | A cost, quantity, or fixed expense may be omitted from the model. | Validate the business formula and confirm that all relevant costs are included. |
| Percentage change is misleading | The original result is zero, negative, or close to zero. | Review absolute changes and use an appropriate comparison method. |
| Results do not refresh as expected | Workbook calculation settings may prevent immediate recalculation. | Check Formulas > Calculation Options and recalculate the workbook if needed. |
| Workbook calculation is slow | Large Data Tables or complex formulas require repeated calculations. | Reduce unnecessary scenarios and review calculation settings carefully. |
A business sells 800 units at ₹400 each. Variable cost is ₹250 per unit, and fixed costs are ₹60,000.
Tasks:
Use the same business model and test selling prices of ₹350, ₹400, ₹450, and ₹500, together with sales quantities of 600, 800, 1,000, and 1,200 units.
Tasks:
A company currently spends ₹180 per unit on raw materials. Test what happens when the unit cost increases by 5%, 10%, 15%, and 20%, assuming the selling price and sales volume remain constant.
Tasks:
Project Objective: Build an Excel report that evaluates the impact of price, sales volume, and cost changes on profitability.
Answer: Sensitivity analysis evaluates how changes in input variables affect an output, such as profit, revenue, cost, or cash flow.
Answer: It helps businesses understand the impact of changing assumptions, identify important variables, assess potential risks, and make better-informed decisions.
Answer: Excel formulas, one-variable and two-variable Data Tables, Scenario Manager, Goal Seek, and Solver can support different forms of business analysis.
Answer: One-variable analysis changes one input while keeping other assumptions fixed. Two-variable analysis evaluates combinations of values from two inputs.
Answer: A Data Table is a What-If Analysis feature that calculates a formula for multiple values of one or two input cells and displays the results in a table.
Answer: It can show which tested variables have the greatest effect on the selected output. The conclusion depends on the range of values tested and the model's assumptions.
Answer: It is the point where total revenue equals total costs, resulting in zero profit or loss.
Answer: Sensitivity analysis often isolates the effect of individual inputs. Scenario analysis evaluates a group of assumptions together, such as a best-case or worst-case business situation.
Answer: No. It calculates possible outcomes based on the model and assumptions. It does not guarantee actual future results.
Answer: Use clearly labeled tables, appropriate number formats, conditional formatting, charts, documented assumptions, and concise business interpretations.
Sensitivity analysis is an important Advanced Excel skill for evaluating how business variables affect financial and operational outcomes. By building a reliable model and testing different input values, users can investigate changes in selling price, sales volume, costs, and other important assumptions.
One-variable analysis helps isolate the effect of a single input, while two-variable analysis reveals how combinations of two inputs affect a result. Excel Data Tables, formulas, charts, and conditional formatting make these comparisons easier to calculate, interpret, and communicate.
For effective decision-making, always verify the model, use realistic assumptions, consider business constraints, and explain what the results mean. Sensitivity analysis supports better decisions, but its conclusions remain dependent on the quality of the model and the assumptions used.
Develop practical skills in Excel formulas, What-If Analysis, business reporting, dashboards, and data analysis through the Advanced Excel course.
Course Details: Explore the Advanced Excel Course
Book a Demo: Register for a Course Demo
Visit JustAcademy: Open the Official Website
