Excel for Accounting

Overview

Students build practical skills using basic Excel functions commonly used in accounting: calculating totals, averages, and conditional amounts, and interpreting the results to support business decisions. This lesson supports later evaluation of data and decisions by strengthening students’ data-handling capability.

Learning intentions

• WALT use Excel functions to calculate accounting totals, averages, and conditional values.
• WALT interpret spreadsheet results using cause-and-effect reasoning (how changing inputs changes outputs).
• WALT recognise and use economic/business data to address a simple financial issue.

Success criteria

• I can use the SUM and AVERAGE functions correctly for accounting calculations.
• I can use a conditional function (e.g., IF) to calculate an amount based on a rule.
• I can explain what happens to the output when I change an input (cause-and-effect).
• I can check my work for accuracy using a simple reasonableness test.

Curriculum links

• Students interpret and analyse data and information to address economic, business, work or financial issues by recognising trends and cause-and-effect relationships.
• Students locate, select, organise and analyse relevant information and data from a range of sources to support calculations and conclusions.
• Students develop and evaluate a response to an economic and business issue by drawing logical conclusions from data (applied in a simple accounting scenario).
• Knowledge focus on economic indicators as measures of economic performance can be linked through discussion of how data supports decisions (e.g., income/expenses trends).

Lesson structure (60 minutes)

1. 0–10 min · Prior knowledge check (data + spreadsheet basics). Teacher displays three quick prompts on the board: “What does a formula do?”, “What is the difference between data and a calculation?”, “When might accounting need a conditional rule?” Students do a 2-minute think, then pair-share, then teacher records key terms (cell, formula, function, input, output).

Success criteria check: Students identify what needs calculating and what data is.

2. 10–15 min · Mini diagnostic (fast scaffold). Teacher shows a partially completed sheet with columns: Date, Description, Amount. Students answer verbally: “Which function fits: total sales/total expenses/average transaction?” Teacher notes misconceptions (students often confuse SUM vs AVERAGE).

3. 15–35 min · Main content: explicit teaching with videos + discussion. Teacher runs two short video clips (teacher-controlled; pause often) and models steps on a projector.

• 15–23 min · SUM for totals. Teacher demonstrates entering =SUM(range) and naming the column logic (income vs expenses). Students replicate on a practice sheet. Discussion: “If you change one expense value, what should happen to the total?” (cause-and-effect).
• 23–31 min · AVERAGE for typical value. Teacher demonstrates =AVERAGE(range) and highlights the need to average only the correct category. Students replicate. Discussion: “Average can hide extremes—why might this matter for a business?”
• 31–35 min · IF for conditional rules. Teacher demonstrates =IF(test, value_if_true, value_if_false) using a simple rule: “If Amount is above 100, apply label ‘High’; else ‘Normal’” then extend to “apply a discount/surcharge amount”. Students replicate.

Embedded success criteria: Students can match each function to a business need and explain input-to-output change.

4. 35–55 min · Activity (video-supported accounting task). Students work in pairs on a worksheet: a mini set of accounting entries (income and expenses). They must:

• Use SUM to calculate total income and total expenses.
• Use AVERAGE to calculate average transaction for income and for expenses (two separate ranges).
• Use IF to calculate a “flagged expense” amount (example rule provided): If an expense is over a threshold, record it in a “flagged” column; otherwise put 0. Teacher plays a third short clip showing a completed example, then students complete. Checkpoints (teacher circulates):
• Are ranges correct (don’t include headers or wrong category)?
• Do formulas use correct order and cell references?
• Can students explain what changes if a value changes?

5. 55–60 min · Exit ticket (5 minutes). Students submit:

• One question response: “Write one sentence explaining cause-and-effect: if you change one expense, what happens to total expenses and profit?”
• One quick calculation check: “If expenses are 20, 50, 130, what does SUM give? What does IF give for ‘flagged over 100’?”

Resources

• Student Excel practice workbook (same structure for all pairs)
• Data worksheet (income/expense table with threshold for IF)
• Projector and speakers for teacher videos
• Printed function cheat sheet (SUM, AVERAGE, IF with example templates)
• Timer for activity segments
• Dyslexia-friendly option: printed large-font version of the worksheet and a text-only rules sheet (no dense tables)
• Extension cards (advanced learners) with extra scenarios for IF and nested logic

Assessment

• Formative during prior knowledge (teacher listens for correct function terminology)
• Observation checklist during activity: correct use of SUM, AVERAGE, IF; range accuracy; ability to explain cause-and-effect
• Exit ticket (accuracy of SUM + IF threshold logic; one-sentence explanation)

Differentiation

• Support for students needing scaffolds: provide sentence starters (“I used SUM to calculate…”, “When I changed…, the output…”) and function templates with highlighted range areas. Allow students to keep a printed cheat sheet visible.
• Support for diverse learners: pairing strategy (strong Excel user + developing user). Teacher provides step-by-step “first, then, next” prompts at each checkpoint.
• Dyslexia-friendly reading options: offer a simplified, large-font version of the worksheet; provide rules as short bullet statements; allow audio read-aloud of instructions (teacher reads the IF rule aloud before students begin).
• Language support: pre-teach “threshold”, “conditional”, “average”, “total” with brief examples using numbers from the worksheet.

Extension (advanced learners)

• Students attempt a harder IF: “If Amount is over 100, calculate 10% of Amount; else 0”, then compare with “flagged expense” logic.
• Challenge: create a second conditional for income (e.g., bonus if income is over a target) and calculate an updated “profit” column.
• Optional extension question during wrap-up: “How would you change the IF formula if the threshold increased from 100 to 150?”