Financial Models

[ammartino44]

Hello. I've watched a couple of videos and read a few blogs on creating income statement reporting in power pivot. I've come up with something decent but one thing I've never been able to figure out is how to get a line item like TOTAL OPERATING EXPENSE that sums other operating expense and personnel. Cascading subtotals works ok but it can't get you to to TOTAL OPERATING EXPENSE. Does anyone have any dummy financial statement models that they have created? That would be awesome. 

[Waseem]

Hi

I created actual financial statements with Power BI. How I did that is as follows:

Step 1 : Extract trial balance on GL code level

Step 2 :  Create another excel file for mapping of these GL codes. Codes will be in first column (say column A). Againts each code, you can write if it is Cost of Sales, operating Expense or Non-Operating Expense. You should create multiple levels for each GL code, meaning in first column (say column B) against code lD write cost description (Manpower, Repair & Maintenance etc.) and in second column (say column C) mention its classification as Cost of Sales or operating Expense.

Step 3: Add both files to Power BI.

Step 4 : Create relationship between Mapping file (column A containing GL code) and Trial Balance respective column that contains GL code. You wil see two files in Power BI, i.e. GL and mapping file.

Step 5 : Drag Column C (containing mapping of Operating Cost) and amount column from Trial Balance source file. 

Thats it. you will get summary of Operating Cost. In case you need more help, please let me know. You can send me your sample file and I can do it for you.

Cheers

[ammartino44]

Hi Waseem. Were you able to compute TOTAL OPERATING PROFIT? Would you mind sending me a dummy version of your BI model? I'd appreciate. 

[BhaveshPatel]

You can check biaccountant.com for this. She got some nice dashboards on financial modelling in powerbi.

[Baskar]

can u please share some sample screen