Information, tips, news and announcements about SQL Server Analysis Services and PowerPivot directly from the product team.
Accounting and Finance Professionals have a new learning resource. The Profit and Loss Data Modeling and Analysis with Microsoft PowerPivot in Excel article, Excel workbook samples, and sample Access database provide scenario based data modeling and analysis help for self-service BI with PowerPivot and Power View.
The scenario describes how Finance professionals at Contoso Ltd. create a PowerPivot data model, Excel PivotTables, and Power View reports to analyze budget, forecasting, and other profit and loss account metrics, such as:
Extensive details of over 80 DAX formulas are provided, as well as tips for creating highly efficient and fast performing data models. The Excel 2013 version of the sample workbook also provides several dynamic Power View reports.
Click here to download the whitepaper: http://www.microsoft.com/en-us/download/details.aspx?id=38838
Thank you for providing this great learning resource. I am a CPA so I am very interested in this model. I wanted to ask a couple of questions. On P. 17 of the Word document you show a VTB measure (Variance to Budget) as the following: [Budget $] - [Actual $]. Shouldn't this be reversed, that is [Actual %] - [Budget $]. Looking at the math in the table for FY 12 and FY 13, it appears that it should be.
The other thing I noticed is that you use the All () function on the fact table - p. 21 "ALL('Finance Data'[Date])" and you use the Filter () function against the fact table - p. 51 "FILTER('Finance Data', RELATED(Accounts[Sub Class])="People"))"
From reading Rob Collie and Russo and Ferrari, I'm used to seeing All () and Filter () used against dimension tables. In fact one of Rob's rules is for Filter () is only use it against lookup (dimension) tables.
Arthur makes some great points. I have also read Collie, Russo, and Ferrari describe similar rules of thumb.
I have a busy P&L with financials for ASSUMPTIONS & RECOUPMENTS & DEAL NOTES, my issues is that i'm trying to capture the data onto a custom ingest template but the P&L is too busy with deals and notes, do you have any advise on how to capture the P&L data onto my custom template?