Budget Trend Analysis — Excel Exercise #3

Before You Begin

Getting Started

This exercise uses the line-item budget for a city's Development Department (building permits & inspections). You will analyze expenditure and revenue trends over six fiscal years, then develop a proposed budget for FY8.

Department Policy Goal

This city has a policy goal that development fees will cover operating expenditures of this department. Your analysis will determine whether this goal is being met and whether it can be sustained.

Setup Steps

1Click Download Starter Spreadsheet. It has three tabs: Exp (Expenditures), Rev (Revenues), and Budget (summary worksheet).

2The Exp tab has 32 line items across 8 columns (FY1–FY6 actuals, FY7 adopted budget, FY7 revised estimate).

3You will add columns for percentage changes, averages, and a proposed FY8 budget.

Two Ways to Check Your Work

Quick Check: Enter specific computed values for instant feedback
Full Check: Upload your spreadsheet for comprehensive scoring

Progressive hints are available if you get stuck.

Task 01 Pending

Compute Column Totals

Sum each fiscal year column in both the Expenditures and Revenues tabs. This gives you total departmental spending and revenue for each year.

Why Does This Matter?

Column totals let you see the overall trajectory of spending and revenue. Before diving into individual line items, the budget analyst needs the big picture: are total costs growing faster than revenues?

Formula

Total = SUM of all line items in each column

=SUM(C5:C36) for the Exp tab (adjust for Rev tab which has more rows)

Worked Example — Exp FY1

Total FY1 Expenditures: $3,561,200+$143,700+ ... = computed on load

What to Do in Excel

1In the Exp tab, go to row 37 (below the last line item). In column C, type =SUM(C5:C36). Label it "TOTAL EXPENDITURES" in column B.

2Copy the formula across to columns D through J to total all fiscal year columns.

3Switch to the Rev tab. Repeat for revenues: =SUM(C5:C38) in row 39. Copy across all columns.

Need Help?

Conceptual hint: Make sure you include all 32 line items in the Exp tab and all 34 line items in the Rev tab. The SUM range should cover exactly those rows.

Detail: In the Exp tab, data runs from row 5 to row 36 (32 items). In the Rev tab, data runs from row 5 to row 38 (34 items). Use AutoSum or type the formula manually.

Answers:
Total Exp FY1: loading...
Total Exp FY7 Revised: loading...
Total Rev FY1: loading...
Total Rev FY7 Revised: loading...

Check My Work — Task 1

Enter the totals you computed:

Total Exp FY1:

Total Rev FY7 Revised:

Task 02 Pending

Compute Annual Percentage Changes

For each line item, compute the annual percentage change between consecutive fiscal years. This produces six columns of percentage changes.

Why Does This Matter?

Percentage changes reveal the rate of growth, which is more useful than dollar amounts for trend analysis. A $10,000 increase in a $100,000 line item (10%) is very different from a $10,000 increase in a $4,000,000 item (0.25%).

Formula

% Change = (New Year − Old Year) ÷ Old Year

=(D5-C5)/C5 for FY1→FY2 (first % change column)

Important: For the last column (FY6→FY7), use the Revised Estimate (column J), not the Adopted Budget. Formula: =(J5-H5)/H5

Worked Example — Salaries FY1→FY2

($3,774,900−$3,561,200) ÷$3,561,200=6.00%

What to Do in Excel

1Move to cell K5 in the Exp tab. Type: =(D5-C5)/C5. Format as percentage. You should see ~6.00%.

2Copy K5 down to K37 (all line items plus total).

3Repeat for columns L through P (FY2→FY3 through FY5→FY6).

4For column Q (FY6→FY7), use the Revised Estimate: =(J5-H5)/H5. Copy down.

Need Help?

Conceptual hint: The formula is (New - Old) / Old. Make sure you divide by the earlier year. The result should be a decimal like 0.06 (which formats as 6%).

Detail: You can fill an entire block at once: enter K5, drag to K37, then select K5:K37 and drag the fill handle right to Q37. But remember to fix column Q to use the Revised Estimate!

Answers:
Salaries FY1→FY2: loading...
Salaries FY6→FY7 (revised): loading...

Check My Work — Task 2

Enter percentage changes (as decimals or percentages):

Salaries % Chg FY1→FY2:

Salaries % Chg FY6→FY7 (rev):

Task 03 Pending

Average % Change & Proposed FY8 Budget

Compute the average percentage change across all six periods for each line item, then use a 1.0% growth factor to propose FY8 expenditures based on the Revised Estimate.

Why 1.0% Instead of the Average?

The average percent change may overstate needs since growth rates have been trending downward. A conservative 1.0% increase provides a more realistic baseline. In practice, analysts exercise judgment about each line item.

Formulas

Avg % Change = AVERAGE of 6 annual % change columns

=AVERAGE(K5:P5) — but remember you have columns K through Q (6 columns)

Proposed FY8 = Revised Estimate × 1.01

=(J5*1.01)

Worked Example — Salaries

Avg % Change:loading...

Proposed FY8:$4,332,300×1.01=loading...

What to Do in Excel

1In column R, type =AVERAGE(K5:Q5) and label it "Avg % Chg". Copy down for all line items.

2In column S, type =(J5*1.01) and label it "Proposed Budget FY8". Copy down and sum the column.

3Repeat both steps for the Revenue tab.

Need Help?

Conceptual hint: The AVERAGE function in Excel handles this automatically. Make sure your range covers all 6 percentage change columns (K through Q, not K through P which would only be 5).

Detail: The 1.01 factor means: original amount (1.00) plus 1% growth (0.01). Multiplying by 1.01 increases the Revised Estimate by exactly 1%.

Answers:
Total Proposed Exp FY8: loading...
Total Proposed Rev FY8: loading...

Check My Work — Task 3

Enter computed values:

Salaries Proposed FY8:

Total Exp Proposed FY8:

Total Rev Proposed FY8:

Task 04 Pending

Budget Worksheet & Balance Analysis

Transfer the totals for Adopted, Revised, and Proposed budgets to the Budget Worksheet tab. Then compute the Net (Revenue − Expenditure) for each column.

Why Does This Matter?

The budget worksheet is the executive summary that decision-makers see. It answers the fundamental question: will revenues cover expenditures? A negative Net means the department is running a deficit.

Link Formula

In Budget tab: =Exp!S37 (links to the total on the Exp tab)

This auto-updates when you change the Exp tab — a powerful Excel feature for budget management.

Revenue vs. Expenditure Trends

Need Help?

Conceptual hint: The Budget Worksheet has three columns: Adopted FY7, Revised FY7, and Proposed FY8. Use link formulas (=Exp!cell and =Rev!cell) to pull totals from the other tabs.

Detail: Net = Total Revenue − Total Expenditure. If positive, the department has a surplus. If negative, it has a deficit. Check each column.

Answers (Budget Worksheet):
Proposed FY8 Net: loading...

Check My Work — Task 4

Enter the Net (Rev − Exp) for Proposed FY8:

Net Proposed FY8:

Discussion

Discussion Questions

1What trends exist in the percent changes in revenues and expenditures? Did the department meet its goal of generating revenues sufficient to cover direct expenditures?

Key Points

Both expenditures and revenues show positive but declining growth rates
Compare total revenues to total expenditures for each fiscal year to assess the policy goal
Look at whether the gap between revenues and expenditures is widening or narrowing

2Did the department adopt a "balanced budget" for the current year? What issues might arise when defining a balanced budget? Are the department's services public or private goods?

Key Points

Compare the Adopted Budget totals for revenue and expenditure
"Balanced" can mean different things: revenues = expenditures, or revenues ≥ expenditures
Building permits and inspections have characteristics of both public goods (safety regulation) and private goods (service to specific property owners)
This affects whether fee-for-service or tax-based funding is more appropriate

3Given your preliminary projections, will the department be able to adopt a balanced budget next year? What options exist if revenues fall short?

Key Points

Check your proposed FY8 Net — is it positive or negative?
Options include: raising fees, reducing services, transferring from general fund, deferring capital purchases, or reorganizing operations
Consider the political implications of each option

4If target-base budgeting is used, what target level of funding would you recommend for FY8?

Key Points

Target-base budgeting sets a baseline (e.g., current services level) and requires justification for anything above it
The "current services" level would maintain existing service levels adjusted for inflation and workload changes
Consider which line items represent mandatory vs. discretionary spending

5Discuss the key budget linkages for this department.

Key Points

Revenue linkage: fees are tied to construction activity, which is cyclical
Expenditure linkage: personnel costs (salaries + benefits) dominate and are relatively fixed
The mismatch between cyclical revenue and fixed costs creates budget risk
Capital costs (construction, vehicles, equipment) offer more budget flexibility

Reference

Answer Key

Complete all four tasks to reveal, or:

Development Department Budget Trend Analysis

Getting Started

Department Policy Goal

Setup Steps

Two Ways to Check Your Work

Compute Column Totals

Why Does This Matter?

Worked Example — Exp FY1

What to Do in Excel

Compute Annual Percentage Changes

Why Does This Matter?

Worked Example — Salaries FY1→FY2

What to Do in Excel

Average % Change & Proposed FY8 Budget

Why 1.0% Instead of the Average?

Worked Example — Salaries

What to Do in Excel

Budget Worksheet & Balance Analysis

Why Does This Matter?

Revenue vs. Expenditure Trends

Discussion Questions

Key Points

Key Points

Key Points

Key Points

Key Points

Answer Key

Budget Worksheet Summary

Expenditure Proposed FY8

Revenue Proposed FY8