Math for Business Operations

Applied Accounting with Excel

UNIT03 - Lesson 4

Excel Mastery: INDEX/MATCH & Named Ranges

45 minutes
Lesson Overview

Lesson Focus

Hands-on INDEX/MATCH and named ranges to create dynamic links

Key Unit Objectives

Enduring Understandings:

  • Financial statements tell the complete story of business performance and position
  • Integrated financial statements reveal connections between profit, balance sheet strength, and cash flow
  • Dynamic dashboards transform raw financial data into actionable business insights
  • Professional financial communication builds investor confidence and trust

Lesson Activities

Activity 1: INDEX/MATCH Deep Dive
18 minutes

Teach professional INDEX/MATCH patterns for statement mapping

Details:

  • Compare INDEX/MATCH vs. VLOOKUP for flexibility and maintainability
  • Create named ranges and use structured references in formulas
  • Refactor Income Statement links to rely on INDEX/MATCH and names
  • Add IFERROR guards for missing accounts
Activity 2: Guided Refactor
20 minutes

Convert existing student models to named ranges + structured references

Details:

  • Turn data into Tables to enable auto‑expansion
  • Replace positional references with Table[Column] notation
  • Validate dynamic updates by inserting rows into source data
  • Document key ranges and assumptions
Activity 3: Quick Check
7 minutes

Short comprehension check on INDEX/MATCH and naming conventions

Details:

  • Multiple‑choice concept check (ComprehensionCheck)
  • Vocabulary reinforcement (FillInTheBlank)
Required Materials
  • INDEX/MATCH tutorial screencast
  • Named ranges quick guide
  • Income Statement refactor worksheet
Differentiation Strategies

For Struggling Students

  • • Formula Templates: Provide INDEX/MATCH syntax with specific cell references pre-filled
  • • Step-by-Step Guides: Visual walkthroughs for each statement construction process
  • • Simplified KPIs: Focus on 2-3 basic ratios rather than complex dashboard features
  • • Peer Support: Pair with students strong in Excel for collaborative construction

For Advanced Students

  • • Advanced Features: Implement dropdown menus, scenario analysis, and dynamic charting
  • • Industry Customization: Research industry-specific KPIs and benchmarking data
  • • Teaching Roles: Support struggling classmates with formula construction and troubleshooting
  • • Extension Projects: Create automated reporting features or basic VBA functionality

For English Language Learners

  • • Financial Vocabulary Support: Glossary with visual definitions of key accounting terms
  • • Template Scaffolding: Pre-structured templates with clear headings and organization
  • • Visual Learning Aids: Flowcharts showing statement relationships and data flow
  • • Collaborative Support: Mixed-language team structures for peer translation and explanation