SolveWithExcel

Excel Challenge #19: Month-over-Month Growth & Variance Analysis

Difficulty

Intermediate–Advanced

Estimated Time

60–85 minutes

The Problem

Cumulative trends show progress, but leaders also ask:

  • “How fast are we growing month over month?”
  • “Where did performance accelerate or slow down?”
  • “Are we ahead of plan or behind forecast?”

In this challenge, you’ll build a month-over-month (MoM) growth and variance dashboard that highlights change, not just totals.

Dataset

Use SalesData (same structure as previous challenges):

| Date | Region | Product | Sales |

(You can reuse the data from Challenge #18.)

Your Task

Part A — Monthly Actuals

Create a monthly summary table:

MonthActual Sales
Jan
Feb
Mar
…

Rules:

  • Month must be a real date (month start).
  • Use SUMIFS to aggregate monthly sales.
  • (Optional) Add a Region filter with “All”.

Part B — Month-over-Month Growth

Extend the table:

MonthActualMoM ΔMoM %

Where:

  • MoM Δ = Current Month − Prior Month
  • MoM % = MoM Δ ÷ Prior Month

Handle the first month gracefully (blank or 0).

Part C — Forecast & Variance

Add a simple Forecast column:

  • Use a fixed growth assumption (e.g. +5% MoM), or
  • Base forecast on the prior month’s actual

Extend the table:

MonthActualForecastVarianceVariance %

Where:

  • Variance = Actual − Forecast
  • Variance % = Variance ÷ Forecast

Part D — Insight Callouts

Add KPI callouts:

  • Best Growth Month (highest MoM %)
  • Worst Growth Month (lowest MoM %)
  • Current Month vs Forecast (Ahead / Behind)

Part E — Visualization (Recommended)

Add:

  • Column chart: Actual vs Forecast
  • Line chart: MoM % trend

Charts should update automatically.

Requirements

  • Formulas only (SUMIFS, IF, LAG logic via cell references)
  • No Pivot Tables
  • Charts allowed
  • Must update when data or filters change

Learning Objectives

By completing this challenge, you will learn how to:

  • Calculate month-over-month growth rates
  • Perform variance analysis against a forecast
  • Build executive-style growth dashboards
  • Identify acceleration and deceleration patterns

Next Challenge

Excel Challenge #20: Scenario Analysis & What-If Forecasting

🔗 View reference solution on GitHub
(After you’ve tried the challenge)

Want more practical Excel challenges?
Subscribe to the Solve With Excel newsletter and get new problems delivered to your inbox.