Build the investor-facing summary layer with professional formatting and clear evidence chain.
Build Sarah's Investor-Facing Summary Layer
Now it's your turn to build the summary sheet that turns a working ledger into an investor-ready document. You'll link key metrics, add visual status cues, and document the evidence chain.
Start with your completed Lesson 05 workbook (or use the checkpoint workbook below). It should have a working ledger, trial balance, and self-auditing formulas.
Download Checkpoint: unit01-lesson05-checkpoint.xlsxIf you use your own workbook, verify that:
- Debits and credits balance
- Check column shows 0 for all rows
- Error flags are working
- Create Summary Sheet: Insert a new sheet named "Summary" at the front of the workbook. Add a title, your name, and the current date using =TEXT(TODAY(), "mm/dd/yyyy").
- Link Key Metrics: Pull Debits Total, Credits Total, Difference, Account Count, and Transaction Count from the Trial Balance sheet using cell references (e.g., ='Trial Balance'!B10).
- Build Status Formulas: Use IF formulas to create plain-language status messages (e.g., =IF(Difference=0, "Balanced", "Review Needed")).
- Apply Conditional Formatting: Add green/red/yellow fill to status cells based on values. Green for perfect balance, red for issues, yellow for warnings.
- Write Evidence Chain: In a labeled section, document what the workbook proves, how it proves it, and where the data comes from.
- Professional Polish: Apply consistent fonts, alignment, borders, and spacing. Lock formula cells so users can't accidentally edit them.
Quick test: intentionally break one transaction (change a debit amount). If your Summary sheet shows "Review Needed" and turns red immediately, your status system is working.
- Summary sheet is first tab with clear title, author, and date.
- Key metrics are linked from Trial Balance (no hard-coded numbers).
- Status cells show plain-language messages with conditional formatting colors.
- Evidence chain section explains what the workbook proves and how.
- Professional formatting: consistent fonts, alignment, borders, and spacing.
- Formula cells are locked to prevent accidental edits.
Use this template for your evidence chain section:
What This Workbook Proves
Debits equal credits for all transactions from [date range].
How It Proves It
- Trial balance compares total debits and total credits
- Check column verifies each transaction balances individually
- Error flags catch common posting mistakes
Data Source
[Number] transactions from [start date] to [end date] for TechStart Solutions.
Ready for the next level? Add a "Quick Stats" section with:
- Percentage of accounts with debit balances
- Largest single transaction amount
- Number of unique accounts used
Bonus idea: add a small line chart showing monthly balance trends if you have historical data.