Hello! Welcome to the next lesson in your journey to becoming a top-tier FP&A analyst.
In our last session, we mastered NPV and IRR, the core tools for evaluating a project's profitability based on a set of cash flow forecasts. However, a crucial part of an analyst's job is not just to evaluate a forecast, but to challenge and test its underlying assumptions. What if our price assumption is wrong? How does a price change ripple through the entire financial picture?
Today, we will address this by tackling the learning outcome: Build a dynamic pricing model that incorporates price elasticity assumptions to simulate revenue and profit impacts. We will move from static assumptions to a dynamic model that helps answer one of the most critical business questions: "What price should we charge to maximize our profit?"
This lesson will blend economic theory with practical Excel modeling, equipping you with a powerful tool for strategic analysis.
1. The Foundation: Price Elasticity of Demand (PED)
Before we can build a pricing model, we need to understand the fundamental relationship between price and customer demand. This is measured by Price Elasticity of Demand (PED).
In simple terms, PED tells you how sensitive the quantity of a product sold is to a change in its price.
The formula is:
The result of this calculation tells us a lot:
- Inelastic Demand (|PED| < 1): A change in price has a relatively small effect on the quantity demanded. This is common for necessities like gasoline or medicine.
- Elastic Demand (|PED| > 1): A change in price has a large effect on the quantity demanded. This is common for luxury goods or items with many substitutes.
- Unitary Elastic Demand (|PED| = 1): The percentage change in quantity demanded is exactly equal to the percentage change in price. This is the theoretical point where revenue is maximized.
Let's begin with a resource that explains the core concept and its significance.
Price Elasticity Of Demand Formula
This article provides a solid introduction to the Price Elasticity of Demand formula and its importance in business decision-making.
Read the introductory sections 'Price Elasticity Of Demand Formula' and 'Significance and Use'. Focus on understanding the basic formula and why a company would need to analyze its PED.
Now, let's see how to calculate this in Excel. The following video demonstrates a practical, step-by-step method using a data table of prices and corresponding quantities.
How to Calculate Elasticity of Demand with Excel using a table example
This video from Econ Examples provides a clear, hands-on demonstration of how to calculate PED in Excel.
Watch the video from the beginning until the 6:37 mark. Pay close attention to how the instructor builds the formulas for: Total Revenue (Price * Quantity) Marginal Revenue (the change in total revenue for each additional unit) Percentage change in Quantity and Price (using the midpoint method) The final Elasticity of Demand calculation.
This practical skill of calculating elasticity from a schedule of price and quantity data is a fundamental building block for any pricing analysis.
2. From Elasticity to a Predictive Model
Calculating elasticity from historical data is useful, but the real power comes from using this concept to build a predictive model. The goal is to create a model where you can input a price and have it predict the demand, revenue, and ultimately, the profit.
The core of this model is a demand curve, which is a mathematical representation of the relationship between price and quantity. For many modeling purposes, we can start by assuming a linear relationship.
Now, let's build the model. This next video is the centerpiece of our lesson. It will walk us through creating a pricing model from scratch and then optimizing it.
Pricing Analytics: Optimizing Price
This tutorial from Michael Lamont is an excellent demonstration of applied pricing analytics. We'll start by building the core components of our model.
Please watch the segment from 01:36 to 03:39. Focus on these steps: How to derive a linear demand curve equation from two known points (e.g., at a price of $75, we sell 5,000 units). Setting up the initial Excel model with cells for price, manufacturing cost, the demand formula, and the profit formula.
You've just built a simple but powerful model:
- Input: Price
- Calculations: Demand (based on the demand curve equation)
- Output: Profit (based on
(Price - Cost) * Demand)
This structure is the foundation of our dynamic pricing model.
3. Optimization with Excel Solver
Our model is "dynamic" in the sense that we can manually change the price and instantly see the impact on profit. But how do we find the best price without endless trial and error? For this, we use one of Excel's most powerful tools: Solver.
Solver is an add-in that performs optimization. You can tell it to maximize or minimize a specific cell (our objective) by changing other cells (our variables), subject to certain rules (our constraints).
Let's see it in action.
Pricing Analytics: Optimizing Price
We'll continue with the same video to see how to use Solver to find the profit-maximizing price.
Rewatch the section from 02:50 to 04:52. If you don't have the Solver add-in enabled in Excel, the video starts by showing you where to find it (0:00 - 1:36) under Data -> Analysis. Pay close attention to how the three components of Solver are set: Set Objective: The cell containing the Profit formula. To: Max (since we want to maximize profit). By Changing Variable Cells: The cell containing our guess for the Price.
This is the essence of dynamic price modeling. We've created a logical model of our business and used an optimization tool to find the best possible outcome.
An Advanced Application: Complementary Products
The analysis can become even more insightful when we consider related products. The "razor and blades" model, where one product drives sales of another, is a classic business strategy. Our pricing model can handle this complexity easily.
Pricing Analytics: Optimizing Price
Now, let's look at a more advanced and realistic scenario. What happens to our printer price if we also profit from selling ink cartridges?
Watch the final part of the video from 04:52 to 07:53. Note how the profit formula is updated to include the profit from ink cartridges. Observe how this drastically changes the optimal price for the printer, even leading to a situation where it's best to sell the printer at a loss to maximize total profit.
This example perfectly illustrates why this type of modeling is so valuable for an FP&A analyst. A simple, product-level profitability analysis would miss the larger strategic picture.
Test your understanding!
You are tasked with setting the price for a new software subscription.
- Your variable cost per user is $10/month.
- Through market research, you've estimated a linear demand curve given by the equation: Quantity = 5000 - 20 * Price.
Set up a simple model in Excel and describe how you would use Solver to find the profit-maximizing monthly price.
Show answer
-
Set up the model in Excel:
- Cell A1: "Price" (Enter an initial guess, e.g., $100)
- Cell A2: "Variable Cost" (Enter $10)
- Cell A3: "Quantity" (Enter the formula
=5000 - 20 * A1) - Cell A4: "Profit" (Enter the formula
=(A1 - A2) * A3)
-
Use Solver:
- Go to
Data > Solver. - Set Objective: Select cell A4 (the Profit cell).
- To: Select
Max. - By Changing Variable Cells: Select cell A1 (the Price cell).
- Add Constraint (Good Practice):
A1 >= 10(Price must be at least equal to the variable cost). - Click Solve. Solver will find the optimal price is $130, which yields a quantity of 2,400 units and a maximum profit of $288,000.
- Go to
Conclusion
In this lesson, we have constructed a powerful and practical tool for strategic financial analysis. You've gone beyond using static assumptions and learned how to model the dynamic relationship between price, demand, and profitability. This skill is invaluable in business case development, strategic planning, and pricing committee discussions.
Key Takeaways:
- Price Elasticity of Demand (PED) is the core concept that measures how demand responds to price changes.
- A demand curve models the relationship between price and quantity, forming the engine of a pricing model.
- By combining the demand curve with cost information, you can create a profit formula that is a function of price.
- Excel Solver is an optimization tool that can automatically find the price that maximizes profit within your model.
- This modeling approach can handle complex scenarios, such as the impact of complementary products, providing deep strategic insights.
Preview of the Next Lesson:
Our pricing model found the optimal price based on a deterministic demand curve—meaning we assumed the relationship Quantity = 5000 - 20 * Price was certain. But in the real world, there's always uncertainty. What if the '5000' is really a range between 4500 and 5500? In our next lesson, we will learn how to use Monte Carlo simulation to model the probability distribution of project outcomes and assess risk. This will allow us to move from finding a single optimal point to understanding the full range of potential outcomes and their likelihood.