BYU Student Author: @Benjamin_Lau
Reviewers: @Sterling_Lane, @jimmy_han
Estimated Time to Solve: 40 Minutes
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
Overview
Life is good! You landed a great internship opportunity in the M&A transaction advisory service line of a renowned accounting firm. Today is the first day of work. The senior associate has assigned you to help him with a financial due diligence (FDD) project. Specifically, you are to prepare an Accounts Receivable Aging Schedule from raw invoice data. It is a common practice in FDD to reconcile client-provided data with data from the database. Your task is to reconcile A/R through constructing an A/R aging schedule.
Since this is the first day of work, your senior wants to test how well you use Excel, especially with the keyboard shortcuts. He told you that using keyboard shortcuts will speed up the process by a lot and differentiate you from your peers. He hopes that in this task, you will try not to use the keyboard at all.
Instructions
Before You Start (5-10 mins)
- When the Challenge204_InvoiceData.xlsm is downloaded, make sure to enable macros because your senior has created a VBA program to track your mouse usage.
a. If VBA is disabled, go to File > Options > Trust Center > Trust Center Settings > Macro Settings > Enable VBA macros.
b. If “Developer” tab does not appear in your Excel, right click anywhere on the tool bar. Click “Customize the Ribbon”. Check the “Developer” box when the new window pops up.
c. Make sure to unblock the file by right-clicking the file icon in the saved folder > choosing Properties from the menu > selecting the Unblock checkbox and OK at the bottom of the General tab. (more guidance on A potentially dangerous macro has been blocked - Microsoft Support)
- Make sure to download If_You_Use_Your_Mouse.bas file. In the opened Challenge204_InvoiceData.xlsm file, go to Developer > Visual Basic (or press ALT + F11). On the right panel of the Visual Basic menu, right click on “ThisWorkbook”, then press Import File. Select the If_You_Use_Your_Mouse.bas file to import. The Modules folder should show up below after importing the file.
- Set up a keyboard shortcut key for this newly imported macro! Go to Developer > Macros. Click on If_You_Use_Your_Mouse (*if there are more than one module shown, choose Module 1), then click Options (*if Options does not show up, please refer to step 1 to fix the issue) . Set the shortcut key as CTRL + SHIFT + D (You can choose other buttons too but CTRL + SHIFT ensures no original shortcuts will be overridden.) Click OK after that and close the Macro box. Press the hotkeys for the macro when you are ready to start! (Sidenote: To end the mouse tracker program, press CTRL + ENTER)
- Create a new column Total Unpaid to capture the amount the clients have not paid before fiscal year-end and a new column Days Outstanding to capture how many days those unpaid amounts are outstanding. Keep in mind that today is 3/10/2024 and fiscal year-end is 12/31/2023.
- Create a new sheet. Create a column with client names (no duplicates). Then, create columns for days outstanding 0-30, 31-60, 61-90, and >90. Use Excel formulas to find the balances in each period category for each client.
- Create a new sheet. Create a title and type in the total A/R at the top. Copy and paste VALUES ONLY the newly created raw aging schedule to the new sheet. (If you do not do values only, the sorting process will be more difficult.) Sort the schedule by 0-30 descending, then by 31-60 descending, then by 61-90 descending, then by >90 descending, and finally by Client Name ascending. Sum the total for each column at the bottom. Present the aging schedule professionally and cleanly. (Hint: you can do that by adding borders, changing cell types, etc.)
When You Finish
Press CTRL + ENTER to stop the macro in the background. A message box should pop up showing you how many times you have moved your mouse. The program is extremely sensitive, so even if you just move a pixel, it counts as a move.
BONUS
Group rows that have zeros in all columns. (“Group” can hide or unhide rows or columns, which is great for clean formatting.)
Data Files
Suggestions and Hints
- Most keyboard shortcuts used here are CTRL + something or ALT + something.
- When using any ALT shortcuts, Excel gives you a list of keys to press on for each icon.
Solution
Challenge204_Solution.xlsm
Solution Video: Challenge 204|EXCEL – A/R Aging
