How do I use the Power BI Financial Performance Overview dashboards?

Learn how to use the Power BI Financial Performance Overview dashboards.

This template is a plug and play template that provides an overview of an organization or group of organization’s financial performance over different periods. This template will help you analyse and understand how your business performed on an MoM, QoQ and YoY basis.

:bulb: TIP:
To make the most of this template you will need to download and install the Power BI desktop by using this :arrow_down: download link.

View the Dashboards :chart_with_upwards_trend:

Xero View dashboard with sample Xero data - :link: open dashboard

QuickBooks Online View dashboard with sample QuickBooks data - :link: open dashboard

Allocating an organisation or group

Once you have successfully chosen a plan and purchased the template you will be directed to the “My paid templates” section of the DataDear website. Here you will need to allocate the organisation that you will be using this template for.

Click on the Manage allocations button

Then click on the green box and choose the organisation or group you desire.

:bulb: NOTE:
Once an organisation has been selected it cannot be removed or replaced with another organisation or group.

Using the Templates

The zip file downloaded contains an excel file and a power BI file. Open the Excel file, in the “Notes and Controls Tab” enter the parameters you desire.

Continue to the “Profit and Loss Data” tab, connect your organisation or group and click Refresh Current Sheet (quick). Once the report is done refreshing make sure to click Save.

After you have downloaded and installed the Power BI desktop on your device. You can open the Power BI template by double-clicking on the Power BI template to automatically launch the template.

Once you launch the template, you will be prompted to type in the file path of the underlying data associated with the report.

To locate the File Path, follow the steps below:

  • Open file explorer and go to the location of the excel file previously saved.

  • Select the excel file.

  • Click on “copy path” on the ribbon under the Home tab.

  • Copy and paste the path into the box.

  • Remove the “double quotation marks” on both ends and click load.

:bulb: NOTE:
Once your Power BI desktop has been loaded with your data, go ahead and select the respective Year and Month name to be analyzed, you will find this at the top right of the dashboard.

Understanding the Template

The template consists of two reports, namely:

  • Financial Performance Overview Report
  • Income statement Report

Report 1- Financial Performance Overview

This report gives a snapshot of the financial performance of an organization or group of organizations, it has different visuals which provides a clearer picture of the monthly and yearly performance on a relative basis.

This report contains the following visuals:

  • The MTD Visual : The MTD visual shows the MTD revenue, cogs, gross profit, total expenses, operating profit. These MTD visuals are KPI card visuals with relative and absolute MoM comparison values.

  • The YTD Visual : The YTD visual shows the YTD revenue, cogs, gross profit, total expenses, operating profit. These YTD visuals are KPI visuals with relative and absolute YoY comparison values.

  • Slicers: We have the organization name slicer, year and month name slicers. The purpose of the slicers is for you to select one or multiple items inside the slicer to visualize the result across all the visuals. For example, you can select a particular organization name by clicking on the drop-down to view the performance of the organization, while to select multiple organization, hold the CTRL-key to select multiple organization. Likewise, you can select the month and year, you want to visualize the result.

  • Bar Chart Visual: The Bar chart visual visualizes the total revenue, total expenses and operating profit on a monthly trend.

  • Matrix table Visual: This visual is showcasing the profit & loss account every month with absolute and relative change, the value column is dynamic, this implies that if no month is selected, it will showcase the annual values.

Report 2- Income Statement

This report shows the quarterly and yearly income statement of an organization or group of organizations.

This report contains the following visuals:

  • Matrix table Visual: This visual is showcasing the profit & loss account every quarter, you can visualize any year by selecting your preferred year from the year slicers.

  • KPI Card Visual: This visual shows the revenue, cogs, gross profit %, total expenses, operating profit % with a relative and absolute YoY comparison.

  • Slicers: We have the organization name slicers, tracking category 1 slicers, tracking category 2 slicers, year and month name slicers. The purpose of the slicers is for you to select one or multiple items inside the slicer to visualize the result across all the visuals. For example, you can select a particular organization name by clicking on the drop-down to view the performance of the organization, while to select multiple organization, hold the CTRL-key to select multiple organization. Likewise, you can select the month and year, you want to visualize the result.

  • Area Chart Visual: This visual shows the cumulative revenue and cumulative expenses on a monthly trend, the cumulative expenses comprise COGS, Overhead and expenses.

Account Type Definition for Xero

Revenue- This includes all the account names that belongs to Revenue or Sales Account type.

COGS- This includes all the account names that belongs to Direct cost account type.

Gross Profit- This is the difference between Revenue and COGS.

Expenses- This includes all the account names that belongs to Expense account type.

Total Expenses- This includes all the account names that belongs to Direct cost, Expense and Overhead account type.

EBITDA- This is the difference between Gross profit and Expense & Overhead.

Operating Profit- This is the difference between the EBITDA and depreciation. Where there is no depreciation, the operating profit is the same as EBITDA.

Account Type Definition for QBO

Revenue - This includes all the account names that belongs to Income Account type.

COGS- This includes all the account names that belongs to Cost of goods sold account type.

Gross Profit- This is the difference between Revenue and COGS.

Expenses- This includes all the account names that belongs to Expense account type.

Total Expenses- This includes all the account names that belongs to Cost of goods sold, Expense and Other expense account type.

EBITDA- This is the difference between Gross profit, Expenses and other expenses.

Operating Profit- This is the difference between the EBITDA and depreciation. Where there is no depreciation, the operating profit is the same as EBITDA.

Refreshing the Report

To refresh your data in this report, click the Refresh ribbon under the Home tab.

Saving the Report

To save your report, go to the File tab and select Save as, to save your report.

:warning: IMPORTANT:

The following are very important to take note of:

:white_check_mark: Do not change the location of the excel file.

:white_check_mark: Do not change the name of the excel file.

:white_check_mark: Do not change the table name of the sheet.

:white_check_mark: Do not change the name of a column.