Hands-On Exercise

Waste Water Cost Schedule Analysis

POLS 557 — Public Budgeting & Finance
University of North Texas
⏱ ~45 min  |  Beginner  |  Excel / Google Sheets
Before You Begin

Getting Started

This exercise walks you through completing a short-run cost schedule for a city's waste water collection and treatment system. You will calculate missing cost columns, graph the results, and analyze pricing strategies.

Setup Steps

1Click Download Starter Spreadsheet above. The file contains volume data (q in millions of gallons) and actual costs ($ in 000s).
2Open the file in Excel, Google Sheets, or LibreOffice Calc. You should see 9 rows of data (q = 0 through q = 8).
3Several columns are blank: Fixed Costs, Variable Costs, Average Costs, and Marginal Costs. Those are the columns you will fill in.

Key Definitions

  • Fixed Costs: Costs that remain constant regardless of the quantity produced
  • Variable Costs: Costs that change as the quantity of units produced changes
  • Total Costs: Fixed Costs + Variable Costs
  • Average Costs: Total Costs ÷ Number of Units Produced
  • Marginal Costs: The cost of producing one additional unit

Two Ways to Check Your Work

  • Quick Check: Type in specific cell values for instant feedback with error diagnosis
  • Full Check: Upload your completed spreadsheet for a comprehensive scorecard

Each task has a progressive hint system — try to work through it yourself first!

Task 01 Pending

Complete the Cost Schedule

The spreadsheet gives you the volume of waste water (q) and the Actual CostsThese are the Total Costs observed at each production level (which represent Total Costs). Your job is to compute the four missing columns.

Why Does This Matter?

Understanding cost structures is essential for public utilities. Fixed costs (like infrastructure) don't change with usage, but variable costs (like treatment chemicals) do. A budget analyst must understand these relationships to set fair and sustainable utility rates.

Formulas
Fixed Costs = Total Costs when q = 0
Variable Costs = Total Costs − Fixed Costs
Average Costs = Total Costs ÷ q
Marginal Costs = TCq − TCq−1
Note: Average Cost is undefined at q = 0 (division by zero). Marginal Cost starts at q = 1.

Worked Example — q = 1 (millions of gallons)

Total Cost at q=0 is $360 (in 000s), so Fixed Costs = $360 for all rows.

Variable Costs: $380 − $360 = $20
Average Costs: $380 ÷ 1 = $380
Marginal Costs: $380 − $360 = $20

What to Do in Excel

1In the Fixed Costs column (C), enter 360 for every row (q=0 through q=8). Fixed costs don't change with output.
2In the Variable Costs column (E), for row q=0 enter 0. For q=1, type: =D5-C5 (assuming Total Costs are in column D and Fixed Costs in column C). Copy down through q=8.
3In the Average Costs column (B), leave q=0 blank (or N/A). For q=1, type: =D5/A5. Copy down through q=8.
4In the Marginal Costs column (G), leave q=0 blank. For q=1, type: =D5-D4. Copy down through q=8.
Need Help?
Conceptual hint: Fixed costs are the costs that exist even when no waste water is processed (q=0). Look at the Actual Costs when q=0 — that's your fixed cost for every row. Variable costs are just what's left over after subtracting fixed costs from total costs.
Detail: Marginal cost is the change in total cost from one unit to the next. At q=1: MC = 380 − 360 = 20. At q=2: MC = 422 − 380 = 42. Notice how marginal costs increase as output grows — this is the law of increasing marginal costs.
Complete answers:
Fixed Costs (all rows): 360
Variable Costs: 0, 20, 62, 129, 224, 350, 510, 707, 944
Average Costs (q=1–8): 380, 211, 163, 146, 142, 145, 152.43, 163
Marginal Costs (q=1–8): 20, 42, 67, 95, 126, 160, 197, 237
Check My Work — Task 1

Enter the values from your spreadsheet for these specific cells:

Task 02 Pending

Graph the Cost Curves

Create a chart showing the Average Cost and Marginal Cost curves. This visualization reveals where the two curves intersect — a critical point for pricing decisions.

Why Graph These Curves?

The intersection of the average cost and marginal cost curves occurs at the minimum of the average cost curve. This is because when marginal cost is below average cost, it pulls the average down; when it is above, it pulls the average up. The intersection point is the most efficient scale of production.

Average cost starts high at $380 (q=1), decreases to minimum around $142 (q=5), then rises. Marginal cost starts at $20 (q=1), rises steadily to $237 (q=8). The curves cross near q=5.

What to Do in Excel

1Select the data for Average Costs and Marginal Costs (q=1 through q=8).
2Insert a Line Chart (Insert → Chart → Line).
3Label the X-axis as "Quantity (millions of gallons)" and the Y-axis as "Cost ($000s)".
4Identify where the two curves intersect. Note the quantity at that point.
Need Help?
Conceptual hint: The intersection occurs where the marginal cost curve crosses the average cost curve from below. At that point, average cost is at its minimum. Look at your data — where does MC first exceed AC?
Detail: Compare MC and AC at each quantity. At q=4, MC=95 and AC=146. At q=5, MC=126 and AC=142. At q=6, MC=160 and AC=145. The crossover happens between q=5 and q=6.
Answer: The curves intersect between q = 5 and q = 6. At q=5, AC ($142) > MC ($126). At q=6, AC ($145) < MC ($160). The minimum average cost occurs at q=5 ($142).
Check My Work — Task 2

At what quantity (q) does average cost approximately equal marginal cost?

Task 03 Pending

MC Pricing & Total Cost Recovery

If the city uses marginal cost pricingSetting the price equal to the cost of producing one additional unit, at what point would total costs be fully recovered?

Why Does This Matter?

Marginal cost pricing is economically efficient — it signals the true cost of consuming one more unit. However, when average cost is above marginal cost, MC pricing does not cover total costs, creating a deficit. Only when MC ≥ AC does the revenue from MC pricing cover all costs.

The question asks: at what output level does the total revenue from MC pricing (price × quantity) first equal or exceed total costs?

Analysis
Revenue at MC price = MC × q
Cost recovery when: MC × q ≥ Total Cost

Worked Example

At q=5: MC = $126, Total Revenue = 126 × 5 = $630, but Total Cost = $710. Not recovered.

At q=7: MC = $197, Total Revenue = 197 × 7 = $1,379, but Total Cost = $1,067. Fully recovered!

Need Help?
Conceptual hint: For MC pricing to recover total costs, the revenue (MC × q) must be at least as large as Total Cost. Build a column: MC × q, and compare it to Total Cost.
Detail: Compute MC × q for each row: q=1: 20×1=20 vs 380. q=2: 42×2=84 vs 422. The revenue grows faster than costs at higher quantities because MC rises steeply.
Answer: At q = 7, MC pricing revenue ($197 × 7 = $1,379) first exceeds Total Costs ($1,067). Total costs are fully recovered at q=7.
Check My Work — Task 3

At what quantity (q) does MC pricing first fully recover total costs?

Task 04 Pending

Cost Recovery Pricing

The city expects monthly waste water volume of 6 million gallons next year and has 15,000 sewer connections. What price per gallon must be charged to recover total costs?

Why Does This Matter?

This is the core question for any public utility: how do you set rates that cover all costs? The budget analyst must translate the cost schedule into a per-unit price that generates enough revenue to fund operations, including both fixed and variable costs.

Formula
Price per gallon = Total Cost at q=6 ÷ (6,000,000 gallons)
Remember: the cost schedule is in $000s. Total cost at q=6 is $870 (thousands), or $870,000.

Worked Calculation

Total Cost at q=6: $870,000
Total gallons: 6,000,000
Price per gallon: $870,000 ÷ 6,000,000 = $0.145

The city must charge $0.145 per gallon (about 14.5 cents) to fully recover costs.

Need Help?
Conceptual hint: Don't forget the units! The cost schedule shows costs in thousands of dollars ($000s). You need to convert to actual dollars before dividing by gallons.
Detail: Total cost at q=6 is listed as $870, but that means $870,000. The volume is 6 million gallons. So: $870,000 ÷ 6,000,000 = $0.145 per gallon.
Answer: Price per gallon = $0.145 (14.5 cents per gallon). Alternatively: $870 ÷ 6,000 = $0.145 per gallon (keeping units in $000s and gallons in 000s).
Check My Work — Task 4

What price per gallon must the city charge?

Discussion

Discussion Questions

Reference

Answer Key

Complete all four tasks to automatically reveal the answer key, or: