Which expense category had the highest total transaction amount?

Ecology by Geoffrey

Excel Case: Julia’s Cookies’ General Ledger Journal Entries

What You Need

Download and save the following file:

Chapter 15 Raw Data.xlsx (included as an attachment to this assignment)

Case Background

Big Picture:

Analyze fraud red flags in expense reports.

Details:

Why is this data important, and what questions or problems need to be addressed?

  • The employee expenses data reflects how employees are spending company money on expenses related to the office, travel, subscriptions, memberships, food, and more. Before you can start spending money and calling it a reimbursable business expense, you should review the company’s policies and procedures. There may be a prohibited vendor list or cost limits to comply with.
  • Analysis of this data helps identify potential fraud (the objective). While some employees may accidentally submit noncompliant expenses for reimbursement, there may be other employees who try to take advantage of internal control deficiencies and attempt to defraud the company. For example, fraudsters may attempt to:
  • Double dip for a single purchase and be reimbursed twice
  • Provide fraudulent mileage that might have been for personal travel instead of business travel (usually around the holiday months)
  • Ask the vendor to split a single invoice into multiple smaller ones in order to stay within the dollar amount limits that the business sets for a single purchase

Plan:

What data is needed, and how should it be analyzed?

  • The data needed to answer the objective is pulled from Julia’s Cookies’ Employee Expenses database.
  • Analyzing this data involves performing tests for four common employee reimbursement fraud schemes: employees approving their own reports, purchases at prohibited vendors, inappropriate mileage, and duplicate reimbursements.

Now it’s your turn to evaluate, analyze, and communicate the results!

 

 

Questions:

  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!

  1. 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?
  2. Which employee had duplicate reimbursements of airline tickets?

Complete this assignment using the data file given, creating a new tab/sheet for each question labeling it accordingly.  Then, be sure that you explicitly identify your answer to the question with the data visually using a PivotTable and possibly graph or chart and type out the answer so it is clear as well. 

 

 

 

 

A few examples