How to Make Income Statements Using Excel
When preparing your business's financial statements, formatting is critical to the quality of the presentation. Use the columns in Excel to keep all of your reporting data in legible columns, simplify the reporting process, and make it easier to present your company's financial position to investors and shareholders.
Compile a list of every income source the company recognized over the reporting period. The company's income sources include gross revenue, interest revenues and other income. Enter each income source in a new cell of the Excel spreadsheet and then list the total amount for the period in the adjacent cell. Enter "Income" in the cell at the top of the list. Create a subtotal at the bottom and label it "Total Income." Calculate the subtotal automatically by entering "=sum(xx:xx)" where the "xx:xx" is the cell range of the totals. For example, if your income totals are in cells C1 through C6, the formula would read "=sum(C1:C6)."
Leave two blank rows after the income section and then enter "Expenses" in the next cell. List each type of expense that the company paid during the period in this section as you did in the income section. Business expenses include things like supplies, administrative costs, salaries, employer taxes and any other costs of doing business. Create a subtotal at the bottom of this section labeled "Expenses." Use the "SUM" feature for this calculation as you did with the income figures.
Enter "Net Income" two lines below the expense section. In the adjacent cell, subtract the total expenses from the total income. If there are more expenses than income, change the label on the line to read "Net Loss." Use Excel's automatic calculations for this by entering "=sum(xx-zz)" where "xx" is the total income and "zz" is the total expenses.
Add a header at the top of the report. Highlight the top row of cells across the columns of the report. For example, if you have data in columns A, C and E, highlight cells A1, B1, C1, D1 and E1. Click "Format" under the Cells field of the Home tab. Choose "Format Cells." Click the "Alignment" tab and choose "Merge Cells." Repeat this for the same cells in row 2 and row 3.
Enter the company name in the merged cell field of row 1. Enter "Income Statement" in the merged cell of row 2, then the effective date of the report in row 3. Center all three lines for formatting purposes.