Creating P&L type reports with PowerPivot?

[dk]

Hi there

I am a bit late to the PowerPivot party but I have finally downloaded it and installed. I have not had a great deal of time to play with it and research but I'm wondering if it could help with a problem I have.

Currently I have an Access database that has 3 key tables:

BALANCE - fields are Year, Period, Cost Centre, Account Code, Balance Type (Actuals, Forecast, Budget) and Amount.

BUSLINES - many fields but key ones are Cost Centre (primary key), Business Line, and Business Segment and State.

ACCOUNTS - again, many fields but key ones are Account Code (PK) and P&L mapping (i.e. 1 or more account codes maps to a P&L Line such as "Salaries and Wages" or "Transaction Fees"

Would it be possible to use PowerPivot to create a single report but with slicers for State, Business Line, Segment, Cost Centre and then have the report update based on what has been selected on the slicers? I have created a pivot table that does this and it works fine, but I need a nicely formatted P&L with nice subtotals, variance $ and %, and so on i.e. I don't think a pivot table would be suitable in terms of something that I could hand to my CEO.

Ideally what I'd have is a single Excel file that I update once a month and people can then open and just choose whatever criteria they want, the report updates and they can print or view on screen as they wish. Does this make sense? If so, would I be able to use the DAX functions within the report or are they only for use within the PowerPivot source data? I am not after a detailed answer but more of a "no, you've missed the point entirely!" or maybe some ideas on how I could achieve this.

Happy to provide more info if needed.

Thanks
DK

 

[powerpivotpro]

Hi DK!

There are a bunch of posts on this topic and related topics:


http://www.powerpivotpro.com/2011/09/profit-lossthe-art-of-the-cascading-subtotal/
http://www.powerpivotpro.com/2011/10/profit-loss-part-2compare-and-analyse/
http://www.powerpivotpro.com/2011/10/profit-loss-part-3return-on-sales-and-variances/
http://www.powerpivotpro.com/2010/0...-in-powerpivot-using-dynamic-measures-in-dax/
http://www.powerpivotpro.com/2011/12/cash-flow-statement-in-powerpivot/
http://www.powerpivotpro.com/2011/11/trended-moving-averages/

And in general any of the posts under the Accounting/Financial category:

http://www.powerpivotpro.com/category/accountingfinancial-techniques/

Note that some of the techniques covered in those posts are on the more advanced side of the spectrum, kinda like jumping straight from the SUM function in Excel to array formulas. I'm not saying you won't follow them, just warning you that there's a lot of value to get out of PowerPivot before you even get to that level

Let me (and David, who wrote most of them) know what you think, or if you hit snags ok? We're happy to help.

 

[dk]

Thanks Rob, there was some very useful info there and I think I need to add your website to my favourites!

I have read through the first two articles written by David Churchward and it seems as though you can indeed do some very fancy stuff. I think I need to go away and digest this and play around with some examples. What he does in the second article is basically what I'm trying to do (actuals vs budgets, variance columns and the ability to show month to date, or year to date, etc) so that will definitely be helpful.

Cheers
DK

 

[powerpivotpro]

Happy to help!

For a relatively simple intro to actuals vs. budget, try this two-part series as well:

http://www.powerpivotpro.com/2012/01/salesbudget-integrating-data-of-different-grains/

http://www.powerpivotpro.com/2012/01/data-of-different-grains-a-followup/

 

[David Churchward]

Hi DK

I'm intrigued to find out how you've been getting on with this. If there's any issues, please shout.

Hope it's going well
Cheers
David