Calculating Core Business KPIs

Raw transaction data tells you what happened. KPIs tell you what it means. Before building forecasts, you need a set of calculated metrics that summarize business performance: growth rates, margins, running totals, and moving averages.

Calculating Revenue Growth Rate

Growth rate measures how much revenue changed from one period to the next. It is the single most important input in trend-based forecasting.

Month-over-Month growth:

=(This_Month - Prior_Month) / Prior_Month

Year-over-Year growth:

=(This_Year_Total - Prior_Year_Total) / Prior_Year_Total

Compound Annual Growth Rate (CAGR) over N years:

=(End_Value / Start_Value)^(1/N) - 1

CAGR is more reliable than a single year's growth rate because it smooths out one-off spikes and gives you a realistic baseline growth assumption for multi-year forecasts.

Measuring Gross Margin

Gross Margin tells you what portion of revenue remains after direct costs. It is essential for cost forecasting and profitability modeling.

Row-level margin (works directly on the dataset):

(Revenue - Total_Cost) / Revenue

Aggregated margin by category (using SUMIF first):

• Total Revenue (Software): =SUMIF(J:J,"Software",O:O)
• Total Cost (Software): =SUMIF(J:J,"Software",Q:Q)
• Gross Margin % (Software): =(Total_Rev - Total_Cost) / Total_Rev

Always calculate margin at the aggregated level for forecasting inputs — row-level margins fluctuate too much to use as direct forecast assumptions.

Building Running Totals

Running totals show how a metric accumulates over time. They are used in Year-to-Date dashboards, budget tracking, and cumulative forecast comparisons.

Simple running total (data sorted by date, revenue in column O):

• Row 2: =O2
• Row 3 onward: =W2+O3 (W = your running total column)

Better — expanding range (safe even if rows are inserted):

=SUM($O$2:O2) → locks the start, expands the end as you autofill down.

YTD revenue (recalculates automatically when the year changes):

=SUMPRODUCT((YEAR(B$2:B$1501)=YEAR(TODAY()))*(B$2:B$1501<=TODAY())*O$2:O$1501)

Calculating Moving Averages

Moving averages smooth out short-term noise to reveal the underlying trend. A 3-month moving average is standard for monthly business data; a 12-month average is better for spotting long-term direction.

3-month moving average (monthly revenue in column C, starting row 4):

=AVERAGE(C2:C4)          → place in row 4, autofill down

Row 4 = average of months 1, 2, 3 Row 5 = average of months 2, 3, 4 (window slides forward)

To avoid errors in the first two rows where there isn't enough history yet:

=IF(ROW()-ROW($C$2)+1>=3, AVERAGE(OFFSET(C4,-2,0,3,1)), "")

12-month moving average (sliding window):

=IFERROR(AVERAGE(OFFSET(C13,-11,0,12,1)), "")     → place in row 13, autofill down

Building the KPI_Calculations Sheet

Create a dedicated KPI_Calculations sheet with a clean summary table. This becomes the single source of truth that all your forecast models reference for baseline assumptions.

Format all revenue and cost values as $#,##0. Format all rates and percentages as 0.0%.

Task: Build the KPI Summary Table

Goal: Create the baseline metrics that every forecast model in this course will reference.

1. Insert a new sheet and name it KPI_Calculations.
2. Create a table with KPI names in column A and three value columns for 2022, 2023, and 2024.
3. Use =SUMIF(Data!C:C, 2022, Data!O:O) — and repeat for 2023 and 2024 — to populate Total Revenue for each year.
4. Repeat the same SUMIF pattern for Total Cost (column Q) and Total Profit (column R).
5. Add a Gross Margin % row using the aggregated revenue and cost figures for each year.
6. Calculate YoY Revenue Growth for 2023 vs 2022 and 2024 vs 2023.
7. Add a CAGR row using =(D4/B4)^(1/2)-1 where D4 = 2024 revenue and B4 = 2022 revenue.
8. Format all revenue and cost cells as currency. Format all growth and margin cells as percentages with one decimal place.

Task: Create a Moving Average Chart

Goal: Visualize the revenue trend and smooth out monthly noise using moving averages.

1. Create a PivotTable from the main dataset (Insert → PivotTable). Put Month_Start in Rows and sum of Revenue in Values.
2. Copy the PivotTable output as values to a clean area — two columns: Month and Monthly Revenue.
3. In a third column, calculate the 3-month moving average using =IFERROR(AVERAGE(OFFSET(B4,-2,0,3,1)),"") starting in row 4.
4. In a fourth column, calculate the 12-month moving average using =IFERROR(AVERAGE(OFFSET(B13,-11,0,12,1)),"") starting in row 13.
5. Select all four columns and insert a Line Chart (Insert → Line → Line with Markers for Monthly Revenue, plain lines for both averages).
6. Label the three series clearly: "Monthly Revenue", "3M Moving Avg", "12M Moving Avg". This chart becomes your trend reference for all forecasting work in Section 2.

A completed workbook is attached for comparison and troubleshooting if needed.