Profit and Loss Dashboard Template

profit and loss dashboard template excel

The Excel Profit and Loss Dashboard Template display a P&L Statement and uses a grid layout, sparklines, and a rotated waterfall chart.

Generally, we use a profit and loss dashboard to display incomes and expenses over a month or quarter period. This tutorial will demonstrate how to transform a raw data set into a dashboard.

The template uses two views (monthly and yearly) to show the performance and provides a detailed analysis of revenues and expenditures.

P&L Template: Categories

Take a look at the ‘Data‘ Worksheet. Before we show you how the P&L statement works in Excel, quickly examine the main categories. In the Indicator column, you can find the following categories:

  • Total Net Revenue
  • Cost of Goods Sold
  • Gross profit
  • Total Expense
  • EBIT
  • Interest Expense
  • Income Taxes
  • Net Earnings
pl data

The data set contains data for 12 months and a Total column.

Build a Profit and Loss Dashboard Template

We want to compile a report into a visually effective spreadsheet in the example. If you want to build an effective P&L visualization and break down the structure, we recommend you use a waterfall or a rotated waterfall chart.

Here are some formulas to calculate each stage:

  • Gross Profit = Total Net Revenue – Cost of Goods Sold
  • EBIT = Gross Profit – Total Expenses
  • EBT (Earnings Before Taxes) = EBIT – Interest Expense
  • Net Earnings = EBT – Income Taxes
waterfall chart profit and loss dashboard

The practice file contains all formulas and charts. To build a rotated waterfall chart, you can use stacked bar charts and Scatter with Straight Lines. We integrated a helper Worksheet if you want to take a closer look at how to build the chart.

Profit and Loss Dashboard – Dynamic visualization

Another important part of the dashboard is the sparkline-based card layout.

Let us create the first card, in this case, the ‘Total Net Revenue’. To insert a tiny chart (sparklines), select the E7:P7 range on the ‘Data’ Worksheet. To highlight the minimum and maximum points, select the in-cell chart. Now, the ‘Sparkline’ Tab will appear on the ribbon. Under the ‘Show’ Group, activate the High Point and Low Point checkboxes. Finally, choose your preferred color for highs and lows.

using sparklines monthly data

The next part of a card is the actual value. Type an equal sign and link the data from the Data Worksheet to the Dashboard Worksheet.

To calculate the variance between the selected and the previous month, use the formula:

=IFERROR(IF($T$7=$E$6,0,IF($T$7=$F$6,F7/E7-1,IF($T$7=$G$6,G7/F7-1,IF($T$7=$H$6,H7/G7-1,IF($T$7=$I$6,I7/H7-1,IF($T$7=$J$6,J7/I7-1,IF($T$7=$K$6,K7/J7-1,IF($T$7=$L$6,L7/K7-1,IF($T$7=$M$6,M7/L7-1,IF($T$7=$N$6,N7/M7-1,IF($T$7=$O$6,O7/N7-1,IF($T$7=$P$6,P7/O7-1,"")))))))))))),0)

Then copy the formula down to get the value for all cards. Last, you can apply an optional step to make the profit and loss dashboard easy-to-readable. Apply conditional formatting!

Additional resources: