# 10|EXCEL – Budget Blitz

BYU Student Author: @Spencer
Reviewers: @TylerBooth, @Mike_Paulsin, @Jonathan_Weston
Estimated Time to Solve: 35 Minutes

We provide the solution to this challenge using:

Overview
It’s your first day on the job as a financial analyst and your manager is already upset. The company has blown past the January budget…what a way to start the new year. It’s estimated that the company overspent upwards of half a million dollars, but no one knows for sure. Your task is to:

1. Find out who is spending so much and on what
2. Present your findings in a dynamic, visual format

Instructions
Using your excel knowledge, calculate budget variances by department and by expense category. Your findings must be presented on a single excel sheet with the following elements:

1. Waterfall Chart – The x-axis will be expense category, and the y-axis will be budget variance.
2. Data Table (Department) – This table should display two columns. The first will contain each department in the company. The second will be budget variance (that is, the variance of each department listed in the first column). Apply conditional formatting to the variance column to make the largest negative variances stand out.
3. Data Table (Expense Category) – This table will also display two columns. The first will contain each expense category. The second will be the budget variance of said categories. Apply conditional formatting to the variance column to make the largest negative variances stand out. Note, this table contains the same data as the waterfall chart, just in table format.
4. Dropdown List/Slicer – Your visualization must have one dropdown list that contains each department’s name and an option that says “All”. Your charts and tables must only show information for the department selected in the slicer. For example, if the slicer says “Legal”, your waterfall chart and tables must only show budget variance data for the Legal department. If “All” is selected, the entire company’s variance data must be shown.

Data Files

Suggestions and Hints
• Budget Variance is calculated as (Budget – Actual Expenses). Thus, overspending is expressed as a negative.
• You will likely need to use a combination of IF statements and SUMIFS statements to pull in and display the correct data in your Department Table and Expense Category Table.
• If you’re having trouble visualizing the final deliverable, view the “Challenge Hint” image below. It contains a basic outline of what we’re looking for.
Challenge Hint

Solution

Clever solution. I think I should try to format my solutions like yours in the video I couldn’t figure out how to make “All” an option in my slicer, but it looks like you just used the data validation tool instead. You can still select all departments in my solution, but there is no button that says “All.” Now I know for next time but this was a fun challenge with lots of room for creativity. Kudos!

2 Likes

Great challenge! Your presentation was much more clean than mine, but we came out with the same totals. Thank you for introducing me to the slicer tool. I can see why this would be useful for creating interactive visualizations within Excel.

My solution looks a little more like yours. I agree that the data validation was clever, but the slicer available in the “Table Design” tab works very well too.

This was a tough challenge for me, but I enjoyed learning about the waterfall chart and how you went about solving the challenge. For the formula calculating your budget by department, I found a simpler way that cuts the amount of work in half : =IF(OR(\$U\$4=“All”,D20=TRUE), SUMIFS(Budget[BUDGET AMOUNT],Budget[DEP],Dash!E20),0)

Looks like HR and Travel aren’t doing too hot!

I’d consider myself not very great with technology yet so this assignment was difficult and I had a hard time beginning and finishing.

I first completed this challenge in Power BI and found it easy. When I tried the excel version, it took me a lot longer. Instead of using a helper column for my tables, I just added more conditions to my formula. Troubleshooting the formulas took the most time, but I was eventually able to figure it out.

Challenge10a_Complete_Dataset_Dutton.xlsx (2.9 MB)