This is a bonus post in the project management using excel series.
Gantt charts are very good to understand a project progress and status. But they are heavy on planning side. They give little insight in to what is happening. A burn down chart on the other hand is good for understanding the project progress and how deliverables are coming along. According to Wikipedia,
A burn down chart is graphical representation of work left to do versus time. The outstanding work (or backlog) is often on the vertical axis, with time along the horizontal. That is, it is a run chart of outstanding work. It is useful for predicting when all of the work will be completed.
An Example Burn Down Chart:
Making a burn down chart in excel
Step 1: Arrange the data for making a burn down chart
To make a burn down chart, you need to have 2 pieces of data. The schedule of actual and planned burn downs. As with most of the charts, we need to massage the data. I am showing the 3 additional columns that I have calculated to make the burn down chart. You can guess what the formulas are. (Hint: there is a NA() too)
Step 2: Make a good old line chart
Just select the Balance Planned and Balance Actual series and create a line chart. Use the first column (days) in the above table for x-axis labels.
Step 3: Add the daily completed values to burn down chart
Select the “daily completed” column and add it to the burn down chart. Once added, change the chart type for this series to bar chart (read how you can combine 2 different chart types in one)
Step 4: Adjust formatting and colors
Remove or set grid lines as you may want. Adjust colors and add legend if needed.
Step 5:
There is no step 5, just go burn down some work.
Download the excel burn down chart template
Click here to download the excel burn down chart template. Use it in your latest project status report and tell me what your team thinks about it.
Download 24 Project Management Templates for Excel
What Next?
This is a bonus installment to the project management using excel series. We will revisit the burn down charts during part 6 of the series – Project Status Reporting Dashboards. Meanwhile, make sure you have read the remaining parts of the series:
Preparing & tracking a project plan using Gantt Charts
Team To Do Lists – Project Tracking Tools
Project Status Reporting – Create a Timeline to display milestones
Time sheets and Resource management
Issue Trackers & Risk Management
Project Status Reporting – Dashboard
Also check out the budget vs. actual charting alternative post to get more ideas.
Where would you use burn down charts?
We have used burn down charts in a recent presentation to client and they loved the way it was able to tell them the story about status of Phase 1 of the project. We needed to deliver 340 items in that phase and we have finished 45 when we made the presentation. My client could easily understand where the project is heading and what is causing the delays (of course by asking questions).
What do you think about burn down charts? Where will you use it? Tell me about it using comments.
Resources for Project Managers
Check out my Project Management using Excel page for more resources and helpful information on project management.
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