Building My First Dynamic Pricing Analysis: A Business Analyst's Journey

The Starting Point

I'm a Business Analyst preparing my portfolio, and I had just finished reading about dynamic pricing concepts - the theory, the examples, the math. I understood the what and the why. But I hadn't actually done it myself.

That's when I decided: I need to build a real business case analysis in Excel. Something I could put in my portfolio. Something that would prove I can take a business problem and work through it analytically.

My goal was clear:

• Build a dynamic pricing analysis for a CPG product

• Use Excel to demonstrate my analytical skills

• Keep it beginner-friendly but portfolio-worthy

• Show logical thinking, business understanding, and math skills

The Problem I Chose to Solve

After discussing requirements, I landed on this scenario:

FreshJuice, a beverage brand, sells 1L juice packs across 30 retail stores at a fixed price of $4.00. The question: Should they implement dynamic pricing (different prices for weekdays vs weekends) to increase revenue?

This felt real. This felt solvable. This felt like something I could actually present to a hiring manager.

The Challenge: Raw Data, No Instructions

I asked for datasets - and I got four CSV files:

1. Baseline sales data - 30 stores with current sales, locations, competitor info

2. Customer behavior - Who shops when, and how price-sensitive they are

3. Demand elasticity reference - The coefficients showing how demand responds to price changes

4. Historical promotions - Past experiments with pricing changes

But here's the thing: no formulas, no templates, no hand-holding. Just raw data and a problem to solve.

I opened Excel, imported everything, and stared at the screen.

Now what?

The Framework: Learning to Think, Not Just Calculate

Before jumping into calculations, I asked for a thinking framework. Not solutions - just principles to guide my analysis.

That's when I got the mental models that changed how I approached the problem:

Mental Model 1: The Revenue Equation

Revenue = Price × Quantity

The core tension: raising prices gives you more per unit, but you sell fewer units. Lowering prices means less per unit, but potentially more volume. The art is finding the sweet spot.

Mental Model 2: Customer Willingness to Pay

Not all customers are the same:

• Desperate customers (inelastic) will pay more - they need the product

• Flexible customers (elastic) will walk away if price is too high

The key question: Which customer segments can afford a price increase? Which need a discount to buy more?

Mental Model 3: The Elasticity Trade-Off

This is where it got technical. Elasticity coefficients show how "stretchy" demand is:

• Low elasticity (like -0.4): Customers barely react to price changes → opportunity to increase price

• High elasticity (like -1.2): Customers react strongly → opportunity to attract volume with discounts

Mental Model 4: Segment Thinking

Urban stores aren't the same as Rural stores. Weekday shoppers aren't the same as weekend shoppers. Don't apply one-size-fits-all pricing.

Mental Model 5: Competitor Context

I have competitor pricing data. I should use it. Don't price in a vacuum.

The First Roadblock: What Does Elasticity Actually Mean?

I opened the elasticity dataset and saw this:

And I thought: "I don't understand this very well."

This was my first honest moment of confusion.

The Breakthrough: Understanding Elasticity Through Examples

Here's what I learned:

The elasticity coefficient tells you: "When I change price by X%, how much does demand change?"

The formula:

% Change in Demand = Elasticity × % Change in Price

Example 1: Urban Weekday (-0.5)

If I increase price by +10%:

• % Change in Demand = -0.5 × (+10%) = -5%

• Demand drops by only 5%

Why so little? Office workers on Monday morning need their juice. They're not price-sensitive.

Example 2: Urban Weekend (-1.2)

If I increase price by +10%:

• % Change in Demand = -1.2 × (+10%) = -12%

• Demand drops by 12%!

Why so much? Weekend shoppers are families comparing prices. They're very price-sensitive.

The insight hit me: The size of the elasticity number tells me how easily customers walk away.

Getting My Hands Dirty: The First Calculation

I decided to test my understanding with real numbers from the dataset.

Test 1: Store S021 (Rural Weekday)

• Original demand: 75 units

• Elasticity: -0.4

• What if I increase price by +10%?

My calculation:

New Demand = 75 × (1 + (-0.4) × 0.1)
           = 75 × (1 - 0.04)
           = 75 × 0.96
           = 72 units

Result: Demand drops by only 3 units.

My first mistake: I initially calculated 76.96 and said "demand goes up by 2 units" - wrong direction! Price increase means demand decreases.

After correcting myself: 75 → 72 units. Lost 3 units.

The insight: Rural weekday customers are NOT price-sensitive. They'll keep buying even if I raise prices. This is a pricing opportunity.

Test 2: Store S001 (Urban Weekend)

• Original demand: 295 units (I initially used 185 by mistake, but the method was correct)

• Elasticity: -1.2

• What if I decrease price by -5%?

My calculation:

New Demand = 295 × (1 + (-1.2) × (-0.05))
           = 295 × (1 + 0.06)
           = 295 × 1.06
           = 312.7 units

Result: Demand increases by 17 units!

The insight: Urban weekend customers are VERY price-sensitive. A small discount attracts many customers. This is a volume opportunity.

The Pattern Emerged: Different Customers, Different Strategies

After working through these calculations, I started seeing the strategic picture:

The realization: This isn't just about math. It's about understanding human behavior and market dynamics.

The Temptation: Almost Taking the Shortcut

After understanding the concept, I got excited and wanted to speed up. I asked: "Can you help me design the Excel workbook with the first scenario worked out?"

And then I caught myself.

Earlier, I had specifically said: "Strictly no solutions, let me give this an honest try."

Was I about to undermine my own learning? Was I about to build a portfolio piece I didn't actually understand?

I pulled back: "You are right buddy. I went overboard. I'll do this by myself."

That moment of discipline mattered. Because when I sit in an interview and someone asks "Walk me through how you built this analysis" - I need to have actually built it.

The Testing Framework: What Price Points Should I Try?

Before diving into Excel, I asked one more strategic question: "How do I systematically test different pricing scenarios? Should I use 5% jumps? Random changes? Is there a standard?"

Here's the framework I got:

Option 1: Percentage-Based Testing

• Conservative: ±5%, ±10%

• Moderate: ±5%, ±10%, ±15%

• Easy to compare, businesspeople think in percentages

Option 2: Competitor-Based Testing

• Match competitor prices

• Slight premium (competitor + 5%)

• Slight discount (competitor - 5%)

Option 3: Psychological Price Points

• $3.99, $3.79, $4.29, etc.

• But for analysis, round numbers work fine

My Starting Hypothesis

Based on elasticity logic, I should test:

The plan: Build this scenario first, calculate revenue impact, then iterate.

What I've Learned So Far

Technical Skills

• How to apply elasticity coefficients to calculate demand changes

• How to structure scenario analysis in Excel

• How to think about revenue trade-offs (price vs. volume)

Business Thinking

• Customer segmentation matters - one strategy doesn't fit all

• Data should guide strategy, not just intuition

• Competitor context is critical

• Small percentage gains at scale can mean big dollars

The Mindset Shift

• Don't rush to solutions - understand the problem first

• Theory is useless without application

• Making mistakes (like my first calculation error) is part of learning

• Discipline to do the work yourself > taking shortcuts

What's Next: Building the Analysis

Now I'm ready to actually build this in Excel. My plan:

Phase 1: Build the Calculation Engine

• Set up columns for demand calculations with elasticity

• Create formulas that reference elasticity coefficients

• Calculate new demand and revenue for each store

Phase 2: Test the First Scenario

• Apply my hypothesis (increase rural weekday, decrease urban weekend, etc.)

• Calculate total revenue vs. baseline

• See if it works

Phase 3: Iterate and Optimize

• If revenue improves, can I optimize further?

• If it doesn't, what assumptions were wrong?

• Test multiple scenarios

Phase 4: Present Findings

• Create a summary dashboard

• Visualize revenue changes

• Write clear recommendations

• Include risks and implementation considerations

The Honest Truth

I'm at the beginning of actually building this analysis. I understand the concepts. I've worked through the math manually. I know the framework.

But I haven't built the Excel workbook yet. I haven't tested the scenarios. I don't know if my hypothesis will work.

And that's okay.

Because this is real learning. Not copying formulas from someone else. Not presenting work I didn't understand. Not faking it.

This is the messy, uncertain, trial-and-error process of actually solving a business problem.

And when I finish this analysis - when I've tested scenarios, made decisions, documented my logic - I'll have something real to show. Something I can defend. Something I actually understand.

Key Takeaways for Fellow Aspiring Analysts

If you're trying to build portfolio projects like I am, here's what I'd tell you:

1. Start with real problems, not templates - Don't just download a Kaggle dataset and run models. Pick a business problem you care about.

2. Ask for frameworks, not solutions - Getting principles and mental models is more valuable than getting answers.

3. Work through examples manually first - Before building complex Excel models, do the math by hand. Understand the logic.

4. Embrace confusion as a signal - When I didn't understand elasticity, I asked. That was the breakthrough moment.

5. Resist shortcuts - When I almost asked for the complete workbook, I would've robbed myself of learning.

6. Document your thinking - This blog is as much for me as for others. Writing this down solidifies my understanding.

Final Reflection

A week ago, I knew dynamic pricing existed but had never analyzed it myself.

Today, I understand:

• How elasticity works

• Why different customers need different strategies

• How to calculate demand changes from price changes

• How to structure a pricing analysis

Tomorrow, I'll build the Excel analysis that proves I can apply these concepts.

This is what portfolio building should feel like: challenging, uncertain, but ultimately empowering.

Because when I walk into an interview and say "I built a dynamic pricing analysis for a CPG brand" - I won't be bluffing. I'll know exactly what I did and why.

And that confidence? That's worth more than any pre-built template.

Status: Analysis in progress. Concepts understood. Excel workbook pending. Learning happening.

Next update: When I've completed the analysis and can share results, insights, and what I learned from actually building it.

This blog documents my journey from theory to application - the messy middle where real learning happens.