Easy Excel PivotTable Profit & Loss Statements - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Patrick
Hi Mynda, great tutorial, as always. I have one question: at 0:22 you show the structure of your data table with actual and budget in two separate columns. I am just starting to get to grips with the process of extracting, loading and transforming messy data using power query (using some of your other excellent videos) with a view to getting it into this kind of format so I can then pivot it. It seems to me that the actual and budget columns could be unpivoted into a single column, but evidently you have elected not to do that in this example. I was just wondering whether there is any specific best practice on when to unpivot data and when not to?
reply
Hi Mynda, great tutorial, as always. I have one question: at 0:22 you show the structure of your data table with actual and budget in two separate columns. I am just starting to get to grips with the process of extracting, loading and transforming messy data using power query (using some of your other excellent videos) with a view to getting it into this kind of format so I can then pivot it. It seems to me that the actual and budget columns could be unpivoted into a single column, but evidently you have elected not to do that in this example. I was just wondering whether there is any specific best practice on when to unpivot data and when not to?
reply
Sifo
Hi, thank you ever so much for your amazing videos and explanations. Quick question, could you advise how to group budget and actual for each month together. Presently, I am only able to have columns for months with actuals followed by columns for budget.
Is there a way I can have actual and budget of, say for the month May, next to each other in columns then followed columns for actual and budget for June, then followed by columns for actual and budget for July, and so on and so forth.
Grateful if you could help.
reply
Hi, thank you ever so much for your amazing videos and explanations. Quick question, could you advise how to group budget and actual for each month together. Presently, I am only able to have columns for months with actuals followed by columns for budget.
Is there a way I can have actual and budget of, say for the month May, next to each other in columns then followed columns for actual and budget for June, then followed by columns for actual and budget for July, and so on and so forth.
Grateful if you could help.
reply
Florencio
As usual, very nice video. I have a question about the columns in the pivot table: It seems that on the pivot tables the default is to get the total sum, but I'm more interested in averages and standard deviations. I know how to change it, but it appears I have to do it column by column and I often have a bunch of them. I tried multiple selection, but it only applies it to the first column. Any suggestions to either change the default or apply the change to all columns and not just one at a time?
reply
As usual, very nice video. I have a question about the columns in the pivot table: It seems that on the pivot tables the default is to get the total sum, but I'm more interested in averages and standard deviations. I know how to change it, but it appears I have to do it column by column and I often have a bunch of them. I tried multiple selection, but it only applies it to the first column. Any suggestions to either change the default or apply the change to all columns and not just one at a time?
reply
Jason
Hi, Thank you for the tutorial. It is very clear and informative. Can we add subfields in the pivot table? Income - COGS = Gross profit. After the Gross profit level, I want to give the break up of expenses. The heading should be -Less Operating Expenses- and under this group A) Staff Costs and under this, there will be accounts. Then - B) Rent Expenses- and under this, there will be all the rent expenses. Thank you
reply
Hi, Thank you for the tutorial. It is very clear and informative. Can we add subfields in the pivot table? Income - COGS = Gross profit. After the Gross profit level, I want to give the break up of expenses. The heading should be -Less Operating Expenses- and under this group A) Staff Costs and under this, there will be accounts. Then - B) Rent Expenses- and under this, there will be all the rent expenses. Thank you
reply
Farzona
Thanks for this wonderful video tutorial! I'm using this technic for YTD (for 12 months in columns) financials, and want to add the calculated item for GM%, i.e. GM%=Gross Profit/Total Revenue, so the calculated item appears as a row under each month column. Is it possible?
reply
Thanks for this wonderful video tutorial! I'm using this technic for YTD (for 12 months in columns) financials, and want to add the calculated item for GM%, i.e. GM%=Gross Profit/Total Revenue, so the calculated item appears as a row under each month column. Is it possible?
reply
haresh756
Hello Maam Super insight about calculated item, thank you! how can we use this function in power query? when loading the data to power query and making pivot table, calculated items appearing deactivated, please share your thoghts.
reply
Hello Maam Super insight about calculated item, thank you! how can we use this function in power query? when loading the data to power query and making pivot table, calculated items appearing deactivated, please share your thoghts.
reply
efren
This is awesome. I tried to use this in my work, however, it was very slow to calculate maybe because the file is too large. is there a way to calculate quickly using the calculated item, or this is the fall back of pivotable?
reply
This is awesome. I tried to use this in my work, however, it was very slow to calculate maybe because the file is too large. is there a way to calculate quickly using the calculated item, or this is the fall back of pivotable?
reply
Sam
Finally I used this method for an appropriated P & L, splicing data from two systems a CRM and Sage Accounts 50c! Brilliant! You make me look like a genius at work! Thank You Very Much and in Welsh -Diolch yn fawr i chi-! :)
reply
Finally I used this method for an appropriated P & L, splicing data from two systems a CRM and Sage Accounts 50c! Brilliant! You make me look like a genius at work! Thank You Very Much and in Welsh -Diolch yn fawr i chi-! :)
reply
itech
Excelent explanation ! But too nice to be true... if as it is almost always the actual and budget are not different fields but rather one field sorted by year / budget (it is not possible then to calculate the variance )
reply
Excelent explanation ! But too nice to be true... if as it is almost always the actual and budget are not different fields but rather one field sorted by year / budget (it is not possible then to calculate the variance )
reply
Goo
Not sure if or how this would work; database consists of transaction lines with in the same column the lable of either Actual or Budget. Is it possible to make a variance measure out of this and if so, how?
reply
Not sure if or how this would work; database consists of transaction lines with in the same column the lable of either Actual or Budget. Is it possible to make a variance measure out of this and if so, how?
reply
Add a review, comment
Other channel videos
