How do I create a profit and loss chart in Excel?

If there is one report that's a business constant, it's the income statement (also known as a profit and loss statement). Its core purpose is to show managers and investors the bot

How do I create a profit and loss chart in Excel?

If there is one report that's a business constant, it's the income statement (also known as a profit and loss statement). Its core purpose is to show managers and investors the bottom line of a company for a specific period. It is normally delivered as a structured spreadsheet that shows how the top line (revenues) is transformed into the bottom line after taking out expenses.

There is a much better way, however. An income statement tells a very important story about your company and you can tell it in a much more effective way. Instead of relying on just numbers, you can use a visual language that makes your story not just much more attractive but also much easier to understand.

A Basic Income Statement

First, let's take a look at a relatively simple income statement showing business results for two years.

Tip: There are many things you can do to improve the readability of your report. Some are more subtle than others - for example, in the structure waterfall chart, the numbers that have positive contribution are displayed to the right of the bar, while negative contribution values are marked on the left. This is a good practice that clarifies your story without any added visual noise.

The first and second columns of waterfall charts show the different types of revenue and costs that make up total profit across two different years. The first section of the chart shows revenue sources and growth that contribute to the overall profit, followed by costs, such as purchases, material and other operating expenses that reduce the overall profit.Note the "Operating expenses" which is a subtotal of all other expenses above it. This intermediate calculation improves readability and helps visualize the end result. These subtotals are actually very easy to do in ZebraBI. You only need to tell the software how many rows above a certain cell should be included in the calculation and Zebra BI does the rest.

When you create a report like this, you use the "Structure" waterfall chart. This type of waterfall chart shows the structure of the final profit and contributions of individual items. You could also use the "Variance" chart that puts the focus on actual differences.

The two waterfall charts are complemented by a variance bar chart and lollipop chart on the right:

The bar chart displaysabsolute values and their change. The lollipop chart represents relative growth or decrease in percentages. The important thing about the variance chart is that it is scaled. The length of the chart now corresponds to the actual difference between 2012 and 2013 values. (You can switch between absolute and relative variance charts with the click of a single button in Zebra BI chart properties.)

Tip: Use a waterfall chart instead of a bar chart to display absolute variance. You will be able to visualize not only the values but also how they contribute to the total difference. Like this:

When working with comparisons, you will soon meet the category class column. Instead of using negative or positive numbers, you can use the category class column to define whether an item is a positive or negative addition to the total. You can read more about the category class column here. Using this feature you can easily invert KPIs that have a negative contribution despite being a positive value. For example, an increase in the cost of raw materials is a positive value with a negative impact on your overall profit. Zebra BI will show it in red, despite it being a positive value.

Taking It Up a Notch

In real life, you might want to show data for more than just two years. A typical scenario is a report with the current and past year data and plans for the coming year.

Notice a different approach to designing the report. With three data sets, we are coming up short on space. Since the variance data is much more relevant, we used a table and chart combination. The PY, AC and BU values are displayed in a table, while the more important variance data is visualized with charts.

The bar and a lollipop charts are telling the same story as with the previous template. However, we now have two pairs of them - one pair for showing variance from the past year andthe second pair displaying the difference compared to the plan.

The great thing about Zebra BI is that you have complete design freedom. For example, you could change the order of charts or even place data columns between charts.

Tip:When designing a more complex template like this, decide on an order of charts and stick to it. I prefer showing the variance compared to the previous year and then the comparison to the plan. You might want to use a different approach, just make sure to stay consistent. This way, you'll ensure your income statement always tells a clear story at first glance.

When designing a report like this, make sure that all the charts that visualize the same type of data are scaled. You can see here that bar lengths clearly display the difference between the previous year and the plan. Keep in mind that this type of reports is fairly useless if it's not scaled. Luckily, scaling is one of the strong points of Zebra BI. To scale your charts, you just need to select the charts you want to adjust and click on a single "Scale" button.

The more the merrier

These two sample income statements were for a single company. But what about if we are creating an income statement for a group of companies, subsidiaries or profit centers? In this case you might want to show multiple countries or companies on a single page. Take a look at an example:

Data here is more aggregated - it's five smaller charts with waterfall charts showingkey data: net sales, costs, operating profit, EBITDA and net result. On the right are variance charts showing differences compared to the plan. This is a great way to show the picture of your business on a single page.

Again, pay attention to scaling. This report will only work if it is scaled - it must make it clear that Germany is the largest market, followed by France and Great Britain. In this example, the variance bar charts are also scaled to the waterfall charts to show the actual difference.

Tip: When designing visual reports like this, pay attention to labeling and headings. Clear and understandable headings help you tell the story while comments help you draw attention to significant data points.

This is a very complex page - it contains 10 charts in addition to the headings and labels. However, these visualizations comply with the IBCS standards, which ensures that you can easily read this page without losing sight of the overall picture.

Export your work

Zebra BI has predefined page templates. The page templates fit perfectly into a PowerPoint slide, so you just need to enter your data, create your charts and export everything to create a presentation. Page templates include an area for inputting data and an area for designing your reports. In the first area, you prepare your data that will tell your story. Then simply move your charts into the areas predesigned for them. This is one of the easiest ways to create an income statement for your presentation.

You can choose between a number of templates available, including pages that display up to 25 charts. This way, you can choose the page with the data density you require - from a 2x2 grid up to 5x5 grid.

Want to create income statements like these?

If you'd like to create income statements that you see in this article, click below to add Zebra BI to your Excel:

Video liên quan