Finxora Logo
Sign In
How to Prepare CMA Report Data in Excel with Formulas
Back to Blog

How to Prepare CMA Report Data in Excel with Formulas

FINXORA
5 min read
CMA report
Excel
bank loans
financial analysis
data analysis

How to prepare CMA report data in Excel with advance formulas for ratios, MPBF and DSCR, and where linked statements beat a plain spreadsheet.

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.

  1. One sheet per statement, with the same order of heads as the bank's format.
  2. One column per year, and the same years in the same order on every sheet, so a row reads across the pack.
  3. 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.
  4. 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 workingFormulaExcel expression
Current ratioCurrent assets / Current liabilities=CA/CL
Quick ratio(Current assets - Inventory) / Current liabilities=(CA-Inventory)/CL
Debt-equityTotal outside liabilities / Net worth=TOL/NW
TOL/TNWTotal outside liabilities / Tangible net worth=TOL/TNW_
Interest coverageEBIT / Interest=EBIT/Interest
DSCR(Net profit + Depreciation + Term-loan interest) / (Principal repayment + Interest)=(NP+Dep+TLInt)/(Principal+TLInt)
Gross profit marginGross profit / Sales=GP/Sales
Net profit marginNet profit / Sales=NP/Sales
Return on capital employedEBIT / Capital employed=EBIT/CE
Stock turnoverCost of goods sold / Average stock=COGS/((OpenStock+CloseStock)/2)
MPBF, first method75% x (Total current assets - Other current liabilities)=0.75*(TCA-OCL)
MPBF, second method75% 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.

Frequently Asked Questions

Published on February 13, 2026

Updated on February 21, 2026

Back to Blog

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