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.

Budget vs Actual dashboard skeletal structure

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”.

Financial dashboard data source (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”.

Financial dashboard data source (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

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

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.

Budget vs Actual dashboard user selection cells

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.

Calculating the current year using the YEAR and TODAY functions

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 ValidationList” option.

To do this, perform the following steps:

  1. Select cell C3.
  2. Click Data (tab) – Data Tools (group) – Data Validation.
Data ribbon in Excel highlighting the Data Validation tool
  1. 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.
Data Validation dialog box selecting list and source

We now have the Year/Month selection controls completed.

Data Validation dropdown used in cell

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.

Actual calculations for financial dashboard

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], "")
Previous Year calculations for financial dashboard

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”.

Selected month and current city's PLAN value
Cross-tabular structure

Featured Course

Fundamentals of Financial Analysis

Whether you’re a newbie or have an MBA in Finance, you’ll FINALLY “get” the big picture. This comprehensive course will equip you with these critical skills – even if you’ve never taken a finance or accounting class.
Learn More
Financial Analysis Course Cover

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”.

Budget vs Actual dashboard headings for delta calculations

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 – 1

We 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).

Percentage change from previous year actual

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, "")
Percentage change from plan

Featured Course

Visually Effective Excel Dashboards

Create eye-catching Excel Dashboards with actionable tips you can use right away.
Learn More
Excel dashboards course cover

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).

Selecting discontinuous columns using the CTRL key

Next, we’ll insert a column chart onto this sheet by pressing the ALT-F1 key combination.

Inserting a chart onto the current sheet using 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
Number formatting for chart axis

This is what you’re aiming for:

Customized financial chart showing actuals vs plan

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).

Selecting discontinuous columns using the CTRL key

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
Overlapping charts to aid in alignment and spacing
  • 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”).
Control to hide labels on a horizontal axis
  • 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.
Setting alternate colors for positive and negative values of chart series

The result is as follows:

Two charts in a financial dashboard: Sales Actual vs Plan and Variance to Previous Year

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.

Excel Download Practice file

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

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.