Note: This is a not an Excel tips post. It is a diary of one of the most awesome conferences I have ever attended.
I just finished attending PASS Business Analytics conference in Santa Clara (USA) and am now heading back home to India. And it has been one of the most fun, uplifting and educational experiences of my life. I met so many remarkable people in this trip.
Just to name a few, I met Dan Fylstra (one of the pioneers of VisiCalc & founder of Solver), Bob Phillips, Ken Puls, Jordan Goldmeier, Oz Du Soliel, Rick Grantham, Szilvia Juhasz, Zack Baresse, Kevin Jones, Avi Singh, Chris Webb, Rob Collie, Bill Jelen, Scott Senkeresty, Matt Allington, Jon Acampora, Marco Russo & Jen Stirrup.
I also felt fortunate to meet many of Chandoo.org fans, followers, customers & supporters who attended the conference. It was non stop fun for 3 days.
As if meeting all these great people, sharing a conversation, beer, snack, moment or ride (in a cramped backseat with 2 other Excel MVPs) with them was not enough, I also got to attend few of the amazing sessions at PASS BA.
- I learned CUBE formulas from Bob Phillips
- Introduction to R from Jen Stirrup
- Power Query trickery from Chris Webb
- Charting best practices from Jordan
- Keynote presentations by Mico & Carlo
I wish I had the time to attend more sessions. But I was busy teaching a few or meeting people.
All in all, in one word, PASS Business Conference has been AWESOME.
Couple of funny & interesting experiences from the conference:
5 MVPs in a car
At the end of day 3 (April 22nd), a bunch of us were sitting at the hotel lobby bar and chatting. When I asked Ken (Excelguru) what they are doing for dinner, Ken said Zack is taking him for dinner. Then Zack looked at me and said, “why don’t you tag along?”
By then we were 4 people – Ken, Zack, Wessex Bob & myself.
We all agreed to head back to rooms, fresh up & meet downstairs in 20 minutes.
When we all came down, Jordan was also at the bar area. So we asked him to join us.
Jordan, Bob & I shared the backseat and lots of laughs all the way to some upscale sea food restaurant in another suburb of San Francisco.
Here is a selfies from backseat of Zack’s car.
Bob, Jordan & Chandoo
We meet Kevin Jones there and we all share really amazing food, insightful (often hilarious) conversation. As Ken recently quit his job to be self-employed, we all shared our words of wisdom with him.
But the night is not over yet
We reached the hotel at 9:30. I find Rob, Scott, Matt, few members from Microsoft Excel & Power BI teams all having drinks at the lobby bar. So I joined them for more laughs, conversation & selfies.
Here is a pic with Rob, Scott, Matt & Ken
Chandoo, Rob, Matt, Scott & Ken
By the time I head to my room it was 11:30 PM.
Dany’s Recalc or Die stricker
Dany Hoter from Excel team has this cool laptop sticker.
Almost all the Excel MVPs at the conference in one epic pic
And here it is:
Zack, Jon, Bob, Ken, Chris, Marco, Gregory
Cat, Oz, Chandoo, Rick & Szilvia
My first impressions of everyone
This is the first time I met so many Excel MVPs & bloggers. Here is the first thought that came to me when I saw them.
- Ken: He is big!!! and he talks fast
- Oz: What a hat! and whats with the Sriracha hot sauce?!?
- Rick: he means business
- Dan Fylstra: Wow, he is so cool & down to earth
- Scott: Boy his laughter is really loud
- Avi: small packet of energy & enthusiasm
- Bob: funny and awesomely English
Thank you PASS & everyone who showed up
Thanks a lot to the PASS team for inviting me to this conference. I had an awesome time.
Also thanks to everyone from Chandoo.org community who signed up for this event & made it even more awesome. Thank you.
17 Responses to “Budget vs. Actual Profit Loss Report using Pivot Tables”
Good Work, Yogesh & Chandoo! Thanks.
Hi everybody,
first sorry I am late to say something about this topic;actually I was waiting last part
second I am not accountant I am an Engineer
third """"Very Important""" the idea is not about Loss but I am sure it is profit
Based on third it shows:
1- How to use EXCEL
2- How to use pivot TABLES
3- How to collect and arrange DATA
4- How to make reports
Many Thanks
Hi Yogesh and Chandoo,
Thank you for sharing your knowledge!
You guys are great!
thanks chandoo and yogesh, thanks for you lessons, are great!....i have a idea for a budget. I try to do it..... thanks for all
Thanks a lot for sharing the most powerful tool worldwide "knowledge"
Warm greetings from Peru
Hi -
This is a really great article because it's a simple and common thing you'd want to do with a pivot table but not at all obvious how to do it! So - muchas gracias to Chandoo and Yogesh!
One thing - I couldn't get past the group error in the sample file. I would click on ungroup but it didn't seem to have any effect. I'd appreciate it if anybody has any pointers here.
-Juanito
Hi Chandoo
I am also having the group error. Can't seem to ungroup? Appreciate if you explain further on the steps required in order to get to calculated items.
Many thanks and keep up the great work.
Cheers
Adam
Hi Chandoo,
I'm struggling resolving the problem depicted below:
I have a set of data, with (among others) a "Region" field (can be APJ, EMEA, or AMS), and a "Country" field.
Unfortunately, I need to group data by the following 4 Regions: APeJ, Japan, EMEA and AMS.
I first tried to make a pivot with Region and Country in the rows (or columns), and then group Country data as per the above.
Alas, as soon as I have a new Country that appear in my data set, my groupings are broken, and I have to redo the job of ungrouping, grouping etc.
I thought I could try to use calculated item, by adding first a new column to my dataset concatenating Region_Country, and create an "APeJ" calculated item that would sum all the "APJ_*" and substract the "APJ_Japan", but again, no clue, as I can't find a way to use any wild card in those formulas.
Given that I already found extremely helpful tips and tricks in your site that helped me manage that bunch of data, I'm pretty sure you'll have a bright idea on how I can solve that one!
Thanks in advance for your lights!
Hi Catherine...
In such cases, I advice using an additional column in the data itself. You can set-up a grouping table else where with country in first column, region in second column. And then in the data, you can add an extra column and use VLOOKUP to fetch the region based on the country.
Then feed this entire data (with extra column) to pivot table and use the extra column to group the data.
Hi Chandoo,
Thank you for your prompt answer.
I finally came to the same conclusion - after a rest 🙂 . I was probably too tired Friday evening (it was rather late), having spent hours in manipulating all my surveys data so as to pull rolling averages, make nice graphs and so on, and was trying to find a complex solution when there was a simple one.
Thanks again,
Catherine
Hey,
Great post!
I for example have different database structure with the following fields :
Date, Expense, Income, Sum (Income - Expense), Category (Sales, Cost of Goods and etc).
Creating a P&L report for the whole year works great. Including gross margin % and etc.
Though, creating P&L report by QTR/Month is becoming impossible since i get the following error : “This PivotTable report field is grouped. You cannot add calculated item to grouped filed.”
Is there a solution for this kind of problem?
Like Adam and Juanito, I also cannot ungroup.
Would appreciate it if you can add a few more lines and a screenshot or two on where to put the mouse cursor to ungroup.
Hi, I have figured out the ungrouping problem. One of the earlier steps was to group by month, if you pull the month back down to the column then right click and then select ungroup, then pull the month back up so you end up with just data source and budget/actual as the headings, then you can continue on.
To solve the ungroup problem, my method is:
Copy the "data" sheet to a whole new Excel workbook
and directly work on Part 6.
And since it is a fresh copy, Excel don't show me the "can't ungroup" problem. Hope this help.
Thank you Yogesh for this wonderful tutorial.
Kent, Malaysia
Just when i thought pivots were awesome i learn about inserting the calculated fields and that makes them more awesome. chandoo where have you been all my life.
Hello - your P&L pivot version has really impressed my boss and would like to use it. I have applied it for a actual vs budget vs forecast model I have created. One problem. In your variance above the operating profit percent % variance shows 33.8% but I want it to show (0.01) point or the true diff from prior budget.
I know I can add calculation to the side but boss would like to see it in pivot table.
Please help
Thanks
I have a further query which may solve my above dilemma. Is it possible to add a column that calculates percent increase. So in the example above a new column would be added to show variance %.
Any help would be appreciated.
Thanks