Accountants, auditors and CFO’s often want to know how Power BI can assist with month end processes and reporting functions.

In this article I describe some Power BI ideas you can implement to provide your business additional options when it comes to reporting and month end audit tasks.

One of the key pains for most professionals producing or managing accounting data is syncing and verifying external reporting applications to live accounting systems via a trial balance.

At Five Ledger we have found using Power BI can make the compliance and reporting function more efficient and reduce the numerous repetitive manual tasks adopted by most report authors.

For those who are not accountants or auditors, the single source of truth and trust of any business is a document called a trial balance. This document acts as a reconciliation mechanism which provides a level of comfort in the data being presented and gives your accounting processes an easy way to agree data pulled into a Power BI reporting model.

A trial balance is simply the output of two tables from your accounting system generated against a given time period. Its made up of two types of accounts being profit and loss and balance sheet accounts.

The first table is your chart of accounts which we refer to as an account dimension table. This table holds information like the account number, the account name, current balance and other important information like the header and sub header structure of the chart of accounts for the business.

The second table is a fact table which holds all the general journal transactions made in your accounting system. In some cases you can get summary journal values by month if your accounting system generates this data (which MYOB does) or you can generate the monthly, quarterly or annual values in Power BI using some power query transformations to adjust the granularity of the data.

Importantly, the result of all the journal entries will/should always be zero as per the rules of double sided accounting. Note most accounting system journal entries will have a flag or value range to indicate a positive (debit) or a negative (credit) entry. The way this fact data and chart of accounts dimension table is structured will determine how to approach writing the correct formula to output each side of the journal entry.

I always recommend clients check with your software providers website to understand how to the journal entries and chart of accounts are structured so you can achieve the best method to retrieve and organise the data.

Here is an example of the two tables from the MYOB AccountRight accounting package which is the most used accounting software in Australia.

You can see below the chart of accounts dimension table and the journal transactions fact table have been linked via the Account ID field to provide a reporting relationship. This relationship allows you to plot the journal transaction values against the account dimensions it has been coded to, just like the trial balance does.

As mentioned earlier, some accounting systems don’t use negative numbers in fact tables and all the transactions are shown as positive amounts in the raw data from the API.

To ascertain whether the journal amount is negative or positive, a flag has been implemented called “IsCredit”.

This journal status flag allows the report author to distinguish which side the journal line entry belongs to i.e. debit or credit journal. You can write formulas to ascertain debits and credits and also a calculated column if you want negative values.

Here is what the raw fact journal table looks like initially using MYOB’s API.

Examining the sales entry below in orange we can see the individual line entries the sale is made up of for invoice number 30.

Breaking down the lines of the sales journal above, the entry has a net value of zero where the debits and credits offset each other.

The single journal entry consists of four lines which I have split using the flag Is Credit (for debit or credit entries) and the linked account name. Note the name is generated from the Account.UID field in the fact table which is linked via a one to many relationship with the account dimension table that holds the detailed information about each account.

So lets try to build our trial balance now that we have the raw data in Power BI.

The first step is to create a calculated column to turn credit values into negative in my fact table and three measures to obtain all journal value balances with a sign, debit journals and credit journal values.

Here are the formulas I have used to achieve this result below:

Calculated Column added to Fact Table
= Table.AddColumn(DataTypes, “Amount Sign“, each if [IsCredit] = “true” then [Amount]*-1 else [Amount])

Measure 1 All Journal Amounts using Calcualted Column with + or – sign
Journal Amount Sign = SUM (‘FxGeneralLedger'[Amount Sign])

Measures 2 Credit Journals
Journal CREDIT = CALCULATE([Journal Amount Sign], ‘FxGeneralLedger'[IsCredit] = “true”)

Measure 3 Debit Journals
Journal DEBIT = CALCULATE([Journal Amount Sign], ‘FxGeneralLedger'[IsCredit] = “false”)

We can now see below, the updated journal fact table with the new calculated column added to show negative value amounts.

 

 

 

 

 

 

 

 

 

 

 

 

Note that any account can have a debit or credit value so this step is useful to work out the balance in each account and allow the values in each account to be summarised correctly.

Adding up the original amount field will not give you the correct account balances as the journal entries are all positive in the raw API data and a flag determines whether its a positive or negative value.

Okay so that’s it , now we have our fact and dimension tables, written the calculated column and created required measures.

We can now output the account name and journal measures to produce a trial balance and check everything balances. i.e. Debits = Credits

Adding the calculated column measure and a date calendar allows analysis of the fact data by any time period and provides closing balances as shown below.

Its super helpful as an accountant or auditor to see the lifetime and periodical movements in each account that comprises the balances shown in a client’s accounting system.

Now that we have built our model we can simply just refresh the data model in a few minutes with the latest data file from the clients accounting system and check the key control balances are in sync.

I hope you enjoyed reading this blog and gained some ideas of what is possible with Power BI desktop software.

If you haven’t downloaded this free application or need help in your every day reporting and management tasks feel free to contact our help desk for more information.

I have written other blogs on Power BI which you can also read from the link below.

Blogs