How to Do Sensitivity Analysis
Sensitivity analysis is a straightforward way to understand which variables have the biggest impact on your outcomes. Often called a “what-if” analysis, it’s a powerful technique for stress-testing your financial models, marketing plans, and business forecasts. This article will show you exactly what it is, why it matters, and how you can perform one yourself using a simple spreadsheet.
What is Sensitivity Analysis, Really?
Imagine you have a financial model for your business. It’s built on a set of assumptions: a specific customer acquisition cost, a certain conversion rate, and an average order value. Your final profit projection looks good based on these numbers. But what happens if one of those assumptions is wrong? What if your ad costs are 15% higher than expected? Or if your conversion rate drops by just half a percentage point? That's where sensitivity analysis comes in.
It’s the process of systematically changing your key input variables one by one to see how those changes affect your final result, or output. Think of it like adjusting the knobs on a studio mixing board. You tweak one knob (ad spend), see how it affects the total sound (profit), then reset it. Then you tweak another knob (conversion rate) to see its impact. By doing this, you can identify which "knobs" are the most sensitive - the ones that can drastically alter the final mix with just a small turn.
This process helps you move past having a single, rigid forecast and instead gives you a range of possible outcomes, highlighting both risks and opportunities.
Why You Should Bother with Sensitivity Analysis
Building models based on a single set of assumptions is fragile. The real world is uncertain, and being prepared for that uncertainty is what separates proactive businesses from reactive ones. Here’s why this practice is so valuable:
- Better Risk Management: It helps you pinpoint the biggest threats to your success. If you discover that a small 5% increase in your material costs would erase your entire profit margin, you know you need to lock in a price with your supplier or find an alternative.
- Improved Decision-Making: It replaces gut feelings with data. Instead of just hoping your new pricing strategy will work, you can model how different customer adoption rates would impact revenue, helping you set more realistic targets.
- Smarter Resource Allocation: It shows you where to focus your attention. If your analysis reveals that website conversion rate has a much bigger impact on profit than website traffic, you know it’s more valuable to invest in optimizing your landing pages than just buying more ads.
- Enhanced Credibility: When you present a plan to stakeholders, investors, or your team, showing a sensitivity analysis demonstrates that you've considered various scenarios. It shows you’ve thought through potential risks and have a solid understanding of the business drivers.
Free PDF · the crash course
AI Agents for Marketing Crash Course
Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.
A Step-by-Step Guide to Performing Sensitivity Analysis in a Spreadsheet
The best way to understand sensitivity analysis is to build one yourself. All you need is a spreadsheet tool like Microsoft Excel or Google Sheets. Let's walk through a simple e-commerce profit model.
Step 1: Build Your Base Case Model
First, create a simple model with your core assumptions. Our goal is to calculate profit, so we’ll start with a basic formula outlining our business operations. Make sure each assumption is in its own cell so you can easily reference and change it later.
Your model might look like this:
--- Assumptions ---
Units Sold: 5,000
Price per Unit: $50.00
Cost per Unit: $20.00
Fixed Costs: $100,000
--- Calculation ---
Revenue: = Units Sold * Price per Unit ($250,000)
Total Variable Costs:= Units Sold * Cost per Unit ($100,000)
Gross Profit: = Revenue - Total Variable Costs ($150,000)
Net Profit: = Gross Profit - Fixed Costs ($50,000)In this "base case," our projected net profit is $50,000.
Step 2: Identify Your Key Input Variables
Next, decide which variables are the most uncertain or impactful. You don't need to test everything. Focus on the inputs that are most likely to change or that you have the least control over.
In our example, the key variables might be:
- Units Sold
- Price per Unit
- Cost per Unit
Fixed Costs could also be a variable, but for this example, we’ll assume they are stable.
Step 3: Define the Testing Range for Each Variable
For each variable you identified, determine a realistic range of values to test. This could be based on historical performance, industry benchmarks, or simply educated guesses for optimistic and pessimistic scenarios. A common approach is to use percentage changes from your base case.
For example, let's test a -20%, -10%, +10%, and +20% change for each of our three variables.
Step 4: Run the Scenarios Using an Excel Data Table
Manually changing each variable and noting the result is tedious. This is where Excel's "What-If Analysis" tools, specifically the Data Table feature, become incredibly useful.
Let's test the sensitivity of our Cost per Unit:
- Set up your table: Create a column with the different "Cost per Unit" values you want to test. In the cell to the right and one row above your first value, link directly to your final output formula (in our case, the Net Profit cell).
- Select the Data Table: Highlight the entire table, including both the column of input values and the header with the formula link.
- Open the Data Table tool: Go to the Data tab in Excel, click What-If Analysis, and select Data Table.
- Define the Input Cell: A pop-up will appear. Since our test values are organized in a column, you'll use the "Column input cell" field. In this field, select the original "Cost per Unit" assumption cell from your model.
- Click OK: Excel will automatically fill the table by substituting each value from your column into the model and calculating the resulting Net Profit for each case.
You can repeat this process for "Units Sold" and "Price per Unit" to see how sensitive your profit is to each variable independently.
For a more advanced view, you can use a Two-Variable Data Table to see how two variables interact. For example, you can see how different combinations of Units Sold (in a column) and Price per Unit (in a row) affect Net Profit simultaneously.
Step 5: Analyze and Visualize the Results
Once your tables are complete, it's time to interpret the findings. Look at the range of outcomes for each variable. Which one created the biggest swing in Net Profit?
A great way to visualize this is with a Tornado Chart. This type of bar chart arranges the variables vertically, with the variable causing the largest impact at the top and the smallest at the bottom, resembling a tornado. For our model, you might find that a 20% change in Units Sold has a far greater impact on profit than a 20% change in Cost per Unit.
This insight is the entire goal of the exercise: you’ve identified that hitting your sales volume is the most critical factor for profitability.
Putting It Into Practice: Real-World Examples
Example 1: SaaS Startup Pricing Model
Imagine a SaaS company trying to decide on a new pricing tier. Their base case assumption is a $49/month price with a 3% monthly churn rate. They can use a two-variable sensitivity analysis to model how revenue is affected by different price points (e.g., $39, $49, $59) and different churn rates (e.g., 2%, 3%, 4%). The analysis might reveal that even at a higher price, a small increase in churn could wipe out the gains, indicating they need to focus on customer retention alongside the price change.
Free PDF · the crash course
AI Agents for Marketing Crash Course
Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.
Example 2: Marketing Campaign ROAS
A marketing manager is planning a campaign with a projected Return on Ad Spend (ROAS) of 3:1. This is based on an assumed Cost-Per-Click (CPC) of $2.00 and a conversion rate of 2.5%. They can run a sensitivity analysis to see what happens if their CPC rises to $2.50 or their conversion rate drops to 2.0%. This helps them set a budget with a clear understanding of the risks and identify the break-even points for each metric.
Common Pitfalls to Avoid
- Analysis Paralysis: Don't try to model every possible variable. Stick to the 3-5 drivers you believe have the most significant and uncertain impact. The goal is clarity, not complexity.
- Unrealistic Ranges: Ensure your test ranges are plausible. Modeling a 300% increase in sales overnight isn't useful. Ground your assumptions in historical data or realistic market assessments.
- Ignoring Interdependencies: Standard sensitivity analysis changes one variable at a time. However, in the real world, variables are often connected. For example, increasing your price will likely decrease your units sold. You should use your judgment when interpreting results. If variables are highly dependent, what you might need is Scenario Analysis, where you change multiple variables at once to create a specific scenario (e.g., a "recession scenario" with lower demand and higher customer churn).
Final Thoughts
Sensitivity analysis is a practical technique that transforms a static business model into a dynamic tool for decision-making. By exploring a range of possible futures instead of relying on a single guess, you can better understand your key business drivers, anticipate risks, and make smarter, more resilient plans. It's a foundational skill for anyone looking to make data-backed strategic choices.
While modeling in spreadsheets is a fantastic way to learn, it can become difficult to manage when your actual data is live and scattered across different platforms like Google Analytics, Shopify, and Salesforce. At Graphed, we help overcome this by connecting directly to your key data sources. This allows you to ask 'what-if' questions in plain English about your real-time performance, without building manual models or exporting CSVs. You can simply ask how a drop in ad spend will impact qualified leads and get an instant, data-driven answer. This makes sensitivity and scenario analysis an accessible, everyday tool for the entire team, something we're quite excited about. Graphed turns complex analysis into a simple conversation.
Related Articles
How to Create Facebook Ads API for Claude Code
Create a Meta Marketing API access token that never expires, using a system user, so Claude Code can pull Facebook ad reports and manage campaigns.
ClickHouse vs Postgres: When to Switch to Columnar
Comparing ClickHouse vs PostgreSQL means matching storage architecture to workload. Row storage wins for transactions. Columnar wins for scan-heavy analytics.
Google Ads for Orthodontists: Get More Booked Consults
Most orthodontic Google Ads campaigns waste money on low-intent searches. The problem is structure, not the platform.