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. Julia’s Cookies has a list of prohibited vendors that employees are not allowed to use while on business travel. One prohibited vendor is Airbnb. Julia’s 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 Julia’s 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?

Answer rating: 100% (QA)

The detailed answer for the above question is provided below 1 Which department had the largest count of unique expense claims submitted The department with the largest count of unique expense claims ... View the full answer