# Balance Sheet Model Build a five-year balance sheet that actually balances, and know why. Working capital runs off days sales outstanding, days inventory on hand and days payable outstanding rather than a percentage of sales; net PP&E rolls forward through capex and a MIN-guarded depreciation charge; an amortising term loan steps the debt balance down; and equity carries share capital plus a full retained-earnings roll-forward. Cash is not a plug - a dedicated cash roll-forward derives it from net income, depreciation, every working-capital movement, capex, debt repayment and dividends, which is the accounting identity that forces the statement to balance. The opening column ties at day zero because opening retained earnings are solved as the residual of opening assets, liabilities and share capital. - Canonical: https://finamodel.com/templates/balance-sheet - Excel download: https://finamodel.com/templates/balance-sheet.xlsx - Category: Corporate Finance - Model type: Operating model - Difficulty: Beginner - Audiences: CFOs & FP&A, Founders & operators, Founders and operators, FP&A and finance teams, Accountants and bookkeepers, Analysts and students - Tags: balance-sheet, financial-statements, working-capital, forecasting, accounting ## Overview This model gives you a complete five-year balance sheet with an opening balance sheet column, built so the statement balances because of how it is constructed rather than because a figure was forced. Working capital runs off days-based ratios, fixed assets and debt roll forward, and cash is derived from an explicit roll-forward of every flow that moves the business. Use it to project financial position alongside a profit forecast, to plan working capital and liquidity, or to test leverage. Change the collection and payment days, the capex plan or the repayment schedule and watch cash, equity and the leverage ratios respond. ## What's included - Trading base: opening-year revenue, revenue growth, cost of sales, net margin, dividend payout - Working capital: days sales outstanding, days inventory on hand, days payable outstanding, prepaid expenses and accrued liabilities as % of revenue - Fixed assets: opening net PP&E, capital expenditure as % of revenue, depreciation rate on the opening balance - Capital structure: opening debt, scheduled repayment, share capital, opening cash - Working_Capital sheet: receivables, inventory and prepayments on days and ratios, payables and accruals, and net working capital - Fixed_Assets sheet: opening net PP&E, capex, MIN-guarded depreciation, closing net PP&E - Debt_Equity sheet: opening equity derivation, the amortising debt schedule, and the retained-earnings roll-forward - Cash_Flow sheet: opening cash, net income, depreciation, each working-capital movement, capex, debt repayment, dividends, closing cash - Balance_Sheet sheet: current assets, net fixed assets, total assets, current liabilities, long-term debt, total liabilities, equity, and a balance check that reads zero in every column - Ratios: current ratio, debt to equity and equity ratio, plus a dashboard with the headline balances, funding mix and an Assets to Equity bridge ## Balance Sheet Template: How the Five-Year Model Balances by Construction This balance sheet template builds a five-year forecast with an opening column, so the statement balances through a cash roll-forward rather than a plug. It captures working capital, fixed assets, debt and equity relationships, allowing you to see how operating drivers and funding decisions affect cash, leverage and the overall financial position. ### Operating Drivers and Assumptions The model runs on a compact trading base. Opening-year revenue compounds at a growth rate, cost of sales is a share of revenue, and net income follows a net margin. - Working capital is driven by days: receivables from days sales outstanding, inventory from days inventory on hand against cost of sales, and payables from days payable outstanding. Prepaid expenses and accrued liabilities are geared to revenue as percentages. - Fixed assets depend on opening net PP&E, capex as a percentage of revenue, and a depreciation rate on the opening balance. Funding assumptions include opening cash, opening debt, scheduled repayment, share capital and dividend payout. These inputs feed each schedule and keep the forecast internally consistent. ### Calculation Flow and Balance Mechanism The balance sheet balances by construction, not by a balancing entry. A dedicated cash roll-forward derives closing cash as opening cash plus net income plus depreciation, adjusted for movements in receivables, inventory, prepaid expenses, payables and accruals, then less capex, debt repayment and dividends. - Separately, fixed assets roll forward from opening net PP&E plus capex less depreciation, with depreciation capped so it cannot exceed the carrying value. Debt amortises through a repayment schedule, and retained earnings accumulates net income less dividends. - Because the cash roll captures every flow that moves the business, the change in assets matches the change in liabilities and equity each period, and the balance check reads zero across all columns. ### Outputs, Ratios and the Opening Column The model presents a full balance sheet: current assets including cash, receivables, inventory and prepaid expenses; net fixed assets; current liabilities such as payables and accruals; long-term debt; total liabilities; and shareholders' equity made up of share capital and retained earnings. Three ratios summarise position: current ratio, debt to equity and equity ratio. - A dashboard adds headline balances, funding mix, liquidity and an assets-to-equity bridge, plus trend charts. Uniquely, the opening column is built from explicit day-zero assumptions—opening cash, PP&E, debt and share capital—with receivables, inventory, payables and accruals computed on the same day-count formulas used in forecast years. - Opening retained earnings is solved as the residual so the opening column ties at day zero. ### Practical Use for Planning and Testing Use this model to project financial position alongside a profit forecast, to plan working capital and liquidity, or to test leverage. Because the statement balances mechanically, changing collection and payment days, the capex plan or the repayment schedule immediately shows the effect on cash, equity and the leverage ratios. - The days-based working capital approach is more realistic than percentage-of-sales because gearing inventory and payables to cost of sales avoids distortions when gross margin changes. The opening column provides a sanity-checkable starting point, and the cash roll-forward makes the source of cash transparent. - The public download is a values-only preview; the underlying model contains the live formulas and roll-forwards described here. ## It balances by construction, not by plug Cash is derived from an explicit roll-forward of net income, depreciation, every working-capital movement, capex, debt repayment and dividends. Substitute that into the change in assets and the change in liabilities and equity and the two collapse to the same expression, so total assets equal total liabilities and equity in all six columns. The balance check row is a genuine test rather than a formatting flourish. ## An opening balance sheet that ties at day zero Most templates start at Year 1 and quietly plug the difference. Here opening receivables, inventory, payables and accruals are computed from the opening year's trading on the same day-count formulas used in every forecast year, opening cash, PP&E, debt and share capital are direct inputs, and opening retained earnings are solved as the residual - the one figure that genuinely is a residual of a company's history. ## Working capital on days, not percentages Receivables run on revenue times DSO, while inventory and payables run on cost of sales times DIO and DPO. Gearing inventory and payables to revenue instead of cost of sales overstates both whenever gross margin moves, and it is the reason a percentage-of-sales working capital model drifts away from reality as the mix changes. ## Designed for one-edit responsiveness Every input - the trading base, the three working-capital day counts, the prepaid and accrued ratios, capex and the depreciation rate, the loan terms, share capital and the payout ratio - is a named-range cell. Edit one and working capital, fixed assets, funding, the cash roll-forward, the statement and the dashboard all recompute, with the balance check confirming the statement still ties. ## Workbook structure ### Cover Workbook overview, sheet legend, units, and tab-colour key. - Title and scope framing - Sheet-by-sheet purpose summary - Units and tab-colour legend ### Assumptions Every driver in one sheet: trading, working capital, fixed assets, capital structure. - Opening-year revenue, revenue growth, cost of sales, net margin, dividend payout - Days sales outstanding, days inventory on hand, days payable outstanding - Prepaid expenses and accrued liabilities as a share of revenue - Opening net PP&E, capital expenditure, depreciation rate - Opening debt, scheduled repayment, share capital, opening cash ### Working_Capital Days-driven operating assets and liabilities. - Revenue, cost of sales and net income as the trading base - Receivables equal revenue times DSO over 365 - Inventory equals cost of sales times DIO over 365 - Prepaid expenses geared to revenue, subtotalled into operating assets - Payables equal cost of sales times DPO over 365; accrued liabilities geared to revenue - Net working capital equals operating assets less operating liabilities ### Fixed_Assets PP&E roll-forward. - Opening net PP&E carries the day-zero value - Capital expenditure as a share of revenue - Depreciation at a fixed rate on the opening balance, guarded with MIN - Closing net PP&E equals opening plus capex less depreciation ### Debt_Equity Opening equity derivation, debt amortisation, and retained earnings. - Opening total assets and opening liabilities - Opening net assets, and implied opening reserves as the residual after share capital - Debt schedule: opening balance, repayment guarded with MIN, closing balance - Retained earnings: opening plus net income less dividends floored at zero - Shareholders equity as share capital plus reserves ### Cash_Flow Cash roll-forward that makes the statement balance. - Opening cash, net income and depreciation - Movements in receivables, inventory and prepayments - Movements in payables and accruals - Capital expenditure, debt repayment and dividends paid - Closing cash as the sum of the block ### Balance_Sheet Assets, liabilities, equity, and the balance check. - Cash, receivables, inventory, prepaid expenses and total current assets - Net fixed assets and total assets - Payables, accruals, total current liabilities, long-term debt and total liabilities - Share capital, retained earnings and total equity - Total liabilities and equity, and a balance check that reads zero in every column - Current ratio, debt to equity and equity ratio ### Dashboard Headline balances, trend charts, and a funding bridge. - KPI cards for total assets, cash, net fixed assets, liabilities, equity and debt - Net working capital, current ratio, debt to equity and return on equity - Balance sheet summary table across the opening column and five years - Trend charts for total assets, cash, net fixed assets and long-term debt - Funding mix of liabilities against equity - Assets to Equity funding bridge ## Features - **It balances by construction, not by plug:** Cash is derived from an explicit roll-forward of net income, depreciation, every working-capital movement, capex, debt repayment and dividends. Substitute that into the change in assets and the change in liabilities and equity and the two collapse to the same expression, so total assets equal total liabilities and equity in all six columns. The balance check row is a genuine test rather than a formatting flourish. - **An opening balance sheet that ties at day zero:** Most templates start at Year 1 and quietly plug the difference. Here opening receivables, inventory, payables and accruals are computed from the opening year's trading on the same day-count formulas used in every forecast year, opening cash, PP&E, debt and share capital are direct inputs, and opening retained earnings are solved as the residual - the one figure that genuinely is a residual of a company's history. - **Working capital on days, not percentages:** Receivables run on revenue times DSO, while inventory and payables run on cost of sales times DIO and DPO. Gearing inventory and payables to revenue instead of cost of sales overstates both whenever gross margin moves, and it is the reason a percentage-of-sales working capital model drifts away from reality as the mix changes. ## Use cases - **Forecast the balance sheet alongside a P&L:** Set the trading base, the working capital days and the capital structure, and read the projected balance sheet for each year. Because the statement balances by construction, any error in a driver shows up as an implausible balance rather than as a hidden plug. - **Working capital and liquidity planning:** Flex days sales outstanding, days inventory on hand and days payable outstanding to see how much cash is tied up in the operating cycle. The cash roll-forward isolates each movement individually, so the contribution of receivables, inventory and payables is visible line by line. - **Leverage and covenant testing:** Adjust the opening debt and the scheduled repayment and watch debt to equity, the current ratio and the equity ratio move across the horizon. The debt schedule guards the repayment with MIN so the balance cannot go negative once the loan runs down. ## Frequently asked questions ### What is a balance sheet model? A balance sheet model projects what a company owns and owes at a series of points in time - cash, receivables, inventory and fixed assets against payables, accruals, debt and equity. This template does it over five years plus an opening balance sheet, driving working capital from days-based ratios, rolling fixed assets and debt forward, and deriving cash from an explicit roll-forward so the statement balances in every period. ### Why does a balance sheet have to balance? Because every asset is funded by either a liability or equity - that is the accounting identity the statement is named after. In a model it balances only if the cash figure is derived from the same flows that move every other line. This template does exactly that, so the balance check row reads zero in all six columns without any balancing entry. ### How is cash calculated in this model? Cash is a roll-forward, not a plug. Opening cash plus net income plus depreciation, less the increase in receivables, inventory and prepayments, plus the increase in payables and accruals, less capital expenditure, debt repayment and dividends, gives closing cash. Because that is the same identity that ties the two sides of the balance sheet together, the statement balances by construction. ### Where do opening retained earnings come from? They are derived, not assumed. Opening assets less opening liabilities gives net assets, and net assets less share capital gives the reserves the company must have accumulated to arrive at that position. That makes the opening balance sheet internally consistent at day zero, and gives the user a figure they can sanity-check against the business's history. ## Related templates - [Income Statement Model](https://finamodel.com/templates/income-statement) - [3 Statement Model](https://finamodel.com/templates/3-statement-model) - [Working Capital Model](https://finamodel.com/templates/working-capital-model)