BYU Student Author: @Spencer
Reviewers: @TylerBooth, @Mike_Paulsin, @Jonathan_Weston
Estimated Time to Solve: 35 Minutes
We provide the solution to this challenge using:
Need a program? Click here.
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:
- Find out who is spending so much and on what
- 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:
- Waterfall Chart – The x-axis will be expense category, and the y-axis will be budget variance.
- 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.
- 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.
- 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.
Solution
Challenge10_Solution.xlsx
Solution Video: Challenge 10|EXCEL – Budget Blitz
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!
: 
