Math for Business Operations

Applied Accounting with Excel

UNIT02 - Lesson 4

Excel Tables & SUMIF: Building the Month-End Wizard Foundation

45 minutes
Lesson Overview

Lesson Focus

Hands-on Excel Tables with structured references and SUMIF automation

Key Unit Objectives

Enduring Understandings:

  • Automation reduces human error and increases efficiency in financial processes
  • GAAP compliance requires systematic tracking of accruals, deferrals, and adjusting entries
  • User interface design determines the usability and adoption of financial tools
  • Time savings through automation creates competitive advantage for businesses

Lesson Activities

Activity 1: Excel Tables: Structured References
15 minutes

Convert data ranges to Tables and use structured references

Details:

  • Create TransactionTable with headers and banded rows
  • Use structured references (e.g., TransactionTable[Amount])
  • Demonstrate auto-expansion when adding new rows
  • Discuss benefits for reliability and scalability
Activity 2: SUMIF Foundations
15 minutes

Build SUMIF formulas to automate conditional totals

Details:

  • Revenue total: =SUMIF(TransactionTable[Type], "Revenue", TransactionTable[Amount])
  • Expense total: =SUMIF(TransactionTable[Type], "Expense", TransactionTable[Amount])
  • Accrual total: =SUMIF(TransactionTable[Type], "Accrual", TransactionTable[Amount])
  • Troubleshoot criteria and reference errors
Activity 3: Named Ranges & Linking
10 minutes

Define named cells and link summaries to dashboard outputs

Details:

  • Create MonthlyRevenue and MonthlyExpenses named cells
  • Link to SUMIF totals for reporting
  • Check for dynamic updates as data grows
Activity 4: Quick Check: Tables & SUMIF
5 minutes

Short formative check on structured references and SUMIF

Details:

  • Students answer 3–4 questions on syntax and behavior
  • Verify understanding before advanced automation
Required Materials
  • Sample transaction dataset
  • SUMIF reference sheet
  • Excel Tables quick guide
Differentiation Strategies

For Struggling Students

  • • Step-by-Step Macro Guides: Visual walkthroughs for macro recorder usage
  • • Pre-built Templates: Partial VBA code with clear modification instructions
  • • Simplified Scenarios: Focus on one adjusting entry type before expanding
  • • Peer Support: Pair with students strong in Excel automation skills

For Advanced Students

  • • Advanced VBA Functions: Error handling routines and user input validation
  • • Cross-Sheet Integration: Link multiple workbooks for comprehensive systems
  • • Custom Functions: Write UDF (User Defined Functions) for complex calculations
  • • Mentoring Role: Support other teams with technical troubleshooting

For English Language Learners

  • • Visual Interface Design: Use icons and graphics to supplement text instructions
  • • Technical Vocabulary Support: Accounting and Excel terms with visual definitions
  • • Collaborative Documentation: Partner with native speakers for user instruction writing
  • • Multiple Demonstration Formats: Video tutorials with visual emphasis over verbal explanation