Capstone: build and document a workbook system
Finish the course with a complete, documented workbook: README, inputs, data, calculations, dashboard and checks, with a real model built and verified.
In this lesson you will
- Plan a capstone with all six parts
- Build checks first and test like a stranger
- Write a clear README
- Present results honestly
About 20 minutes
One piece of work that proves you can do it
Noor has finished twenty-nine lessons. The last step is a capstone: a single workbook that shows what she has learned, and that a stranger could open, understand, trust and update. This is what employers and clients are really asking when they say ‘show me something you built’. In this lesson you will plan, build and document a small but complete workbook system, with inputs, data, calculations, a dashboard, checks and a README.
What your capstone must include
| Part | What it shows |
|---|---|
| README sheet | Purpose, how to update, who to ask |
| Inputs sheet | Targets, rates, lists: every assumption in one place |
| Data sheet | A clean table, with data validation where people type |
| Calc sheet | Formulas: SUMIFS, lookups, IF, dates or text as needed |
| Dashboard | 4 to 6 KPIs and 2 or 3 charts, nothing typed |
| Checks sheet | Tests that must all show TRUE |
Step 1: choose a real problem
Pick something you understand: a shop’s monthly sales, a class marks sheet, a club’s finances, a personal budget with goals, a project tracker, a stock list. It should have real inputs (rows of data), a few rules and a question to answer. Keep it small enough to finish in a week.
Step 2: write the brief and the acceptance tests
Use lesson 29. Half a page: the purpose, the users, the inputs, the outputs, the rules and what it must never do. Then list four to six checks that will tell you it works, such as ‘revenue by region adds up to total revenue’, ‘no region is blank’ and ‘every row has units and a price’. Write the checks before you build.
Step 3: build it in this order
- Inputs and Data sheets, with validation and a Table.
- The Calc sheet, one formula pattern at a time, copied down and checked.
- The Checks sheet, so you can see problems early.
- The Dashboard, using only cells from Calc.
- Formatting: number formats, frozen headers, colours for input cells, a legend.
- Protection: lock formulas, leave inputs open.
- README last, but keep notes as you go.
A model capstone, built and checked
The program below builds the model capstone workbook: README, Inputs, Data (twelve sales rows), Calc, Dashboard and Checks. It writes the formulas, recalculates the file in a spreadsheet program and prints the dashboard and check results. The sample data is invented. The output is real:
import openpyxl, subprocess, os
wb = openpyxl.Workbook()
readme = wb.active; readme.title = "README"
for i, line in enumerate(["Sales report v1.0", "Purpose: monthly sales by region for the owner", "Inputs: Data sheet (paste the export) and Inputs sheet (target, tax)",
"Outputs: Dashboard sheet", "Checks: Checks sheet must show all TRUE before sending", "Update: paste new rows, refresh, read Checks"], start=1):
readme[f"A{i}"] = line
inp = wb.create_sheet("Inputs"); inp["A1"], inp["B1"] = "Target per region", 1000; inp["A2"], inp["B2"] = "Tax rate", 0.18
data = wb.create_sheet("Data"); data.append(["Region", "Units", "Price", "Revenue"])
rows = [("North", 10, 20), ("South", 5, 60), ("North", 8, 60), ("East", 20, 20), ("East", 3, 60), ("South", 15, 20),
("West", 6, 60), ("North", 12, 20), ("East", 9, 60), ("South", 4, 60), ("West", 25, 20), ("North", 7, 60)]
for i, (r, u, p) in enumerate(rows, start=2):
data.append([r, u, p, f"=B{i}*C{i}"])
calc = wb.create_sheet("Calc"); calc.append(["Region", "Revenue", "Status", "With tax"])
for i, r in enumerate(["North", "South", "East", "West"], start=2):
calc.append([r, f"=SUMIF(Data!A2:A13,A{i},Data!D2:D13)", f'=IF(B{i}>=Inputs!$B$1,"On target","Below target")', f"=B{i}*(1+Inputs!$B$2)"])
dash = wb.create_sheet("Dashboard")
dash["A1"], dash["B1"] = "Total revenue", "=SUM(Calc!B2:B5)"
dash["A2"], dash["B2"] = "Regions on target", '=COUNTIF(Calc!C2:C5,"On target")'
dash["A3"], dash["B3"] = "Best region", "=INDEX(Calc!A2:A5,MATCH(MAX(Calc!B2:B5),Calc!B2:B5,0))"
chk = wb.create_sheet("Checks")
chk["A1"], chk["B1"] = "Regions add up to the data", "=SUM(Calc!B2:B5)=SUM(Data!D2:D13)"
chk["A2"], chk["B2"] = "No blank regions in data", '=COUNTBLANK(Data!A2:A13)=0'
chk["A3"], chk["B3"] = "Every row has units and price", "=COUNT(Data!B2:C13)=24"
chk["A4"], chk["B4"] = "All checks pass", "=AND(B1:B3)"
wb.save("capstone.xlsx")
os.makedirs("out", exist_ok=True)
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "out", "capstone.xlsx"], capture_output=True, timeout=120)
v = openpyxl.load_workbook("out/capstone.xlsx", data_only=True)
print("sheets:", ", ".join(wb.sheetnames))
for r in range(1, 4):
print(f"Dashboard {v['Dashboard'][f'A{r}'].value:18s}", v["Dashboard"][f"B{r}"].value)
for r in range(1, 5):
print(f"Checks {v['Checks'][f'A{r}'].value:32s}", v["Checks"][f"B{r}"].value)
sheets: README, Inputs, Data, Calc, Dashboard, Checks
Dashboard Total revenue 4160
Dashboard Regions on target 2
Dashboard Best region North
Checks Regions add up to the data True
Checks No blank regions in data True
Checks Every row has units and price True
Checks All checks pass True
The total revenue of 4160 and the best region North match an independent calculation, two regions are on target and all four checks show TRUE. The last check combines the others with AND, so a single glance tells you whether it is safe to send.
Step 4: test like a stranger
- Paste in new data with an extra region and see whether the Calc sheet notices (a Check should fail).
- Change a target and an input rate, and watch results update.
- Try to type a wrong value in a validated cell.
- Ask someone else to update it using only your README.
Step 5: write the README
Write for someone who has never met you. State the purpose, the sheets, how to update step by step, what each check means, what to do if one fails, the version and date, known limits and your contact. A good README fits on one screen.
Step 6: present it
- State the problem in one sentence.
- Show the dashboard and say what it tells you.
- Show the checks and one failure you found and fixed.
- Name the limits honestly.
- Offer the README and the file.
Self-review checklist
- No typed numbers hidden inside formulas.
- Every assumption is in the Inputs sheet.
- Every KPI has been checked by hand once.
- Checks all show TRUE and failing checks would show clearly.
- No private data is included.
- Someone else has updated it using only the README.
Where to go next
Keep building. Add Power Query for your regular imports, learn Power BI or SQL if your data grows, and teach what you know. Add this capstone and two others to a portfolio, and describe what each one saved or improved in plain terms. You now have the skills to start, and the method to keep improving.
Practice
Complete your capstone with all six parts. Have one person follow your README without your help, and write down every place they get stuck. Fix those places in the README and the workbook, and add a line to a Change Log.
Typical gaps found by a second person: the paste location was unclear, the meaning of a check was missing, or an input cell looked like a calculation. Add colour and labels for inputs, a line per check and a numbered update guide.
Showing only a polished dashboard. The checks, the notes and the failures you fixed are the best evidence that you can be trusted with someone else’s numbers.
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
Which is NOT part of the capstone?
- A single sheet with typed totals and no checks
- README sheet
- Inputs sheet
- Checks sheet
The capstone is a system, not a quick sheet.
Why write the checks before building?
- They look nice
- They define what working means
- Excel needs them
- They shorten the file
It avoids moving the goalposts.
What should the Dashboard contain?
- Typed numbers
- Private data
- Macros only
- Only values pulled from Calc
Nothing typed, so it stays correct.
What does the final AND check do?
- Adds the data
- Tells you at a glance if all checks pass
- Sorts rows
- Hides errors
One cell, one answer.
What is the best test of your README?
- You read it twice
- It is long
- It has colours
- A stranger updates the file using only it
Their stuck points are your missing lines.
Summary
- A capstone is a system: README, inputs, data, calculations, dashboard, checks.
- Write checks before you build.
- Nothing typed on the dashboard; assumptions in Inputs.
- Let a stranger test your README.
Questions
How long should the capstone take?
A small, focused one takes a week of part-time work. Keep the scope small and finish it.
Can I use my own data?
Yes, if it is yours and contains no private details about other people. Otherwise use invented data.
What if a check fails at the end?
Investigate before presenting. A failing check that you explain and fix is better evidence than a perfect-looking sheet.
