hello i really need help with excel doc asap. Question 10-11

Collaboration Assessment # 4B - (Due Monday, April 4th, @ 11:59 pm) Problem 10-1: Performing a Basic NPV Analysis The Sweetwater Candy Company would like to buy a new machine that would automatically dip chocolates. The dipping operation is currently done largely by hand. The machine the company is considering costs $105,000. The manufacturer estimates that the machine would be usable for 12 years, but would require the replacement of several key parts at the end of the sixth year. These parts would cost $6,600, including installation. After 12 years, the machine could be sold for about $5,250. The company estimates that the cost to operate the machine will be only $7,000 per year. The present method of dipping chocolates costs $30,000 per year. In addition to reducing costs, the new machine will increase production by 2,000 boxes of chocolates per year. The company realizes a contribution margin of $1.50 per box. A 20% rate of return is required on all investments. Use the information in Exhibit 10-1 and Exhibit 10-2, to determine the appropriate discount factor(s) using tables. Required: 1. What are the net annual cash inflows that will be provided by the new dipping machine? 2. Compute the new machine's net present value using the incremental cost approach. (Round discount factor(s) to 3 decimal places.) Problem 10-2 : Performing an NPV Analysis In eight years, Kent Duncan will retire. He has $300,000 to invest, and he is exploring the possibility of opening a self-service car wash. The car wash could be managed in the free time he has available from his regular occupation, and it could be closed easily when he retires. After careful study, Kent has determined the following: a. A building, in which a car wash could be installed, is available under an eight-year lease at a cost of $2,800 per month. b. Purchase and installation costs of equipment would total $300,000. In eight years, the equipment could be sold for about 10% of its original cost. c. An investment of an additional $3,000 would be required to cover working capital needs for cleaning supplies, change funds, and so forth. After eight years, this working capital would be released for investment elsewhere. d. Both a car wash and a vacuum service would be offered, with a wash costing $3 and the vacuum costing 60 cents per use. e. The only variable costs associated with the operation would be 30 cents per wash for water and 20 cents per use of the vacuum for electricity. f. In addition to rent, monthly costs of operation would be as follows: cleaning, $700; insurance, $150; maintenance, $1,000. g. Gross receipts from the car wash would be about $2,550 per week. According to the experience of other car washes, 70% of the customers using the wash would also use the vacuum. h. Kent will not open the car wash unless it provides at least a 10% return, since this is the amount that could be earned by simply placing the $300,000 in high-grade securities. Use the information in Exhibit 10-1 and Exhibit 10-2, to determine the appropriate discount factor(s) using tables. Required: 1. Assuming that the car wash will be open 52 weeks a year, compute the expected net annual cash receipts (gross cash receipts less cash disbursements) from its operation. (Do not include the cost of the equipment, the working capital, or the salvage value in these computations.) 2-a. Compute the NPV using the method of investment analysis. (Round discount factor(s) to 3 decimal places. Round other intermediate calculations and final answer to the nearest whole number.) 2-b.Would you advise Kent to open the car wash? Why or Why not? Problem 10-11 : Performing an NPV Analysis of a Lease or Buy Decision The Wrongway Ad Agency provides cars for its sales staff. In the past, the company has always purchased its cars from a dealer and then sold the cars after three years of use. The company's present fleet of cars is three years old and will be sold very shortly. To provide a replacement fleet, the company is considering two alternatives as follows: Purchase Alternative. The company can purchase the cars, as in the past, and sell the cars after three years of use. Ten cars will be needed, which can be purchased at a discounted price of $18,400 each. If this alternative is accepted, the following costs will be incurred on the fleet as a whole: Annual cost of servicing, taxes, and licensing Repairs, year 1 Repairs, year 2 Repairs, year 3 $ 6,750 1,750 5,550 6,975 At the end of three years, the fleet could be sold for one-half of the original purchase price. Lease Alternative. The company can lease the cars under a three-year lease contract. The lease cost would be $61,500 per year (with the first payment due at the end of year 1). As part of this lease cost, the owner would provide all servicing and repairs, license the cars, and pay all the taxes. Wrongway would be required to make a $11,250 security deposit at the beginning of the lease period, which would be refunded when the cars were returned to the owner at the end of the lease contract. Wrongway's required rate of return is 18%. Use Exhibits 10-1 and Exhibit 10-2, to determine the appropriate discount factor(s) using tables. Required: 1. Use the total cost approach to determine the present value of the cash flows associated with each alternative. (Negative amounts should be indicated with a minus sign. Round discount factor(s) to 3 decimal places. Round other intermediate calculations and final answers to the nearest whole dollar amounts.) 2. Which alternative should the company accept based on the calculations in part (1)? Why? PROBLEM 10-2 Requirement 1 The expected net annual cash receipts will be: Auto wash cash receipts Vacuum cash receipts Total cash receipts Deduct: Cash disbursements: Water Electricity Rent Cleaning Insurance Maintenance Total cash disbursements Net annual cash receipts Enter interest rate you will be using for your discount factor Requirement 2-a. Item Year(s) Amount of Cash Flows % Factor Present Value of Cash Flows Cost of equipment Working capital needed Net annual cash receipts (above) Salvage of equipment Working capital released Net present value Requirement 2 Enter your "Net present value" answer here AND in CONNECT PROBLEM 10-11 Requirement 1 Item Enter interest rate you will be using for your discount factor Year(s) Amount of Cash Flows x% Factor Present Value of Cash Flows Purchase of fleet: Initial payment cars Annual cost of servicing, taxes and licensing Repairs - Year 1 Repairs - Year 2 Repairs - Year 3 Resale value of the fleet (b) Purchase of Fleet : Present value of cash outflows Lease of cars: Year(s) Amount of % Factor Cash Flows Present Value of Cash Flows Enter interest rate you will be using for your discount factor Initial deposit Lease payments Return of deposit Lease Purchase : Present value of cash outflows Net present value in favour or not in favour of buying the cars Requirement 2 (a) (a - b)