Professor Jozsi ACC 3211 Spreadsheet Assignment: Capital Budgeting Fall 2022

In accordance with Florida Southern College’s Honor Code, I attest that the work for this assignment is strictly my own without assistance from anyone.

Name: ________________________ Signature:________________________

Due date: November 18, 2022

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 plant-wide 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:

full cost of the new machine (including discount, freight, and installation)

tax rate

cost of capital

direct materials, direct labor, variable overhead (and, projected savings for each product cost)

fixed cost expected savings

salvage values for both old and new machines

depreciation rates

annual volume of units

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.

Provide results screen and formulae for each part. Clearly label results, formulae pages, and your name. You must also provide your signature attesting that you preformed the assignment without assistance from others. Failure to provide the ethical confirmation will result in 0 points awarded for the assignment.

Part I:

Using the information presented, calculate the NPV of the estimated after-tax cash flows at 12/31/22 for this “make or buy” decision. Identify which alternative is better. Suggested format is on the next page as follows:

Dates Machine Cost Salvage Value Tax Effect Gain/Loss Product Cost Difference Fixed Cost Decrease Depreciation Shield Working Capital Annual Cash Flows

2022 2023 2024 2025 2026 2027 Change the cost of capital to 10.1%, the direct materials and direct labor savings to 18%, tax rate to 23%, and the cost of the new machine to $286,500, and terms of1/10, n/30. Calculate NPV for the problem again.

Refer to the original facts. Calculate NPV if expected volume is as follows:

2023: 32,700

2024: 31,300

2025: 32,000

2026: 30,900

2027: 29,500

Part II: Using the original data from Part I A, determine the IRR for the project.

Part III: Using the original data from Part I A, determine the payback for the project.

Part IV: Discuss below what other relevant factors you would consider for this decision. Discuss at least five other relevant factors.