We provide the solution to this challenge using:
Need a program? Click here.
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:
- Generate a list of executives that need a reminder email
- Isolate a list of past-due invoices and corresponding detail
- Highlight the executives that are causing bottlenecks and late fees.
- Cleanse the data so that there are no mistakes further down the line
- Join the data together
- Use tools to manipulate the data to produce the reports needed
- Output the reports using an output tool, or a browse tool
Suggestions and Hints
- Formula tools are great for creating calculated fields (late fees)
- In order to decide what invoices are due, the filter tool can be useful.
- For reviewing the solution, make sure the solution file and the excel workbook are in the same file folder.