How To Create An Excel Data Table To Reflect Ending Balances

Handling cumulative GL balances

Handling cumulative GL business relationship balances in Power Pivot

Overview

Suppose you import into Ability Pivot monthly general (or nominal) ledger balances from your accounting/ERP system. Balance sail account balances volition usually exist the balance at the end of the month, which is what yous desire. But commonly remainder on each P&L GL account will be the cumulative balance to the end of that month from the offset of the financial year. For reporting purposes it will usually be more user-friendly to have bachelor in your Data Model the monthly P&L business relationship balances rather than the cumulative YTD balances. This topic is ane of the many specifically addressed in the Excelcraft Training Kit Excel Data Model Accounting Made Easy.

Logical Blueprint

You can summate these monthly balances by adding some calculated columns to your Information Model and adding some DAX functions in the format below. You shouldn't need to sympathize the precise workings of these DAX functions. This approach uses the Earlier() role; the objective is to catch the prior month'south cumulative account balance and subtract information technology from the calendar month you are looking at to get merely the monthly number. With later versions of the Data Model you lot can use DAX variables to achieve what the EARLIER() role does hither - a topic for another mail service.

Fiscal Month Key

In your date table (I telephone call information technology Time) yous will need to add a column for FiscalMonthKey. This is:

[Fiscal Twelvemonth Number] x 100 + [Number of the month in the fiscal year]

So if you have a fiscal year that ends on June xxx, yous might refer to the year from July ane, 2013 to June 30, 2014 as financial year 2014. Some might telephone call it financial yr 2013. It doesn't matter as long as you are consistent. And then August, for example, would exist the second month in that financial year. So using the formula explained above the Fiscal Calendar month Key would be:

2014 x 100 + ii i.due east. 201402

Like logic tin can be applied where an arrangement has thirteen iv-week periods in a fiscal year.

Chart of Accounts

I have a adequately simplistic chart of accounts (COA) tabular array that for each account flags information technology as a Flow or Remainder. Flows are accounts that tin can be added over time; these are typically P&L business relationship items such that a twelvemonth'due south worth of data is the sum of the months. Conversely, for balance sheet accounts (such as cash, inventory, payables), the balance for the year is the residue at the cease of the last calendar month of the year not the sum of the months. In database modeling speak, balance sheet accounts are referred to as semi-additive.

Chart of accounts extract

Monthly cumulative account balances

The third Excel linked table represents the balances exported from the bookkeeping/ERP system for 4 months:

Jun 2014 - financial 201412
Jul 2014 - fiscal 201501
Aug 2014 - fiscal 201502
Sep 2014 - financial 201503

For P&Fifty accounts, the balances are cumulative from the beginning of the fiscal twelvemonth. The Sep 2014 rest is the cumulative sum of transactions from Jul to Sep 2014.

Monthly balances extract

Power Pivot Data Model

The information model diagram looks like this:

Data Model diagram

About of the activity takes identify in the MonthlyBalances table:

Monthly balances table

The essence of this practice is to determine the prior period residue (which will be zero in the case of the first financial month of the yr). Then, for P&L accounts, subtract this from the (cumulative) calendar month in question to get the total transactions just for the month. As mentioned, this uses the Before() function thus:

[Prior menstruation YTD amt] =

Calculate(SUM('MonthlyBalances'[YTD amt]),

FILTER('MonthlyBalances','MonthlyBalances' [FiscalMonthKey]=EARLIER('MonthlyBalances'[FiscalMonthKey])-1),

FILTER('MonthlyBalances','MonthlyBalances'[Account]=EARLIER('MonthlyBalances'[Business relationship])))

The Summate() function sums [YTD amt] where the FiscalMonthKey is one less that the one we are looking at (i.e. the prior month), … and we are looking at the same account. The SUM function appears to be redundant merely is required. If your data has a department, price center, or like on each row, y'all would need an additional filter something similar:

FILTER('MonthlyBalances','MonthlyBalances'[Department]=EARLIER('MonthlyBalances'[Department]))

For want of a improve caption, the EARLIER() function allows you to find the same record (same account, same department) merely for the prior month - hence the "- i" in the formula above. Once yous accept that number y'all subtract it from the current calendar month, in the example of a Flow account, or go out it as is in the case of a Balance account:

=IF(RELATED('ChartOfAccounts'[Flow or Balance])="FLOW",[YTD amt]-[Prior Period YTD Amt],[YTD amt])

PivotTable extract

This report shows a selection of residuum sheets accounts ("Rest") and P&Fifty business relationship accounts ("Menses") with the original cumulative balance and the calculated monthly balance for P&50 accounts.

Cumulative-to-monthly PivotTable

For the balance canvas accounts the cumulative YTD number and the monthly number are the same. For the P&L accounts the monthly numbers are the difference betwixt the cumulative and the prior month cumulative. July is the start calendar month of the fiscal year in this example then cumulative and monthly numbers are the same. The prior month returns zero because the DAX formula is looking for FiscalMonthKey 201400 which has no data.

Concluding Thoughts

Similar many things Excel/Power Pivot/life-in-full general there are multiple ways to slice this cat, some probably better than the one I am suggesting above. My intention has been to minimize the number/complexity of DAX functions. Sometimes this is at the expense of adding actress calculated columns which swallow resources. However finance and accounting applications modeled in Power Pivot are non usually resources-intensive so petty harm washed, I remember.