instructions and 3 excel sheets are attached

Wine Depot Ch 07-07 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 Wine Depot Ch 07-08 Predicting Costs Month Jan Feb Mar Apr May Jun High Expense Low Expense Difference Hi-Lo Method Variable cost/unit sold Fixed cost Prediction of selling expense with 11,000 units sold = Least Squares / Regression Method Variable cost/unit sold Fixed cost Prediction of selling expense with 11,000 units sold = Units Sold 10,000 12,000 18,000 16,000 14,000 8,500 Selling Expense $28,000 $36,000 $45,000 $27,000 $24,000 $22,000 Wine Depot Ch 7-09 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 The Wine Depot is planning for the future and would like you to prepare a present value analysis. Using the file ch7-07, complete a present value analysis for the following situations. Save the file as ch7-07_student_name (replacing student_ name with your name). Print both a Value view and Formula view of this completed worksheet. The Wine Depot would like to know the following: a. How much they would have to pay at the end of each year, assuming a 3 percent rate of return, to yield $86,421 at the end of 10 years. b. How much they would have at the end of 10 years if they invested $8,000 today earning 2 percent per year. c. How much they would have at the end of 10 years if they invested $550 at the end of each year earning 4 percent per year. d. How much they would have to invest today to have $12,500 in 10 years, earning 1 percent per year. The Wine Depot is trying to better understand the behavior of their selling expenses. They have accumulated selling expenses over the last 6 months and believe units sold are a good predictor of expense behavior. Selling expenses include commissions on sales and advertising. Use the file ch7-08 to complete a cost prediction worksheet. Save the file as ch7-08_student_name (replacing student_name with your name). The worksheet should do the following: a. Calculate variable cost per unit sold, fixed costs, and a prediction of selling expense when 11,000 units are sold using the HiLo method. b. Calculate variable cost per unit sold, fixed costs, and a prediction of selling expense when 11,000 units are sold using the Least Squares/ Regression method. c. Display a chart of selling expense/units sold with a trend line. (Be sure to modify each axis so your scatter diagram is better displayed as you did earlier in this chapter.) During a recent year, the Wine Depot had sales on account of $1,601,542, collections of $1,523,541, write-offs of $23,487, a beginning balance in accounts receivable of $758,271, and a beginning balance in the allowance for uncollectible accounts of $25,121. At year end, $781,513 of accounts receivable were current, $45,812 were 0-30 days past due, $21,012 were 31-60 days past due, $6,422 were 61-90 days past due, and $5,000 were over 90 days past due. The company believes 1.5 percent of sales will not be collected. They also have experience that 180181suggests that 2 percent of all current receivables, 10 percent of receivables 0-30 days past due, 15 percent of receivables 31-60 days past due, 22 percent of receivables 61-90 days past due, and 45 percent of receivables over 90 days past due will not be collected. Using the file ch7-09, complete the allowance for uncollectible accounts analysis for both standard methods. Save the file as ch7-09_student_name (replacing student_name with your name). Print both a Value view and Formula view of this completed worksheet