1 - Introduction ~5 mins
Welcome
Last session you ran a KA1 gap check on your project budget and noted what was still missing. Today you close those gaps. You will add formulas that answer real questions about your Something Real project, and every formula has to earn its place by changing a decision, not just sitting on the sheet for show.
Before we start: If you cannot find {{code:05_project_budget}} or your gap-check notes, tell your teacher now so you can get a working file before Independent Practice.
By the end of this lesson, you will be able to:
- Close KA1 gaps with formulas that answer real project questions
- Extend the sheet with extra decision-ready calculations or a balanced mini-table where needed
- For each formula, state one plain sentence about the decision it changes, and remove any formula that has no sentence
- Save {{code:05_ka1_best_spreadsheet}} and export {{code:05_ka1_best_spreadsheet.pdf}}
Warm-up
Look at your current budget in your head. If someone asked you right now, "Can I afford one more item?" or "Am I actually covered by the money coming in?", could your spreadsheet answer in one glance, or would you still be adding things up yourself?
2 - Key Concepts ~5 mins
Before you upgrade your sheet, lock in the two rules that separate a KA1-ready spreadsheet from a sheet that only looks busy.
| Concept | Why it matters | Example |
|---|---|---|
| Decision-ready formula — a calculation that answers a real project question the moment the data changes | ICT2 KA1 wants formulas that perform calculations for a task you are involved in, not decoration | Remaining budget: limit minus total cost. Add a stall fee and the leftover figure updates before you overspend |
| Formula that earns its place — if you cannot say the decision it changes in one plain sentence, cut it | Padding the sheet with unused functions weakens KA1 evidence and confuses anyone reading it | An IF flag that marks any item over €25 as "Review" helps you choose what to drop. A random length check on a notes column usually does not |
Worked sample you will build
In the step-by-step you will upgrade a short sample for Ciara's custom tote-bag side hustle (Saturday market stall).
Core path (everyone):
- Cost total with SUM
- Remaining under a budget limit
- IF review flag on pricey items
If time allows: % of total (with a locked total cell) and a funding mini-table with surplus or gap.
You then apply the same pattern to your own Something Real budget. You do not need every Ciara feature, only formulas that close your real gaps.
3 - Step-by-step Task ~20 mins
Build a short worked upgrade for Ciara's tote-bag market stall.
Core path (stay with your teacher): costs, SUM, Remaining, and the IF flag. These already count as real decision formulas.
Stretch path (% of total and the funding mini-table): your teacher will say when to stop or continue. If the later blocks feel like a stretch, stop after Remaining and IF with your teacher.
4 - Common Issues ~4 mins
Common Issues
| Issue | Solution |
|---|---|
| My percentages look huge (like 2760%) or tiny after fill-down | Check the absolute reference on the total. The formula should look like {{code:=C2/$C$10}}, not {{code:=C2/C10}} filled down. Also confirm the column is formatted as Percentage, not a plain number times 100 again. |
| IF shows {{code:#NAME?}} or the whole formula as text | Start with {{code:=}}. Use straight quotes around text results ({{code:"Review"}} and {{code:"OK"}}). Do not use curly/smart quotes copied from a message app. |
| Surplus or gap does not match what I expect | Confirm Total funding sums only the funding amounts, and Surplus or gap subtracts the cost total cell (for example {{code:C10}}), not a single item. Change one funding figure and watch the gap update. |
| Remaining stays the same when I edit a cost | Your Remaining formula is probably subtracting a typed number instead of the Total cost cell. Point it at the SUM cell, then edit a cost again to test. |
5 - Independent Practice ~15 mins
Independent Practice
1. Open and map. Open your Project Portfolio {{code:05_budget}} folder and open {{code:05_project_budget}}. Use the Ciara sample as a model for ideas, not as a layout to copy cell-for-cell. Before you edit, answer these three checks:
- Where is your cost column and your total cell?
- What will Remaining be as limit cell minus total cell using your own references?
- Where will any second block sit (two rows below your data is a clean default)?
Recovery path (if you rebuilt a minimal budget today): SUM plus Remaining (or one other decision formula) is enough; PDF if time.
3. Name the decision. Beside each new formula (or in a Notes cell next to it), type one plain sentence naming the decision it changes. Delete any formula that has no sentence.
4. Rename. When finished, rename this same file (click the title) to {{code:05_ka1_best_spreadsheet}}. Do not create a second Untitled copy.
5. Export PDF. Save {{code:05_ka1_best_spreadsheet.pdf}} beside the editable file in the same folder.
- Excel Online: open the File menu, choose Print, then Save as PDF or Microsoft Print to PDF. Use portrait and fit sheet on one page.
- Google Sheets: open the File menu, choose Download, then PDF document. Use portrait and fit to width on one page.
- {{code:05_ka1_best_spreadsheet}} is saved in your {{code:05_budget}} folder with your real Something Real data, not the sample
- Standard bar: the sheet answers at least two real project questions automatically; recovery bar: SUM plus Remaining (or one other decision formula) if you rebuilt a minimal file today
- Changing one cost or related figure updates the linked results without retyping totals, and each new formula has a typed one-sentence decision note beside it
- {{code:05_ka1_best_spreadsheet.pdf}} sits beside the editable file in the same folder and is readable on one page (recovery path: PDF if time)
Unlock the full learning experience
You're previewing this lesson. Get full access to this lesson and hundreds more — each one ready to teach, with interactive activities, printable resources and pupil progress tracking built in.
This lesson is copyright of Coding Ireland 2017 - 2025. Unauthorised use, copying or distribution is not allowed.