Why Convert an Excel Balance Sheet to CMA Format
A balance sheet is a snapshot of what a business owns and owes on a particular date. Excel is where most businesses keep it, because it is flexible and familiar. But a standard Excel balance sheet is built for presentation, not for the analysis a bank asks for during a loan appraisal. A CMA-ready balance sheet goes further: it reclassifies accounts, shows common-size figures, computes ratios and ties into the MPBF computation.
This guide sets out a structured way to convert an existing Excel balance sheet into a format that supports CMA analysis and bank submission, without re-keying the figures. The same approach works whether the source is a hand-built spreadsheet or an export from accounting software.
What Makes a CMA-Ready Balance Sheet Different
A CMA-formatted balance sheet usually involves three things beyond a standard presentation:
Reclassification of accounts: grouping accounts by liquidity and operational relevance, which may differ from standard accounting presentation.
Common-size analysis: expressing each line item as a percentage of total assets, to enable trend analysis and benchmarking.
Ratio calculation: deriving key financial ratios directly from the balance sheet, giving insight into solvency, liquidity and the use of funds.
For Indian bank loans, the presentation should follow Schedule III of the Companies Act, 2013 for companies, with adapted layouts for proprietorships, partnerships, LLPs, HUFs, trusts and societies, since Schedule III applies to companies.
Step 1: Understand the Standard CMA Balance Sheet Format
Before converting, know the target. A Schedule III-style balance sheet presents equity and liabilities first, then assets, each split into non-current and current, with notes cross-referenced from the face of the statement. The main groups are:
| Side of the statement | Typical heads |
|---|---|
| Shareholders' funds | Share capital, reserves and surplus |
| Non-current liabilities | Long-term borrowings, deferred tax, long-term provisions |
| Current liabilities | Short-term borrowings, trade payables, other current liabilities |
| Non-current assets | Fixed assets, non-current investments, long-term loans and advances |
| Current assets | Inventories, trade receivables, cash and bank balances, short-term advances |
The equity section changes with the constitution: a proprietor's capital account for proprietorships, partners' capital for firms, partners' contribution for LLPs, share capital with authorised, issued and paid-up detail for companies, a Karta account for HUFs, and corpus or general funds with an Income and Expenditure Account for trusts and societies.
Step 2: Map Your Excel Accounts to the Target Heads
Create a mapping table that links each line in your Excel balance sheet to a head in the target format. Use VLOOKUP or INDEX and MATCH to automate the mapping, especially if you have a consistent account numbering system. Keep the original column untouched and add the mapped head in a new column, so the source is always traceable.
Group trade receivables, other receivables and advances separately.
Split borrowings into long-term and short-term, and into bank and other.
Separate fixed assets from investments and current assets.
Identify other current liabilities clearly, because they drive the MPBF computation.
Step 3: Reclassify and Build the Statement
Using the mapping, build the balance sheet in the target format. Carry the figures by formula, not by re-keying. Check that the total assets equal total liabilities plus equity. If they do not, find the missing or misclassified line before proceeding.
For a company, follow Schedule III presentation with notes cross-referenced from the face. For other constitutions, use the adapted layouts, because Schedule III applies to companies.
Step 4: Perform Common-Size Analysis
Add a column next to each line item and compute (Line Item Value / Total Assets) x 100. This expresses every item as a percentage of total assets, allowing trend analysis across years and benchmarking against peers. Common-size analysis reveals shifts in the capital structure, such as rising receivables or growing short-term borrowings, that a rupee figure alone hides.
Step 5: Compute the Ratios
Derive the key ratios directly from the reclassified balance sheet:
Current ratio = current assets / current liabilities.
Quick ratio = (current assets - inventory) / current liabilities.
Debt-equity ratio = total debt / shareholders' funds.
TOL/TNW = total outside liabilities / net worth.
Interest coverage = operating profit / interest expense.
These ratios feed the bank's risk view and the MPBF computation. Compute them by formula so they update when the underlying figures change.
Step 6: Link to MPBF and the Rest of the CMA Pack
The CMA pack needs the balance sheet to agree with the operating statement, fund flow, cash flow, MPBF and ratios. Link the balance sheet figures into the MPBF computation, where Method I = 75% x (Total Current Assets - Other Current Liabilities) and Method II = 75% x Total Current Assets - Other Current Liabilities. Other current liabilities are current liabilities excluding bank borrowings. The turnover method places a second reading beside them: working capital requirement = 25% of projected annual turnover, borrower margin = 5% of turnover, bank finance up to 20% of turnover.
If the balance sheet changes, every dependent figure should move. Test by changing one line and watching the downstream statements.
Step 7: Review and Export
Before submission, run a short verification pass:
The balance sheet tallies.
Common-size percentages sum to 100% of total assets.
Ratios and MPBF recalculate when an input changes.
Year labels, units and currency are consistent.
Provisional and audited years are clearly marked.
Export the finished statement as a formatted PDF for submission, and keep an Excel copy for queries. The same data can feed a CMA report or a project report without re-entry.
When Software Replaces the Spreadsheet
Excel suits a single entity and a preparer comfortable with mapping and formulas. As the practice grows to many entities, annual renewals and repeated conversions, manual mapping becomes the bottleneck and the risk of a misclassified line grows. Dedicated balance sheet software turns trial-balance figures into a schedule-wise balance sheet that tallies, with Tally XML import, entity-specific formats and four layouts from the same figures. The same data can then seed a CMA report directly. You can open a sample balance sheet to see the finished format before deciding.
