Math for Business Operations

Applied Accounting with Excel

UNIT01 - Lesson 4

Excel Model: Tables & SUMIF Functions

45 minutes
Lesson Overview

Lesson Focus

Build Sarah's ledger foundation using Excel Tables and aggregation formulas

Key Unit Objectives

Enduring Understandings:

  • Accurate financial records are the foundation of business credibility and investor confidence
  • The accounting equation (Assets = Liabilities + Equity) governs all business transactions
  • Self-auditing systems prevent errors and build stakeholder trust through automation
  • Professional presentation of financial data communicates business competence

Lesson Activities

Activity 1: Sarah's Excel Challenge
5 minutes

Connect manual recording to digital ledger system

Details:

  • Review: Sarah knows how to record transactions, but needs a digital system
  • Challenge: How can Excel help Sarah track hundreds of transactions efficiently?
  • Goal: Build the foundation of Sarah's self-auditing ledger
Activity 2: Excel Tables for TechStart Ledger
20 minutes

Hands-on creation of structured ledger using Excel Tables

Details:

  • Demonstrate: Convert Sarah's transaction list to Excel Table format
  • Show structured references: How Table columns become formula names
  • Practice: Students create their own TechStart ledger table
  • Benefits: Dynamic expansion as Sarah's business grows
Why Excel Tables for Business Ledgers?

Excel Tables provide the professional structure Sarah needs

  • Structured references: Formulas use column names instead of cell references
  • Dynamic expansion: New transactions automatically included in calculations
  • Professional formatting: Consistent appearance for investor presentations
  • Error reduction: Built-in data validation and formatting rules
Activity 3: SUMIF Functions for Account Totals
15 minutes

Build automatic account balance calculations using SUMIF

Details:

  • Demonstrate: SUMIF to total all cash transactions for Sarah's business
  • Guided practice: Students build SUMIF for TechStart revenue accounts
  • Partner work: Create SUMIF formulas for expense account totals
  • Test: Verify formulas work correctly with new transaction entries
Activity 4: Excel Model Checkpoint
5 minutes

Verify working ledger foundation

Details:

  • Students test their Excel Table with sample TechStart transactions
  • Check: Do SUMIF formulas update automatically when new data is added?
  • Preview Day 5: How do we add error-checking to catch Sarah's mistakes?
Required Materials
  • TechStart transaction dataset (Excel format)
  • Excel Tables step-by-step guide
  • SUMIF formula templates
  • Ledger structure template
Differentiation Strategies

For Struggling Students

  • • Scaffolded Formulas: Provide partial SUMIF syntax templates
  • • Step-by-Step Guides: Visual walkthroughs for each Excel skill
  • • Peer Tutoring: Pair with students strong in Excel
  • • Alternative Assessment: Oral explanation option for presentations

For Advanced Students

  • • Extension Challenges: Dynamic dropdowns for account selection
  • • Peer Teaching: Support struggling classmates
  • • Advanced Features: Explore pivot tables or basic VBA
  • • Leadership Roles: Facilitate team discussions and planning

For English Language Learners

  • • Vocabulary Support: Business terms glossary with visuals
  • • Translation Tools: Key concepts in home language
  • • Visual Aids: Infographics and flowcharts for processes
  • • Collaborative Support: Mixed-language team structures