What's the best chart for showing P&L trends?

The cluster bar chart is often the first chart type to try when showing financial data from a Profit and Loss statement. This chart type allows you to see the relationship between revenue and cost items for a year, but, it is difficult to compare the trends over time.

We turned this chart into a stacked bar chart with a net line and CAGR column to solve this problem. In this chart, you can see the relationship between the revenue, cost and net income in a specific year, but you can also easily see the trend. The CAGR column allows you to highlight which P&L line items are growing the fastest. This P&L trend chart is one of the featured charts in 10 Finance Charts, a guide to presenting financial insights.

Here's how we did it.

First, we made the cost series negative values and then converted the chart to a stacked bar. By right clicking on the Net Income series, we changed it to a net line on the chart. Then, we added a CAGR column to the chart (Column G below) using the CAGR formula embedded in the data sheet: For Revenue, it is =cagr(B2, F2,4). We copied this formula down to calculate the CAGR for the cost and net income series. Then, we right clicked on Column G to set it as a CAGR column. We added a legend to the chart to make it easy to connect each CAGR value to a series and chose Align to CAGR as the position for the legend.

You'll notice that the data in the data sheet is shown in millions. We wanted to simplify the display of labels on the chart so we converted the data to billions, without altering the underlying data using the Data Scale (^3) feature in the Format Chart task pane under the Values tab (see below).

We used this same methodology to create one of the most popular Charts of the Week, Comparing the Profits of Apple, Amazon, Microsoft, Google and Facebook.

To learn more about how to use CAGRs, check out this video: