AutoSave Excel Assignment - Part II - Data Set and Project - Spring 2022 Search (Alt+Q) Jessica Valencia JV Comments Share File Home Insert Draw Page Layout Formulas Data Review View Help Cut Calibri 11 AA = = ab Wrap Text General Copy Paste BIU H A Merge & Center $ % 950000 Format Painter Conditional Format as Cell Formatting Table Styles Insert Delete Format > AutoSum Fill Clear T Sort & Find & Filter Select Clipboard F20 A > Font Alignment Number Styles Cells Editing Analyze Data Analysis ^ v fx D E F G H J K L M B 4 Due Date - 04/20/2022- 5:00 PM 456781 The "Purchases" tab of the workbook contains all the purchases a small company had in 2021. The "Not Paid" tab Instructions 1) Complete the table in the "Not Paid" tab utilizing the data in the "Purchases" tab. Completing this data will be useful in your analysis of the year end unpaid orders (accounts receiveable). This table of product prices below will help complete the data set. Payment for orders is due 30 days after the date of purchase and are considered delinquent any day it is unpaid. 10 11 12 13 14 15 16 Product Callaway Recycled Golf Balls Recycled Golf Balls Recycled Top-Flite Gold Balls Cost per ball 2.99 PGSI Assorted Mid-Grade Recycled Golf Balls 2.39 1.75 2.39 17 Titleist Pro V1 Golf Balls 7.00 18 Titleist Pro V1 Recycled Golf Balls 6.50 19 20 21 2) All customers are supposed to have a credit limit of $40,000, the client admits that at times they allow certain customers to purchase over the limit, how many of the clients with outstanding balances at 12/31/21 are over their credit limit? In the "Not Paid" tab, format the order total column to be highlighted in red if the amount exceeds the credit limit, so they easily stand out. 3) What is is average number of days delinquent for all the unpaid orders? How many orders are more than 180 days delinquent? What percentage of the total orders unpaid at 12/31/2021 are delinquient as of 12/31/2021? 22 23 24 4) What are is the average order total for the unpaid orders? 25 26 27 28 29 Ready H 5) After reviewing and analyzing the date for the unpaid orders as of 12/31/2021 what concerns would you have for the company? Can you identify any weaknesses or issues in their current practices? 6) What other accounts may you ask to look at to test the validity of these orders and receivables? Instructions Purchases Not Paid (+) Type here to search o 35F Cloudy + 100% 8:45 AM 4/18/2022