Excel and Power Pivot for accountants and finance managers
BI tools in general and Excel in particular have long been the resting place for summarized accounting data. Reasons include:
- to create reports and dashboards with precision formatting and charting not available in the ERP or accounting software
- to merge actual data with budget/plan data for business performance measurement
- to carry out ad-hoc analysis and reporting
The degree of success in analyzing and reporting on accounting data in a BI tool depends to a significant degree on how the data repository is architected. There is not always a clear right answer; there are pros, cons, and compromises. This page has articles and links to articles describing how accountants, finance managers and finance professionals might leverage Power Pivot as a robust platform to handle a variety of financial reporting and analysis needs.
Handling cumulative general/nominal ledger account balances in Power Pivot
How to convert P&L cumulative balances to monthly balances in Power Pivot. Read more >
Managing Reporting Journal Entries in Power Pivot
If you create financial statements in Excel, you may find yourself needing to apply adjustments or reporting journal entries to your financials. This video
shows how Power Pivot can help.
Ever tried creating a financial report using a PivotTable?
then you know it can be frustrating getting accounts and sub-totals where you want them. And forget inserting rows or columns for spacing or to add ratios or commentary; Excel frowns on that.
So how do you lay out your financial or management reports exactly the way you want it? The answer is Excel Cube Formula Reports
driven by Excel Cube Functions
. Read more >
Business Modeling and Planning with Power Pivot
Power Pivot for Excel does not support write-back to a data model and as Personal BI it is not intended for shared usage. But it still offers some significant planning capabilities. Read more >
Measure for Measure
When working with accounting data in Power Pivot one option is to make each account a measure; this can work if you have only a few accounts, such as [Measures].[Sales], [Measures].[Cost of Sales], Measures].[Overheads]. Using measures as accounts in this way also makes it easier to do modeling in Power Pivot. However things get complicated if you require time intelligence calculations such as QTD, YTD, same period last year etc. Then you need to create measures for [Sales MTD], [Sales YTD], [Sales Last Year], [Cost of Sales QTD], [Cost of Sales YTD], and so on. Read more >