Build the real automation artifact with verification checkpoints and Definition of Done
Build the Automation
Open the starter workbook and build your first clickable close flow.
Download the starter workbook. It contains TechStart's March unadjusted trial balance and blank sections for you to build.
Required incoming state: The workbook has an "Inputs" sheet with unadjusted trial balance data. You will add named ranges, calculation blocks, and a button to the same file.
Your finished workbook should have this structure. Use the teacher workbook as a reference if you get stuck.
| Section | Location | Purpose |
|---|---|---|
| Input Area | Inputs sheet, rows 2-15 | Unadjusted TB values, period dates |
| Named Ranges | Formula tab → Name Manager | Supplies, PrepaidInsurance, Equipment, WagesPayable, UnearnedRevenue |
| Adjusting Entries Block | Inputs sheet, rows 18-30 | Computes each adjustment amount from named ranges |
| Verification Checkpoint | Inputs sheet, row 32 | Checks total debits = total credits |
| Adjusted TB Block | Inputs sheet, rows 35-50 | Unadjusted + adjustments = adjusted balances |
| CloseStatus Cell | Inputs sheet, cell A1 | Shows "Complete" or "Error—check flagged items" |
| Run Close Button | Inputs sheet, top-right | Triggers the full close flow |
Define Named Ranges
Select each input cell range and define a name: Supplies, PrepaidInsurance, Equipment, WagesPayable, UnearnedRevenue. Verify each name appears in the Name Manager.
Build Adjusting Entries Block
Create a table with columns: Account, Unadjusted, Adjustment, Adjusted. Use named ranges in formulas—never hard-coded cell addresses.
Add Verification Checkpoint
Add a cell that checks: =IF(TotalDebits=TotalCredits, "OK", "ERROR: Debits ≠ Credits"). This must appear before the button.
Build Adjusted Trial Balance Block
Below the adjusting entries, create the Adjusted TB: each account's unadjusted balance plus its adjustment. Total both columns.
Insert the Run Close Button
Developer tab → Insert → Button → Draw it on the sheet → Assign a macro that reads inputs, runs calculations, checks verification, and updates CloseStatus.
Your workbook is complete when all of the following are true:
- At least 5 named ranges defined and working (Supplies, PrepaidInsurance, Equipment, WagesPayable, UnearnedRevenue)
- Input area is color-coded and physically separate from calculation blocks
- Adjusting entries block computes all 5 adjustments using named ranges
- Verification checkpoint cell shows "OK" or "ERROR" based on debits vs credits
- Adjusted TB block produces correct balances and totals match
- Button runs the full flow and CloseStatus updates to "Complete"
- Workbook saved as .xlsm (macro-enabled format)
Named range not working? Check the Name Manager (Formula tab → Name Manager). Make sure the range reference is correct and not pointing to a blank cell.
Button does nothing? Right-click the button → Assign Macro. Make sure the macro name is selected. Also verify macros are enabled (File → Options → Trust Center → Macro Settings).
Verification always shows ERROR? Check that your adjusting entry debits and credits are equal. Each adjustment should have one debit and one credit of the same amount.
If none of these help, open the teacher reference workbook and compare your layout step by step.