# 54|EXCEL&POWERBI – Interview Skills

BYU Student Author: @Marco
Reviewers: @DylanKing, @TylerBooth
Estimated Time to Solve: 40 Minutes

We provide the solution to this challenge using:

• Excel
• Power BI

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

1. You may add onto the Excel sheet, but it must be deleted afterwards (except instructions).
2. Move the “Employees” worksheet to the “Expenses” worksheet and start on the “Expenses” sheet.
3. Fix the capitalization of the “Category” column to normal (Office Supp).
4. Get rid of the extra spaces between words in the “Description” column.
5. Add “EmployeeID” & “EmployeeName” columns between columns “Date” & “Category”.
6. Randomly assign ID #’s into the “EmployeeID” column only if the corresponding category is “Meals”, “Office Supp”, or “Travel”.
7. Fill the “EmployeeName” column with names that correspond with the employee IDs
8. Calculate the # of expenses that occurred in January.
9. Sum all January expenses.
10. Sum all Meals expenses.
11. Sum up the total employee expenses twice, 1st using the “EmployeeName” column and 2nd using the “Category” column (They should be the same).
12. Make a pivot table showing the categories and amounts associated with them, also add a slicer with the employee names and another for months.
13. Go to the “Employees” sheet and calculate the expenses for each employee under an “Expenses” column.
14. Make a “Budget” column and assign an amount to each employee. (Make at least 1 be lower than their expenses).
15. Calculate each employee’s difference under a “Difference” column.
16. Now, import the worksheet into Power BI.
17. 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.
18. Show the budget and expenses for each employee.
19. 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

Solution

This challenge was great practice for both Excel and Power BI! The only thing I couldn’t figure out is how to conditionally format the multi-row card based on negative and positive amounts. If anyone is able to do this please let me know!

Here is my solution:

1 Like

Ran into a few troubles importing the excel sheet into PowerBI, but most of that was due to formatting. Gave me some good practice in importing different types of data sets into PowerBI and was a fun challenge!

Dude I loved this challenge! I have not used Pivot Tables in a while and I had a fun time using conditional formatting in Power BI. I used bar charts in my viz!

2 Likes