BYU Student Author: @Spencer
Reviewers: @Parker_Sherwood, @Boston, @Erick_Sizilio
Estimated Time to Solve: 20 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 Power BI knowledge, calculate budget variances by department and by expense category. Your final visual must be presented on one page and contain 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” (or “Select 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
- Ignore any errors you receive for the “Transactions” sheet while loading in the data. The errors will not affect your analysis.
- Budget Variance is calculated as (Budget – Actual Expenses). Thus, overspending is expressed as a negative. You will need to create a measure to calculate budget variance.
- If you need help applying conditional formatting, click here.
Solution
Challenge10_Solution.pbix
Solution Video: Challenge 10|POWERBI – Budget Blitz
