Finxora Logo
Sign In
5-Year CMA Data Excel Sheet for a Cash Credit Limit
Back to Blog

5-Year CMA Data Excel Sheet for a Cash Credit Limit

FINXORA
6 min read
cma
excel
cash credit
financial analysis
data analysis

Prepare a five-year CMA workbook for a cash credit proposal with clear year labels, linked statements, working capital calculations and review checks.

What a five-year CMA workbook should show

A five-year CMA data Excel sheet presents a business's financial position and working capital requirement across five reporting columns. CMA means Credit Monitoring Arrangement. For a cash credit proposal, the workbook should explain the relationship between sales, inventory, customer collections, supplier credit and the borrowing requested. A lender needs a financial argument supported by records, rather than five columns of growth percentages.

Five years does not necessarily mean five historical years. The mix can include audited years, the current year's estimate and future projections. Ask the bank to confirm the periods it requires for the particular renewal, enhancement or new limit. State the status of every column clearly. A provisional estimate must never appear to be an audited result.

Choose the years before building formulas

An illustrative five-column arrangement is two completed financial years, one current estimate and two projected years. This is a planning example, not a universal bank prescription. Another borrower may need a different mix because its last audit is pending, its business is new or the lender wants a longer projection horizon.

Use actual financial year labels throughout the workbook. Record the source date of provisional accounts and explain how the remaining months have been estimated. If actual sales cover nine months, seasonal demand may make a straight twelve-month extrapolation misleading. Separate actual performance from the forecast for the balance of the year.

  • Identify the financial year and its audited, provisional or projected status.
  • Use one unit, such as rupees or lakh rupees, across all schedules.
  • Keep a source reference for each historical total.
  • Record assumptions in a separate input area.
  • Confirm that opening balances agree with the preceding year's closing figures.

Organise the workbook around connected statements

Start with borrower particulars and existing credit facilities, including sanctioned amounts, utilisation, interest assumptions and repayment obligations. Follow with the operating statement, balance sheet analysis, current asset and current liability comparison, working capital assessment, fund flow and ratio analysis. Include a cash flow forecast where the lender needs visibility over collections and payments.

WorksheetPurposeReview point
Inputs and assumptionsSources and projection driversEditable cells are identifiable
Operating statementSales, costs, interest and profitMargins match operating assumptions
Balance sheetAssets, liabilities and capitalBoth sides reconcile each year
Working capitalOperating assets and supplier fundingBank borrowing is separately classified
Assessment and ratiosFinance requirement and financial measuresMethods and definitions are disclosed

Keep formulas linked to their underlying schedules. Retyping profit into the balance sheet creates a second figure that can become stale. The same concern applies to depreciation, interest and loan balances. A report with individually plausible sheets can still be unreliable if the sheets do not reconcile with one another.

Build projections from the operating cycle

Forecast sales using order visibility, production capacity, customer demand and realistic selling prices. Estimate material consumption or purchase costs consistently with those sales. Separate fixed expenses from costs that move with volume. Explain major changes in gross margin instead of assuming that every extra sale earns the historical average margin.

Translate stock and receivable assumptions into year-end balances. If inventory holding is calculated from cost of sales, label that denominator. Receivables normally relate to credit sales, while trade creditors relate to credit purchases. Mixing annual turnover with purchases can distort the cycle. State whether the calculation uses 365 days or another documented convention and apply it consistently.

Consider the peak requirement separately from year-end balances. A seasonal trader can have low March inventory but substantial borrowing needs before a festival season. Prepare a monthly supporting schedule if annual closing figures conceal that peak. Document how the proposed limit serves the operating cycle rather than long-term expenditure.

Present the working capital assessment correctly

Other current liabilities in the MPBF calculation mean current liabilities excluding bank borrowings. Define eligible current assets and the bank's accepted classifications before applying a formula. The Tandon Committee methods are assessment approaches; the lender decides the method applicable to the proposal.

MPBF Method I = 75% x (Total Current Assets - Other Current Liabilities). The borrower contributes 25% of the working capital gap under this method. Method II = 75% x Total Current Assets - Other Current Liabilities. Here the borrower margin is 25% of total current assets. These two margins are different and should not be described interchangeably.

For an illustration in lakh rupees, current assets of 100 and other current liabilities of 20 produce a working capital gap of 80. Method I produces 60, while Method II produces 55. These arithmetic results are not sanctioned limits. Eligibility, security, existing exposure and the lender's appraisal can change the amount actually offered.

Under the turnover method associated with the Nayak Committee approach for MSMEs, working capital requirement is 25% of projected annual turnover, borrower margin is 5% of turnover and bank finance is up to 20% of turnover. Confirm applicability with the bank. Do not combine the turnover-method margin with an MPBF formula.

Distinguish assessment from drawing power

A proposed limit, a sanctioned limit and drawing power serve different purposes. The assessment supports the credit request. Sanction sets the facility terms. Drawing power is calculated under those terms from eligible assets and applicable margins, often using periodic stock and receivable statements. A projected CMA number does not establish the amount that can be drawn on a particular date.

Review the current ratio, leverage, profitability and interest coverage alongside the assessment. Explain deterioration and identify its cause. A ratio should not be improved by moving liabilities into another category without a valid basis. Where term debt is material, reconcile repayment and cash generation rather than relying on operating profit alone.

Check the Excel file before submission

  1. Reconcile historical sales and balances to the accounts and explain material differences from supporting returns.
  2. Check that each balance sheet balances without an unexplained adjustment.
  3. Test whether changing sales updates inventory, receivables, interest and cash requirements coherently.
  4. Review signs, units, percentages, broken references and hidden hard-coded totals.
  5. Print or export every schedule to check year headings and readable figures.
  6. Attach an assumptions note and the lender's requested supporting documents.

For an alternative to maintaining interlinked formulas manually, Finxora's CMA report software produces operating statements, balance sheet analysis, MPBF, fund flow and ratio analysis, with PDF, Excel and Word exports. Review the sample reports against the bank's checklist before choosing a preparation format. The final responsibility remains to verify source figures, assumptions and lender-specific requirements.

Frequently Asked Questions

Published on February 21, 2026

Updated on July 06, 2026

Back to Blog

Need bank-ready financials? Create a CMA report or a project report with Finxora in minutes.