The by-function income statement is a type of income statement that classifies expenses by their functions, such as cost of goods sold, selling expenses, and administrative expenses. This format provides more information about the nature and behavior of the expenses than the by-nature income statement, which groups expenses by their categories, such as wages, rent, and depreciation.
To create a by-function income statement in Excel, you need to follow these steps:
- Enter the revenue and expense data for each function in separate columns. For example, you can have columns for sales revenue, cost of goods sold, selling expenses, administrative expenses, and other expenses.
- Calculate the gross profit by subtracting the cost of goods sold from the sales revenue. You can use the formula
=B2-B3in cell B4, where B2 is the sales revenue and B3 is the cost of goods sold. - Calculate the operating income by subtracting the selling expenses and administrative expenses from the gross profit. You can use the formula
=B4-B5-B6in cell B7, where B4 is the gross profit, B5 is the selling expenses, and B6 is the administrative expenses. - Calculate the net income by subtracting the other expenses and income tax from the operating income. You can use the formula
=B7-B8-B9in cell B10, where B7 is the operating income, B8 is the other expenses, and B9 is the income tax. - Format the income statement by adding labels, borders, and alignment. You can also use the SUM function to check the accuracy of your calculations.
To illustrate the by-function income statement format, let’s use a scenario of a hypothetical company that sells books. Here are the data for the year 2020:
- Sales revenue: $100,000
- Cost of goods sold: $40,000
- Selling expenses: $15,000
- Administrative expenses: $10,000
- Other expenses: $5,000
- Income tax: $10,000
Using the steps above, we can create the following by-function income statement in Excel:
| 2020 | |
|---|---|
| Sales revenue | $100,000 |
| Cost of goods sold | $40,000 |
| Gross profit | $60,000 |
| Selling expenses | $15,000 |
| Administrative expenses | $10,000 |
| Operating income | $35,000 |
| Other expenses | $5,000 |
| Income before tax | $30,000 |
| Income tax | $10,000 |
| Net income | $20,000 |
The result of the scenario is that the company has a net income of $20,000 for the year 2020, which is 20% of its sales revenue. This means that for every dollar of sales, the company earns 20 cents of profit after paying all the expenses. The by-function income statement also shows that the company has a gross profit margin of 60%, an operating profit margin of 35%, and a net profit margin of 20%. These ratios indicate how efficiently the company manages its costs and generates profits.
