Integrated Financial Workbook Build: Bookkeeping Simulation

Integrated Financial Workbook Build: Bookkeeping Simulation

This lab is the capstone experience for the Excel portion of your program. You will build a complete, professional-grade bookkeeping workbook from scratch, using every major skill developed across the course. The finished file must be accurate enough to hand to another bookkeeper and have them understand, use, and maintain it without asking you questions.

What the Finished Workbook Contains

Your workbook will have at least five sheets:

• Chart of Accounts — account numbers and names, no formulas needed here beyond the lookup reference range.
• Transactions — a formatted Excel Table containing all 50–60 entries, with data validation on key columns.
• Budget — provided for you; contains budgeted amounts by expense category.
• Income Statement — a one-page summary driven entirely by formulas that pull from the Transactions table.
• Notes — your one-page narrative explaining design decisions.

A PivotTable sheet will be created automatically when you insert the PivotTable from the Transactions table.

Building the Chart of Accounts Sheet

Start by entering your account numbers and names from the provided template. Use a consistent numbering scheme (for example, 1000s for assets, 2000s for liabilities, 3000s for equity, 4000s for revenue, 5000s–6000s for expenses). Name this sheet COA. Give the header row clear labels: Account Number and Account Name. This range will serve as the lookup table for your VLOOKUP or XLOOKUP formulas on the Transactions sheet.

Setting Up the Transactions Table

Format your transaction data as a named Excel Table (Insert → Table). A Table expands automatically when new rows are added and makes formula references self-documenting. Include columns for: Date, Account Number, Account Name, Description, Category, Receipt Amount, Disbursement Amount, and Payroll Flag.

Apply data validation to enforce accuracy:

• Restrict the Date column to dates within the simulation month using a date range validation.
• Restrict Category to a drop-down list of your defined expense categories so entries stay consistent for PivotTable grouping.
• Restrict Payroll Flag to a Yes/No drop-down.

Data validation prevents entry errors before they happen — this is the bookkeeping equivalent of double-entry control.

Lookup Formulas: Pulling Account Names

In the Account Name column, write an XLOOKUP (or VLOOKUP) that reads the Account Number in the same row and returns the matching name from your COA sheet. This eliminates manual typing and ensures every account name is spelled exactly as it appears in your chart.

XLOOKUP syntax: `=XLOOKUP([@[Account Number]], COA!$A:$A, COA!$B:$B, "Not Found")`

The `"Not Found"` fallback alerts you immediately if an account number was miskeyed.

Building the Income Statement Summary

On the Income Statement sheet, use SUMIF formulas that pull totals directly from the Transactions table. For example, total service revenue pulls all receipts where Category equals "Service Revenue." Total wages expense pulls all disbursements where Category equals "Wages." Structure the sheet in standard income statement order: Revenue, then Cost of Goods Sold (if applicable), then Operating Expenses, then Net Income at the bottom. Net Income must be a formula, not a typed number.

PivotTable: Expenses by Category

Insert a PivotTable from your Transactions Table. Place Category in Rows and Disbursement Amount in Values (Sum). This gives a one-glance summary of spending by category. On the same or adjacent sheet, add conditional formatting that compares each category's actual total against the budget figures on the Budget sheet. A common approach: use a formula-based conditional formatting rule that turns a cell red when the actual exceeds the budgeted amount.

Protecting Formula Cells

Before submitting, protect all cells that contain formulas. The standard workflow is: select all cells (Ctrl+A), open Format Cells → Protection, and uncheck Locked. Then select only formula cells (Home → Find & Select → Formulas), re-check Locked, and apply sheet protection (Review → Protect Sheet). This prevents accidental overwriting of formulas while leaving data-entry cells editable.

Professional Formatting and Print-Ready Output

The Income Statement sheet must be print-ready: set print area, add a header with the business name and report date, use consistent fonts and number formats (currency with two decimal places, no unnecessary decimals elsewhere), and verify it fits on one printed page. Use Page Layout view to confirm before submitting.

The Notes Narrative

Your one-page narrative on the Notes sheet should explain: why you structured the Chart of Accounts as you did, how the SUMIF categories match the Chart of Accounts, what a future bookkeeper must do to add a new month of transactions, and any formula that might look unusual. Write in plain business English — imagine your audience is a competent bookkeeper who has never seen this specific file.

Accuracy and Data Integrity

Double-check that total receipts and total disbursements on your Income Statement agree with column totals on the Transactions sheet. A one-cent discrepancy matters in bookkeeping. Before finalizing, use Excel's Trace Precedents tool (Formulas → Trace Precedents) on your Net Income cell to visually confirm every formula chain is intact.

Workplace Safety Note

Office work involves sustained computer use. Take short breaks to reduce eye strain and repetitive-stress risk. Keep cables managed so walkways remain clear. Save your file frequently and to a backed-up location — data loss is a professional hazard in office work.