Accounting with Power Pivot: Reporting Journal Entries

If you are an accountant who prepares accounts or financial statements using Microsoft Excel, then you are not alone. The practice is very prevalent even where the accounting/ERP system includes report writing functionality that accommodates profit and loss statements and balance sheets. Explanations for this abound; here are some examples:

  • Excel offers formatting precision not found elsewhere
  • Interim financials can be prepared, applying period-end adjustments in Excel, rather than putting them through the accounting system and reversing them out again
  • Excel provides the opportunity to do some what-if analysis for creative minds
  • Data, potentially from other sources, such as budget/forecast, industry statistics can be displayed alongside actual data
  • Excel is a tool that most knowledge workers are familiar with

This type of flexibility comes at a price; there is a danger that errors will be introduced as data is moved from the accounting/ERP system to Excel, or when Excel functions are added to the presentation sheet. The concept of a column of numbers that doesn't add up to the displayed column total because of a bad SUM() function will not be new to most Excel users.

Power Pivot for Excel, an add-in to Excel 2010 and certain flavors of Excel 2013 provides an analytic platform that is more powerful and robust for certain types of data manipulation than raw Excel. It puts guard rails around calculations, preventing parts of a calculated column (as they are called) being accidentally deleted and prohibiting formulas that are unintentionally inconsistent down the column (Excel tables are somewhat similar in this regard). And Power Pivot supports robust table relationships, powerful DAX functions and much, much more.

There are several ways to get accounting data into Power Pivot. Power Pivot supports data import from numerous sources including databases and text files such as .CSVs. For smaller volumes, data can be pasted into an Excel table that is linked to the Power Pivot data model. After importing account balances or a trial balance into Power Pivot, it is likely that some adjustments will be needed, possible closing adjustments in the preparation of interim financials, or adjustments that have not yet made their way into the general/nominal ledger (for additional accruals and the like). (Video)

I'll refer to these as reporting journal entries, which are added to the data model using a linked table like this:

Reporting journal entries

The corresponding data model table is basically the same:

JE data model table


Then there are a few steps required to apply these entries to the trial balance imported as described earlier. Here are the ones based on the sample data used in the screen shots:

  1. You will need to have lookup tables (also called dimension tables) in your Power Pivot model for:
    • Time lookup table

    • dates - only two in this example, and the table is called Time


    • the chart of accounts - this can be imported from the accounting system; only the account code and account description are required but additional metadata can be included (more on that later)


    • Chart of accounts lookup
    • if your account balances are further categorized by department or entity (for multiple companies), you will need tables for those. I have kept the example simple - you can probably work out how to add department and/or entity if you need to.
  2. You will need to create relationships between your data tables (TB and reporting journal entries) and your lookup tables as shown in the screen shot below:
    Data model diagram
  3. To the TB table, I have added two Adjustment columns - one for debits and one for credits. Each of these uses the DAX functions SUMX() and RELATEDTABLE(). You don't need to understand the way this works. I'm not sure I do. But the RELATEDTABLE() function allows you to traverse a Power Pivot table relationship in the opposite direction to the relationship and sum up all the debit adjustments for the particular combination of date and account for this particular row of the TB. And similarly for the credit adjustments.

    TB table
  4. I also added an Adjusted TB column that is the initial TB column adjusted for the TB Journal Dr and TB Journal Cr. The extra column, ValueToReport, is optional and is explained later.

    Adjusted TB column

The Profit & Loss account and balance sheet have been created using Excel's cube functions (and regular functions). Cube functions are well suited for this type of application where precise placement of reported (summarized) information is important.


Financials

Circling back to a couple of topics postponed above, the Chart of Accounts table has additional so-called metadata about the accounts, such as the normal balance for the particular account: Plant & Machinery gets a D and Sales accounts get a C. In the ValueToReport column, this switches the sign on credit balances (Cs) so that in, say, a Profit & Loss report, Sales aren't displayed as negative numbers. In addition the chart of accounts has some columns that group certain accounts together (Report Group 2 and Report Group 1) for reporting at more summarized levels than the general/nominal ledger account code level.

Clearly it is important that the reporting journal entries that are intended to be permanent should be booked in the accounting system and removed from any off-line reporting system such as this. All reports produced in Excel should be tested for accuracy, preferably by someone other than the preparer. Compare Excel-produced numbers with those in reports from the accounting/ERP system.


Home

 

© 2011-17 Excelcraft.com. Microsoft and Excel are registered trademarks of Microsoft Corporation in the USA and other countries.
Template design by Andreas Viklund

Terms of Use            Privacy            Cookies            Contact