Balance Sheet Template in Excel: Layout, Formulas, Balance Check and Key Ratios (With Example)
Build a small business balance sheet template in Excel: assets, liabilities and equity sections, formulas, an automatic balance check, monthly comparison, links to the P&L, and the ratios that matter (working capital, current ratio, quick ratio, debt-to-equity), with a worked example.
Quick answer: a balance sheet template in Excel lists what the business owns (assets), what it owes (liabilities) and what belongs to the owners (equity) on a specific date. Assets are split into current (cash, receivables, inventory) and non-current (equipment, vehicles, net of depreciation); liabilities into current (due within a year) and long-term. A check cell confirms that total assets = total liabilities + equity, and a few ratios below the statement show liquidity and leverage.
This template is a management tool for small businesses; formal financial statements and tax returns follow the rules that apply to your entity, so have your accountant review year-end figures. The example is illustrative.
Balance sheet vs P&L vs cash flow
| Statement | What it shows | Period |
|---|---|---|
| Balance sheet | Assets, liabilities and equity | A single date (for example, October 31) |
| Profit and loss | Revenue, expenses and profit | A period (for example, October) |
| Cash flow statement | Where cash came from and went | A period |
The three connect: the period’s profit increases retained earnings on the balance sheet, and the change in cash on the balance sheet is explained by the cash flow statement.
Step 1: The layout
| Section | Typical lines |
|---|---|
| Current assets | Cash and bank, accounts receivable, inventory, prepaid expenses |
| Non-current assets | Equipment, furniture, vehicles, software (at cost), less accumulated depreciation; long-term deposits |
| Current liabilities | Accounts payable, credit cards, sales tax payable, accrued wages, payroll taxes payable, current portion of loans |
| Long-term liabilities | Bank loans and equipment financing due after 12 months |
| Equity | Owner’s capital or contributions, owner’s draws (negative), retained earnings, current year profit |
Put labels in column A, the current date’s amounts in column B and the previous month in column C. Section totals use SUM over their lines.
Step 2: Formulas and the balance check
- Total current assets:
=SUM(B5:B8) - Net equipment:
=B11-B12(cost minus accumulated depreciation) - Total assets: current + non-current
- Total liabilities: current + long-term
- Total equity:
=SUM(B30:B33) - Check:
=IF(ROUND(TotalAssets-(TotalLiabilities+TotalEquity),2)=0,"Balanced","Out of balance: "&TEXT(TotalAssets-(TotalLiabilities+TotalEquity),"$#,##0.00"))
If the check fails, the most common causes are a missing account, a loan payment recorded entirely as an expense (the principal reduces the loan instead), or the current period’s profit not added to equity.
Worked example: small retail business, October 31
| Assets | Amount | Liabilities and equity | Amount |
|---|---|---|---|
| Cash and bank | $18,500 | Accounts payable | $9,600 |
| Accounts receivable | $12,300 | Credit card balance | $2,400 |
| Inventory | $21,400 | Sales tax payable | $1,500 |
| Prepaid expenses | $1,800 | Accrued wages | $3,500 |
| Current portion of loan | $6,000 | ||
| Current assets | $54,000 | Current liabilities | $23,000 |
| Equipment at cost | $40,000 | Bank loan (long-term part) | $24,000 |
| Less accumulated depreciation | −$12,000 | Total liabilities | $47,000 |
| Delivery van (net) | $16,000 | Owner’s capital | $20,000 |
| Non-current assets | $44,000 | Retained earnings (incl. this year) | $31,000 |
| Total equity | $51,000 | ||
| Total assets | $98,000 | Total liabilities and equity | $98,000 |
Check: current assets 18,500 + 12,300 + 21,400 + 1,800 = 54,000; non-current 40,000 − 12,000 + 16,000 = 44,000; current liabilities 9,600 + 2,400 + 1,500 + 3,500 + 6,000 = 23,000; total liabilities 23,000 + 24,000 = 47,000; equity 51,000; 47,000 + 51,000 = 98,000.
Step 3: Where each number comes from
- Cash: bank statement balances on the balance sheet date, plus cash on hand.
- Accounts receivable: unpaid customer invoices, for example from the tracker in your invoice template.
- Inventory: quantity on hand × unit cost from your inventory template or a physical count.
- Equipment and depreciation: a small fixed-asset list with cost, date and depreciation per year.
- Payables, card balances, sales tax: open bills, card statements, tax returns.
- Loans: lender statements; split the next 12 months of principal into the current portion.
- Retained earnings: prior retained earnings + profit for the year − owner’s draws (for sole proprietors and partnerships, draws are often tracked separately in equity).
If you keep your books in a spreadsheet, most of these balances come straight from your bookkeeping template.
Step 4: Ratios that matter
| Ratio | Formula | Example | What it tells you |
|---|---|---|---|
| Working capital | Current assets − current liabilities | $31,000 | Short-term cushion |
| Current ratio | Current assets / current liabilities | 2.35 | Ability to pay short-term obligations |
| Quick ratio | (Current assets − inventory − prepaids) / current liabilities | 1.34 | Same, without relying on selling inventory |
| Debt-to-equity | Total liabilities / total equity | 0.92 | How much the business is financed by debt |
Check: 54,000 − 23,000 = 31,000; 54,000 / 23,000 = 2.35; (54,000 − 21,400 − 1,800) / 23,000 = 30,800 / 23,000 = 1.34; 47,000 / 51,000 = 0.92. Use IFERROR around each ratio to avoid errors when a denominator is zero. There is no single “right” value; watch the trend month to month and compare with businesses like yours.
Step 5: Monthly comparison
Add columns for each month-end and a change column: =B5-C5. Big movements deserve an explanation. For example, if inventory rose by $8,000 while sales were flat, cash is being tied up on shelves; if accounts receivable grew faster than sales, customers are paying more slowly.
Common mistakes
- Listing equipment at purchase price forever without depreciation.
- Treating loan principal payments as expenses: only interest is an expense; principal reduces the liability.
- Forgetting sales tax collected: it belongs to the government, so it is a liability until paid.
- Mixing personal and business assets.
- Ignoring the check cell: an out-of-balance sheet means something is missing.
FAQ
How often should a small business prepare a balance sheet?
At least at year end; monthly or quarterly is better for spotting cash, receivable and inventory problems early.
What is the difference between equity and profit?
Profit is earned over a period; equity is the accumulated stake of the owners at a point in time, including capital contributed and profits kept in the business.
Can I use Google Sheets?
Yes. SUM, IF, ROUND, TEXT and IFERROR work the same way.
More guides: Small Business Finance.