Setting Up the Static Elements
For our Budget vs Actual dashboard, we want to look at the sales performance of various store locations (i.e., cities) that display actual sales, previous year’s sales, and planned sales.
We want to limit the report to the current year and a month that the user selects from a list of months.
To keep things from going off on too many tangents, the data above is static text that was just typed into cells with some basic cosmetic formatting.
If you need this list to be more dynamic, like having the list of cities change when new cities are added or removed, you will need to implement more sophisticated solutions, like formulas that use dynamic functions like SORT and UNIQUE. We’re going to assume our list of cities remains constant.
Acquiring the Data
Obtaining Actual Sales
Our dashboard data will be acquiring sales data from a sheet named “Actuals”.
This data covers a multi-year timeframe, so we’ll be able to compare a month’s sales from both the current and previous year.
This data has been formatted as a proper Excel Table and named “TSales”.
This information can come from anywhere: copy/paste from another Excel file, Power Query output, dynamic array formulas, or an Excel add-in that automatically connects to and extracts data from some other system. The source of the data is unimportant for this study.
Obtaining Planned Sales
Our planned sales data for the current year will be acquired from a sheet named “Plan”.
This data is set up in a cross-tabular structure where any value is an intersection of a set city and month combination.
Featured Course
Excel Essentials for the Real World
Creating an Excel Drop-Down List for Month Selection
On our dashboard, we want to provide the user with a drop-down list to select a month from the current year.
For the “Year” option, we want Excel to automatically determine the current year.
In cell C2, we’ll use the following formulas to calculate the current date, and then extract the year from that date.
=YEAR(TODAY() )
NOTE: If the results of the formula display as some odd date from days gone by, set the cell formatting to General and this should solve the issue.
For the “Month” option, we want the user to be able to select a month from a drop-down list. This will be accomplished using a Data Validation “List” option.
To do this, perform the following steps:
- Select cell C3.
- Click Data (tab) – Data Tools (group) – Data Validation.
- In the Data Validation dialog box, select “List” from the “Allow” option, then for the “Source” option, highlight the list of month names on the “Plan” sheet occupying cells B2 through M2.
We now have the Year/Month selection controls completed.
Calculating the Necessary Data Points
Now it’s time to calculate the actual, previous year, and planned values for our table’s report.
Using a Lookup Function to Fetch Data
Let’s start by fetching the “Actual” values from the “Actuals” sheet.
There are many ways to fetch this data, but for this example, we’ll use the XLOOKUP function (one of my favorites).
Remember, we need to get a single value from the “Actuals” sheet based on three requirements: current year (cell C2), selected month (cell C3), and current row’s city (cell C4).
To do this, we’ll tell XLOOKUP to combine these three elements into a single lookup value.
Next, we’ll combine the three columns of data from the “Actuals” sheet using the same “year/month/city” arrangement.
Finally, we’ll tell it which column to return data from once a “year/month/city” combination match has occurred.
XLOOKUP also provides a messaging mechanism for undiscoverable entries. If that occurs, we’ll respond with an empty cell (two sets of double quotes).
In cell C7, write the following formula:
=XLOOKUP($C$2&$C$3&$B7, TSales[Year]&TSales[Month]&TSales[Store Location], TSales[Sales], "")We use the “&” (ampersand) character to concatenate (i.e., combine) cells and ranges together.
Notice in the blue portion of the formula, that we have “fixed/locked” the cell references for cells C2 and C3. This is so that when we fill the formula down to the adjacent cities, those references do not move. We want the cell reference for cell B7 to move down the rows so we can see a different city for each row, but we can fix the column so that it stays on the cities when we copy the formula later.
Now for the “Previous Year” calculations.
Since the formula for “Previous Year” is very close in design to the “Actuals” formula we just created, we can recycle the “Actuals” formula by copy/pasting the formula from cell C7 into cell D7.
NOTE: Copy/paste rather than drag the formula to the right. Otherwise, the table references will shift. Because we fixed the cell references for lookup value, they will work as expected.
The only change will be to adjust the “Year” reference ($C$2) to reduce the result by 1 so we can go back in time by one year. The rest of the formula is fine as is.
=XLOOKUP($C$2-1&$C$3&$B7, TSales[Year]&TSales[Month]&TSales[Store Location], TSales[Sales], "")
Now it’s time to fetch the values for the current year’s monthly plan.
Remember that the “Plan” values are in a cross-tabular structure, so we’ll need to perform a 2-way lookup to locate the needed value(s). We need to first locate the relevant city, then we’ll need to locate the relevant month. Once we have those two locations, we can fetch the value located at the intersection of said locations.
Select cell E7 and enter the following formula:
=XLOOKUP(B7, Plan!$A$3:$A$13, XLOOKUP($C$3, Plan!$B$2:$M$2,Plan!$B$3:$M$13), "")As you can see, this formula requires the use of two XLOOKUP functions, one nested within the other.
The first XLOOKUP (in blue) locates the city within the list of cities on the left of the table. The second XLOOKUP (in red) locates the month within the list of months at the top of the table. That same XLOOKUP is responsible for returning the value from the intersection of “City” and “Month”.
Featured Course
Fundamentals of Financial Analysis
Calculating Variances in Excel
To create titles for the variance columns (cells F5 and G5), we’ll use the Win-Period key combination to open the Windows Emoji library, which also includes symbols. We’ll use a triangle (point up) to indicate our change (delta) followed by the remainder of the title. We’ll use “∆ PY %” for “change from previous year”, and “∆ PL %” for “change from plan”.
Calculating Percent Change from the Previous Year (Year-over-year variance)
In cell F7, calculating change from the previous year is a standard “Actual” divided by “PY” (previous) then subtract 1 type of formula.
= $C7 / D7 – 1We will fix/lock the column reference for C7, so that we can copy it to the right (to ∆ PL %) without shifting the reference to Actuals, but leave the row reference relative to each city’s results. D7 should stay relative (not fixed).
In case there is no previous year’s data, or something else is missing, we can make this formula more robust by nesting it within an IFERROR function.
=IFERROR($C7 / D7 - 1, "")Calculating Percent Change from Plan (Actual to Budget variance)
In cell G7, the formula for calculating change from the plan is almost the same: “Actual” divided by “Plan” then subtract 1. We’ll also include the robustness of error-checking using the IFERROR function.
You can simply copy the previous formula. Actuals ($C7) will stay in place, while PY (D7) will shift to Plan (E7).
=IFERROR($C7 / E7 - 1, "")
Featured Course
Visually Effective Excel Dashboards
Add Visual Insights for Comparisons & Variance
Let’s turn some of these numbers into insightful visuals.
Actual vs. Plan Chart
We’ll start by making a comparison chart for “Actual” vs. “Plan” using a column chart.
Begin by selecting the city names and their associated actuals (cells B5 through C16), then hold down the CTRL key and select the “Plan” calculations (cells E5 through E16).
Next, we’ll insert a column chart onto this sheet by pressing the ALT-F1 key combination.
Let’s update the formatting with the following adjustments:
- Change the color of “Actual” to dark gray
- Change the color of “Plan” to light gray
- Move the legend to the upper-right corner of the chart
- Move the chart title to the upper-left corner of the chart and customize it to read “Sales Actual vs. Plan”
- Set the gridlines to a very light gray
- Remove the chart’s outline
- Set a custom number format for the vertical axis using the #,##0,, “M” code sequence
This is what you’re aiming for:
Change to Previous Year Percentage Chart
For the chart displaying the percentage change to the previous year, select the city names including the 2 empty cells above the city names (cells B5 through B16), then hold down the CTRL key and select the “∆ PY %” calculations (cells F5 through F16).
Insert a column chart onto this sheet by pressing the ALT-F1 key combination.
Update the formatting with the following adjustments:
- Remove the gridlines
- Remove the vertical axis values
- Add data labels to the bars of the chart
- Remove the chart area’s fill color (i.e., transparent)
- Remove the chart’s border
- Resize the chart by placing it atop the previous chart, then stretching the sides until the city’s bars line up with one another
- Move the chart below the first chart to its final position.
- Remove the horizontal axis labels (2x-click a city name, then find the Format Axis – Axis Options – Labels – Label Position – set to “None”).
- Move the chart title to the left side of the chart.
- Make the colors of the bars light green for positive values and light orange for negative values. This is done by changing the Format Data Series – Fill & Line – Fill – Invert if Negative option.
The result is as follows:
Final Thoughts
As you can see, building an effective budget vs. actual dashboard doesn’t have to be complicated. By using Excel formulas, we ensured it will be fully dynamic and update automatically once we get new data.
Creating professional-looking charts requires some manual adjustments but the final effect is worth it the effort. You get visuals that tell the story behind the numbers at a glance.
If you would like to learn more about the add-in I demonstrate in the second half of the video, check out the Zebra BI website.
Practice Workbook
Feel free to Download the Workbook HERE.
Featured Course
Black Belt Excel Package
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
