Project preparation
Projected balance sheet: format, worked example and Excel template
See how next year’s sales, customer collections, equipment purchases and loan repayments become a balance sheet. Follow a worked example, then adapt the editable Excel model to your own assumptions.
Free Excel resource
A starting model you can inspect and change
The workbook includes an opening balance sheet and three forecast years, supported by editable assumptions and linked profit, working-capital, fixed-asset, debt and cash-flow schedules. Change the inputs to see the effect on the statements.
XLSX · No macros · INR lakh · Free access, no form required
Pre-filled with a fictional manufacturing example. Replace the teaching assumptions before using it for a real project.
What is a projected balance sheet?
A projected balance sheet models the assets, liabilities and equity a business expects to have at a future date. It brings together the effect of planned operations, investment and financing on the financial position.
A useful projection explains how each closing amount was reached. Customer collections affect receivables and cash. Equipment purchases affect cash and fixed assets. Profit and dividends change retained earnings. Loan drawdowns and principal repayments change borrowing.
Promoters and finance teams can use it when preparing a Detailed Project Report, evaluating an expansion or discussing funding. Learners can use the same connections to understand why a profitable business may still need cash.
Projected balance sheet format: a worked example
The example below follows a fictional manufacturer. The opening column is a teaching balance at 31 March 2026. The next three columns are hypothetical year-end projections, not actual company accounts. All figures are ₹ lakh.
| Particulars | 31 Mar 2026 (opening) | 31 Mar 2027 | 31 Mar 2028 | 31 Mar 2029 |
|---|---|---|---|---|
| Cash and bank | 10.00 | 4.83 | 13.03 | 29.75 |
| Trade receivables | 30.00 | 29.59 | 35.51 | 40.83 |
| Inventory | 24.00 | 23.67 | 28.41 | 32.67 |
| Total current assets | 64.00 | 58.09 | 76.94 | 103.25 |
| Gross property, plant and equipment | 100.00 | 120.00 | 135.00 | 150.00 |
| Less accumulated depreciation | (20.00) | (34.00) | (51.00) | (71.00) |
| Net property, plant and equipment | 80.00 | 86.00 | 84.00 | 79.00 |
| Total assets | 144.00 | 144.09 | 160.94 | 182.25 |
| Trade payables | 24.00 | 17.71 | 21.89 | 25.03 |
| Term debt (total outstanding) | 60.00 | 50.00 | 40.00 | 30.00 |
| Total liabilities | 84.00 | 67.71 | 61.89 | 55.03 |
| Share capital | 40.00 | 40.00 | 40.00 | 40.00 |
| Retained earnings | 20.00 | 36.38 | 59.05 | 87.23 |
| Total equity | 60.00 | 76.38 | 99.05 | 127.23 |
| Total liabilities and equity | 144.00 | 144.09 | 160.94 | 182.25 |
| Assets less liabilities and equity | 0.00 | 0.00 | 0.00 | 0.00 |
Displayed figures are rounded to two decimals. The workbook calculates with full precision, so a displayed subtotal may differ by ₹0.01 lakh from the sum of its rounded components.
The assumptions behind Year 1
- Sales of ₹240.00 lakh; cost of goods sold at 60% of sales and fixed cash operating costs of ₹48 lakh.
- Receivables at 45 days of sales, inventory at 60 days of cost of goods sold, and payables at 45 days of purchases, using a 365-day convention.
- Equipment additions of ₹20.00 lakh, assumed to be in use from the start of the year.
- Principal repayment of ₹10.00 lakh, with no new borrowing or equity in the base example. Interest uses an illustrative 10% rate on average debt.
- An illustrative 25% current-tax assumption on positive profit before tax, paid in the same year; dividends of ₹5.00 lakh.
Depreciation uses the opening net equipment value over an assumed eight-year remaining life and each new equipment cohort over five years, with zero residual value. These are teaching assumptions, not prescribed tax rates, lending terms or statutory depreciation policies.
Follow the connections between the statements
Year 1 produces profit after tax of ₹21.38 lakh, but closing cash is ₹4.83 lakh. The difference becomes clear when the operating, investment and funding schedules are read together.
| Cash-flow step | ₹ lakh | Basis |
|---|---|---|
| Opening cash | 10.00 | The opening balance-sheet input. |
| Cash from operations | 29.83 | Profit, non-cash depreciation and changes in receivables, inventory and payables. Interest and current tax are paid in-year in this example. |
| Equipment purchases | (20.00) | Cash paid for new fixed assets. |
| Loan principal repaid | (10.00) | Reduces borrowing; it is not a profit-and-loss expense. |
| Dividends paid | (5.00) | Reduces retained earnings and cash. |
| Closing cash | 4.83 | Links directly to the balance sheet. |
- Retained earnings: opening retained earnings + profit after tax − dividends.
- Net fixed assets: opening net fixed assets + capital additions − depreciation, with disposals handled separately if relevant.
- Debt principal: opening principal + new borrowing − principal repayments.
- Closing cash: opening cash + operating, investing and financing cash flows.
The spreadsheet derives cash from cash flow. It does not insert an unexplained loan or equity amount to make the balance sheet balance. If changed assumptions produce insufficient cash, resolve the funding need explicitly.
The base example sets a minimum year-end cash target of ₹5 lakh. Year 1’s positive cash balance of ₹4.83 lakh still leaves a ₹0.17 lakh gap against that target. This is a period-end funding gap before any cost of additional financing, not the peak cash requirement during the year.
How to prepare a projected balance sheet in Excel
- Set the dates and units. Use one reporting period and currency scale across the statements. ₹1 lakh equals ₹100,000.
- Establish the opening position. Reconcile cash, receivables, inventory, fixed assets, creditors, debt and equity to the available accounts. Record whether those accounts are audited, final or provisional.
- Support operating assumptions. Build sales, costs and collection terms from capacity, contracts, demand evidence and the operating plan.
- Prepare supporting schedules. Forecast working capital, equipment and depreciation, borrowing, repayment, tax and distributions. Record the basis of each assumption.
- Connect profit and cash flow. Carry profit into retained earnings and closing cash into the balance sheet. Treat principal repayments separately from interest expense.
- Review changed assumptions. Try slower collections or lower sales, then inspect cash, debt and the reconciliation checks. Agree any funding response before treating it as available finance.
Use the downloaded workbook
Save a working copy. Read the scope notes, replace the clearly marked input cells and retain the formulas in the linked schedules. A zero is a valid assumption where appropriate; an unknown required input should be investigated rather than silently treated as zero. Read the model’s warnings alongside the balance-sheet check.
The example uses annual periods. For a project with commissioning delays, seasonal stock or uneven receipts and payments, add a monthly cash-flow assessment. A positive year-end cash balance does not prove that every payment can be made during the year.
Projected, estimated and provisional balance sheets
| Term | Typical purpose | What to state clearly |
|---|---|---|
| Projected | An assumption-based view of financial position at a future date. | Forecast dates, assumptions, funding basis and scenario. |
| Estimated | In lending schedules, often the expected current-year closing position, combining actual information so far with estimates for the remaining period. | Actual-data cut-off and the part of the period being estimated. |
| Provisional | A preliminary statement for a date already reached, prepared before closing adjustments or finalisation are complete. | Statement date, preparation status and outstanding adjustments. |
Terminology can vary by recipient. For example, Federal Bank’s SME application distinguishes latest-date provisional statements, current-year estimates and ensuing-year projections. Confirm the format and horizon required by your intended lender.
Checks before you share the projection
- Opening figures reconcile. Assets equal liabilities plus equity before the forecast begins.
- Every closing balance has an explanation. Debt and assets roll forward from documented opening balances and planned transactions.
- Profit and retained earnings agree. Distributions, accumulated losses and other equity changes are included where applicable.
- Cash agrees with cash flow. A balance-sheet reconciliation can pass while the model still has a funding shortfall.
- Assumptions reflect the project. Credit periods, stock levels, commissioning dates, funding and repayment terms have an identifiable basis.
- Presentation meets the purpose. Reclassify borrowing between current and non-current portions and add the accounting disclosures required for your actual submission.
For the wider report, use the annotated DPR sample and the industry input checklists.
Common questions
Is this projected balance sheet template free?
Yes. Download the XLSX file without submitting an enquiry. It contains editable example assumptions and linked formulas, with no macros.
Can I use it for a bank loan application?
Use it as a starting point for understanding and preparing linked projections. An actual application needs your records, justified assumptions and the recipient’s required format, projection horizon and professional review. The sample is not bank-approved or certified.
How many years should I project?
This teaching workbook covers three years after the opening position. Your lender or decision-maker may require a longer horizon, including the full loan period. Confirm the requirement before developing the final model.
Why does my balance sheet balance but cash is insufficient?
The accounting equation checks consistency. It does not establish that the proposed funding is enough or that cash is available at the right time. Investigate the cash-flow schedule and identify any additional funding or operating changes explicitly.
Is this the same example as the DPR sample?
No. This resource uses a separate fictional manufacturer to demonstrate a projected balance sheet. The DPR PDF teaches the structure of a full project report. Do not combine their figures as if they described one project.
Sources and model scope
The workbook and worked figures are original, fictional teaching material. The following references explain the terminology and statement relationships:
- Federal Bank SME application: an example of a lender distinguishing provisional accounts, estimates and projections.
- CFI: how the three financial statements are linked: profit, retained earnings, cash flow and supporting schedules.
The example treats cost of goods sold as credit-purchased production supplies. Inventory is valued on that cost only, without allocating fixed operating costs or depreciation; it is not a full manufacturing inventory-cost model.
This simplified model excludes GST, deferred tax, asset disposals, leases and within-year seasonality. Its tax and depreciation inputs are illustrative. It is a planning example, not a complete statutory financial-statement format or an assurance report.
Need projections for your project?
Bitcon’s DPR preparation work connects the technical plan, project costs, funding and operating assumptions. Share the sector, location, stage and intended recipient to discuss the required scope.