This is where the bank decides how much money to lend. In Excel, this is usually a summary table using one of three methods (Method 1, 2, or 3/Tandon Committee norms).
Excel Calculation Flow:
Analysis Tip: If the "Requested Limit" in Part I is higher than the "MPBF" calculated here, the loan request is technically inflated, and the borrower needs to bring in more equity.
The fluorescent lights of the accounting firm hummed a low, mocking tune as Arjun stared at the blank spreadsheet on his screen. It was 11:00 PM, and the "Credit Monitoring Arrangement" (CMA) report—the lifeblood of his client’s massive working capital loan—was due by dawn. cma data report in excel format
To the uninitiated, a CMA data report in Excel is just a grid of numbers. To Arjun, it was a high-stakes puzzle where every piece had to fit perfectly across seven mandatory layers:
The Foundation (Form I & II): He started by inputting the particulars of the existing and proposed fund-based limits. This wasn't just data entry; it was the narrative of the company’s growth.
The History (Form III): He pulled the audited balance sheets from the last two years. The Excel cells glowed as he reconciled the assets against the liabilities. This is where the bank decides how much money to lend
The Crystal Ball (Form IV): This was the "Comparative Statement of Current Assets and Current Liabilities." Arjun had to project next year’s performance. One overly optimistic cell for "Sundry Debtors" could trigger a red flag at the bank.
The Engine (Form V): The Maximum Permissible Bank Finance (MPBF). This was the heart of the report. Arjun’s fingers flew over the keyboard, setting up formulas to ensure the "Current Ratio" stayed comfortably above 1.33. If that number dipped, the loan was dead.
The Flow (Form VI & VII): Finally, he built the Fund Flow Statement and the analytical ratios. Analysis Tip: If the "Requested Limit" in Part
At 2:00 AM, the Excel sheet finally "balanced." The cells turned a soft, automated green, signaling that the projected turnover matched the working capital cycle.
He didn't just see a report; he saw a business saved. He hit Save, the file extension .xlsx gleaming like a trophy, and finally turned off the lights.
I can give you step-by-step instructions to create this report in Excel, including: