How to create an income statement in Excel step-by-step
By Indeed Editorial Team
Published 6 May 2022
The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.
Businesses of all sizes and kinds, from sole traders to corporate partnerships, receive income that they declare to the government. At the end of each year, a business produces an income statement to report their income for tax calculations which they can also use to develop strategies and measure growth. It's easy to learn how to create an income statement using basic database software on your computer. In this article, we explain how to create an income statement in Excel that does all the complex calculations for you, which you can reuse every year.
What is an income statement?
The term' income statement' refers to a specific financial document that breaks down business income into different figures. There can be many variations between individual income statements because some businesses have different types of expenses than others. Usually, this document serves as a record for declaring taxes or inquests but many companies also use it to measure growth. Sharing this statement with shareholders can also help report on the company's profitability and promote further investment.
How to create an income statement in Excel step-by-step
If you're wondering how to create an income statement in Excel these steps may help you:
1. Create and set up the document
Open Microsoft Excel on your computer, or on a mobile device if you are comfortable building spreadsheets in mobile applications. Decide where you would like to put your table on the sheet and leave room above and to the left, so the table isn't hard to read relative to the column and row axes. Also, leave enough room to fill in some information above the table to provide context for when you have created the statement and what data it refers to.
2. Fill in essential information
Before you make your table, type some essential information in the cells above where you're going to put the table. For example, write the name of your business, the date you are creating the statement, the dates the data refers to and your name as the one who created the statement. Make sure you type the date rather than using a date function because you do not want it to change every time somebody opens the sheet. Remember to double click the lines separating columns to make the cells expand to fit the text inside them.
3. Create and format your column headers
There are a few ways to lay out an income statement, and designs vary between companies. In this simple guide, we help you create a table of three columns and two sections of rows for revenues and expenses. Make a column for 'Revenue', a column for the previous year, and a column for the current year. You can format the cells to be a specific colour, change the text colour and font or make it bold depending on how you want to design the table.
4. Fill in the rows
In the 'Revenue' column, start filling in as many rows as you need with your different sources of income. Examples could include 'sales', 'donations', 'grants' and 'crowdfunding'. Then, in the column for each year, fill in the amounts you have for the total income the business received from these sources in those years. If your income statement measures income monthly, this system still works for different date ranges, provided you build an appropriate number of columns.
5. Add a row for totals
Once you have added a row for all your revenue streams, add one final row with 'Total Revenue' in your 'Revenues' column. Leave the cells in the different year columns blank for now, which we can fill in once we start filling in formulae. Leave a row or two of space below to keep your sheet easy to read. Remember, you can use easy keyboard shortcuts like the 'Tab' key to move across, navigate Excel quickly and easily replicate content.
6. Repeat this process for your expenses
Create another table below with 'Expenses' in the first column instead of revenues. Make a row for each of the company's expenses, such as 'production costs', 'wages', 'utilities' or 'postage' for example. Fill in the annual values for each expense and create a final row listing the total costs. You can colour code your revenue section as green and your expenses section as red to make it easier to read.
Related: What is net pay?
7. Use AutoSum to calculate your totals
There are various ways to calculate totals in Excel, but the easiest and most efficient for the purpose of an income statement is to use AutoSum. Go to your revenues section and highlight all the cells with figures in a particular year's column, then click on 'AutoSum' in the 'Editing' panel of the 'Home' bar. This outputs the total sum of the cells you selected in the cell directly below your selection, which is in our 'Total Revenue' row. Repeat this for subsequent years, and repeat the process in your 'Expenses' table in both columns.
8. Add additional features
Depending on what you want your income statement to do, you can add additional sections to the table to give yourself or your shareholders more information. For example, the 'Total Revenue' and 'Total Expenses' sections only show you the totals for these areas, but you can use this table to calculate gross profit and net income. Label a row' Gross Profit' and in the previous year's column use '=SUM' to subtract the figure for 'Production Costs' in that year from 'Sales'. Use the same =SUM function in a row labelled Net Income to subtract 'Total Expenses' from 'Total Revenue'.
9. Add columns and input new data
Now that the basic setup of the table is complete, you can add new columns for every year that you review the table. By adding new columns to the existing table, you can track your company's growth over a period of time and represent each year's figures on a line graph. This simple table can form the foundation for monitoring how your business behaves, and you can add to this sheet whenever you need a specific type of data. You don't have to redo the calculations every time you input new data since you used AutoSum and functions.
10. Save the document
Click on 'File' and 'Save As' to save the document to your computer or cloud storage. Make sure you name the document something appropriate and memorable. It can be helpful to include a version number or date such as 'income statement version 1.1.xlsx' for archiving purposes. If you want to change the data in this table, make sure to use 'Save As' to create a new copy of the table before you change anything. Alternatively, you can use 'Print' to save PDFs of the table's information for different periods or keep adding columns for successive years.
What scenarios are income statements useful for?
Income statements are immediately useful for declaring revenue and expenses, but there are also some other specific situations where they can be helpful. Here are some examples:
Corporate profit and loss: Beyond just revenue and expenses, you can use an income statement sheet to find your figures for profit and loss. Based on this information, you can identify where you have too many expenses or what your most profitable areas are so that you can change your approach.
Charity balance sheet: Charities can use income statements to declare their takings to organisations and analyse where their money comes from. In a more complex sheet, charities can see which regular sources of donations make up the majority of their income to change their marketing to target that group.
Personal tax returns: If you regularly fill out Self Assessment forms for tax as a sole trader, an income statement can be a great starting point for your calculations or supporting evidence. Updating this document yearly can also help you see where you are making the most money and what costs you can cut to try and optimise your business.
Grant applications: An income statement can help writers declare the profitability of a project and its financial viability in applications for grants. Many investors and organisations want detailed breakdowns on the sources of any outside funding and a detailed breakdown of costs before they commit to a payment.
Business partnerships: You can calculate the appropriate split of revenues between business partners using an income statement sheet. The breakdown of revenues and expenses ensures that you divide the right figure between partners to comply with the terms of the business agreement.
Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.
Explore more articles
- What is continuous deployment? (And best ways to adapt it)
- What is financial well-being and how can employers help?
- What is alt text and why is it important for businesses?
- What is marketing automation? (tips, benefits and examples)
- 11 workplace trends (with definition and benefits)
- What is a target market and why is it important? A guide
- What is a documentation writer? (With definition and types)
- What is a vulnerability assessment? (With steps and types)
- How to create strategic priorities within a strategic plan
- What is email deliverability and how do you improve it?
- Definition, symptoms and ways to prevent WFH burnout
- 9 of the best universities for computer science in the UK