Cristina-Carlos Company manufactures various health products. The company is contemplating continuing to make the product with a new machine or buying the finished product from an outside vendor. Expected volume of sales is: 2023: 26,000 2024: 28,000 2025: 33,000 2026: 31,000 2027: 29,000 The machine used to manufacture the products is 100% depreciated. It must be replaced on 12/31/22 if the company wishes to continue manufacturing the product and could be sold for $11,800 on 12/31/22. The company could place into service a new machine on January 2, 2023 with cost $314,000 and terms of 2/10, n/30, freight of $6,300, and installation costs of $10,700. The new machine, with a 5-year economic life, 3-year recovery period under MACRS, and expected salvage value of $19,000 at the end of 2027, would be more efficient than the old machine, resulting in 15% reductions in materials, 10% reduction in labor, as well as a one-time decrease in working capital of $5,500. The working-capital benefit would apply upon equipment acquisition and cease at the end of 2027. A supplier has quoted a price of $13 per finished unit, which is $2 less than the current full product cost. Current unit product costs are: direct materials $4.50; direct labor $5.50; variable overhead $1; fixed overhead $4. The $4 plantwide fixed overhead rate applied to each unit is based on practical capacity of 32,000 units. Purchasing the units from an outside supplier would save $17,300 in annual avoidable fixed costs. There would be no other changes, except depreciation on the new equipment. The expected sales price per unit is $23 for 2023 through 2026 and $21 in 2027. The income tax rate is 21%. Assume annual cash flows and tax payments are at year-end. Cost of capital is 8.7%. 

Required: Using Excel, perform the analyses described in Part I (A, B, C), Part II, Part III, and Part IV below. Set up an input area to place all of the information: a) full cost of the new machine (including discount, freight, and installation) b) tax rate c) cost of capital d) direct materials, direct labor, variable overhead (and, projected savings for each product cost) e) fixed cost expected savings f) salvage values for both old and new machines g) depreciation rates h) annual volume of units i) working capital difference The goal of the analysis is that changes in data entered in the input area change the spreadsheet values automatically. Since the company is facing mutually exclusive options (continuing to make the product with a new machine versus buying the product from the outside vendor), the two options should be netted.

Answer rating: 100% (QA)

SOLUTION To perform the analysis Ill set up an input area with the following cells A B C D E F G H I J where A Full cost of new machine including disc... View the full answer