129|EXCEL – Passion for Payables

Challenges
, , , ,
1

BYU Student Author: @Brett_Lowe
Reviewers: @Donovon, @Spencer
Estimated Time to Solve: 40 Minutes

We provide the solution to this challenge using:

Need a program? Click here.

Overview
You are an accounts payable associate for a large conglomerate company called Little Laboratories LLC. Little Laboratories has 22 subsidiaries, which offer a broad spectrum of services ranging from patent/legal and venture capital work to entrepreneurial R&D and other engineering-related pursuits. Despite the wave of invoices that you receive each week, you are the sole member of the accounts payable team responsible for processing them. After you process the invoices in the payables software, they are sent to the corresponding subsidiary’s executive for approval. Following approval, the system pushes the invoices into QuickBooks and prepares the bills for payment.

Each week, you review the invoices that haven’t been approved as of Friday at 5pm and send reminder emails to those that are responsible for approving them. Some executives are good at clearing out their approval queue before the end of the week and don’t require an email. Others fall behind and accumulate past-due invoices and late fees. Approvers with more than 2 past due invoices must be reported to Little Laboratories’ CFO.

Because you’re passionate about processing payables, you decide to build a workbook that streamlines the process. Assume that today is 4/14/2023. Your workbook should do the following:

  1. Generate a list of executives that need a reminder email
  2. Isolate a list of past-due invoices and corresponding detail
  3. Highlight the executives that are causing bottlenecks and late fees.

Instructions

  1. Download the excel file provided
  2. Find the Approver# of each executive that will need a reminder email. Starting in cell B3 of the EmailList sheet, write a formula that will reference column G of the RawData sheet and display only unique values.
  3. In cell C3 of the EmailList sheet, write a formula that finds the email for the Approver# in cell B3. Copy the formula down so that emails are listed for each Approver# in the email reminder list.
  4. In cell D3 of the EmailList sheet, write a formula that finds the number of outstanding invoices that need to be approved by the Approver# in cell B3. Copy the formula down so that a total is listed for each Approver#.
  5. Beginning in cells C3:I3 of the PastDueReport sheet, write a formula that will reference the raw data and return only invoices that are past due. Assume that today is 4/14/2023. The solution uses a formula that can be written in cell C3, copied over to I3, and copied down to the extent of the past due invoices.
  6. In cell L6, write a formula that sums the total number of past due invoices.
  7. In cell L7, write a formula that estimates the amount of late fees owed on the past due invoices. Assume that you can negotiate a late fee of 1.5% of the invoice amount with each vendor, regardless of how late the invoice is.
  8. Starting in cell K11 of the PastDueReport sheet, write a formula that will reference the Approver#s in column I and display only unique values.
  9. In cell L11 of the PastDueReport sheet, write a formula that finds the number of past due invoices that need to be approved by the Approver# in cell K11. Copy the formula down so that a total is listed for each Approver#.
  10. Highlight totals in excess of 2 that were generated in step 9.

Data Files

Suggestions and Hints
  1. None
  2. The solution uses a UNIQUE formula
  3. The solution uses an XLOOKUP formula
  4. The solution uses a COUNTIF formula
  5. The solution uses a megaformula, combining the INDEX, SMALL, IF, MATCH, ROW, ROWS, and COLUMNS formulas. However, users are encouraged to find a simpler solution. The excel date for 4/14/2023 is 45030
  6. The solution uses a COUNT formula
  7. The solution uses a SUM formula
  8. The solution uses a UNIQUE formula
  9. The solution uses a COUNTIF formula
  10. The solution uses conditional formatting

Solution

Challenge129_Solution.xlsx
Solution Video: Challenge 129|EXCEL – Passion for Payables

2

That was an excellent challenge! I took some time to learn how the index function worked and a significant amount of time figuring out how to eliminate the blank rows that my formula produced. Other than the megaformula, the project was rather straight forward and simple to solve. Here is my solution.
Challenge129_Data.xlsx (34.1 KB)

I was also able to solve this problem using Alteryx in about 10 minutes. Although the report is not as uniform, it would be very simple to out put this to an excel document or csv file and do some formatting adjustments from there.
129.yxmd (23.3 KB)

3

In the tech hub training’s recent data challenge, I leaned about megaformulas in Excel for the first time. Navigating their complexities proved challenging, requiring significant time and effort. Despite the initial learning curve, the experience enhanced my problem-solving skills and expanded my Excel proficiency. The training was valuable to increase my advanced Excel functionalities and strengthening my data analysis skill set.
Challenge129_Data.xlsx (28.4 KB)

4

https://studentuml-my.sharepoint.com/:x:/r/personal/victoria_roche_student_uml_edu/Documents/ACCT%203030%20Exercise%201%20Challenge.xlsx?d=wfa61c5ee152d459ba5b514d858fd7141&csf=1&web=1&e=g07EhP

5

Challenge129_Data.xlsx (33.3 KB)

6

Time to Complete: 50 minutes
My rating: Intermediate
This was a fun challenge to learn! I thought this level was more intermediate than beginner, especially with the mega formula we were required to write.
Finished Challenge129_Data.xlsx (27.6 KB)

7

Time to complete: A little over an hour
Rating: Easy to intermediate
I did not use the index function, but rather relied on a “spill” from an IF statement that referenced a range. This required a couple more formatting steps, but got me to the same solution provided!
Challenge129_Data.xlsx (29.7 KB)

8

Time to Complete: 60minutes
Rating: medium
This was a fun challenge, but a little more difficult than I expected. I enjoyed learning new excel functions that I can use in the future.
Challenge129_Data_TN.xlsx (27.7 KB)

9

Time to complete: 60 minutes
Rating: medium
This challenge was fun, but it was a little tougher than I anticipated. It was enjoyable to learn new excel formulas that I can implement going forward.
Challenge129_Data.xlsx (28.4 KB)

10

Time to Complete: Hour
Rating: Beginner to Intermediate
Overall a fun challenge. Can help you to become comfortable using large index formulas. Other than pulling the late invoices the challenge is very straight forward.
Challenge129_Data.xlsx (27.1 KB)

11

Time to complete: 1 hour
My rating: intermediate
I thought the exercise was definitely a bit challenging as I have not used these functions in a while but I liked learning about them when doing this project.
Challenge129_Data.xlsx (27.7 KB)

12

Time to complete: 60 minutes
Rating: Intermediate
Most of the steps use functions that I am comfortable with, except for step 5! I admittedly had to reference the solution document for that step. All of the formulas that I was coming up with would leave blank rows for the invoices that were not past due. However, looking at the solution worked in my favor as I had not heard of the SMALL function before. This was a nice challenge that refreshed skills I already had while still learning!
Challenge129_Data_SNL.xlsx (28.0 KB)