You have been employed as a consultant to Golf R Us, Inc. The company has been...

Transcribed Image Text:

You have been employed as a consultant to Golf R Us, Inc. The company has been having problems with account receivables and they want to use various dunning methods to better their receivables. But first, they need to compile all the customers who have not paid so that they can demand payment that includes finance charges. Two files which contain what products were bought, how many balls were bought, when the balls have been bought, and payment history have been extracted out of different database tables. Rong Wright, the manager of Golf R Us, has asked you to combine the different files to one Excel worksheet and clean up the data. He wants you to name the worksheet as "Customers". One of the files contains a Customer ID column. The Customer_ID is a result of an algorithm that is employed by the company. The algorithm used by the company is as follows: The format of ID for each customer needs to be "lastname" followed by an underscore followed by a number starting with "0001". The company added the character "D" to the end of a created ID if there is a repeat of any last name. (For example, let us say that we have an ID "Madigan_0001"; if there are multiple Madigan as last names, the first Madigan will be "Madigan_0001", the next Madigan will be "Madigan_0002D", the following one will be "Madigan_00030", and so on. The next name Mae will be Mae_0004). The price catalog of the products is as follows: Products Titleist Pro V1 Golf Balls Titleist Pro V1 Recycled Golf Balls PGSI Assorted Mid-Grade Recycled Golf Balls Recycled Top-Flite Gold Balls Recycled Golf Balls Callaway Recycled Golf Balls Balls per pack 50 12 12 24 12 15 Pack Price 49.99 17.99 14.99 11.99 9.99 9.99 The finance charges for delinquent accounts as of February 14, 2018 are as follows (assume that the day that you are preparing the delinquent charge is February 14, 2018): No of Days Finance charges <-90 days 6% of total price <=180 days 10% of total price 15% of total price 20% of total price <=270 days <=360 days >360 days 35% of total price Note: To calculate the finance charges, you may need to find the number of days (NDays) between purchased date and the day that you are preparing the delinquent charge. 1. Set up the excel model using the data (20 points) which contains worksheets named "Title", "Assumptions", "Customers", "FC_Model" Please list any assumptions you have made in the "Assumptions" worksheet. 2. (Use Input data that is available in two text files) (60 points). The Excel model worksheet named "FC_Model" should contain the following data: ID, NoofBalls Purchased, Product, PurchasedDate, SalesAmount NotPaid, NDays, FinanceCharge Amount 3. Prepare an appropriate chart to compare the number of "paid" customers and "not-paid" customers. (20 points) You have been employed as a consultant to Golf R Us, Inc. The company has been having problems with account receivables and they want to use various dunning methods to better their receivables. But first, they need to compile all the customers who have not paid so that they can demand payment that includes finance charges. Two files which contain what products were bought, how many balls were bought, when the balls have been bought, and payment history have been extracted out of different database tables. Rong Wright, the manager of Golf R Us, has asked you to combine the different files to one Excel worksheet and clean up the data. He wants you to name the worksheet as "Customers". One of the files contains a Customer ID column. The Customer_ID is a result of an algorithm that is employed by the company. The algorithm used by the company is as follows: The format of ID for each customer needs to be "lastname" followed by an underscore followed by a number starting with "0001". The company added the character "D" to the end of a created ID if there is a repeat of any last name. (For example, let us say that we have an ID "Madigan_0001"; if there are multiple Madigan as last names, the first Madigan will be "Madigan_0001", the next Madigan will be "Madigan_0002D", the following one will be "Madigan_00030", and so on. The next name Mae will be Mae_0004). The price catalog of the products is as follows: Products Titleist Pro V1 Golf Balls Titleist Pro V1 Recycled Golf Balls PGSI Assorted Mid-Grade Recycled Golf Balls Recycled Top-Flite Gold Balls Recycled Golf Balls Callaway Recycled Golf Balls Balls per pack 50 12 12 24 12 15 Pack Price 49.99 17.99 14.99 11.99 9.99 9.99 The finance charges for delinquent accounts as of February 14, 2018 are as follows (assume that the day that you are preparing the delinquent charge is February 14, 2018): No of Days Finance charges <-90 days 6% of total price <=180 days 10% of total price 15% of total price 20% of total price <=270 days <=360 days >360 days 35% of total price Note: To calculate the finance charges, you may need to find the number of days (NDays) between purchased date and the day that you are preparing the delinquent charge. 1. Set up the excel model using the data (20 points) which contains worksheets named "Title", "Assumptions", "Customers", "FC_Model" Please list any assumptions you have made in the "Assumptions" worksheet. 2. (Use Input data that is available in two text files) (60 points). The Excel model worksheet named "FC_Model" should contain the following data: ID, NoofBalls Purchased, Product, PurchasedDate, SalesAmount NotPaid, NDays, FinanceCharge Amount 3. Prepare an appropriate chart to compare the number of "paid" customers and "not-paid" customers. (20 points)

Answer rating: 100% (QA)

1 Set up the Excel model using the given data Assumptions Worksheet In the Assumptions worksheet list the assumptions you have made during the analysi... View the full answer