Math for Business Operations

Applied Accounting with Excel

UNIT03 - Lesson 6

New Skill Focus: Balance Sheet Linking & Retained Earnings

45 minutes
Lesson Overview

Lesson Focus

Direct instruction on Balance Sheet construction with cross-sheet linking

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: Balance Sheet Architecture Review
8 minutes

Understanding Balance Sheet structure and its connection to other statements

Details:

  • Review Balance Sheet equation: Assets = Liabilities + Equity
  • Connect to TechStart context: How Sarah's business assets and liabilities are organized
  • Explain current vs. non-current classifications and why they matter to investors
  • Preview the critical link: Retained Earnings connection to Income Statement
Activity 2: Cross-Sheet Linking with Named Ranges
15 minutes

Hands-on instruction for linking Balance Sheet to trial balance and Income Statement

Details:

  • Demonstrate creating named ranges for trial balance accounts
  • Show INDEX/MATCH formulas for pulling asset and liability balances
  • Build links from Balance Sheet to Income Statement Net Income
  • Practice with TechStart data: linking cash, accounts receivable, equipment accounts
Professional Cross-Sheet Linking

Named ranges and proper linking create maintainable financial models

  • Named Ranges: Use descriptive names like 'TB_Cash' for trial balance cash account
  • Consistent References: All Balance Sheet links should reference same source data
  • Error Prevention: Use IFERROR to handle missing accounts gracefully
  • Documentation: Comment formulas to explain linking logic for future users
Activity 3: Retained Earnings Reconciliation
15 minutes

Critical instruction on linking Retained Earnings to Income Statement Net Income

Details:

  • Explain Retained Earnings as cumulative business profits reinvested
  • Demonstrate formula: Beginning Retained Earnings + Net Income - Dividends
  • Show link from Income Statement Net Income to Balance Sheet Retained Earnings
  • Verify Balance Sheet equation balances with proper Retained Earnings calculation
Activity 4: TechStart Balance Sheet Construction
5 minutes

Begin guided construction of TechStart Balance Sheet

Details:

  • Students begin building Balance Sheet using techniques demonstrated
  • Focus on proper account categorization and professional formatting
  • Preview Day 7: Complete Balance Sheet construction and validation
Activity 5: Milestone 2 Preview
2 minutes

Preview Milestone 2 success criteria for Balance Sheet completion

Details:

  • Review Milestone 2 criteria: linked accounts, reconciled Retained Earnings, balanced equation
  • Set expectation for Day 7: completed Balance Sheet ready for integration with Cash Flow
Required Materials
  • Balance Sheet template with linking formulas
  • Named ranges creation tutorial
  • Retained Earnings reconciliation examples
  • TechStart trial balance with Balance Sheet accounts highlighted
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