Start with a defined purpose and timeline
A five-year financial projection should explain how a business expects to earn, invest, fund operations and meet commitments. For an Indian bank-loan proposal, the workbook must connect the business plan with projected profit, assets, liabilities, cash and debt service. A sales forecast alone cannot show whether the borrower will have enough money for instalments or additional inventory.
Define the financial years and the operating start date before entering formulas. A unit starting midway through the first year should not automatically show twelve months of production. Decide whether the model requires monthly detail for the initial period and annual summaries thereafter. Keep the period labels identical across every worksheet and exported report.
Arrange the workbook around inputs and schedules
Create a cover or instructions sheet that states the purpose, units, version date and preparer. Use a separate assumptions sheet for operating and funding inputs. Keep revenue, expenses, working capital, assets and borrowings in supporting schedules, then link them to the projected statements. This structure makes the workbook easier to inspect and revise.
Distinguish input cells from formulas through a consistent convention and a written legend. Avoid scattered hard-coded growth rates because they are difficult to locate during review. Use clear row descriptions and keep units visible. If amounts are in lakh, a loan entered in rupees can produce a convincing-looking but materially incorrect forecast.
Reconcile opening balances
For an existing business, use the latest reliable balance sheet as the opening position. Reconcile bank balances, receivables, inventory, payables, fixed assets and borrowings with supporting records. Label provisional figures and retain the reconciliation. Five years of linked formulas cannot correct an unexplained opening difference.
For a new project, separate the position before investment from the planned funding and spending. Promoter contribution and loan drawdowns should enter cash when expected, while machinery and other project payments should leave cash when payable. Do not show a completed asset and an undrawn loan without explaining how the purchase is initially funded.
Build revenue from operating assumptions
Use a volume-and-price approach where it suits the business. For manufacturing, annual revenue can begin with installed capacity multiplied by utilisation, saleable yield and selling price. For services, use a relevant driver such as clients, assignments, billable hours or seats. Separate price growth from volume growth so the reviewer can understand what creates the increase.
Check the first year against commissioning and sales timing. Include seasonality where material. A business with a festival sales peak may need inventory and cash months before that revenue appears. For later years, apply growth assumptions that remain consistent with capacity, market evidence and any planned expansion expenditure.
Project costs according to their behaviour
Link material and other variable costs to the appropriate activity driver. Forecast fixed costs separately, including rent, staff and administration. Some costs rise in steps: another shift may require additional supervision, or a second premises may change rent. Explain these events in the assumptions rather than burying them inside a uniform expense growth percentage.
Keep depreciation and financing costs in their own schedules. Review the applicable tax basis with the adviser rather than applying an old tax rate automatically. State whether a tax provision is a planning estimate. Keep profit before tax, tax and profit after tax as separate rows so that changes remain visible.
Calculate working capital consistently
Inventory, receivables and payables connect operating activity to funding needs. Choose and document the basis for each holding-period calculation. Debtors generally relate to credit sales, while raw material inventory relates to consumption and supplier balances relate to relevant purchases. Do not apply one sales-based formula to every current asset and liability.
For a simplified example, annual credit sales of Rs 365 lakh on a 365-day basis equal Rs 1 lakh per day. A 30-day receivable assumption indicates Rs 30 lakh. If the next year's credit sales rise to Rs 438 lakh with the same assumption, receivables become Rs 36 lakh. The additional Rs 6 lakh absorbs cash; it is not an extra profit expense.
Link fixed assets and loan schedules
Track opening fixed assets, additions, disposals, depreciation and closing carrying value. Align depreciation assumptions with the intended reporting basis and identify any separate tax working. Asset additions should match project-cost and cash-payment schedules, allowing for timing differences that are explicitly explained.
For each loan, record drawdowns, opening principal, interest, repayments and closing principal. Use the proposed rate, frequency, tenure and moratorium terms. Check that principal repayments sum to the financed amount over the complete schedule. If repayment continues beyond five years, retain the full debt schedule even when the main statements show only five projected years.
Connect the three statements
The profit and loss account calculates the period's result. The balance sheet carries the closing financial position. Cash flow explains how opening cash becomes closing cash. Retained profits should reflect profit after tax and distributions or drawings as applicable. Loan principal reduces cash and debt, while interest affects profit and cash under the chosen timing assumptions.
- Closing assets must equal liabilities plus equity.
- Closing cash must agree with the cash flow.
- Loan balances must agree with repayment schedules.
- Fixed assets must agree with the asset schedule.
- Profit movements must reconcile with reserves or capital.
- Project cost must reconcile with the planned means of finance.
A cash shortfall should remain visible as a funding gap until a credible funding source is modelled. Do not automatically label negative cash as bank borrowing when no facility has been arranged.
Add ratios and scenarios
Review current ratio, leverage, margins, interest coverage and debt service using clearly stated definitions. For a bank DPR, confirm the lender's DSCR convention and required periods. Avoid presenting an unweighted average of yearly ratios as interchangeable with a ratio of aggregate receipts to repayments; the chosen approach should be labelled.
Build a downside scenario with lower sales, slower collections or delayed commissioning. Keep the assumptions together so the base case can be restored without manually reversing many cells. Review both the effect on annual profit and the lowest cash position. A business may remain profitable while requiring additional short-term funding.
For the related preparation guidance, see three-statement financial model.
Review and present the workbook
Check formulas across all year columns, inspect totals and remove external links that the recipient cannot access. Keep a clean assumptions note and mark estimated information honestly. Print or export the statements and confirm that headings, units and year columns remain readable. Preserve the approved version before updating the forecast.
For a bank-loan reporting workflow, Finxora's project report software prepares linked projected statements, repayment schedules, DSCR and other report sections, with Excel export available. Compare the structure with the sample reports. Whether you use Excel directly or reporting software, the underlying assumptions and reconciliations still require professional review.
