Multi Step Income Statement Excel Template, Row by Row

A multi step income statement in Excel runs from net sales down through cost of goods sold to gross profit, then operating expenses to operating income, then other income and expense to net income. Each step is its own subtotal row. A step code on the trial balance mapping tab sends every account to the right block.

This post builds that template for a fictional retailer, Larkspur Outdoor Goods LLC, for the year ended December 31, 2026. The company, its accounts and every amount are examples. The arithmetic ties: the steps add to the same net income the trial balance produces, and the margin cells divide cleanly.

What a multi-step income statement shows that a single-step one hides

A multi-step income statement separates the profit a business makes on what it sells from the cost of running the business and from items outside its main activity. A single-step statement adds all revenue, subtracts all expenses and shows one result. Both reach the same net income; only the multi-step version shows how.

Larkspur's net income is $68,800 either way. The single-step version tells a reader nothing more. The multi-step version shows that the company kept $372,000 after paying for its merchandise, spent $297,600 running the stores, and gave up a net $5,600 to interest and one-off items. A banker reading it can see that the stores make money before financing costs, and that the gross margin is 40 percent.

Gross profit answers a pricing and purchasing question: does the business sell goods for enough above their cost? A falling gross margin points at discounting, rising supplier prices or inventory shrinkage. Operating income answers a running-cost question: after rent, wages and fees, does the core business earn money? A business can have a healthy gross margin and still lose money at the operating line.

The other income and expense block keeps unusual items from distorting those two answers. A one-time gain on selling a delivery van raises net income for one year. If it sat among operating revenue, operating income would look better than the stores earned.

The multi step income statement template rows in Excel

The multi step income statement template in Excel has a fixed set of rows: gross sales, returns and allowances, sales discounts, net sales, cost of goods sold, gross profit, operating expenses by type, total operating expenses, operating income, other income and expense, pretax income and net income. Subtotal rows are formulas; detail rows come from the mapping tab.

Here is Larkspur's statement, row by row:

Larkspur is an LLC taxed as a partnership in this example, so the income tax row is zero and pretax income equals net income. A C corporation would show its own income tax expense on that row. Keep the row in the template even when it is zero, so the same layout serves every client.

The subtotal formulas are plain arithmetic on the rows above them:

• Net sales, row 8: =C5-C6-C7, with returns and discounts stored as positive amounts.
• Gross profit, row 12: =C8-C11, which is 930,000 less 558,000.
• Operating income, row 21: =C12-C20, which is 372,000 less 297,600.
• Pretax income, row 26: =C21+C25, which is 74,400 less 5,600.
• Net income, row 28: =C26-C27.

Many client statements arrive as monthly columns or with a year-to-date column beside the month. The post on income statement layouts clients send, monthly, quarterly and year to date covers lining those up; the template here uses one annual column. The same post's section on expense signs on an income statement in Excel explains why the template stores every amount as a positive number and lets the formulas subtract.

Which accounts sit below operating income on a multi-step income statement?

Accounts below operating income on a multi-step income statement are the ones outside the company's main activity: interest expense on its borrowing, interest earned on its cash, gains and losses on selling equipment or other assets, and items the firm judges one-time. Income tax, when the entity pays it, follows pretax income.

Interest expense goes below the line because it reflects how the business is financed, not how it operates. Two identical stores, one paid for with a loan and one with the owner's cash, should show the same operating income. Larkspur's $9,800 of interest on its equipment note sits in the other income and expense block for that reason.

Interest income on a retailer's bank balance is small and unrelated to selling outdoor gear, so it sits below the line too. A lender or finance company is the exception: for them, interest is the main revenue and belongs in net sales.

A gain on an asset sale is the difference between the sale price and the asset's book value. Larkspur sold a delivery trailer with a book value of $2,000 for $5,000, so the gain is $3,000. A loss would sit in the same block as a negative amount. The cash side of this sale, and why a gain is subtracted again when operating cash flow is worked out, are covered in the post on the cash flow statement from a trial balance.

"One-time" is the label to use most sparingly. A lawsuit settlement or an insurance recovery after a flood may qualify. Moving expenses that recur every few years probably do not. Whatever the firm decides, record the reason, because a reader who later finds the same item in three years of other income will question the label.

Gross margin and operating margin formulas on the multi-step income statement

Gross margin is gross profit divided by net sales. Operating margin is operating income divided by net sales. On the multi-step income statement, both go in cells beside the subtotals they use, shown as percentages, and each division is wrapped so an empty month or a client with no sales shows text instead of an error.

For Larkspur:

The arithmetic: 372,000 divided by 930,000 is exactly 0.40, and 74,400 divided by 930,000 is exactly 0.08. Net margin is 68,800 divided by 930,000, which is 0.0740, shown as 7.4 percent.

Microsoft's IFERROR function returns its second argument when the first produces an error, and #DIV/0! is on its list. A template copied to a new client before any figures arrive would otherwise fill with #DIV/0! in every margin cell. Showing "n/a" makes it clear the cell is waiting for data rather than broken.

Divide by net sales, not gross sales. Gross sales include goods that came back, and a margin built on them looks smaller than the business's real margin on what it kept. Use the same denominator in every year's column so a comparison means something.

A margin that moves sharply from one year to the next is a question for the client, and the post on which multi-year swings deserve a question to the client sets thresholds for them. Before asking, check how returns, discounts, freight and merchant fees are coded, because a coding change moves a margin without any change in the business.

How do step codes on the trial balance map feed a multi-step income statement?

Step codes on the trial balance map are short labels that send each income statement account to its block: net sales, cost of goods sold, operating expenses, or other income and expense. One extra column on the mapping tab holds them, and the subtotal rows add every account with a given code.

The mapping tab already assigns each account to a statement line; the post on building a reusable trial balance mapping table sets it up. For a multi-step statement, add a step column with four codes: S1 for net sales, S2 for cost of goods sold, S3 for operating expenses and S4 for other income and expense. Larkspur's mapping tab, with signed trial balance amounts (debits positive, credits negative):

The balance column totals -68,800, which is net income as a credit. Each step total is one SUMIFS on the step column:

Microsoft's SUMIFS reference puts the sum range first and the criteria range and criteria after it. The leading minus sign on net sales and on other income turns credit totals into positive income. Other income comes out as -5,600 because Larkspur's interest expense is larger than its interest income and gain together.

Use the step totals as a cross-check against the row-by-row statement. If net sales from the step code differ from row 8, an account is coded S1 but missing from the detail rows, or the reverse. The explanation of the net income check on an Excel income statement adds one more cell: net income on the statement against the total of the trial balance's income statement accounts.

Multi-step income statement classification mistakes: returns, discounts, freight and merchant fees

Multi-step income statement classification mistakes move an amount between blocks without changing net income. Returns, discounts, freight and card processing fees are the usual cases. Net income stays the same, but gross profit, operating income and both margins shift, and a reader comparing years sees a trend that is really a mapping change.

Sales returns and allowances

Returns and allowances reduce sales; they are not an operating expense. IRS Publication 334, the IRS guide for small businesses, treats credits allowed to customers for returned merchandise and other sales allowances as deductions from gross sales in working out net sales. Coding Larkspur's $24,000 of returns as S3 would raise net sales to $954,000 and gross profit to $396,000, lifting the gross margin to about 41.5 percent. Operating expenses would rise by the same $24,000, so operating income and net income would not change.

Discounts

Two different discounts share the name. A sales discount is one Larkspur gives a customer, such as two percent off for paying early; it reduces sales, so it is coded S1 with returns. A purchase discount is one a supplier gives Larkspur. Publication 334 describes two methods for cash discounts on purchases: subtract them from purchases, or credit them to a separate discount income account. It also says the method chosen must be used every year. Under the first method the discount sits in cost of goods sold; under the second it sits in other income. Either way, keep it consistent from year to year.

Freight-in and freight-out

Freight-in is shipping paid to bring merchandise in. Publication 334 lists freight-in among the costs that go into cost of goods sold, so Larkspur's $18,600 is coded S2. Freight-out is shipping paid to deliver goods to customers. Larkspur codes its $9,000 as an operating expense. Mixing the two in one "Freight" account pushes either gross profit or operating expenses off, so split them in the mapping tab when the client's books do not.

Merchant fees

Merchant fees are what the card processor keeps from each sale. Some processors deposit sales net of fees, so the books record $930 for a $960 sale and the fee never appears. Others deposit the full sale and charge fees monthly. Larkspur records gross sales and codes its $27,900 of fees as an operating expense. Netting fees against sales instead is a presentation choice the firm makes; the point is to make it once, note it, and apply it to every year in the comparison.

The figures in this template came from a trial balance, but they often start as a client's own income statement PDF. Schema CPA reads income statements, balance sheets and trial balances among a client's uploads and puts the numbers into Excel or CSV, ready for the mapping tab; the documents section of the homepage lists every type it reads. Once the multi-step statement is done, its net income is the starting figure for the equity rollforward in why retained earnings does not match the prior year.