# 10|EXCEL – Budget Blitz **URL:** \ **Category:** Challenges\ **Tags:** excel, intermediate, managerial\ **Created:** [February 17, 2023, 2:14pm UTC](https://www.techhub.training/t/10-excel-budget-blitz/160 "2023-02-17T14:14:29Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Spencer](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/spencer/32/107_2.png) [@Spencer](https://www.techhub.training/u/Spencer)\ **Post date:** [February 17, 2023, 2:14pm UTC](https://www.techhub.training/t/10-excel-budget-blitz/160/1 "2023-02-17T14:14:29Z")
_BYU Student Author_: @Spencer _Reviewers_: @TylerBooth, @Mike_Paulsin, @Jonathan_Weston _Estimated Time to Solve_: 35 Minutes We provide the solution to this challenge using: - Excel - [Go here to perform this challenge in Power BI](https://www.techhub.training/t/10-powerbi-budget-blitz/576) Need a program? Click [here](https://www.techhub.training/t/information-about-software-used-on-techhub-training/138). **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](https://www.storytellingwithdata.com/blog/2020/11/16/what-is-a-waterfall) – 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** - [Challenge10a\_Complete\_Dataset.xlsx](https://byu.box.com/shared/static/aiiikqtylc3mloqtw6hj0cat6ya4qnxq) - [Challenge10\_Data\_Dictionary.docx](https://byu.box.com/shared/static/1bbe1qqg4ch6yf73j6og17nmgktbobwl) > **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** > > > > ![Challenge10_Hint](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/1X/a4ad3417c26eb8c272247595d638da302ada63e3.jpeg) **Solution** [Challenge10\_Solution.xlsx](https://byu.box.com/shared/static/revgu9t5hvf2r4aeo7vsaervala1ousl) _Solution Video:_ [Challenge 10|EXCEL – Budget Blitz](https://youtu.be/8nFhQ6GNa8w) ---
**Author:** ![Alex\_Garrett](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/alex_garrett/32/57_2.png) [@Alex\_Garrett](https://www.techhub.training/u/Alex_Garrett)\ **Post date:** [February 25, 2023, 6:21pm UTC](https://www.techhub.training/t/10-excel-budget-blitz/160/2 "2023-02-25T18:21:35Z")
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! ![image](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/1X/dfc2e1bdb730f7e84c17e7cdb2b5800359b1e83d.png) ---
**Author:** ![TylerBooth](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/tylerbooth/32/335_2.png) [@TylerBooth](https://www.techhub.training/u/TylerBooth)\ **Post date:** [March 1, 2023, 1:22am UTC](https://www.techhub.training/t/10-excel-budget-blitz/160/3 "2023-03-01T01:22:15Z")
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. ---
**Author:** ![Parker\_Sherwood](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/parker_sherwood/32/167_2.png) [@Parker\_Sherwood](https://www.techhub.training/u/Parker_Sherwood)\ **Post date:** [March 1, 2023, 3:26am UTC](https://www.techhub.training/t/10-excel-budget-blitz/160/4 "2023-03-01T03:26:03Z")
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. ---
**Author:** ![Parker\_Sherwood](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/parker_sherwood/32/167_2.png) [@Parker\_Sherwood](https://www.techhub.training/u/Parker_Sherwood)\ **Post date:** [March 1, 2023, 3:29am UTC](https://www.techhub.training/t/10-excel-budget-blitz/160/5 "2023-03-01T03:29:01Z")
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) ---
**Author:** ![MitchFrei](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/mitchfrei/32/312_2.png) [@MitchFrei](https://www.techhub.training/u/MitchFrei)\ **Post date:** [March 14, 2023, 5:33am UTC](https://www.techhub.training/t/10-excel-budget-blitz/160/6 "2023-03-14T05:33:46Z")
Looks like HR and Travel aren’t doing too hot! ![image](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/1X/21dec0f9edd94a91f8c9d3574ae2137a1fa59f2a.png) ---
**Author:** ![Chase\_Black](https://avatars.discourse-cdn.com/v4/letter/c/22d042/32.png) [@Chase\_Black](https://www.techhub.training/u/Chase_Black)\ **Post date:** [April 17, 2023, 11:57pm UTC](https://www.techhub.training/t/10-excel-budget-blitz/160/7 "2023-04-17T23:57:56Z")
I’d consider myself not very great with technology yet so this assignment was difficult and I had a hard time beginning and finishing. ---
**Author:** ![Jacob\_Dutton](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/jacob_dutton/32/6370_2.png) [@Jacob\_Dutton](https://www.techhub.training/u/Jacob_Dutton)\ **Post date:** [January 16, 2024, 8:28pm UTC](https://www.techhub.training/t/10-excel-budget-blitz/160/8 "2024-01-16T20:28:34Z")
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. ![Screenshot 2024-01-16 at 1.24.48 PM](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/8/87c60a49a33fd483f84dbc37b2b8eb6c66ec315f.png) [Challenge10a\_Complete\_Dataset\_Dutton.xlsx](https://www.techhub.training/uploads/short-url/lHHa3QWNzhhQFzPaMm7JTAu9DWG.xlsx) (2.9 MB)