Balance Sheet Report in PowerBI

Investopedia defined Financial statements as written records that convey a company’s business activities and financial performance.

Last time out, we created an income statement in PowerBI. Check here for a recap. Now we will look at balance sheets, one of the three financial statements used to determine a company’s economic performance.

So what is a balance sheet?

In lay terms, a balance sheet is simply a statement that shows what a company owns (assets), what it owes (liabilities) and what it’s worth (equity) at a particular point in time.

The asset of a company ranges from cash in the bank, office furniture, accounts receivable, patents, trademarks, buildings, equipment, technology etc.

A company’s liability includes accounts payable, i.e. what is owed to the supplier (whenever a company buys on credit, it results in accounts payable), short or long-term loans etc.

Concerning equity, consider Mr A has the following assets a laptop ($2000) and a phone ($1000). But Mr A made a part payment of $500 for the phone and has agreed to pay the balance in 30 days. Currently, Mr A is worth $2500 because he still has a $500 debt to settle.

So, how do we create a balance sheet using PowerBI?

Let’s go into PowerBI to see this.

First, we need a balance sheet template to import into PowerBI. A sample data and template have been provided here.

The following are the tables needed for the balance sheet report.

The balance sheet data have been transformed as the format provided is unsuitable for the model.

Plot twist.

We must create a relationship between the calendar table and the balance sheet data. The only column that links them together is the Year column which will have a many-many relationship, and we don’t want that.

What do you think is the way forward? Try to think of a solution before scrolling down.

There are two ways to go about this.

1. Convert the Year column to a date column, which gives you the first day of every month like this, and you can establish a relationship using the dates.

2. Use the TREATAS function. This is my approach.

The TREATAS function creates a virtual relationship between the tables specified. VALUES (‘Calendar’ [Year]) is used because unique values are required. With the aid of the VALUES function, we have established some virtual 1-many relationships between the Year column on the calendar table and the year column on the balance sheet data.

If we look at the balance sheet template we imported, the itemized rows contain the same values in the balance sheet subcategory.

We have rows like Total current assets, Total fixed assets etc., which are not included in the balance sheet data. Hence the need to calculate the totals separately and embed them in a single measure as was done for the income statement.

Below are the calculations for the totals.

1. Total current assets