Kelly's Boutique has several questions for you that Excel can help answer. 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 the following situations. Save the file as ch7-04_student_name (replacing student_name with your name). Print both a Value view and Formula view of this completed worksheet. Kelly would like to know the following: a. How much she would have to pay at the end of each year, assuming a 7 percent rate of return, to yield $100,000 at the end of 10 years. b. How much she would have at the end of 10 years if she invested $65,000 today, earning 5 percent per year. c. How much she would have at the end of 10 years if she invested $6,325 at the end of each year, earning 9 percent per year. d. How much she would have to invest today to have $154,324 in 10 years, earning 12 percent per year. Kelly has a very fluctuating workforce based on seasonal demand. She's ranged from having 10 employees in one month to 32 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 worksheet should do the following: a. Calculate variable cost per employee, fixed costs, and a prediction of payroll cost with 22 employees using the Hi-Lo method. b. Calculate variable cost per employee, fixed costs, and a prediction of payroll cost with 22 employees using the Least Squares/Regression method. c. 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) Check figures: File 7-05 Hi-Lo Method ?Variable cost/employee $3,318.18 Least Squares / Regression Method ?Fixed cost $19,408.19 File 7-06 Percentage of Sales Method ?Allowance for uncollectible accounts (end) 28,000 Aging Method ?Allowance for uncollectible accounts (end) 19,020

Kelly's Boutique Ch 07-04 Present Value Analysis Annuity payment required: Future need: Term (in years): Interest rate: Investment value in future: Current investment: Interest rate: Term (in years): Investment value in future: Current annuity investment: Interest rate: Term (in years): Invest now: Future need: Interest rate: Term (in years): Year 0 1 2 3 4 5 6 7 8 9 10 Interest Annuity Year 0 1 2 3 4 5 6 7 8 9 10 Interest Investment Year 0 1 2 3 4 5 6 7 8 9 10 Interest Annuity Year 0 1 2 3 4 5 6 7 8 9 10 Interest Investment Investment Investment Kelly's Boutique Ch 07-05 Predicting Costs Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec High Expense Low Expense Difference Hi-Lo Method Variable cost/employee Fixed cost Prediction of expense with 22 employees = Least Squares / Regression Method Variable cost/employee Fixed cost Prediction of expense with 22 employees = Employees 10 12 11 14 17 25 30 32 18 15 12 16 Payroll 52,000 60,000 54,000 63,000 78,000 102,000 109,000 125,000 72,000 65,000 54,000 75,000 Kelly's Boutique Ch 7-06 Allowance for Uncollectible Accounts Aging Method Information Current 0 - 30 Days Past Due 31 - 60 Days Past Due 61 - 90 Days Past Due > 90 Days Past Due Total Past experience ratio for % of sales method Facts: Accounts receivable balance (beg) Sales on account Collections on account Write-offs of accounts receivable Accounts receivable balance (end) Percentage of Sales Method Allowance for uncollectible accounts (beg) Write-offs of accounts receivable Uncollectible accounts expense Allowance for uncollectible accounts (end) Aging Method Allowance for uncollectible accounts (beg) Write-offs of accounts receivable Uncollectible accounts expense Allowance for uncollectible accounts (end) Amount Past experience ratio