What a CMA Report Is and Why Excel Still Helps
CMA stands for Credit Monitoring Arrangement: the set of financial statements Indian banks ask for before sanctioning working capital finance and during the annual review of existing limits. It presents the borrower as a going concern, combining audited history, the current year's provisional figures and projections in a fixed format of interlinked statements.
Most of the pack can be built in Microsoft Excel. Excel gives full control over layout, formulas and cross-checks, which suits firms that already keep their books in spreadsheets. The trade-off is that every total, ratio and MPBF figure must be linked by hand, and a single broken formula can make the whole pack inconsistent. This guide walks through the build in the order a credit team reads it, so the figures agree across statements.
Step 1: Gather the Source Documents
Before opening a workbook, collect the evidence behind each number. Banks cross-check CMA data against supporting documents, so gaps here surface later as queries.
Balance sheets for the last three years, preferably audited, plus the current year's provisional balance sheet.
Profit and loss statements for the same period, with depreciation and interest split out.
Bank statements and stock statements for the current year, to support the provisional figures.
Income tax returns and GST returns for the last three years.
Existing loan details: sanction letters, EMI schedules, outstanding balances and security.
Receivables and payables ageing, inventory levels and the working capital cycle.
Keep everything in one folder with consistent file names. Note the accounting basis (cash or accrual) and the unit (rupees, thousands or lakhs) so that later sheets use the same scale.
Step 2: Design the Workbook Structure
A clean workbook mirrors the standard CMA format. Create one sheet per statement, in this order, so that links flow downward:
Inputs and assumptions (sales growth, margins, working capital days, tax rate).
Operating statement (sales, direct and indirect costs, interest, tax, net profit).
Analysis of balance sheet (capital, reserves, borrowings, current liabilities and assets).
Comparative statement of current assets and current liabilities, with changes.
MPBF computation under the first and second methods.
Fund flow statement (sources and uses of long-term funds).
Cash flow statement and DSCR analysis.
Turnover method computation.
Ratio analysis (liquidity, profitability, leverage, activity, expense and growth).
Depreciation schedule and EMI schedule.
Name the sheets clearly and freeze the header rows. Use a single inputs sheet for assumptions so that changing one figure updates every downstream statement.
Step 3: Enter Historical and Provisional Figures
Key in the audited and provisional figures for each year, column by column. Three checks prevent most errors:
Keep audited and provisional years in separate, clearly labelled columns. Do not mix them in one column.
Reconcile each year's closing balance sheet to the previous year's opening, so depreciation, reserves and loan balances carry forward correctly.
Match the operating statement's net profit to the change in reserves on the balance sheet, after accounting for drawings and dividends.
If the books already live in Tally, you can avoid re-keying by exporting the trial balance as XML and mapping it into the sheets. Many preparers instead use dedicated balance sheet software to produce the audited statements first and then carry those figures into the CMA workbook.
Step 4: Build the Projections
Projections are where a CMA report earns or loses the banker's trust. Build them from drivers, not from a single growth percentage typed on top of last year's sales.
Sales: start from historical growth, then adjust for capacity, market size and pricing. Document the basis.
Cost of sales: link to sales through a margin assumption, and separate fixed from variable costs.
Operating expenses: link to sales or to a fixed base, and flag any step change such as a new unit or added staff.
Working capital: derive receivables, inventory and payables from days outstanding, so the balance sheet follows the operating statement.
Interest: compute from the closing borrowings of the previous year and the assumed rate.
Tax: apply the assumed rate to profit before tax, and disclose the regime.
Keep every assumption on the inputs sheet with a short note. A banker reading the file should be able to trace any projected figure back to an assumption in two clicks.
Step 5: Compute MPBF (Maximum Permissible Bank Finance)
MPBF decides the working capital limit. Compute both methods from the same balance sheet so the assessed limit can be defended.
Method I = 75% x (Total Current Assets - Other Current Liabilities).
Method II = 75% x Total Current Assets - Other Current Liabilities.
Other current liabilities = current liabilities excluding bank borrowings.
Place the turnover-method requirement (Nayak Committee, MSME) beside them: working capital requirement = 25% of projected annual turnover, borrower margin = 5% of turnover, bank finance up to 20% of turnover. Show excess borrowing and margins on working capital. Because all three readings share the same inputs, you can see at a glance which method binds.
Step 6: Run Ratio Analysis
Ratios turn the statements into a risk view. Compute them from the operating statement and balance sheet, never by typing over them. A useful set covers:
Liquidity: current ratio and quick ratio.
Profitability: gross profit ratio, net profit ratio and return on capital employed.
Leverage: debt-equity ratio, total outside liabilities to net worth, and interest coverage.
Activity: stock turnover, receivables turnover and working capital turnover.
Debt servicing: DSCR for each projected year.
Measure each ratio against bank benchmarks and flag any year that falls outside the acceptable band. A CMA report prepared in Finxora's CMA report software computes 20+ financial ratios from the same inputs and recalculates them the moment an assumption changes.
Step 7: Prepare Fund Flow and Cash Flow Statements
Fund flow tracks changes in working capital (current assets minus current liabilities) and shows where long-term funds came from and went. Cash flow tracks the movement of cash itself, split into operating, investing and financing activities. Both are needed: fund flow explains the structure of the business over time, while cash flow explains whether the bank's money can be serviced in cash.
Build fund flow from two consecutive balance sheets, and cash flow from the operating statement and balance sheet changes. Keep the two statements separate; do not merge their lines.
Step 8: Review, Cross-Check and Export
Before submission, run a short verification pass:
Every balance sheet tallies (assets equal liabilities plus equity).
MPBF, ratios and DSCR recalculate when an input changes, with no hard-typed overrides.
Year labels, units and currency are consistent across all sheets.
Provisional figures are marked as provisional and audited figures as audited.
The projected years follow logically from the historical trend, with any step change explained.
Export the final pack as a formatted PDF for submission, and keep an Excel copy for the bank's queries. An Excel workbook is also easier to revise when the relationship manager asks for a different scenario.
When Excel Is Enough and When It Is Not
Excel suits a single borrower, a simple capital structure and a preparer who is comfortable with formulas. It becomes slow when you manage many borrowers, repeat annual renewals, or need the operating statement, balance sheet, fund flow, MPBF and ratios to stay consistent across versions. In those cases, software that links the statements and recalculates totals on every change saves time and reduces the risk of a mismatched figure. You can open a sample CMA report to see the finished format before deciding, and use the tools hub for supporting calculators.
Common Mistakes to Avoid
Typing ratios and MPBF instead of computing them from the statements.
Mixing audited and provisional figures in one column.
Projecting sales with a single percentage and no driver behind it.
Ignoring the working capital cycle when building the balance sheet.
Forgetting to reconcile the operating statement's profit to the balance sheet's reserves.
