KB20200519/04: Sage 200 Evolution Tip & Trick – How to Create a Simple Financial Dashboard in Excel

When viewing numerical data in Excel, you may at some point want to represent this data visually. This gives you the ability to easily analyse large amounts of data. Charts and graphs also make your report look more professional and can even help when creating dashboards. 

In this Excel tip, we will show you how to create a simple financial dashboard:

We are going to work with data from a simple income statement. We will create two charts: a waterfall chart and clustered column chart to show the comparison between the different sections of an income statement (revenue, cost of sales, expenses, gross profit and net income).

Below we have an income statement showing the revenue, cost of sales, expenses, gross profit and net income for the first quarter as well as the year to date.

Now let’s insert two charts to make this report more professional looking and easier to analyse.

First, you need to select the data you want the charts to represent:

  • Ensure the column headings are highlighted.
  • Excel will detect data in the selected cells and find a pattern.

Once you have selected your data, do the following:

  • From the main menu in Excel choose Insert.
  • Go to the Charts section.
  • You should see the chart options. Click on Recommended Charts.

A window should appear showing the available chart types:

  • Choose the first option to insert a clustered column. Clustered column charts are used to compare values across categories.
  • Click OK.

A chart is now inserted in your spreadsheet:

  • Click on the chart.
  • Notice how the data used in the chart is automatically selected and color coded in the income statement. This shows where the chart is pulling its data from.

The chart has a legend showing the different areas of data represented. The blue columns represent data for quarter 1 while the grey represents the Year to Date values.

Click on any of the blue columns. Notice the data selected in the income statement shifts to just the Q1 column and the corresponding income statement categories (Year to Date is no longer part of the selection).

Now click on any of the grey columns. The year to date column of the income statement is now selected (Q1 is no longer part of the selection).

Now, let’s insert a waterfall chart using the same method.

These chart types show running totals for each category. They also show increases and decreases (negatives and positives) where available.

The result should look like the image below:  

You have now created a simple financial dashboard showing the income statement for Q1 and year to date, with two dynamic charts to assist you when analysing your financial data.

We also have some parameters (year and period) and if those parameters were to change, the charts will automatically adjust to reflect these changes. No need to recreate the charts!

For further information please email info@sysfinpro.co.za or call us on +27 12 880 0258