Finxora Logo
Sign In
How to Prepare CMA Report in Excel for a CC Limit
Back to Blog

How to Prepare CMA Report in Excel for a CC Limit

FINXORA
7 min read
cma
excel
cash credit
cc limit
financial analysis

A practical guide to preparing a CMA report in Excel for a cash credit limit: the statements banks want, the MPBF method and the linking errors to avoid.

How to prepare a CMA report in Excel for a CC limit

A cash credit (CC) limit is the working capital facility a bank grants against a borrower's current assets, and the bank fixes its size from the borrower's working capital position rather than from a fixed repayment schedule. Before sanctioning or renewing a CC or overdraft limit, the bank asks for CMA data: the Credit Monitoring Arrangement pack that sets out the operating position, the balance sheet, the working capital gap and the maximum finance the bank can provide. Excel is usually the first tool finance teams reach for, and it can hold a CMA report when the workbook is built in the right order. This guide covers the statements the pack needs for a CC limit, how to build each one in Excel, and where a spreadsheet begins to cost more time than it saves.

What a CC limit needs from the CMA report

A CC limit is a running account, so the bank looks less at a repayment schedule and more at the operating cycle: how long stock and debtors hold funds, how much the business leans on creditors, and how much of the working capital gap the borrower funds from long-term sources and own margin. CMA data answers those questions through a fixed set of statements, normally prepared for the last two audited years, the current year's provisional figures and two to five projected years.

  • Operating statement - sales, direct and indirect costs, interest, tax and net profit for each year.
  • Analysis of balance sheet - capital, reserves, borrowings, current liabilities and current assets, year by year.
  • Comparative statement of current assets and current liabilities - the movement in each working capital item, with the ratios behind it.
  • MPBF computation - the maximum permissible bank finance under the two Tandon Committee methods.
  • Fund flow and cash flow statements, ratio analysis and DSCR for the projected years.

Banks differ in how much history and how many projected years they ask for, so confirm the year span and any annexure with the credit team before you start filling cells.

Step 1: collect the inputs

  • Audited balance sheets and profit and loss accounts for the last two or three years, with the schedules behind them.
  • Provisional figures for the current year, drawn from the trial balance or management accounts.
  • Working capital detail: stock, debtors, creditors and other current items, with the holding levels for each.
  • Existing loan details - limits, outstanding amounts, interest rates and repayment terms, keeping bank borrowings separate from other current liabilities.
  • Projection assumptions for sales growth, margins, the operating cycle and capital expenditure.

Step 2: build the operating statement

Lay out one column per year and enter sales, the direct cost lines, gross profit, indirect expenses, interest, depreciation, tax and net profit. Keep every line as a formula off the year before it and off the assumptions beside it, so a change in sales growth moves the whole column. Do not type totals as values; sum the lines above, or a later edit will leave a total that no longer agrees with its own column.

Step 3: build the balance sheet and the working capital schedule

Enter the historical years from the audited accounts and let the projected years derive from the operating statement and the assumptions. The projected balance sheet is where most Excel CMA files come apart, because reserves must move by the year's net profit, fixed assets must fall by depreciation and rise by capital expenditure, and borrowings must follow the drawing and repayment the projections assume. Add a working capital schedule that lists total current assets, current liabilities excluding bank borrowings, and the working capital gap between them. Compute the holding levels - the days or months of stock and debtors - from those same figures and show the basis, since the operating cycle is what the bank reads the limit against.

Step 4: compute MPBF under Method I and Method II

The Tandon Committee gave two methods, and banks commonly work both and sanction the lower figure.

  • Method I - MPBF = 75% x (Total Current Assets - Other Current Liabilities). The borrower's margin is 25% of the working capital gap.
  • Method II - MPBF = 75% x Total Current Assets - Other Current Liabilities. Here the borrower's margin is 25% of total current assets, so this method gives the lower limit.

Other current liabilities means current liabilities excluding bank borrowings. Take total current assets of ₹100 lakh and other current liabilities of ₹20 lakh. Method I allows 75% x (100 - 20) = ₹60 lakh. Method II allows (75% x 100) - 20 = ₹55 lakh. Method II binds, and the margin the borrower brings in is the gap between the working capital requirement and the finance so allowed.

MSME borrowers assessed under the Nayak Committee turnover method are looked at differently: the working capital requirement is 25% of projected annual turnover, the borrower's margin is 5% of turnover, and the bank's share is up to 20% of turnover. Keep that working beside the MPBF sheet if your bank uses the turnover method for small limits.

Step 5: add the fund flow, cash flow, ratios and DSCR

The fund flow statement shows the sources and uses of long-term funds; the cash flow statement shows the movement in cash across operating, investing and financing activity. Add the ratio block - current ratio, quick ratio, debt-equity, TOL/TNW, interest coverage, stock and debtor turnover, net profit ratio - and compute DSCR for each projected year, because a CC review with a term loan attached will be examined for debt service comfort.

Match the format the bank asked for

Most banks share their own CMA template or name the format in the sanction letter. Copy the statement order, the year columns and the ratio table from that template rather than inventing a layout, because a credit officer reads many files in the same shape and expects the same headings. If the bank has not shared a template, work from a filled sample CMA report so the statements you submit look like the ones the credit team already works with.

Mistakes that send an Excel CMA file back

  • Totals typed as values, so they stop matching the lines above them.
  • Year columns filled independently, so the projected balance sheet no longer tallies.
  • Bank borrowings mixed into other current liabilities, which changes the MPBF figure.
  • Projections that grow sales without the stock, debtors and creditors those sales require.
  • Ratio and MPBF pages calculated once by hand and not refreshed after the assumptions changed.

When Excel stops being the cheaper option

Excel is fine for a single file prepared once. It becomes expensive when you prepare several borrowers every month, when the same client returns at renewal, or when the credit team asks for a ratio or MPBF figure to be reworked after a query. Rebuilding the links each time is where the hours go. Finxora's CMA report software keeps the statements linked from one set of inputs, works the MPBF computation under both methods and the turnover method, and carries the borrower's master forward to the next renewal. You can open a filled report in the samples library and compare it with your bank's format before starting, and see what a CMA plan covers on the CMA pricing page.

Whether you build the file in Excel or in software, the test is the same: the balance sheet must tally, the operating statement must agree with it, and the MPBF working must follow from the same current assets and current liabilities that appear in the audited accounts.

Frequently Asked Questions

Published on February 24, 2026

Updated on July 20, 2026

Back to Blog

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