Analyst checkpoint: an auditable spreadsheet for a new case
About two and a half hours: build a spreadsheet for a school’s rooftop solar and battery, say who pays and who benefits, test the result, then check it against a worked workbook
How this checkpoint works · about 150 minutes, no hints
- A · Boundary and log (about 40 min). Download the starter workbook. State your boundary; finish the assumptions log: every input's type, source, date and, where you will test it, a low and a high value.
- B · The model (about 50 min). Convert the costs to 2025 dollars and build the school's yearly saving and its net present value, every number a formula.
- C · Who pays (about 20 min). Split the school's bill saving into a real cost saved and a transfer; add the parties up (refresher: A13).
- D · Sensitivity (about 20 min). Swing the inputs that matter one at a time and report the main result as a range.
- Submit (about 20 min). Enter your results in the nine tasks below, in any order: the worked workbook opens once all nine are answered.
How it is marked, before you start
- Proficient: at least 5 of the 7 auto-marked tasks right on the first try, plus at least 3 of the 4 points of each written task (1 and 9), which you tick yourself.
- Strong: at least 6 of 7 on the first try and every written point.
- Opening solutions early: a task whose answer you open before answering counts as not right. Opening the worked workbook before all nine are answered makes the attempt practice, with no level.
- After the worked workbook opens: an answer given after that counts as practice, not as right, and a written task then earns no points.
It opens in Excel, LibreOffice Calc, Google Sheets or Apple Numbers. It holds the case's facts with their sources and an empty layout; it gives no formulas and no answers. Refreshers: A1 (cash flows), A4 (tariffs), A13 (who pays), K3 (discounting), K7 (spreadsheets).
The case
A school's rooftop solar and battery
Situation: Prairie Town High School's district is offered a rooftop solar array and a battery behind the school's meter.
Deliverable: An auditable spreadsheet that tells the district what it would gain or lose, who else pays or benefits, and how sure that answer is.
Inputs, illustrative where marked
- System: 250 kW-DC of rooftop solar and a 150 kW / 300 kWh battery, installed together
- School: uses 1,000,000 kWh a year, with a billed peak of 400 kW every month (illustrative)
- Tariff: a demand charge of $10 per kW-month plus an energy charge, set so the school's bill averages Kansas's 2025 commercial price, 11.17 cents/kWh (EIA); exports earn 3 cents/kWh (illustrative)
- Battery power: cuts the monthly billed peak by 60% of its power on average (illustrative)
- Battery energy: about 200 full cycles a year, each taking in 300 kWh from the school's supply and returning 90%; the 10% lost is bought at the energy charge (illustrative)
- Costs: from EIA's 2024 distributed-generation report (2022 dollars) and NLR's 2025 ATB (2023 dollars); convert to 2025 dollars
- Money: 25 years, real 2025 dollars, a 4% real discount rate (the course's), battery replaced at the end of year 15
- End value: at year 25 the replacement battery has 5 of its 15 years left: count 5/15 of its cost as its residual value (straight line)
- The utility: avoids 3 cents for each kWh it no longer supplies and, for half of the battery's peak cut, $180 per kW a year of capacity (illustrative; lesson A4's unit cost)
- Rate reset: assume the utility recovers its whole remaining revenue shortfall from its other customers at the next rate reset
Prairie Town and its high school are fictional; the school's load and several inputs are illustrative.
Without JavaScript nothing here is locked: the worked solution is further down the page. Build your spreadsheet first, write your answers down, then compare.
Your boundary Part A · State the boundary
In two or three sentences: whose money does your main result count, over what period and in which dollars, and what does it leave out?
Self-rated against 4 points, shown after you submit.
Solar output Part B · A number with units
How much electricity does the array make in a year? Within 2% counts as right.
Answer with a number and a unit.
Up-front cost in 2025 dollars Part B · Convert dollar years
What does the system cost up front, in 2025 dollars? Within 1% counts as right.
Answer with a number and a unit.
The school's bill saving Part B · Read a tariff
How much lower is the school's electricity bill in a year (gross: before upkeep)? Within 3% counts as right.
Answer with a number and a unit.
The main result Part B · Present value
Over 25 years at 4%, counting the brief's residual value, by how much does the present value of the school's costs exceed the present value of its savings? (If your model says the savings are larger, recheck it.) Within 3% counts as right.
Answer with a number and a unit.
Who pays the gap Part C · Transfers against real costs
Part of the school's bill saving is not a saving in the utility's costs. Under the brief's rate-reset assumption, who pays it?
- a. The school district
- b. The utility's other customers, at the next rate reset
- c. Federal taxpayers
- d. Nobody: every dollar the school saves is a real cost saved
The cost shift Part C · A screening estimate
Under the same assumption, how much a year moves to the utility's other customers? Within 5% counts as right.
Answer with a number and a unit.
What moves the result most Part D · Sensitivity
Swinging one input at a time over the ranges in the worked log, which moves the school's NPV most?
- a. The up-front cost, ±25%
- b. The discount rate, 2% to 7%
- c. The demand charge, $5 to $20 per kW-month, with the energy charge re-set so the pre-project bill is unchanged
- d. The average electricity price, ±20%
Your log and your sensitivity Part D · Audit trail
Paste three rows of your assumptions log and your sensitivity statement (the main result as a range, and the input that moves it most).
Self-rated against 4 points, shown after you submit.
Prairie Town is fictional; its numbers are illustrative where the log says so. Prices, costs, the capacity factor and the price index are dated public figures (Sources). Nothing you type leaves this browser unless you copy it; the copied summary includes what you typed in tasks 1 and 9.
Worked solution
Opens once all nine tasks are answered. The checkpoint asks for your own spreadsheet first, so the worked workbook waits for it. Opening it now makes this attempt practice, with no level.
The answer in one line
Net present value to the school: −$776,324 (2025 dollars, 25 years, 4% real). In present value the district pays $776,000 more than it saves.
One-at-a-time swings put it between −$993,000 and −$560,000: no single swing changes its sign. Cumulative cash stays below zero in every year (lowest −$1,020,000), so there is no payback within 25 years.
The numbers, step by step
| Quantity | Worked value |
|---|---|
| Energy charge (derived from EIA's average) | $0.0637/kWh |
| Solar output | 389,820 kWh a year (292,365 used, 97,455 sent out) |
| Up-front cost | $866,046 (2025 dollars; $792,500 in 2022 dollars × 1.0928) |
| Bill saving, gross | $31,965 a year = $18,624 energy + $2,924 exports + $10,800 demand − $382 losses |
| Upkeep | $14,972 a year ($4,645 solar, $10,327 battery) |
| Annuity factor | 15.62 (25 years at 4%) |
| Battery replacement, year 15 | $408,490; present value $226,820 |
| Residual value, year 25 | $136,163 (5 of 15 years left, straight line); present value $51,077 |
| Net present value to the school | −$776,324 |
Check in the workbook: the year-by-year cash flow sums to the same −$776,324. The installed cost read as per kW-DC of solar is 7% below the two systems' separate costs, as the report says installing together saves.
Who pays and who benefits
| Party | A year | Present value |
|---|---|---|
| The school district | $16,993 | −$776,324 |
| The utility's other customers (at the next rate reset) | −$12,351 | −$192,941 |
| Federal taxpayers (no credit counted) | $0 | $0 |
| Resource view: all parties together | $4,642 | −$969,265 |
- Gross against net: of the school's $31,965 gross bill saving a year, $19,615 is a real cost the utility avoids; $12,351 is a transfer from its other customers.
- Adds up: school −$776,324 + other customers −$192,941 = resource view −$969,265.
- Screening estimate: the cost shift holds only under the brief's rate-reset assumption. It comes from the accounting of who pays, not from the model of the school's bill.
- CO₂, not valued: about 118 t a year at Kansas's 2024 average factor: an inventory number, not the change the project causes.
- Resilience, not counted: no islanding equipment is costed, so the battery gives no backup in an outage.
- Jobs, not estimated: installation is job-years of temporary work, not permanent jobs.
Sensitivity of the main result
| Input | Low to high | NPV at low | NPV at high |
|---|---|---|---|
| Up-front cost | $649,535 to $1,082,558 | −$559,813 | −$992,836 |
| Average electricity price | $0.0894 to $0.134 per kWh | −$876,265 | −$676,384 |
| Battery's cut in the billed peak | 30% to 90% | −$860,683 | −$691,965 |
| Upkeep, solar and battery | $11,229 to $18,715 a year | −$717,850 | −$834,798 |
| Demand charge (pre-project bill held) | $5 to $20 per kW-month | −$753,317 | −$822,339 |
| Solar capacity factor | 16.0% to 19.6% | −$809,986 | −$742,663 |
| Share used at the school | 60% to 90% | −$807,108 | −$745,540 |
| Discount rate | 2% to 7% | −$754,804 | −$790,985 |
Sorted by swing. The demand-charge row varies the demand charge while holding the school's pre-project annual bill constant, so the energy charge falls as it rises.
Then the reduction in solar savings exceeds the increase in battery savings: the result falls.
Boundary and assumptions log
- Counted: the school district's money: bill saving, capital, upkeep, the battery's replacement and its residual value; 25 years, real 2025 dollars, a 4% real discount rate.
- Left out: tax credits (a transfer if claimed through elective pay), financing, resilience, CO₂'s value, solar wear, tariff changes over time.
- Reported apart: the other customers, federal taxpayers and the resource view.
The worked assumptions log (36 rows)
| Input | Value | Unit | Type | Source |
|---|---|---|---|---|
| Solar array size | 250 | kW-DC | Choice | EIA distributed-generation cost report (2024) |
| Solar capacity factor | 17.8% | fraction | Sourced | EIA distributed-generation cost report (2024) |
| Share of solar output used at the school | 75% | fraction | Illustrative | this case |
| Battery power | 150 | kW | Choice | EIA distributed-generation cost report (2024) |
| Battery energy | 300 | kWh | Choice | EIA distributed-generation cost report (2024) |
| Average cut in the monthly billed peak, share of battery power | 60% | fraction | Illustrative | this case |
| Battery full cycles a year (each takes in 300 kWh) | 200 | per year | Illustrative | this case |
| Battery round trip efficiency | 90% | fraction | Sourced | EIA distributed-generation cost report (2024) |
| School electricity use | 1,000,000 | kWh a year | Illustrative | this case |
| School monthly billed peak | 400 | kW | Illustrative | this case |
| Average commercial electricity price, Kansas | $0.1117 | $/kWh | Sourced | EIA Electric Power Monthly, Table 5.6.B |
| Demand charge | $10 | $/kW-month | Illustrative | this case |
| Credit for each kWh sent to the grid | $0.03 | $/kWh | Illustrative | this case |
| Installed cost, solar + battery, per kW-DC of solar | $3,170 | $/kW-DC (2022 $) | Sourced | EIA distributed-generation cost report (2024) |
| Installed cost, solar alone (check only) | $1,903 | $/kW-DC (2022 $) | Sourced | EIA distributed-generation cost report (2024) |
| Standalone battery cost, for the replacement | $2,492 | $/kW (2022 $) | Sourced | EIA distributed-generation cost report (2024) |
| Solar upkeep | $17.63 | $/kW-DC a year (2023 $) | Sourced | NLR ATB 2025, Commercial PV |
| Battery upkeep | $63 | $/kW a year (2022 $) | Sourced | EIA distributed-generation cost report (2024) |
| Price index, 2022 | 118.035 | index (2017 = 100) | Course data | EIA STEO Table 9a |
| Price index, 2023 | 122.389 | index (2017 = 100) | Course data | EIA STEO Table 9a |
| Price index, 2025 | 128.989 | index (2017 = 100) | Course data | EIA STEO Table 9a |
| Discount rate (real) | 4% | per year | Course data | this case |
| Analysis period | 25 | years | Choice | this case |
| Battery useful life | 15 | years | Sourced | EIA distributed-generation cost report (2024) |
| Utility's avoided energy cost | $0.03 | $/kWh | Illustrative | this case |
| Utility's avoided capacity cost | $180 | $/kW a year | Course data | this case |
| Share of the battery's peak cut that falls in the system's tightest hours | 50% | fraction | Illustrative | this case |
| Grid CO2 factor | 680 | lb per MWh | Sourced | EIA Kansas Electricity Profile 2024 |
| 2022 dollars to 2025 dollars | 1.0928 | factor | Derived | formula in the log |
| 2023 dollars to 2025 dollars | 1.0539 | factor | Derived | formula in the log |
| Up-front cost, solar + battery | $866,046 | $ (2025) | Derived | formula in the log |
| Battery replacement at the end of year 15 | $408,490 | $ (2025) | Derived | formula in the log |
| Solar upkeep a year | $4,645 | $ (2025) | Derived | formula in the log |
| Battery upkeep a year | $10,327 | $ (2025) | Derived | formula in the log |
| Grid CO2 factor in metric tonnes | 0.3084 | t per MWh | Derived | formula in the log |
| Check: saving from installing together | 6.7% | fraction | Derived | formula in the log |
The points of the two written tasks
Your boundary
- Whose money: the school district's, from its electricity bill, the system's capital, upkeep and the battery's replacement.
- Time and money: 25 years, real 2025 dollars, one real discount rate (4%), with costs from other dollar years converted.
- Left out, and said so: tax credits, financing, resilience, CO2's value and solar wear (or a stated subset of these).
- The other parties (the utility's other customers, federal taxpayers, the resource view) reported apart from the main result.
Your log and your sensitivity
- Every input has a value, a unit, a type (sourced, course data, illustrative, choice, derived) and a source or a reason.
- Sourced values name the document, the table or page, and an as-of date.
- Costs from different dollar years are converted to 2025 dollars with a stated index.
- The main result is given as a range from the sensitivity, with the input that moves it most named.
Rubric
The checkpoint's level
- Proficient: at least 5 of the 7 auto-marked tasks right on the first try; at least 3 of the 4 points of your boundary (task 1) and 3 of the 4 points of your log and sensitivity (task 9).
- Strong: at least 6 of 7 on the first try, and every point of both written tasks.
- Developing: at least 3 of 7 on the first try.
- Not yet: anything else. The checkpoint shows readiness for the Modeler tier; it certifies nothing.
- Opened early: a task whose answer you open before answering counts as not right; if the worked workbook opens before all nine tasks are answered (on purpose, or in an earlier attempt), the attempt is practice and gets no level.
- Answered late: an answer given after the worked workbook opened counts as practice, not as right; a written task submitted after it earns no points.
The written tasks are self-rated against their points. A teacher or mentor can rate your workbook again with the course rubric below, from the copied summary and the file.
| Competence | Developing | Proficient (the bar) | Strong |
|---|---|---|---|
| Consistent accounting Look for: One boundary for every cost and saving, in 2025 dollars; the school's result kept apart from the other customers' and the resource view; transfers kept apart from real costs. |
Costs and emissions on different boundaries. | Cost and emissions on one boundary; emissions question named; who pays and who benefits named. | Distributional and environmental side effects estimated with stated assumptions. |
| Uncertainty Look for: Low and high values for the inputs that matter, the main result as a range, the input that moves it most named. |
Single numbers. | Uncertainties classified; scenario or global sensitivity analysis; results as ranges. | Decision-relevant uncertainty identified and its value discussed. |
| Trustworthy analysis Look for: Every number traces to a formula or to a log row with a source and a date; a second calculation (a year-by-year cash flow) checks the first. |
Results cannot be rerun. | Reruns from a clean clone (or a complete audit trail); verification log; one validation against real data or an independent reference. | Independent reproduction by someone else; validation on data not used to build the analysis. |
| Realism Look for: Illustrative inputs labelled as such; what the spreadsheet leaves out listed (credits, resilience, solar wear, tariff changes). |
Assumes announced projects exist. | Evidence status of key inputs; a 'what the analysis leaves out' list. | Realism checked with a practitioner or primary documents. |
Numbers, sources and what’s next
Numbers used in the case
| Number | What it means | Source |
|---|---|---|
| 11.17¢/kWh | Average price of electricity to Kansas's commercial customers, January to August 2025. | EIA, Electric Power Monthly, Table 5.6.B · issue of 23 Oct 2026 |
| $3,170/kW-DC | Installed cost of a commercial 250 kW-DC solar array with a 150 kW, 300 kWh battery, per kW-DC of solar, 2022 dollars. | EIA distributed-generation report (PDF), Appendix A, Figure 3-5, p. 36 · March 2024 |
| 17.8% | Capacity factor of a 250 kW-DC commercial solar system, U.S. average, 2022. | Same report, Table 2-5, p. 14 |
| $63/kW a year | Upkeep of a commercial battery, 2022 dollars; 90% round trip and a 15-year life (Table 3-3, p. 32). | Same report, Figure 3-6, p. 37 |
| $17.63/kW-DC a year | Upkeep of commercial solar for 2024, in 2023 dollars. | NLR, 2025 ATB, Commercial PV · read 6 Nov 2026 |
| 680 lb/MWh | CO₂ per MWh generated by Kansas's power plants, 2024 (an average factor). | EIA, Kansas Electricity Profile 2024, Table 1 |
Keep going
Sources and further reading
- EIA (report by Z Federal and DNV), March 2024. Distributed Generation, Battery Storage, and Combined Heat and Power System Characteristics and Costs in the Buildings and Industrial Sectors (PDF): Appendix A, Table 2-5 (p. 14), Figure 2-4 (p. 19), Table 3-3 (p. 32), Figures 3-4 and 3-5 (p. 36), Figure 3-6 (p. 37); costs in 2022 dollars.
- EIA. Electric Power Monthly, Table 5.6.B: average price of electricity to ultimate customers by end-use sector, year to date (issue of 23 Oct 2026).
- NLR. 2025 Electricity Annual Technology Baseline: Commercial PV; all values in 2023 dollars.
- EIA. Short-Term Energy Outlook, Table 9a: GDP implicit price deflator (October 2026 issue), the course's price index.
- EIA. Kansas Electricity Profile 2024, Table 1.
- IRS. Elective pay and transferability (page last reviewed 27 Jun 2026): how tax-exempt and governmental entities can benefit from certain clean-energy credits; not counted in this case.
Spotted an error, or stuck? Write to hlli@ksu.edu. Corrections are made in place and dated here.
Text licensed CC BY 4.0, code MIT: teachers and students may reuse and adapt it, with credit to Honglin Li.