BYU Student Author: @Marco
Reviewers: @DylanKing, @TylerBooth
Estimated Time to Solve: 40 Minutes
We provide the solution to this challenge using:
- Excel
- Power BI
Need a program? Click here.
Overview
You are in an interview with a local firm for an internship position you have been looking for. Everything is going well, and you think that the interviewer likes you. The interviewer, Jake, would like you to display some of the knowledge you have on programs such as Excel and Power BI since his decision is between you and another candidate. He says that if you do well on this task, you’ll get the job. He tells you to make it look however you’d like, but he will also give you a list of things he’d like to see. You then go home and start grinding.
Instructions
- You may add onto the Excel sheet, but it must be deleted afterwards (except instructions).
- Move the “Employees” worksheet to the “Expenses” worksheet and start on the “Expenses” sheet.
- Fix the capitalization of the “Category” column to normal (Office Supp).
- Get rid of the extra spaces between words in the “Description” column.
- Add “EmployeeID” & “EmployeeName” columns between columns “Date” & “Category”.
- Randomly assign ID #’s into the “EmployeeID” column only if the corresponding category is “Meals”, “Office Supp”, or “Travel”.
- Fill the “EmployeeName” column with names that correspond with the employee IDs
- Calculate the # of expenses that occurred in January.
- Sum all January expenses.
- Sum all Meals expenses.
- Sum up the total employee expenses twice, 1st using the “EmployeeName” column and 2nd using the “Category” column (They should be the same).
- Make a pivot table showing the categories and amounts associated with them, also add a slicer with the employee names and another for months.
- Go to the “Employees” sheet and calculate the expenses for each employee under an “Expenses” column.
- Make a “Budget” column and assign an amount to each employee. (Make at least 1 be lower than their expenses).
- Calculate each employee’s difference under a “Difference” column.
- Now, import the worksheet into Power BI.
- Show the expenses for each month and highlight the expenses over 15,000, also have a way to filter category and employee expenses based off of it.
- Show the budget and expenses for each employee.
- Make a multi-row card with employee name and difference, also format the difference to be blue if positive and red if negative.
Data Files
- Challenge54_Expenses_Data.xlsx
- Challenge54_Employee_Data.xlsx
- Challenge54_Expenses_Data_Dictionary.xlsx
- Challenge54_Employee_Data_Dictionary.xlsx
Solution
