Classified Balance Sheet Excel Layout, Current to Long-Term

A classified balance sheet in Excel adds one column to the trial balance mapping tab: a class for every account, such as current asset or long-term liability. SUMIFS then adds each line by line name and class, subtotals fall out by class, and one reclassification row moves the next 12 months of loan principal into current liabilities.

The example in this post is a fictional company, Brightwater Tile Supply Inc., at December 31, 2026. Every name, account number and amount is made up for the example. The arithmetic is real: the trial balance nets to zero, the reclassification nets to zero, and the finished sheet balances to the dollar.

What makes a balance sheet classified?

A classified balance sheet groups assets and liabilities by when they turn into cash or come due. It shows five sections: current assets, long-term assets, current liabilities, long-term liabilities and equity, each with its own subtotal. An unclassified sheet lists the same accounts in one run, with no split between the near term and the long term.

The split answers the question a lender or a bonding agent asks first: can this business pay what it owes over the next year from what it will collect over the next year? Total current assets against total current liabilities gives that answer in two numbers. A single total of liabilities does not, because a five-year equipment note and next month's supplier bills look the same in it.

Federal tax forms draw the same line for debt. The corporate balance sheet on Form 1120-S, Schedule L has one line for "Mortgages, notes, bonds payable in less than 1 year" and a separate line for those payable "in 1 year or more". A firm that already prepares that schedule has made the current and long-term call for notes; the classified sheet makes it for every account.

The classification lives in the workbook, not in the client's chart of accounts. Clients rarely number their accounts so that current and long-term fall into clean ranges, and a customer deposit account can sit anywhere. A class column on the mapping tab lets the firm decide once per account and reuse the decision next year.

Classified balance sheet sections and the accounts that belong in each

Classified balance sheet sections hold accounts by expected timing. Current assets are cash and items expected to become cash or be used up within a year: receivables, inventory and prepaid expenses. Long-term assets are fixed assets net of depreciation and deposits held for more than a year. Liabilities split the same way, and equity stays one section.

Here is how Brightwater's accounts fall into sections:

Three placements cause most of the questions. Prepaid expenses are current because they will be used up within the year, even though they will never become cash. A security deposit on a lease is long-term when the lease runs past the next year, because the money comes back only at the end. Accumulated depreciation sits in long-term assets as a negative amount beside the equipment it reduces.

The class column on the mapping tab

The mapping tab already assigns each account to a balance sheet line. The post on summing trial balance accounts into balance sheet lines with SUMIFS builds that tab and its balance pull. For a classified sheet, insert a class column between the line and the balance, so the tab reads account, line, class, balance.

Limit the class column to five exact values with a drop-down list: Current asset, Long-term asset, Current liability, Long-term liability, Equity. Microsoft's page on how to create a drop-down list shows the Data Validation steps, with the five values typed into the Source box separated by commas. A typo such as "Curent asset" would match no subtotal and drop the account from every total, and the drop-down stops that.

Brightwater's mapping tab, with signed balances (debits positive, credits negative):

The balance column totals zero. The last two rows are the reclassification the next section explains.

SUMIFS by line and class

On the balance sheet tab, each line carries its class beside it, and the amount formula adds the mapping balances that match both:

=SUMIFS(Map!$D:$D, Map!$B:$B, $A5, Map!$C:$C, $B5) * $C5

Column A holds the line, column B the class and column C the sign (1 for assets, -1 for liabilities and equity). Microsoft's SUMIFS function reference allows up to 127 range and criteria pairs, so the second condition costs nothing. Matching on class as well as line means "Long-term debt" can never pick up the current portion by mistake.

How do you split a loan into current and long-term portions on a classified balance sheet?

A loan is split on a classified balance sheet by moving the principal due in the next 12 months into current liabilities and leaving the rest in long-term liabilities. The figure comes from the loan's amortization schedule: add the principal column for the 12 payments after the balance sheet date. Future interest is not part of it.

Brightwater owes $120,000 on its term loan at December 31, 2026. The lender's amortization schedule shows principal of $1,500 in each of the 12 payments from January through December 2027, so $18,000 is the current portion and $102,000 is long-term. The client's books carry all $120,000 in one account, number 2500, and that is normal. The split is a presentation step, not a change to the books.

The two reclassification rows on the mapping tab do the work. Row RC1 puts -18,000 on the line "Current portion of long-term debt" with the class Current liability. Row RC2 puts +18,000 on "Long-term debt" with the class Long-term liability, which reduces the long-term line from 120,000 to 102,000. Because the two rows net to zero, the mapping tab still totals zero and the balance sheet check cell still reads "Balances".

Type the 18,000 as a value with a note naming its source, such as "Lender schedule dated Dec 31, 2026, payments 13 to 24". A typed value with a source is easier to review than a formula nobody can trace.

When the lender has not sent a schedule, Excel can estimate the principal. Microsoft's CUMPRINC function returns the cumulative principal paid between two payment numbers, given the rate, the number of periods and the present value. For the next 12 months on a monthly loan, the formula is:

=-CUMPRINC(AnnualRate/12, RemainingMonths, CurrentBalance, 1, 12, 0)

Microsoft's examples return negative numbers, so the leading minus sign turns the result positive. An estimate is only as good as its inputs. A loan with a balloon payment, a rate that resets, or extra principal payments will not follow a level schedule, so use the lender's figures when you have them. The post on splitting each loan payment into principal, interest and escrow covers reading those figures off monthly loan statements.

Working capital and current ratio cells on a classified balance sheet in Excel

Working capital and the current ratio come straight from the class subtotals on a classified balance sheet in Excel. Working capital is total current assets minus total current liabilities. The current ratio is total current assets divided by total current liabilities. Both read the class column, so they stay right when accounts are added.

The subtotals do not need the line labels at all. Each one adds the mapping balances for a single class:

Here is how the totals build up. Current assets are 41,500 + 62,300 + 88,700 + 6,500 = 199,000. Long-term assets are equipment of 210,000 less 74,000 of depreciation, plus the 5,000 deposit, which is 141,000. Total assets are 340,000. Current liabilities are 48,200 + 11,300 + 9,800 + 25,000 + 18,000 = 112,300. Long-term liabilities are 102,000 + 30,000 = 132,000. Equity is 10,000 + 61,200 + 24,500 of current-year net income = 95,700. Liabilities and equity total 244,300 + 95,700 = 340,000, equal to total assets.

The current ratio formula wraps the division in two functions. ROUND keeps the ratio to two decimals, so 199,000 divided by 112,300 shows as 1.77. IFERROR catches the #DIV/0! error a company with no current liabilities would produce, and shows "n/a" instead.

The reclassification changes both numbers. Without it, current liabilities would be 94,300, working capital 104,700 and the current ratio 2.11. The same company looks noticeably more liquid when next year's loan payments are left in long-term debt, which is why a reviewer checks for a current portion line whenever a term loan appears. To show each line as a percent of total assets beside these cells, the post on common-size statements for a multi-year comparison covers the formula.

Classified balance sheet edge cases: lines of credit, shareholder loans and customer deposits

Classified balance sheet edge cases are balances whose due date is unclear from the account name. A revolving line of credit, a loan from the owner and money a customer paid in advance each need a reading of the agreement or the facts before they get a class. Record the reason beside the class for next year's preparer.

Lines of credit

A revolving line of credit lets the business borrow, repay and borrow again up to a limit. Brightwater's $25,000 balance is classed as current in the example because the line agreement matures in 2027. A line with a multi-year maturity and no requirement to pay it down could read differently. The agreement's maturity date, not the account name, settles the question, so ask the client for the current line agreement along with the year-end statement.

Shareholder loans

A shareholder loan is money an owner lent the company, or the company lent an owner. Schedule L on Form 1120-S gives "Loans from shareholders" its own line, apart from the less-than-1-year and 1-year-or-more lines for other notes. In the example, Brightwater's $30,000 owner loan is classed as long-term because the signed note in the file states no repayment is due before 2028. With no note and no repayment terms, there is no due date to classify by. Show the balance on its own line, label it, and note what the file does and does not contain.

A loan from the company to an owner is the mirror case: an asset, often with no due date. Give it its own line rather than burying it in receivables.

Customer deposits

Customer deposits are money a customer paid before the work or the goods were delivered. They are a liability because the business owes either the delivery or a refund. Brightwater's $9,800 of deposits are classed as current because the orders ship within the year. A deposit on a contract that runs several years may be partly long-term. Customer deposits sometimes land as negative receivables in the client's books; reclassify them so receivables are not understated and liabilities are not missing.

When a classified balance sheet layout is more than the client needs

A classified balance sheet layout is more than the client needs when nobody outside the firm will read the balance sheet, or when the reader asks only for the tax-basis figures. A small company with no loan covenants, no bonding and no outside investors may need only an unclassified list of balances for the return.

The return itself sets a floor for some corporations. Form 1120-S, Schedule B, question 11, asks whether the corporation's total receipts and its total assets at year end were both less than $250,000; if the answer is yes, the form says the corporation is not required to complete Schedules L and M-1. The Instructions for Form 1120-S say that, where Schedule L is completed, the balance sheets should agree with the corporation's books and records.

Even then, a classified layout costs little once the class column exists. The class column takes a few minutes per client and carries forward to next year, and it is ready the day a bank asks for working capital. The cases where the classified layout matters most are a loan application, a renewal with financial covenants, and a surety bond request, because each reader looks at current assets and current liabilities first.

The income statement side follows the same pattern. The multi-step income statement in Excel adds a step code to the same mapping tab, and the cash flow statement from a trial balance reuses the working capital accounts this post classed as current. A classified sheet, a multi-step income statement and a cash flow statement together answer most lender questions about a small company.

All three start from a trial balance someone has to get into a workbook. Schema CPA reads trial balances, general ledgers and client-prepared balance sheets from whatever the client uploads, and sends the figures to Excel or CSV, so the mapping tab starts from data rather than retyping. The workflow section of the homepage shows how documents move from upload to export.