Creating Income Reports in Excel

Every business needs to track performance through financial reports. Excel can assist you in creating income reports for your business tasks. Follow the guide below.

I. What is an Income Report?

An income report is a relatively simple type of document, based on the formula below to calculate the net income of the business:

1. Organizing information

Before proceeding to create an income report, you must organize the relevant factors. This document should have 4 main categories:

• Revenue: The amount earned and spent for the primary purpose of the company.
• Operating expenses: The daily expenses of the company, the necessary expenses to operate the business.
• Profit (Loss): Interest expenses, taxes, and other non-operating cash flow transactions unrelated to your operations.
• Unusual expenses: Minor gains or losses, not significant or repetitive. It could involve buying and selling significant assets, income from discontinued operations, accounting adjustments, and other items.

II. Creating an income report in Excel

1. Create Excel File

Step 1: To generate your income report, first open Excel and create a new file.

Step 2: In the first cell, type Income Report [Company Name].

Step 3: Skip a row and then write Time. Here you decide the period for the report, whether it's a year, quarter, or month.

2. Add Subcategories

Typically, you'll need to list the following categories. However, feel free to customize them according to your needs.

Here are some examples:

a. Revenue

• Sales
  • Total Revenue
    • Losses
  • Net Revenue
• Cost of Goods Sold: Your inventory cost. This section applies only if your business deals with physical goods. If it's service-based, you can skip this part.
• Beginning Inventory Value
• Goods Sold
• Raw Materials
• Manufacturing Labor
• Total Available Inventory
• Ending Inventory Value
• Total Cost of Goods Sold
• Gross Profit

b. Operating Expenses

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

c. Income (Expense) from Continuous Operations

• Other Revenue Sources
  • Other Expenses
  • Interest Expenses
• Total Income (Expense) from Continuous Operations
• Pre-tax Income
  • Tax Expenses
• Profit from Continuous Operations

d. Non-recurring Activities

• Income from Ceased Operations
  • Losses from Ceased Operations
• Income from Special Items
  • Losses from Special Items
• Income from Accounting Adjustments
  • Losses from Accounting Adjustments
• Total Income (Expense) from Non-recurring Activities
• Net Income

The categories above are what a business typically records in their income statement. Consider adapting based on your business type.
Always add spaces to categories and subcategories. This ensures you won't mix up information.

Indent sub-items to distinguish them from larger items. Click the cell, then use the increase indent tool on the toolbar. For the first sub-item, indent once, for the second sub-item, indent twice as shown in the example below.

You can widen column A by dragging the border between A and B to the right.

3. Set up formulas

Formulas in your income statement are quite simple. Essentially, it's just a matter of adding/subtracting values.

However, all values are interconnected, so be careful when writing your formulas.

First, set the formula for Net Revenue. Enter =SUM( in the cell next to Net Revenue. Then hold Shift and select all cells next to Sales, Total Revenue, and Losses. In my example, the formula is =SUM(B6:B8).

Total Available Inventory, this is the sum of all your current inventory. The formula for this value is the sum of the 4 items above it.

Total Cost of Goods Sold is Total Available Inventory + Ending Inventory Value.

Once you have that value, calculate Gross Profit with the following formula: Net Revenue – Total Cost of Goods Sold.

The formula for Total Operating Expenses is similar to the one used in Net Revenue. Use =SUM( and select all cells in this subcategory.

To calculate your Operating Income, use the formula = Gross Profit – Total Operating Expenses.

Calculate the total income (expense) from continuous operations using the SUM function. Enter =SUM( and select cells in that category.

To calculate the Profit from Continuous Operations, you need to Add Total Income (Expense) from Continuous Operations + Tax Expenses + Operating Income.

Next, you need to find the total profit/loss from non-recurring events. Use the SUM function to do this.

Lastly, for Net Income, add income from continuous activities and non-recurring gains/losses.

4. Format your document

To make your document more reader-friendly, format it so that negative numbers appear in color. This helps you analyze values more easily.

First, select all your numerical data, then click on the drop-down menu in the Number section.

Choose More Number Formats… at the bottom of the menu.

You'll see a new window titled Format Cells, find the Number tab at the top row. Then select Currency.

Select the red-colored rows in the Negative numbers section, then press OK.

Done, all your input data with preceding minus signs will turn into red.

Additionally, for each category, sub-total, and grand total, choose different colors. This action not only makes the report table easy to read but also looks more professional.

Click on the row you want to change the color of, then open the Cell Styles section on the toolbar.

Choose the style you desire.

Once you've finished colorizing the items, you'll have a report table like this:

5. Add your values

Once everything is prepared, now add the values you've prepared. For expenses or losses, you need to add a preceding minus sign. Negative numbers will automatically turn red and lose the minus sign, just as you formatted above.

Conclusion

Now you can understand where your business is making money and where it's spending, whether it's achieving financial goals or not. Wishing you success!