Question
I have the data file handy I have a case study with excel using raw data and I am not sure how to solve it.
I have the data file handy
I have a case study with excel using raw data and I am not sure how to solve it. It's mainly using pivot tables but may have a formula or two to solve it. I included the categories in the excel raw data: Expense Tracking Number Approver Employee Number Approver Name Expense Approved Date Reimbursement Paid Date Expense Type Description Expense Category Ticket Number Ticket Status Transaction Date Vendor Employee Number Employee Name Department Payment Description Mileage Rate Miles Number Guests Transaction Amt Here are the questions to solve:
1. Which department had the largest count of unique expense claims submitted?
2. Which department had the highest average amount per transaction?
3. Which expense category had the highest total transaction amount?
4. Employees should not approve their own expense reports. Usually their manager is the approver; however, someone other than the employee should approve a claim for segregation of duties purposes. Test the data to see which employees approved their own expenses. (Hint: Create a true/false calculation.) What are their names?
5. How much, in total, did the employees from question 4 approve for themselves?
6. Julias Cookies has a list of prohibited vendors that employees are not allowed to use while on business travel. One prohibited vendor is Airbnb. Julias Cookies prohibits employees from staying at Airbnbs as they could be owned by the employee or a family member, creating a conflict of interest. How many employees broke the rules and used Airbnb as a vendor?
7. Employees at Julias Cookies can use their personal vehicles to travel to customer sites. An employee who chooses to do so is reimbursed based on mileage. Sometimes employees abuse the system. A red flag exists when an employee reports more miles than their monthly average. Test the data between the transaction dates 7/1/2025 and 12/31/2025 for above-average mileage. (Hint: Use a line chart.) Identify the employee(s).
8. How many total miles did the employee(s) from question 7 report for the month for which they potentially committed this type of fraud? Take it to the next level!
9. When booking a flight, an employee books and pays for the flight in advance, which means they can submit their air ticket reimbursement prior to their trip. After they come back from the trip, some employees may claim reimbursement again, along with their other expenses, accidentally or with the intent of defrauding the company. Test the data to see if there were any duplicated ticket numbers in the data set. How many air tickets were reimbursed twice?
10. Which employee had duplicate reimbursements of airline tickets?
I only need to know how to solve it, not the answers! FOR EX: #1, using a pivot table for the data, what category would you put in filter, column, row, values? Thank you!
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started