Hands-On Tutorial

Sales Tax Trend Analysis & Revenue Forecasting

Based on the exercise by Prof. Bob Bland & Bernard Boadu
University of North Texas — Public Administration
Before You Begin

Getting Started

This tutorial guides you through building revenue forecasting formulas in Excel (or Google Sheets). You will do all the work in your own spreadsheet, then come back here to verify your answers.

Setup Steps

1 Click the Download Starter Spreadsheet button above to get the .xlsx file with the raw data pre-loaded in columns A through C.
2 Open the file in Excel, Google Sheets, or LibreOffice Calc. You should see 245 rows of data (rows 4–248) with Month, Sales Tax Collections, and CPI.
3 Columns D, E, and F are empty — those are where you will build your formulas. Work through the tasks below, then use the Check My Work panels to verify your answers.

Two Ways to Check Your Work

  • Quick Check: Paste a few specific cell values and get instant feedback with error diagnosis
  • Full Check: Upload your completed spreadsheet for a comprehensive scorecard of every cell

Each task also has a progressive hint system if you get stuck — try to work through it yourself first!

Task 01 Pending

Inflation Adjustment — Real Dollars

Raw sales tax revenue can be misleading because a dollar in 2001 had more purchasing power than in 2022. We adjust for inflation using the Consumer Price IndexCPI measures the average change in prices paid by consumers over time to convert to "real dollars"Dollars expressed in constant purchasing power, removing the effect of inflation.

Why Adjust for Inflation?

If a city collected $2M in October 2001 and $7.5M in January 2022, has revenue really grown that much? Part of the increase is just because everything costs more. Converting to real dollars lets us see the true growth in economic activity.

Formula
Real Dollars = (CPIcurrent ÷ CPIhistorical) × Nominal Dollars
CPIcurrent is the most recent CPI value in the dataset (cell C248 = 211.693).

Worked Example — October 2001 (Row 4)

( 211.693 ÷ 115.3 ) × $2,084,739 = $3,826,518

In today's dollars, the Oct 2001 collection of $2.08M is equivalent to about $3.83M.

What to Do in Excel

1 In cell D4, type the formula: =($C$248/C4)*B4
2 Press Enter. You should see a value around $3,826,518.
3 Select cell D4 and copy it down through cell D248. (Select D4, press Ctrl+C, then select D5:D248, press Ctrl+V — or drag the fill handle.)
4 Label column D as "REAL DOLLARS" in the header row. Save your worksheet.
Need Help?
Conceptual hint: The formula multiplies the nominal dollars by a ratio. The ratio should be greater than 1 for past dates (because prices were lower then), which scales the old dollar amount up to today's purchasing power. Make sure your ratio has the larger (current) CPI on top.
Formula detail: Your formula needs an absolute reference for the current CPI. The $ signs in $C$248 lock that cell so it doesn't shift when you copy the formula down. Without them, row 5 would incorrectly reference C249 (which doesn't exist). The other references (C4 and B4) should be relative so they shift to C5/B5, C6/B6, etc.
Exact formula for D4: =($C$248/C4)*B4

Copy this from D4 down to D248. Cell D248 should equal $7,209,726 (since the CPI ratio is 211.693/211.693 = 1, the last row's real dollars equals its nominal dollars).
Check My Work — Task 1

Paste the values from these cells in your spreadsheet. You can paste with or without the $ sign and commas.

Cell D4:
Cell D100:
Cell D248:
Task 02 Pending

Year-over-Year Percentage Change

Sales tax revenue has strong seasonalityRegular patterns that repeat each year (e.g., holiday shopping in December). To control for this, we compare each month to the same month one year earlier.

Why Year-over-Year?

Comparing Oct 2002 to Oct 2001 (not Sep 2002) removes seasonal effects. The result tells us: "compared to last year at this same time, is revenue growing or shrinking?"

We start at row 16 (Oct-02) because the first 12 months serve as the baseline.

Formula
% Change = ((Current Month − Same Month Prior Year) ÷ Same Month Prior Year) × 100
Use Real Dollars (column D) from Task 1, not the nominal dollars in column B.

Worked Example — Oct-02 (Row 16)

Oct-02 Real Dollars (D16) compared to Oct-01 Real Dollars (D4):

(( D16 − D4 ) ÷ D4 ) × 100 = see below

Complete Task 1 first to see the actual numbers here.

What to Do in Excel

1 In cell E16, type: =((D16-D4)/D4)*100
2 Press Enter. The value compares Oct-02 (row 16) to Oct-01 (row 4) — exactly 12 rows apart.
3 Copy the formula from E16 down through E248. Rows E4:E15 should remain empty (no prior year to compare to).
4 Label column E as "PERCENTAGE CHANGE". Save your worksheet.
Need Help?
Conceptual hint: The formula computes a percentage. A positive result means revenue grew compared to the same month last year; negative means it shrank. Make sure you're using column D (Real Dollars), not column B (nominal). Using nominal dollars would mix inflation with real growth.
Formula detail: The key is the 12-row offset. Row 16 compares to row 4 (16 - 12 = 4). Row 17 compares to row 5 (17 - 12 = 5). This works because the data is monthly and 12 months = 1 year. No absolute references are needed here — all references are relative so they shift correctly when copied.
Exact formula for E16: =((D16-D4)/D4)*100

Copy from E16 through E248. Cell E16 should show approximately 6.67%, meaning Oct-02 real collections were about 6.67% higher than Oct-01.
Check My Work — Task 2

Paste the percentage values from these cells (with or without the % sign).

Cell E16:
Cell E100:
Cell E248:
Task 03 Pending

12-Month Moving Average

Even after adjusting for inflation and seasonality, revenue data is "noisy." A moving averageThe average of a fixed window of data points that slides forward one period at a time smooths out random fluctuations to reveal the true underlying trend.

How the Sliding Window Works

  • First window: Average of % changes from rows E16 through E27 (Oct FY2 to Sep FY3)
  • Second window: Slides one month forward — rows E17 through E28
  • Each new average drops the oldest month and adds the newest
Formula
Moving Average = AVERAGE(prior 12 months of % change)
The first moving average goes in row 27 (Sep-03), averaging E16:E27.

What to Do in Excel

1 In cell F27, type: =AVERAGE(E16:E27)
2 Press Enter. This averages the first 12 months of percentage changes.
3 Copy the formula from F27 down through F248. Rows F4:F26 should remain empty.
4 Label column F as "MOVING AVERAGE". Save your worksheet.
Need Help?
Conceptual hint: The AVERAGE function takes a range of 12 cells. The "window" slides by one cell each row. When you copy the formula down, both the start and end of the range shift together, keeping the window at exactly 12 cells. Think of it like looking through a 12-month lens that moves forward through time.
Formula detail: The range E16:E27 contains 12 cells (16, 17, 18, ... 27). When copied to F28, it becomes E17:E28. When copied to F29, it becomes E18:E29. No absolute references are needed — relative references handle the sliding naturally. Make sure you start in row 27, not row 16 or row 28.
Exact formula for F27: =AVERAGE(E16:E27)

Copy from F27 through F248. Cell F27 should show approximately 7.01%.
Check My Work — Task 3

Paste the moving average values from these cells.

Cell F27:
Cell F100:
Cell F248:
Task 04 Pending

Graphing the Data

Visualizing the moving average on a line chart reveals trends that are hard to spot in numbers. Follow the steps below in Excel, then upload your file to see the reference chart here.

What to Do in Excel

1 Select the data in column F (the moving average values, F27:F248).
2 From the menu, go to INSERT → Line Chart. A chart will appear with numbers on the x-axis.
3 Fix the x-axis: Right-click the axis numbers → Select Data. For Horizontal Axis Labels, set the range to =Sheet1!$A$27:$A$248 (or the equivalent date range).
4 Optional: Right-click any point on the line → Add Trendline → choose Linear.
5 Save your worksheet.

What to Look For

  • Dips around FY3 (2003–04) and FY13–15 (2013–15) correspond with recessions
  • The current trend appears to be declining, though revenue is still growing (above 0%)
  • The trendline shows the overall long-term trajectory

(Uses your verified data from Tasks 1–3, or answer-key data if not yet verified)

Discussion Questions

Analysis & Forecasting

1 Describe the major trends in sales tax collections. Identify periods of (a) increasing at an increasing rate, (b) increasing at a decreasing rate, (c) decreasing at an increasing rate, and (d) decreasing at a decreasing rate. Which characterizes the latest trend? ▼
Guidance
  • (a) Increasing at increasing rate: Line above 0 and sloping upward
  • (b) Increasing at decreasing rate: Line above 0 but sloping downward
  • (c) Decreasing at increasing rate: Line below 0 and sloping downward
  • (d) Decreasing at decreasing rate: Line below 0 but sloping upward
Hint

The most recent segment appears to be case (b): the line remains above zero but has been trending downward (growth is decelerating).

2 Prepare a prediction for sales tax collections for the next three months (March, April, and May FY22). Discuss the rationale for each prediction. ▼
Prediction Calculator

Enter an assumed year-over-year percentage change. The tool projects using actual collections from the same month last year.

Rationale Hints
  • Look at the last 6 months of % Change (column E) to gauge recent momentum
  • Compare last 6 months avg of column E vs. column F — if E is lower, the trend is worsening
  • Increases compound: a 2% growth rate builds on the prior year's already-higher figure
3 Prepare a prediction for monthly sales tax collections for the next fiscal year (Oct FY22 to Sep FY23). What assumptions are you making? ▼
Forecast Template
Key Assumptions to Discuss
  • Does the recent deceleration trend continue, stabilize, or reverse?
  • Are there expected economic events that would affect sales tax?
  • Does the seasonal collection pattern remain stable?
Reference

Answer Key

Use this to spot-check individual values after you have attempted each task. Click to reveal.

▶ Show Answer Key
Revenue Forecasting Tutorial — Excel Exercise #1 — University of North Texas