Chapter 7 Case Problem 1: KELLY'S BOUTIQUE Kelly's Boutique has several questions for you that Excel can help answer. 1 Kelly is planning for the future and would like you to prepare a present value analysis. Using the file ch7-04 complete a present value analysis for a. the following situations. Save the file as ch7-04_student_name (replacing student_name with your name). Print this completed worksheet. Kelly would like to know the following: How much she would have to pay at the end of each year, assuming a 5 percent rate of return, to yield $150,000 at the end of 10 years. b. How much she would have at the end of 10 years if she invested $75,000 today, earning 5 percent per year. How much she would have at the end of 10 years if she invested $4,785 at the end of each year, earning 5 percent per year. d. How much she would have to invest today to have $204,530 in 10 years, earning 5 percent per year. c. 2 Kelly has a very fluctuating workforce based on seasonal demand. She's ranged from having 10 employees in one month to 35 employees in another month. Some employees are paid a salary, others are paid hourly. She would like to know more about how these costs behave. Use the file ch7-05 to complete a cost prediction worksheet. Save the file as ch7- 05_student_name (replacing student_name with your name). The work- sheet should do the following: Calculate variable cost per employee, fixed costs, and a prediction of payroll cost with 43 employees using the Hi-Lo method. b. Calculate variable cost per employee, fixed costs, and a prediction of payroll cost with 43 employees using the Least Squares/Regression method. Display a chart of payroll/employees with a trend line. (Be sure to modify each axis so your scatter diagram is better displayed, as you did earlier in this chapter) a. c. 3 During a recent year Kelly's Boutique had sales on account of $6,025,000, collections of $5,800,000, write-offs of $50,000, a beginning balance in accounts receivable of $500,000, and a beginning balance in the allowance for uncollectible accounts of $37,000. At year end, $600,000 of accounts receivable were current, $39,000 were 030 days past due, $18,000 were 3160 days past due, $10,000 were 6190 days past due, and $8,000 were over 90 days past due. The company believes .8 percent of sales will not be collected. They also have experience that suggests that 4 percent of all current receivables, 8 percent of receivables 030 days past due, 20 per- cent of receivables 3160 days past due, 25 percent of receivables 61-90 days past due, and 75 percent of receivables over 90 days past due will not be collected. Using the file ch7-06, complete the allowance for uncol- lectible accounts analysis for both standard methods. Save the file as ch7- 06_student_name (replacing student_name with your name). Print this completed worksheet