Analytics Mindset:
Blue Planet Inc. – Travel Expense Audit
Overview1
You are an internal auditor for a corporation. The company reimburses employees when they travel for
work purposes using their own vehicles. To receive this travel expense reimbursement, the employee
submits paperwork that lists the addresses of their departure and arrival locations, the total mileage they
traveled and the total mileage to be reimbursed. If the employee has traveled to multiple locations, the
paperwork will indicate the “legs” they traveled for the trip. A trip leg is the distance traveled between two
points. For example, if an employee leaves their home, travels to one business, then visits another
business and then returns home, the trip has three legs: (1) from their home to the first business, (2) the
first business to the second business and (3) the second business back to their home.
Using this paperwork, an employee in the Payroll Department then calculates the reimbursement,
calculated as miles traveled multiplied by the IRS reimbursement rate of $0.655 per mile. Employees are
expected to track the mileage traveled to the nearest tenth of a mile. The company pays the employee the
computed reimbursement amount in their next paycheck.
The head of the Audit Committee recently reviewed the company’s financial data and thought the amount
of employee travel reimbursement might be excessive. She asked you to conduct an audit of the travel
reimbursements to see if employees are following company policies.
In the past, when Internal Audit would audit travel reimbursement expenses, the internal auditor would
take a sample of the trips made by employees. The internal auditor would then use a commercial
mapping service (Google Maps) to measure the distance between the departure and arrival locations.
The auditor would then look for patterns in the sample and make recommendations based on their
findings. Because of the time-consuming nature of gathering this data, the internal auditors were only able
to look at a fraction of the nearly 4,000 trip legs that were made by employees in a year.
Recently, however, the head of Internal Audit learned about robotic process automation (RPA). RPA
allows a person to program a bot to automate tasks that a human would have performed. The head of
Internal Audit assigned an internal auditor to design a bot that automatically calculates the distance
traveled for every trip leg. Using RPA, the internal auditor was able to collect the data for every trip leg
traveled by employees for the last year. The internal auditor has given you the output from this bot to
analyze in preparing your report for the head of the Audit Committee.
1
This case is based on the real-world internal audit example by Marc Eulerich, Justin Pawlowski, Nathan Waddoups
and David A. Wood, “A Framework for Using Robotic Process Automation for Audit Tasks,” SSRN website,
https://papers.ssrn.com/sol3/papers.cfm?abstract_id=3651028 , accessed September 2021.
Analytics mindset case studies – BluePlanet Inc. – Travel expense audit 1
© 2021 Ernst & Young Foundation (US). All Rights Reserved.
SCORE no. 13759-211US_5
The output file is a labeled Case_study_dataset.xlsx and contains three sheets.
► The first sheet is labeled “TravelSheet.” This sheet has 14 columns defined as follows:
– LineId: is a unique identifier for each line in the data set.
– BusinessTripID: is a unique identifier for each trip an employee takes. A trip includes all legs
traveled by the employee.
– LineItemID: is a unique identifier for each unique trip leg on a business trip.
– EmployeeID: is a unique employee number assigned to each employee.
– StartAddress, StartCity, StartZip, StartState: are the beginning address, city, ZIP and state for a
travel leg, respectively.
– EndAddress, EndCity, EndZip, EndState: are the ending address, city, ZIP and state for a travel
leg, respectively.
– ReportedDistance: is the distance that the employee reported traveling between the two
locations.
– ValidatedDistance: is the distance the RPA bot reports that was traveled between the two
locations.
► The sheet labeled “EmployeeData” contains the employee ID, first name, last name, address, and
office for each employee.
► The sheet labeled “CustomerData” contains the customer ID, name, and address for each customer.
Implementing an Analytics Mindset
As you perform this case, remember the value of using an analytics mindset. An analytics mindset is the
ability to:
► Ask the right questions
► Extract, transform and load relevant data
► Apply appropriate data analytics techniques
► Interpret and share the results with stakeholders
Analytics mindset case studies – BluePlanet Inc. – Travel expense audit 2
© 2021 Ernst & Young Foundation (US). All Rights Reserved.
SCORE no. 13759-211US_5
Required
Conduct your analyses by preparing visualizations in Tableau. Your analyses should answer each of the
following questions and your report should address the following requirements. Make sure that you
clearly explain your process and how you arrive at your conclusions.
1. How much money was spent on reimbursing employees for travel expenses? How much money do
you estimate was spent for misreported reimbursements? (Hint: you may need to create additional
calculated variables in Tableau)
2. How much money was spent reimbursing each office for travel expenses? How much money do you
estimate was spent for misreported reimbursements for each office? What is your best estimate of the
correct amount of travel expenses that should have been reported for each office?
3. How much money was reimbursed to each employee? How much of the money that was reimbursed
do you estimate was caused by misreporting for each employee? Separate employees by office and
then sort to show the employee with the greatest misreported amount at the top of each office
section.
4. For each employee that misreported an amount greater than $200, examine their misreporting
behavior over time and provide a possible explanation for their behavior and any other evidence that
might be relevant to their misreporting behavior. As a hint, although there is no time stamp in the file,
the transaction Line ID is assigned so that larger numbers are assigned later in time.
5. What else can you learn? Create at least one additional visualization that adds information relevant to
your examination. Apply the analytics mindset (AMPS framework) to guide you: explicitly consider
and discuss each step in the context of your investigation.
6. Prepare a report for the Audit Committee explaining your analytical procedures and findings. In your
report, you should describe the objectives of the procedures, the analyses you performed, your
findings, and your recommendations. Whenever possible, quantify your findings and
recommendations and use visualizations to convey meaning.
Submission
Submit the following for grading:
1. Your report file (Microsoft Word or pdf) – see formatting guidelines below.
2. The Tableau file you created to calculate your answers and prepare your visualizations saved in
twbx format (Tableau Packaged Workbook). Your file must be submitted with your report and
should be organized in a professional, easy-to-follow way. Add explanations to your visualizations
where necessary. In addition to charts, you should create a Dashboard to organize your analysis.
Formatting of the Report
At least three (3) pages in length, not including cover page, data visualizations, and appendix.
Appendix: include a bibliography (https://www.plagiarism.org/article/whats-a-bibliography) in APA
style (http://www.easybib.com/guides/citation-guides/apa-format/) and additional information, your
visualizations, sources, etc. as necessary.
Include a cover page that lists your name and Student ID. It should also feature the “Certification
of Authorship” statement as outlined in the Syllabus.
Analytics mindset case studies – BluePlanet Inc. – Travel expense audit 3
© 2021 Ernst & Young Foundation (US). All Rights Reserved.
SCORE no. 13759-211US_5
