You have been employed as a consultant to Golf R Us, Inc. The company has been...
Fantastic news! We've Found the answer you've been seeking!
Question:
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)
Expert Answer:
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
Related Book For
Financial Accounting and Reporting a Global Perspective
ISBN: 978-1408076866
4th edition
Authors: Michel Lebas, Herve Stolowy, Yuan Ding
Posted Date:
Students also viewed these general management questions
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Wally Los Gatos, owner of Wally's Wonderful World of Wallcoverings, Etc., has hired you as a consultant to design a database management system for his new online marketplace for wallpaper, draperies,...
-
1. Identify some of the weaknesses in KU's internal controls that allowed fraud to occur in the athletic department. Explain your answer. 2. Do you think that the sentences given to the perpetrators...
-
4. In cells B4:B15, type a yearly salary for each staff member. Format these values as Currency with no decimal places. 5. Calculate the new salary with a 10% increase in column C. 6. Calculate the...
-
Oxford Corporation began operations in 2012 and reported pretax financial income of $225,000 for the year. Oxfords tax depreciation exceeded its book depreciation by $40,000. Oxfords tax rate for...
-
The following information relates to Perry Somers financial affairs in 20X1: 1. Somer is employed as a salesman and is remunerated by commissions. He must pay all of his own expenses. During 20X1, he...
-
What are the values of currents \(I_{\mathrm{B}}\) and \(I_{\mathrm{C}}\) in Figure P22.11? The directions of the currents are as noted. 5A Ic 7 A 3 A FIGURE P22.11
-
Shannons Kettle Corn is a small refreshment stand located near a football stadium. Its fixed expenses total $400 per week and the variable cost per bag of popcorn is $0.50. Complete the table on the...
-
In 2023, USCO, which is subject to a 21% U.S. corporate tax rate, has $1 Million of worldwide active income which is all from foreign sources in Country X. Assume USCO's foreign-source active income...
-
The following table summarizes the operating results for Bene Petits first year of operations: Bene Petit First year operating data: Single (1 serving) Dual (2 servings) Family (4 servings) Total...
-
Riya is a resident of Australia. She is a student pursuing her Bachelor's degree in engineering at a university in Sydney. During this current tax year she received the following amounts: a) Salary...
-
Winey Cork Limited uses the allowance method for estimating uncollectible accounts. Required: Prepare journal entries to record the following transactions: January 5 Sold merchandise to Smith Inc for...
-
Read the Article: "Markets Kick Off 2023 in the Red" by Barron's. (link below)...
-
Spilly Ltd. uses the periodic inventory system and applied FIFO inventory costing. At the end of the annual accounting period, December 31, 2024, the accounting records for the best selling item in...
-
the estate of a grandmother provides 1/3 of the total for her oldest daughter, 1/5 of the total for her younger son, 1.6 of the total to her younger daughter, 1/10 of the total to charity, and the...
-
Beer Company uses the allowance method to account for uncollectible accounts. On January 1, the allowance for doubtful accounts had a credit balance of $2,000. The balance in the accounts receivable...
-
The following transactions occurred at Slinky Inc., a retail toy store, which uses a perpetual inventory system: July 1 July 3 July 4 July 6 July 7 July 8 July 17 July 30 Slinky established a petty...
-
Floyd Distributors, Inc., provides a variety of auto parts to small local garages. Floyd purchases parts from manufacturers according to the EOQ model and then ships the parts from a regional...
-
The following list contains data or information that might be provided about a business. 1 List of managers and directors 2 Compensation packages of directors and managers 3 List of major competitors...
-
Due to the special capital market situation in China during the period 20072008, the authors voluntarily left this case un-updated. Youngor Group, founded in 1979, has grown over 30 years into a...
-
On the Internet, or in the library, find the annual reports of four companies from different sectors of activity in a given country or in different countries. Required 1. How are liabilities...
-
If someone is in the 22 percent marginal tax bracket, is that persons entire income taxed at 22 percent? Why or why not?
-
What are the major categories of adjustments to gross income? For a taxpayer in the 22 percent marginal tax bracket, how much would adjustments totaling $10,000 save in taxes?
-
Although the basic principles of personal finance are the same for men and women, what are the social and demographic factors that make these decisions especially important for women?
Study smarter with the SolutionInn App