From Excel Formulas to Interactive Dashboard: My Dynamic Pricing Journey

Where I Started

In my previous post, I documented learning dynamic pricing concepts - understanding elasticity and the mental models behind pricing strategy. I understood the theory and formulas, but I hadn't actually built the analysis yet.

That's where this chapter begins.

Phase 1: Building the Analysis in Excel

Why Excel First?

Before writing any Python code, I made a deliberate choice: build this analysis completely in Excel first.

Why? To prove I understand the formulas, see the data visually, validate my logic, and demonstrate Excel proficiency - a skill many business roles expect.

This wasn't about avoiding Python. This was about building a solid foundation.

The Manual Work

I opened my four CSV datasets and started building:

Step 1: Understanding the Data

• 30 stores across Urban/Suburban/Rural locations

• Current price: $4.00 (uniform)

• Weekday and weekend demand data

I created a box plot visualization and the insight hit me immediately: Urban weekend demand (~280-330 units) is MASSIVE compared to rural weekday demand (~100-140 units). This isn't a one-size-fits-all pricing problem.

Step 2: Baseline Revenue I calculated baseline revenue for all 30 stores:

• Weekday Revenue: $16,560

• Weekend Revenue: $25,920

• Total Weekly Revenue: $42,480

This became my benchmark.

Step 3: Mapping Elasticity I added elasticity coefficients to each store:

• Urban: Weekday -0.5, Weekend -1.2

• Suburban: Weekday -0.8, Weekend -1.0

• Rural: Weekday -0.4, Weekend -0.6

Step 4: Designing My Strategy Based on elasticity insights:

• Urban: Weekday $4.20 (+5%), Weekend $3.80 (-5%)

• Suburban: Weekday $4.00 (0%), Weekend $3.90 (-2.5%)

• Rural: Weekday $4.40 (+10%), Weekend $4.00 (0%)

Step 5: Calculating New Demand I applied the elasticity formula:

New Demand = Original Demand × (1 + (Price Change % × Elasticity))

For Urban store S001:

• Weekday: 185 × (1 + (0.05 × -0.5)) = 180.4 units

• Weekend: 295 × (1 + (-0.05 × -1.2)) = 312.7 units

Step 6: The Results New revenue totals:

• Weekday: $16,910.57

• Weekend: $26,020.87

• Total: $42,931.44

Revenue gain: $451.44 (+1.06%)

It worked! More importantly, I understood WHY:

• Rural weekday prices up 10% → lost only 2.4% demand → revenue increased

• Urban weekend prices down 5% → gained 6% demand → revenue increased

• The elasticity-guided strategy worked exactly as theory predicted

Phase 2: Excel's Limitations

I had a working analysis, but I noticed problems:

1. Manual scenario testing: Testing different price points meant manually editing cells, recalculating, copying results - tedious and error-prone

2. Hard to share: Email an Excel file? Walk through formulas on a call? Not exactly impressive

3. No real-time experimentation: If asked "What happens at $4.50?" in an interview, I'd fumble through Excel - awkward

I asked myself: "How do professional data analysts handle this?"

The answer: They automate the analysis with code.

Phase 3: Building the Pricing Engine in Python

Why a "Pricing Engine"?

I decided to build a PricingEngine class - a reusable module that could load data automatically, calculate scenarios on demand, generate insights, and be used by other programs.

This felt more professional. More like real software engineering.

Note : Given that I had already understood the fundamentals and worked out manually in Excel, I wanted to speed up the automation and hence relied on my buddy Claude Code to generate below Python code

The Implementation

Structure:

class PricingEngine:
    def __init__(self, data_folder="excel-problem"):
        self.load_data()

Core Formula (Excel → Python):

def calculate_new_demand(self, original_demand, old_price, new_price, elasticity):
    price_change_pct = (new_price - old_price) / old_price
    demand_multiplier = 1 + (price_change_pct * elasticity)
    return original_demand * demand_multiplier

This is the exact same math I did in Excel, just wrapped in a reusable Python function.

Running Scenarios:

def run_pricing_scenario(self, pricing_strategy):
    # Takes: {'Urban': {'weekday': 4.2, 'weekend': 3.8}, ...}
    # Returns: detailed revenue results

The beauty: I can now test different scenarios by just changing dictionary values. No manual Excel edits.

Validation

I ran my test:

engine = PricingEngine()
results = engine.run_pricing_scenario(strategy)

Output:

Baseline total revenue: $42,480.00
New total revenue: $42,931.44

IT MATCHED MY EXCEL ANALYSIS EXACTLY. That validation moment felt incredible.

Phase 4: The Interactive Dashboard

I had a working pricing engine, but it was command-line based. I wanted something better - something interactive.

Discovering Streamlit

Streamlit is a Python library that turns data scripts into interactive web apps with almost no web development knowledge needed.

Instead of learning HTML, CSS, JavaScript, React... I could build a dashboard using just Python.

Building the Dashboard

I created app.py:

import streamlit as st
from pricing_engine import PricingEngine

st.title("🧃 FreshJuice Dynamic Pricing Optimizer")

Ran it: streamlit run app.py

A browser window opened with my title displayed. Magic.

Added sliders:

urban_weekday = st.sidebar.slider(
    "Urban Weekday Price",
    min_value=3.0,
    max_value=5.0,
    value=4.2,
    step=0.1,
    format="$%.2f"
)

I added 6 sliders (3 store types × 2 time periods).

Connected to engine:

pricing_strategy = {
    'Urban': {'weekday': urban_weekday, 'weekend': urban_weekend},
    # ... other store types
}
results = engine.run_pricing_scenario(pricing_strategy)

Every time I moved a slider, the entire analysis re-ran automatically.

Displayed results:

st.metric(
    label="Total Weekly Revenue",
    value=f"${summary['new_total_revenue']:,.2f}",
    delta=f"{summary['total_change']:.2f}"
)

Added interactive charts using Plotly for visualizing baseline vs new strategy.

The Challenges I Faced

Challenge 1: Streamlit Re-run Behavior

Every slider movement re-ran the entire script, reloading CSVs each time - slow and inefficient.

Solution: @st.cache_resource to load the engine once and reuse it. Massive performance improvement.

Challenge 2: DataFrame Formatting

Raw numbers displayed as 42931.44 instead of $42,931.44.

Solution: Format before displaying: display_df['Revenue'].apply(lambda x: f"${x:,.2f}")

Challenge 3: Preset Buttons

Wanted quick preset buttons, but sliders don't automatically respond to programmatic changes. Used st.session_state and st.rerun() as a workaround.

The Outcome

After several hours of coding, I had a fully functional interactive dashboard with:

1. Sidebar controls with 6 pricing sliders

2. Real-time metrics showing revenue impact

3. Comparison charts (baseline vs new strategy)

4. Store type breakdown analysis

5. Store-level details with filters

6. Automated insights generator

7. CSV export functionality

What I Learned

Technical Skills:

• Python Classes and code structure

• Pandas for data manipulation

• Streamlit for interactive web apps

• Plotly for visualizations

• Performance optimization with caching

Business Thinking:

• Separation of concerns (logic vs interface)

• User experience design

• Validation through incremental testing

The Process:

• Foundation first (Excel validated understanding)

• Incremental complexity (Engine → Dashboard)

• Test early and often

Reflections: Was This Approach Right?

Some might ask: "Why not just build the dashboard from the start?"

Here's why my approach worked:

1. Excel Forced Understanding Building formulas manually meant I couldn't hide behind code I didn't understand. When the dashboard showed results, I knew exactly how those numbers were calculated.

2. Python Made It Scalable Testing 100 different scenarios in Excel vs moving sliders 100 times? Automation wins.

3. Streamlit Made It Shareable Interactive demonstrations are WAY more impressive than Excel files.

Key Takeaways for Aspiring Data Analysts

1. Start Simple, Then Automate

Build the analysis manually first. Understand the logic. Then automate.

2. Real Problems > Toy Datasets

I created a business scenario (FreshJuice pricing) and built datasets to solve it. This approach forces critical thinking.

3. Document Your Journey

This blog solidified my understanding. When I interview, I can walk through my thought process clearly.

4. Tools Are Just Tools

Excel, Python, Streamlit - the real skill is analytical thinking. I could rebuild this in R, Tableau, or Power BI - the logic stays the same.

5. Imperfect and Done > Perfect and Never Shipped

My dashboard has rough edges, but it works. It's shippable. And I learned by shipping it.

Final Thoughts

Three weeks ago, I barely understood what price elasticity meant.

Today, I have:

• A working pricing analysis in Excel

• A reusable pricing engine in Python

• An interactive dashboard in Streamlit

• A deep understanding of dynamic pricing strategy

When I walk into my next interview and say "I built a dynamic pricing optimizer with an interactive dashboard" - I won't be bluffing.

I'll know exactly what I built and why.

And that confidence? That's worth more than any certification.