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"?

Answer

• Please upload your answers below in the comment section for the exercise
• A Video Answer to the question will be published within 7-10 days on the Monarch Succcess Plan page in the 'Monarch Exercises' section.

Matthew Morris

Hello, All.

Here is my submission for Exercise 9.

Thanks,

Matthew

Baba_Majekodunmi_703

Matthew Morris said:

Hello, All.

Here is my submission for Exercise 9.

Thanks,

Matthew

Great work here Matthew! You nailed it for #2 and #3. You got #1 too but for some reason our numbers differ. Here's my workspace attached.