# Profit and Loss Data Modeling and Analysis with Microsoft PowerPivot in Excel

## Analysis Services & PowerPivot Blog

Information, tips, news and announcements about SQL Server Analysis Services and PowerPivot directly from the product team.

### Profit and Loss Data Modeling and Analysis with Microsoft PowerPivot in Excel

Rate This

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:

• Aggregate, or value measures such as sums and averages for currency and headcount.
• Comparison measures such as year-over-year, year-to-date, and variance for cash flow and headcount.
• Performance, or ratio measures such as percentage, cost per head, and rate and volume variances. All of which calculate on different combinations of value and comparison measures.

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.

• Post
• 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.

Thank you.

Regards

Arthur Jenkins

ajenkins1969@outlook.com

• Arthur makes some great points. I have also read Collie, Russo, and Ferrari describe similar rules of thumb.

Any guidance?

• 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?

Page 1 of 1 (3 items)