How to Create an Income Statement in Excel

An income statement is a fiscal report that shows a company ‘s net income or loss. here ‘s how to create one in Excel. Businesses, both bad and little, need to track their performance. This is why fiscal statements, such as the libra sheet and income statement, are useful .
To check your business ‘s performance, you do n’t even need an accountant or account software. Excel can help you make your own statements if you follow the instructions .
To help you prepare your statements, here ‘s a steer on how to make an income statement.


What Is an Income Statement?

This text file is pretty simpleton. All you need to remember is this recipe :

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 besides see the measure you ‘re making or spending per category .
This data will let you see your efficiency. The data will besides tell you what aspects of your business needs improvement. Income statement on a laptop screen

1. Choose Your Period

Most income statements are prepared per annum. 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 utilitarian if your commercial enterprise 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 fiscal transactions in a journal .
If you have n’t started a journal even, you can create your income argument with your receipts, bank statements, and other records. ampere long as you have arrant details, you can make a reasonably accurate income statement .
Computing income taxes using paper and pen

3. Arrange Your Information

Before moving ahead with creating the income statement, you should arrange them beginning. 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 late .

4. Make the Excel File

  1. To make your Income Statement, first, open up Microsoft Excel, then create a new file. Income statement Excel file title
  2. In the first cell, type in [Company Name] Income Statement. This helps you organize your files, especially if you need to print this document.
  3. Skip one row and then write Covered Period. This shows what dates are covered by this income statement.

Related : How to Learn Microsoft Excel Quickly

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 space, hera 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)

Revenue entries in an income statement

2. Operating Expenses

  • Salaries
  • Rent
  • Utilities
  • Transportation
  • Advertising
  • Marketing
  • Others
  • Total Operating Expenses
  • Operating Income (Loss)

Operating Expenses entries on an income statement

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

Gains (Losses) from continuing operations in an income statement

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

Non-recurring events and net income entries on an income statement These categories are what most businesses use for their income statement. however, feel detached to change this as you see fit .
Always add spaces between categories and subcategory sections. This ensures that you wo n’t get confused with tightly packed data .
Indent each subsection to help differentiate it from early sections. In the Home ribbon, you can find the Indent button under Alignment. Indent function for laying out cells on Excel You can besides resize the column to ensure that all contents fit. Do that by double-clicking on the tune between column A and B.

Resizing columns on Microsoft Excel

6. Prepare Your Formulas

Formulas in your income affirmation are childlike. After all, it ‘s good 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 hera .
To do that, type =SUM( and then select all entries under the Sales subsection. Do n’t forget to hold the Shift key to select multiple cells. Net sales subtotal formula The Cost of Goods Sold section has two subtotals. The first one is Total Goods Available. This is the total of all your current armory. To find this measure, repeat the formula above and select all entries above Total Goods Available .
Total goods available formula The total Cost of Goods Sold is the summarize of total Goods Available and Less : Ending Inventory. Its formula is =SUM([Total Goods Available]:[Less:Ending Inventory]) Total goods available formula once you have that measure, compute your gross profit using this formula : =[Net Sales]-[Total Cost of Goods Sold] Gross profit formula The formula for sum operate expenses is like the one used in net income sales. Use =SUM( then select all cells under this subcategory. Total operating expenses formula in Excel for income statement To compute your operating income ( passing ), use the formula =[Gross Profit (Loss)]-[Total Operating Expenses]. Operating income formula for Excel Compute your total gains ( losses ) from continuing operations by using the sum function. then once you have that sum, find your income before taxes with this convention : =[Operating Income (Loss)]+[Total Gains (Losses)]. Formula for total gains from continuing operations in Excel To get your income from continuing operations, add your income before taxes, less : tax expenses value, and operating income .
Income from continuing operations formula on Excel You then need to find the sum gains/losses from non-recurring events. Use the SUM function to do this. Non-recurring events formula To calculate final net income, add the income from continuing operations and nonrecurring gains or losses .
Net income formula Related : The Top Financial Functions in Excel

7. Format Your Document

To make your document easier to read, format it so that negative numbers appear bolshevik. Do this so that you can promptly analyze your instruction with barely a glance .
To do that, select all your data. then, look for the dropdown menu in the Number section at the Home ribbon. More number formats option

  1. Choose More number formats…
  2. You will see a new window titled Format Cells. Look for the Number tab at the top row.
  3. Click on it, then, under the Category subwindow, choose Currency.
  4. Find the correct Symbol that represents your currency accurately.
  5. Afterward, under the Negative numbers: subwindow, select the -$1234.10 with a red font color option.

Format cells window in Excel You should then see all negative values in your data, making it easier to find and see .
besides, blue-ribbon each category, subtotal, and full row and assign different colors. This makes it easier to read and gives your income statement a professional spirit. Fully formatted income statement with colorsa

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 rowing that ‘s marked Less :
Five year income statement sample on Microsoft Excel You can access and save a copy of this income argument 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 besides compare this document between two periods. It ‘s specially helpful if you ‘re making strategic changes. You ‘ll see if the changes you made are effective or not .

Related Posts