Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

The Internal Auditing Pocket Guide Preparing Performing Reporting And Follow Up

Authors: J.P. Russell

2nd Edition

1636941303, 978-1636941301

Students also viewed these Accounting questions

Question

Describe how to train managers to coach employees. page 422

Answered: 1 week ago