Financial Reporting Package, Correspondence Set, and Records Binder

Why a Capstone Deliverable Matters

Employers hiring bookkeepers and office administrators expect more than data entry skills. They want evidence that you can communicate financial information clearly, manage records responsibly, and use software tools together—not in isolation. This final project asks you to do exactly that: export QuickBooks data into a polished Excel reporting package, write three professional business documents, and organize everything into an indexed records binder. Each piece mirrors real work you will perform on the job and demonstrates the integrated competency that the AIPB Certified Bookkeeper credential and the Microsoft Office Specialist certification are designed to measure.

Building the Excel Financial Reporting Package

Start inside QuickBooks by running five standard reports for your simulated client: Profit & Loss (P&L), Balance Sheet, Statement of Cash Flows, AR Aging Summary, and AP Aging Summary. Use the Export to Excel feature for each report. Save each file immediately with a clear name that includes the report type and date range before you edit anything.

Once the data lands in Excel, apply consistent, professional formatting across all five tabs:

• Headers: Company name, report title, and date range on rows 1–3 of every tab. Bold, larger font.
• Number formats: Use the Accounting format (dollar sign, two decimals) for currency columns. Use comma style for large whole numbers.
• Borders: Apply outside borders to each report block and bottom borders under column headings.
• Tab labels: Rename each sheet tab to a short version of the report name (e.g., P&L, Bal Sheet, Cash Flow, AR Aging, AP Aging).
• Conditional formatting on aging buckets: In the AR and AP Aging tabs, apply a color scale or highlight rule so that amounts in the 60-day and 90-day-plus columns are visually flagged. Red fill with dark text is a common convention. This helps a business owner spot collection or payment risks at a glance.

Next, create a sixth tab labeled Summary. This tab must pull data from the other five tabs using cell references—never type a number directly into a formula. The three key metrics you will calculate are:

1. Gross Profit Margin — Gross Profit divided by Net Revenue, expressed as a percentage.

2. Current Ratio — Current Assets divided by Current Liabilities.

3. Days Sales Outstanding (DSO) — Total Accounts Receivable divided by average daily Net Revenue (annual Net Revenue divided by 365).

Because all numbers come from cell references, any correction you make to a source report tab will automatically update the Summary tab. This is a fundamental advantage of spreadsheet modeling over static, typed reports.

Writing the Business Correspondence Set

All three documents must be typed on the client's letterhead template, follow the block letter or modified-block format taught in Course 6, and pass a peer review checklist before you submit them.

Engagement Letter: Confirms the scope of bookkeeping services you will perform for the client and the fee structure. State the services clearly (e.g., monthly bank reconciliation, payroll processing, financial statement preparation). Avoid vague language. Use a professional but approachable tone. Both parties should understand exactly what is and is not included.

30-Day Past-Due Collection Notice: You identified the customer in the AR Aging report. The letter must be polite but firm. State the amount owed, the original invoice date(s), and a clear deadline for payment. Offer a contact name and phone number for the customer to reach if there is a dispute. Avoid threatening or demeaning language—it is unprofessional and may have legal implications in some jurisdictions.

Internal Memo — Cash-Handling Recommendation: A memo is an internal document; it does not use the formal letter format, but it must have a clear TO / FROM / DATE / SUBJECT header. Identify the specific control weakness you observed in the scenario (for example, the same employee handles both cash receipts and bank reconciliation—a segregation-of-duties problem). State the risk in plain terms, then recommend a practical corrective action the owner can realistically implement.

Assembling the Records Binder

Whether you build a physical three-ring binder with labeled dividers or a structured shared-drive folder set, the organization principle is identical: every document must be easy to locate, and a reader who is unfamiliar with the file must be able to determine what is inside without opening it.

The typed index is the most important element of the binder. For each item, record four things:

1. Document title — specific, not generic (e.g., Bank Reconciliation — Checking Account — March 31)

2. Date — the date the document was created or covers

3. Retention period — follow the retention schedule from Course 7; for example, bank reconciliations are commonly retained for several years, while payroll records have their own schedule under federal and state rules

4. Security classification — label each item Public, Internal, or Confidential; payroll registers and client engagement letters are typically Confidential

Keep confidential documents in a clearly marked section. In a digital environment, set folder permissions so that only authorized users can access confidential materials. This is standard records management practice and is directly aligned with the office administration standards in this program.

Accuracy and Professional Standards

The AIPB holds bookkeepers to a strict standard of accuracy—errors in financial statements can mislead business owners and, in some cases, create legal liability. Before you submit:

• Cross-check every figure on the Summary tab against the source report tabs.
• Read each correspondence piece aloud to catch awkward phrasing or missing information.
• Verify that every item in the binder has an entry in the index.

Accuracy is not optional in this profession. A habit of self-review before submission is one of the most valuable skills you can develop.