What a CMA report has to contain
CMA data is the pack an Indian bank asks for with a working capital proposal: the operating statement, the analysis of balance sheet, the comparative statement of current assets and current liabilities, the computation of maximum permissible bank finance, the fund flow and cash flow statements, DSCR and the ratio analysis, covering the audited years, the current provisional year and the projections. The bank's credit team reads the pack as one story, so the figures have to agree across every statement.
Excel is where most of this work starts, and it is a reasonable tool for the arithmetic. The formulas below are the ones that carry the analysis, followed by the places where a plain workbook runs out of road.
Setting up the workbook
The structure matters more than the formulas. A workbook that is easy to check follows four rules.
- One sheet per statement, with the same order of heads as the bank's format.
- One column per year, and the same years in the same order on every sheet, so a row reads across the pack.
- One input block for the shared figures, such as sales, opening stock and creditors, with every statement referring to it rather than repeating the number.
- A clear division between the audited years, the provisional year and the projected years, because the last of the three is driven by assumptions while the first is fixed.
Keep the assumptions on their own sheet. Growth rate, gross margin, holding period of stock and debtors, and the repayment terms of every loan are inputs, not results, and a reviewer should be able to see them in one place.
The formulas that carry the analysis
Assuming the working sheet carries current assets (CA), current liabilities (CL), total current assets (TCA), other current liabilities (OCL), net worth (NW) and the rest as named ranges, the working reads as follows.
| Ratio or working | Formula | Excel expression |
|---|---|---|
| Current ratio | Current assets / Current liabilities | =CA/CL |
| Quick ratio | (Current assets - Inventory) / Current liabilities | =(CA-Inventory)/CL |
| Debt-equity | Total outside liabilities / Net worth | =TOL/NW |
| TOL/TNW | Total outside liabilities / Tangible net worth | =TOL/TNW_ |
| Interest coverage | EBIT / Interest | =EBIT/Interest |
| DSCR | (Net profit + Depreciation + Term-loan interest) / (Principal repayment + Interest) | =(NP+Dep+TLInt)/(Principal+TLInt) |
| Gross profit margin | Gross profit / Sales | =GP/Sales |
| Net profit margin | Net profit / Sales | =NP/Sales |
| Return on capital employed | EBIT / Capital employed | =EBIT/CE |
| Stock turnover | Cost of goods sold / Average stock | =COGS/((OpenStock+CloseStock)/2) |
| MPBF, first method | 75% x (Total current assets - Other current liabilities) | =0.75*(TCA-OCL) |
| MPBF, second method | 75% x Total current assets - Other current liabilities | =0.75*TCA-OCL |
Two details decide whether the MPBF figures are right. Other current liabilities means current liabilities excluding bank borrowings, so the borrowings being assessed are left out of the deduction. And the two methods differ in where the margin falls: under the first method the borrower brings 25 percent of the working capital gap, while under the second the margin is 25 percent of total current assets. Computing both side by side shows which one binds.
For an MSME within the turnover-method limit, the Nayak Committee working is used instead: the working capital requirement is 25 percent of projected annual turnover, the borrower's margin is 5 percent of turnover, and bank finance can go up to 20 percent of turnover. That is a separate calculation from MPBF and belongs on the same sheet, so the two can be compared.
Protecting the formulas is worth the few minutes it takes. Use data validation on the input cells, lock the calculated cells, and keep a change log row for assumptions, because a figure that a reviewer questions should be traceable to the cell that produced it.
Where the spreadsheet struggles
The arithmetic is rarely the problem. Four other things are.
- Linked years. Historical years and projections must carry forward: this year's closing stock and reserves are next year's opening figures. In a workbook that link is a formula, and one broken row makes the projected balance sheet stop tallying.
- The projection side. Sales growth, holding periods and margins have to drive the operating statement, the debtors and creditors, the balance sheet and the cash flow together. Rebuilding those links for every what-if takes an afternoon.
- Loans and depreciation. A term loan needs a month-wise schedule with interest on the reducing balance, and depreciation has to follow the fixed-asset chart rather than being typed as a flat figure.
- Renewals and presentation. The following year's review needs the same pack with updated provisional figures, and the finished output has to print in the bank's format with its notes.
A workable division of work
Excel is good for exploring the numbers and for the checks the bank itself will run. A common arrangement is to use a workbook for the initial what-if working, then build the final pack in software that keeps the statements linked, so the version submitted is internally consistent. Finxora's CMA report software computes the same working from one set of inputs, including both MPBF methods, the turnover method, DSCR, 20+ financial ratios, sensitivity and break-even, and exports it in the bank's format.
The handoff is cleanest when the workbook stays a working paper rather than the deliverable. Keeping the spreadsheet costs nothing: the figures in it can be checked against the pack the software produces, and any difference is easier to explain when both versions exist side by side.
Checks before you submit
- Do the totals and the MPBF figure agree with the balance sheet they came from?
- Is the current ratio and TOL/TNW within the bank's benchmark, and if not, is the reason explained?
- Does the DSCR hold in every repayment year, including the first, when interest is highest?
- Do the comparative statement's movement figures match the change between years?
- Does the printed pack follow the format the bank shared, with its annexures in order?
Seeing the finished format
Before building anything, download a filled sample and compare it with your bank's format: the samples library carries a CMA report with the operating statement, balance sheet, MPBF computation and ratio tables as they print. The CMA plans then explain what each option covers.
