Skip to main content

Frac CFO

By Jonas Tyrone Lobaton, MSc Quantitative Finance, FMVA®
Founder, Frac CFO · Updated October 18, 2026

A useful Excel balance sheet begins with a complete, dated accounting trial balance, not a list of bank balances. Map accounts to financial-statement lines, incorporate current-period earnings into equity, and verify both the debit/credit totals and the balance-sheet equation.

This hands-on example uses a fictional sole proprietorship, with $39,600 assets, $16,300 liabilities and $23,300 closing equity. Its pre-closing trial balance includes revenue and expense accounts, and its $500 current-period profit is reconciled into equity. The worked example below provides the formulas and independent control checks; a separate SAMPLE workbook accompanies this review.

For a self-service starting point, see Frac CFO’s Small Business Bookkeeping Excel Template. A spreadsheet layout is not a substitute for reconciled accounting records or professional review.

TL;DR: Six steps to an Excel balance sheet that reconciles

  1. Export a complete, adjusted trial balance containing debit and credit balances, including unclosed income and expense accounts where applicable.
  2. Maintain a chart of accounts with stable numeric IDs and a reviewed mapping to report lines.
  3. Normalize signs: debit-positive source balances; liabilities and equity display as positive credit balances on the statement.
  4. Reconcile current-period revenue less expenses into closing equity, with entity-appropriate distributions and capital movements.
  5. Build the balance sheet with SUMIFS and verify assets = liabilities + equity.
  6. Run separate checks for trial-balance debits/credits, unmapped accounts, bank and ledger support, debt and equity. A zero difference alone never proves accuracy.

Table of Contents

What a balance sheet shows

A balance sheet reports assets, liabilities and equity as of one reporting date. Its governing equation is Assets = Liabilities + Equity. An income statement instead covers income and expenses over a period; current-period profit or loss must ultimately be reflected in equity.

Cash and accounts receivable may be assets; outstanding supplier invoices and loans may be liabilities. Owner capital and accumulated earnings are part of equity. Loan principal received is not revenue, and equipment purchased is generally not an immediate P&L expense merely because cash was paid.

Do not mistake a pre-closing trial balance for the final balance sheet. It commonly contains nominal accounts for revenue and expenses, and is used with adjusting entries, earnings allocation or closing procedures to prepare the statements. Our worked example is an intentionally small fictional sole-proprietor case; corporate dividends and retained earnings differ from owner drawings and proprietorship capital.

Filing rules are jurisdiction- and entity-specific. A general Excel tutorial should not suggest that only US C corporations file a balance sheet or give an unverified SBA-loan threshold. Check the applicable forms, lender instructions, materiality rules and accounting standards with a qualified accountant.

Step 1: Build a chart of accounts and an explicit mapping table

The chart of accounts (COA) assigns each source account a stable Account ID, a clear account name, an accounting type, and one reporting line. Sample IDs are 1000 Cash, 1100 Receivables, 1500 Equipment, 2000 Payables, 2200 Loan, 3000 Owner Capital, 4000 Service Revenue and 5000 Operating Expenses. Number ranges are conventions, not law.

Do not use account-name text alone as the matching key; aliases, renamed accounts and spaces can silently produce zeros. Instead, use a controlled mapping keyed by Account ID, with tests for missing IDs, duplicates and inappropriate report types.

For a pre-closing trial balance, revenue and expense accounts are not direct asset/liability report lines. They flow through a separate current-period profit calculation and closing-equity reconciliation. Contra-asset accounts such as accumulated depreciation can have a normal credit balance and need an explicit reporting treatment.

Keep the underlying accounting export intact. The SAMPLE workbook uses a COA sheet to show the one-to-one mapping, then calculates report amounts from signed trial-balance balances. It is not an accounting ledger or a bank reconciliation system.

Step 2: Use a complete pre-closing trial balance

Begin with a dated, adjusted trial balance exported from the accounting system. A pre-closing trial balance includes revenue and expense accounts as well as balance-sheet accounts. Their balances must be included in current-period earnings or appropriate closing entries before the finished balance sheet is presented.

For a simple fictional sole proprietor, the source convention below records debits and credits in separate columns.

Pre-closing trial balance · USD · Sample
Account Debit Credit
Cash $18,400 —
Accounts receivable $9,200 —
Equipment $12,000 —
Operating expenses $500 —
Accounts payable — $6,300
Business loan — $10,000
Opening owner capital — $22,800
Service revenue — $1,000
Total $40,100 $40,100

Net income: $1,000 revenue minus $500 expenses = $500. Without owner contributions or drawings during this example period, closing equity is $22,800 opening capital plus $500 profit = $23,300.

Bank, customer, supplier, loan and asset schedules are supporting reconciliation evidence—not a substitute for the adjusted general ledger and cutoff procedures. This example omits taxes, depreciation and other adjustments for teaching clarity; a production report must include every applicable account.

Step 3: Report accounts using signed trial-balance data and SUMIFS

In the accompanying Excel SAMPLE workbook, COA maps stable Account IDs to report lines. On Trial Balance, columns E:L contain Account ID, name, debit, credit, signed balance, account type, report line and reporting date.

Normalize Signed Balance = Debit − Credit. Cash and other normal debit-balance assets display as positive numbers. A normal credit-balance liability or equity account is negative in the signed source, so negate it once for a positive statement presentation. Contra balances and exceptional debit/credit balances must be assessed under their actual classifications.

Balance Sheet!F8 – Cash:

=SUMIFS('Trial Balance'!$I$7:$I$14,'Trial Balance'!$K$7:$K$14,E8,'Trial Balance'!$L$7:$L$14,'Report Settings'!$F$7)

Balance Sheet!F15 – Accounts payable:

=-SUMIFS('Trial Balance'!$I$7:$I$14,'Trial Balance'!$K$7:$K$14,E15,'Trial Balance'!$L$7:$L$14,'Report Settings'!$F$7)

Current-period earnings are calculated from the revenue and expense account types on the pre-closing trial balance and reconciled through the separate Equity Roll forward worksheet. This tutorial treats the entity as a sole proprietor; corporate retained earnings instead follow a different dividend/distribution convention.

Finished balance sheet: the amounts must reconcile

Balance sheet line USD
Cash $18,400
Receivables $9,200
Equipment $12,000
Total assets $39,600
Payables and loan $16,300
Owner capital plus current profit $23,300
Liabilities + equity $39,600

The pre-closing trial balance reconciles at $40,100 debit and credit. The separate balance-sheet equation reconciles at $39,600 − $39,600 = $0. Neither result establishes that classification or external source data are error-free.

Step 4: Reconcile earnings, equity and the accounting equation

A finished balance sheet must satisfy Assets = Liabilities + Equity; the SAMPLE uses ='Balance Sheet'!F13-'Balance Sheet'!F23 as a zero-difference check. A separate pre-closing trial-balance control verifies total debits less total credits equals zero.

For the fictional sole proprietor, the equity bridge is:

Owner capital movement USD
Opening owner capital $22,800
Contributions / drawings $0
Current-period net profit $500
Closing owner capital $23,300

Equity Rollforward!F11: =SUM(F7:F9)-F10, representing opening capital plus additional contributions and current profit minus drawings. Corporate retained earnings generally follow opening retained earnings + net income − dividends, subject to other applicable adjustments. Different business entities should not share a generic drawings/dividends formula without review.

A zero difference is necessary but insufficient. A misclassified $10,000 loan recorded as owner equity would still balance. An asset and offsetting liability could both be omitted without disturbing the equation.

Run independent checks for trial-balance debits and credits, unmapped or duplicated accounts, signed balance totals, correct period cutoff, equity roll forward and statement equality. Then separately reconcile cash to bank records, receivables and payables to subledgers, fixed assets to supporting schedules, and loans to lender statements.

For troubleshooting, a difference exactly twice an account value may suggest a reversed sign, but that is only one diagnostic clue—not a general rule that imbalances should be divided by two.

Step 5: Repeat the balance sheet for every month-end

A balance sheet measures closing account balances at a point in time, not the month’s net transaction activity. If source data contains journal-entry movements instead of closing balances, calculate opening balance plus cumulative movements through the month-end cutoff rather than simply summing the current month’s postings.

For monthly comparison, maintain one complete adjusted trial-balance snapshot per reporting date, or derive the as-of balance from the ledger. Then use dates as additional SUMIFS criteria, ensuring every included account uses the same entity, currency and close date.

The SAMPLE workbook keeps the report date in 'Report Settings'!F7 and uses it in the Balance Sheet formulas. Future production versions can extend the rows and report columns for comparative reporting, but should never overwrite historical snapshots or silently mix balances from different cutoffs.

Monitor current ratio, leverage, working capital and overdue receivables only after the underlying classifications are verified; ratios from a balanced but incomplete statement remain misleading.

Excel model checks for a credible balance sheet

  • Keep inputs separate: COA mappings and trial-balance source amounts on dedicated sheets; do not type adjustments directly into the finished report.
  • Control the sign convention: record debit and credit inputs explicitly. Signed balance = debit minus credit. Show normal credit-side liabilities and equity as positive only on presentation.
  • Flag missing mapping: check for COA IDs appearing twice, TB account IDs without a valid mapping, unsupported report lines and unexpected balance signs.
  • Reconcile earnings once: revenue and expense balances flow through the income calculation and equity bridge; do not also present them as separate assets or liabilities.
  • Control cutoffs: keep the report date, source file, version and currency visible, and reconcile to bank, loan and working-capital subledgers outside the workbook’s arithmetic checks.
  • Protect formulas: maintain backed-up versions, distinguish manual inputs from formulas and review access to financial records.

The illustrative eight-sheet SAMPLE workbook includes 10 formula-based internal QA tests and an executive dashboard. The QA tab separately states that third-party bank, loan, receivable and payable reconciliations have not been performed on the fictional dataset. Passing QA does not certify an audit or accounting-standard compliance.

For financial reporting under IFRS, presentation requirements change when IFRS 18 replaces IAS 1 for annual reporting periods beginning on or after 1 January 2027 (early application is permitted). Confirm local and entity-specific accounting and tax requirements before submitting any report externally.

When is an Excel balance sheet appropriate?

Excel works well for a clearly sourced, small or mid-sized reporting task when mapping, source balances, approval, version control and reconciliations are handled reliably. Being able to see a formula is helpful, but it does not guarantee completeness, fraud prevention, correct categorization or compliance.

The right escalation point is not a fixed revenue level; it is recurring unreconciled balances, multi-entity consolidation, complex revenue or inventory accounting, multiple data imports and limited financial review capacity. For budgeting and future projections, a Three Statement Financial Model Excel Template may be helpful, but a forecasting template does not replace the accounting close or reconcile historical trial balances.

Practical advice: start with the adjusted ledger and tested report structure, then add dashboards and forecasting after the core statement reconciles. An actual Excel statement screenshot is more instructive than a generic graphic because reviewers can trace the numbers.

Frac CFO: templates and finance support that match this task

Self-service: the Small Business Bookkeeping Excel Template ($22.99 at the time of review) supports bookkeeping and financial reports. The Small Business Finance Excel Bundle ($48.99) combines multiple finance worksheets. Buyers should verify whether a specific template includes their required reconciliations, entity structure and reporting conventions.

Forecasting rather than historical accounting: the Three Statement Financial Model Excel Template ($29.99) supports five-year projections. It should not be presented as a replacement for a closed, adjusted trial balance.

Custom project: use One-time Projects if you need a defined, scoped chart-of-accounts mapping, historical statement model or specific reconciliation workbook. Confirm required inputs, account-count scope, turnaround and exclusions in writing.

Ongoing finance support: the STARTER, GROWTH and SCALE plans have different recurring deliverables. Check the live plan pages for monthly fees, annual-payment discounts, onboarding and actual included work. Do not assume every tier includes unlimited bookkeeping cleanup or custom financial modeling.

Need a balance sheet you can trace back to the ledger?

Explore the bookkeeping template or request a fixed-scope Excel finance project. Bring an adjusted trial balance and relevant supporting statements; the accuracy of the output depends on the source data and agreed work.

Explore One-time Projects

Common balance sheet mistakes to avoid

  • Incomplete trial balance: leaving out revenues, expenses or post-close retained earnings movements.
  • Incorrect signs: mixing debit-positive sources with a report already showing liabilities and equity as positive.
  • Revenue treated as equity twice: current-period profit flows into closing equity once through the bridge or posted closing entries.
  • Source gaps: relying on bank statements for receivables, payables, accruals, fixed assets or unrecorded liabilities.
  • Overreliance on one check: a balanced report can still omit both sides of a transaction or mislabel a loan as equity.
  • Incorrect cutoff: combining balances from different periods, currencies or entities.
  • Misreading sample data: the figures in this tutorial are fictional and should not be reused for lender or statutory reporting.

Frequently asked questions: Excel balance sheets

Can I use an Excel template instead of creating the report from scratch?

Yes, provided the template suits your account structure and uses a complete, reconciled source trial balance. Check the sign convention, account mapping, period cutoff and the handling of current-period earnings. Templates are an organizational tool, not an alternative to proper accounting records.

Should revenue and expense accounts appear in the trial balance?

Yes, in a typical pre-closing trial balance. They are included in the current-period profit calculation and ultimately affect equity. A post-closing trial balance can exclude closed nominal accounts. Clearly state which trial-balance stage is being reported.

Why might a balance sheet balance and still be incorrect?

A wrong liability-to-equity classification or offsetting omissions may preserve the equation. Reconcile mapped accounts to the ledger and supporting statements, investigate missing accounts and verify cutoff dates; a mathematical zero is only one control.

How does net income appear in equity?

Profit for the relevant period increases equity, subject to distributions and other applicable movements. For a sole proprietor, use an owner-capital reconciliation; for corporations, use an appropriate retained-earnings rollforward and dividend treatment. Do not duplicate earnings already posted in closing entries.

How do debit and credit signs work with SUMIFS?

For the SAMPLE, debit minus credit defines the signed trial-balance balance. Normal debit assets appear positive. Liabilities and capital accounts normally carry credit-negative source balances, which are negated for a positive statement presentation. Review contra balances and exceptional sign reversals explicitly.

What is the Excel balance-sheet check formula?

Subtract liabilities and equity from total assets, for example =F13-F23 in the SAMPLE workbook. Zero indicates the accounting equation reconciles. It does not validate every journal entry, mapping, or source balance.

Do I need to reconcile the balance sheet to outside records?

For reliable external or internal financial reporting, cash should tie to bank reconciliations, receivables and payables to subledgers, debt to lender schedules, and assets and accruals to appropriate support. The supplied fictional example has no independent third-party records and is not audit evidence.

About the author

Jonas Tyrone Lobaton is the founder of Frac CFO. He holds a Master’s degree in Quantitative Finance and the FMVA® certification, with work spanning financial modeling, forecasting, optimization, dashboards, business finance, and spreadsheet automation.

Technical sources and further reading

The numerical example and workbook are fictional educational material, not a verified client balance sheet, tax filing, audit report or accounting advice. Reporting obligations vary by jurisdiction and legal entity.

7 Responses

Leave a Reply