This is 4th part of Creating Management Dashboards in Microsoft Excel 6 post series by Robert.
This 6 Part Tutorial on Management Dashboards Teaches YOU:
Creating a Scrollable List View in Dashboard
Add Ability to Sort on Any KPI to the Dashboard
Highlight KPIs Based on Percentile
Add Microcharts to KPI Dashboards
Compare 2 KPIs in the Dashboards Using Form Controls
Show the Distribution of a KPI using Box Plots
The Challenge – Adding Visualization to the KPI Dashboard
In this final post on KPI dashboards with Microsoft Excel, we will show you how to add meaningful graphical visualization directly into our dashboard table. With scrolling, sorting and highlighting the dash-board already offers some interesting analytical features (see previous posts). But it is still displaying the data as pure numbers. That makes it difficult for the user to recognize the relative sizes of the values at a glance. Furthermore it is often necessary to communicate the relative position of the data compared to one or several other calculated or given values like the total average or a target.
The solution
[click here to view larger size]
Inserting conditionally formatted bar-line-combination-charts directly into the dashboard table visualizes the shown data and enables the user to get an overview at a glance. The bars show the relative sizes of the corresponding values, the conditional formatting let us immediately identify which values are below target (red color) or larger than target (grey color) and the line makes it easy to see whether a value is above or below the total average.
Download the Excel file – KPI Dashboards with visualization
The Implementation
To implement the charts, we need some knowledge about creating and formatting special charts with Microsoft Excel. In my humble opinion, the by far best resource on charts with Microsoft Excel is Jon Peltier’s excel charts pages. All you have to know for our dashboard charts is brilliantly described on Jon’s website (follow the links below).
- Prepare the workbook for the new features (5 extra columns on the dashboard for the bar charts, additional rows on the data worksheet to define the targets and new columns on the calculation sheet).
- Insert 5 conditional formatted bar charts. Read Jon’s method to create a conditional formatted chart.
Use the table on the dashboard as the data source for the chart and use the targets defined on the sheet “data” as the threshold whether a value is formatted red (below target) or grey (larger than or equal target).
- Calculate the total average on the calculation sheet for each KPI and add an average line to each of the bar charts by using an XY-scatter chart type. Read more on Bar line combo.The necessary calculations for the steps 2 and 3 can be found in columns Q to AQ of the sheet “calculation”.
- Format the charts to make only the bars and the average line visible (no axes, no grid lines, no data labels, no caption, no border or fill color of chart area and plot area). Like Albert Einstein said: “as simple as possible, but not any simpler.“
- Adjust the charts on the dashboard to make them fit exactly to the corresponding cell ranges. One tip for this: Holding the ALT key pressed when resizing a chart will make the chart size auto-fit to the size of the cell range beneath it. That makes it easier to position the charts correctly.The bar charts already look exactly the way we want them to. But there is one undesirable effect: when scrolling up or down the table, the maximum scale of the horizontal axis changes and the bars seem to “jump” up or down.
To avoid this, add two additional XY-scatter-series to the chart, representing the minimum and the maximum of the total data and assign them to the secondary axis. Furthermore add 2 additional bar series to the chart, again representing the minimum and the maximum of the total data and assign them to the primary axis. We thereby “force” both horizontal axes to be identical and stay the same when scrolling up or down. Since we do not want to display these dummy-series, format them with no line and invisible markers (XY-scatters) respectively with no fill color and no border.
- Finally update the caption beneath the table to explain the meaning of the line and the bar colors.
What is next?
- Download the excel KPI dashboard final workbook
- Bookmark Dashboards using Excel pages for future reference.
- Drop a lovely note of thanks to Robert if you have benefited from this series.
Read the next part: Part 5: Compare 2 Decision Parameters in the Dashboards Using Form Controls
Also, Checkout our Excel Dashboards Page for more examples and resources.
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