Financial Reporting with Dashboards in Power BI

Chapter 1:  Installing Power BI

I suspect you already have Power BI, but in case you need to install it, here are the steps.

Click on: Downloads | Microsoft Power BI

The page below opens.

For this book, you only need the free desktop version. When you click on Download, you will be prompted to sign in or create an account.

If you do not have an account, you will be asked to create one as shown in the message below.

After your account is confirmed and you are signed in, the downloading process takes you to the Microsoft store.

After you open the store, you will be taken to the screen below where you only have to click on the big blue button.

After installation, you will have the screen below. You only have to click on the yellow Get started.

You do not have to buy anything that is offered. Power BI desktop is free.

Now you can begin creating data models and reports. It starts with the screen below.

This screen is what you see every time you start Power BI.

It has a ribbon, tabs, layouts, Filters, Visualizations, and Fields panes.

Fields: This section shows all the tables uploaded or created. You can expand the tables to see all columns. They are in alphabetical order, not necessarily in the order of table columns.

The File tab is where you go to save, open, or create a new report.

After clicking on Save, we will continue to build the financial statements in this file.

Chapter 2:  CHAPTER 2: Financial data

In order to show how to create financial reports, this book uses the data from ABC Ltd., a fictional company with operations in 6 cities located in Canada and USA. The data is comprised of 4 Excel files as follows:

Summary TB

Chart of accounts

Account mapping

Budget

This book assumes that the reader has access to these documents in an organization. We can use all kinds of data for different financial reports provided that the data is complete and accurate. However, the quality of the financial reports depends on the data we are using.

ABC’s summary trial balance is in Excel format and can be downloaded at:

https://monica.scheianu.com/download/ABC_TB.xlsx

This summary TB gives the opening balance, the debits, credits, and the closing balance of each account. Only the closing balance is used for creating the financial statements. I use the TB in this form as a one more check: Closing balance = Opening Balance + Year-to-Date Debits – Year-to-Date Credits.

Some accounting systems give only the closing balance for each account in the summary TB and that is all it is needed in order to create the financial statements. The important feature of the TB is that the sum of all closing balances is zero. This is how we check that the data we have is complete. If the sum of the ending balances is different from zero, something is missing from the data and we cannot create reliable reports.

The chart of accounts of ABC Ltd. can be downloaded at:

https://monica.scheianu.com/download/ABC_Chart_of_Acc.xlsx

A company’s chart of accounts depends on its operations and the options available in the computerized accounting system it is using. An account has a number and a description. In many cases the length of the account number depends on how complex the operations are. For some companies, a group of 4 digits is enough as account number. ABC Ltd. is more complex, but does not go into extreme length of characters for its accounts.

ABC Ltd. identifies its accounts in the form xxx-xxx-xxxxxx-xxxx

The first 3 digits represent the cost center.

The next 3 digits represent the location. The location is also identified by name in a different column.

The group of 6 digits identifies a department or a project. I created this fictional company to show an example where it is possible to have many projects and each can have its own revenue and expense accounts. Normally the project managers will need periodic financial reports to see how they are doing compared to the budget they were given. They will have to be provided with reports in order to know how much revenue and expenses each project generates. In the next chapters I will show how these questions can be easily answered by creating the reports in Power BI. Once created, the reports are easily updated periodically with new data so that the project managers can oversee the