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.
