Monarch Learning Series 2023 Exercise 9 - AP Expense Report Analysis

Baba_Majekodunmi_703

Exercise Info

• Application (Classic vs Data Prep Studio): Data Prep Studio
  
• Level of Difficulty (Beginner, Intermediate, Advanced): Beginner
• Skills/Features Tested: Join, Analyze, Formula Field, Lookup Field, RegexIsMatch Formula
  
• Systems of Record: Not Applicable
  
• Estimated Time to Solve: 10 mins
• Exercise files required are attached in the Zip File Below;
  
  • Expense Report, 2016-12.pdf
  • Expense Report, 2017-01.pdf
  • Expense Report, 2017-02.pdf
  • Currency Rate Pairs.xlsx

Exercise Overview

You are an Accounts Payable Accountant at a Software Company and you need to analyze 3 months worth of expense reports to answer the following questions:

1. What is the Total Expense in USD for the 3 Expense Reports (2016-12, 2017-01, 2017-02)? (HINT: You will need to convert any expenses not in USD by using the Currency Rate Pair.xlsx file and creating Formula field for the USD rate for the non-USD expenses)
2. Who are the Top 3 Employees that spent the most on any Expense that contains
   • "Travel"
   • "Taxi"
   • "Airfare"
   • "Airline Fees"
   • "Hotel"?
3. Who are the Top 3 Employees that spent the most on any Expense that contains
   • "Lunch"
   • "Breakfast"
   • "Dinner"?

Exercise Answer

Not sure you got the correct answer? No problem. Watch the video below for a demonstration of this exercise from start to finish.