How to Make an Income Statement in Excel
Businesses, both big and small, need to track their performance. This is why financial statements, such as the balance sheet and income statement, are useful.
To check your business's performance, you don't even need an accountant or accounting software. Excel can help you make your own statements if you follow the instructions.
To help you prepare your statements, here's a guide on how to make an income statement.
What Is an Income Statement?
This document is pretty simple. All you need to remember is this formula:
Net Income = (Total Revenue + Gains) - (Total Expenses + Losses) Once you've created your income statement, you'll see how much you've made (or lost) in a period. You will also see the amount you're making or spending per category.
This information will let you see your efficiency. The data will also tell you what aspects of your business needs improvement.
1. Choose Your Period
Most income statements are prepared annually. That way, you can see how you did your previous year and what you can improve.
However, you can create quarterly (or even monthly) income statements. This is particularly useful if your business is new or if you're changing strategies. That's because it allows you to see the overall impact of the changes you make.
2. Have Your Journal on Hand
Accurate record-keeping is necessary for any statement you make. That's why businesses need to keep track of their financial transactions in a journal.
If you haven't started a journal yet, you can create your income statement with your receipts, bank statements, and other records. As long as you have complete details, you can make a reasonably accurate income statement.
3. Arrange Your Information
Before moving forward with creating the income statement, you should arrange them first. This document has four primary categories:
- Revenue/Profit Section: refer to money made and spent under your company's primary purpose.
- Operating Expenses: refers to your company's day-to-day expenses. These are your required expenditures to run your business.
- Gains (Losses) from Continuing Operations: refers to your interest expense, taxes, and other cash movements unrelated to operations.
- Non-recurring Events: refers to significant, non-recurring gains and losses. These could be the sale or purchase of significant assets, income from discontinued operations, accounting considerations, and other items.
Find which section each transaction falls in so it will be easier to fill out your Excel file later.
4. Make the Excel File
- To make your Income Statement, first, open up Microsoft Excel, then create a new file.
- In the first cell, type in [Company Name] Income Statement. This helps you organize your files, especially if you need to print this document.
- Skip one row and then write Covered Period. This shows what dates are covered by this income statement.
5. Find Your Subcategories
The four categories provided remain the same across most companies. However, the sections under here will change from business to business.
To help you choose which section to place, here are some examples:
1. Revenue
- Sales
- Gross Sales
- Bad Merchandise
- Net Sales
- Cost of Goods Sold: this refers to your capital for your inventory. This is only applicable if your business deals with physical items. If you're a service-based business, you can skip this section.
- Beginning inventory
- Purchased goods
- Raw materials
- Manufacturing labor
- Total Goods Available
- Ending Inventory
- Total Cost of Goods Sold
- Gross Profit (Loss)
2. Operating Expenses
- Salaries
- Rent
- Utilities
- Transportation
- Advertising
- Marketing
- Others
- Total Operating Expenses
- Operating Income (Loss)
3. Gains (Losses) From Continuing Operations
- Other Gains
- Other Expenses
- Interest Expenses
- Total Gains (Losses) from Continuing Operations
- Income Before Taxes
- Tax Expenses
- Income (Loss) from Continuing Operations
4. Non-Recurring Events
- Income from discontinued operations
- Losses from discontinued operations
- Gains from extraordinary items
- Losses from extraordinary items
- Gains from accounting changes
- Losses from accounting changes
- Total Gains (Losses) from Non-recurring Events
- Net Income
These categories are what most businesses use for their income statement. However, feel free to change this as you see fit.
Always add spaces between categories and subcategory sections. This ensures that you won't get confused with tightly packed information.
Indent each subsection to help differentiate it from other sections. In the Home ribbon, you can find the Indent button under Alignment.
You can also resize the columns to ensure that all contents fit. Do that by double-clicking on the line between columns A and B.
6. Prepare Your Formulas
Formulas in your income statement are simple. After all, it's just a matter of adding or subtracting values.
However, all values are connected, so you should be careful when writing your formulas. First, prepare the formula for Net Sales. All you need is to find the sum of the entries here.
To do that, type =SUM( and then select all entries under the Sales subsection. Don't forget to hold the Shift key to select multiple cells.
The Cost of Goods Sold section has two subtotals. The first one is Total Goods Available. This is the sum of all your current inventory. To find this value, repeat the formula above and select all entries above Total Goods Available.
The Total Cost of Goods Sold is the sum of Total Goods Available and Less: Ending Inventory. Its formula is =SUM([Total Goods Available]:[Less:Ending Inventory])
Once you have that value, compute your gross profit using this formula: =[Net Sales]-[Total Cost of Goods Sold]
The formula for total operating expenses is like the one used in net sales. Use =SUM( then select all cells under this subcategory.
To compute your operating income (loss), use the formula =[Gross Profit (Loss)]-[Total Operating Expenses].
Compute your total gains (losses) from continuing operations by using the sum function. Then once you have that amount, find your income before taxes with this formula: =[Operating Income (Loss)]+[Total Gains (Losses)].
To get your income from continuing operations, add your income before taxes, less: tax expenses value, and operating income.
You then need to find the total gains/losses from non-recurring events. Use the SUM function to do this.
To calculate net profit, add the income from continuing operations and nonrecurring gains or losses.
7. Format Your Document
To make your document easier to read, format it so that negative numbers appear red. Do this so that you can quickly analyze your statement with just a glance.
To do that, select all your data. Then, look for the dropdown menu in the Number section at the Home ribbon.
- Choose More number formats...
- You will see a new window titled Format Cells. Look for the Number tab at the top row.
- Click on it, then, under the Category subwindow, choose Currency.
- Find the correct Symbol that represents your currency accurately.
- Afterward, under the Negative numbers: subwindow, select the -$1234.10 with a red font color option.
You should then see all negative values in your data, making it easier to find and see.
Also, select each category, subtotal, and total row and assign different colors. This makes it easier to read and gives your income statement a professional look.
8. Place Your Values
Once you've prepared everything, place all the actual values that you've prepared. All amounts you enter should be positive unless it's for a row that's marked Less:
You can access and save a copy of this income statement sample here.
See How Your Business Performed
Now that you know where you make money and where your expenses are, you can see if you are hitting your goals. Using this document, you can see how efficiently you run your business.
You can also compare this document between two periods. It's especially helpful if you're making strategic changes. You'll see if the changes you made are effective or not.
How to Make a Cash Flow Statement in Microsoft Excel
A cash flow statement shows a company's inflow and outflow of funds over a given period of time. It's easy to make in Excel.
Read Next
About The Author 
Jowi Morales (135 Articles Published)
Jowi is a writer, a career coach, and a pilot. He developed a love for anything PC ever since his father bought a desktop computer when he was 5 years old. From then on, he has been using and maximizing technology in every aspect of his life.
More From Jowi Morales
How to Make an Income Statement in Excel
Source: https://www.makeuseof.com/create-income-statement-microsoft-excel/