BYU Student Author: @Brett_Lowe
Reviewers: @Andrew, @Christian, @Spencer
Estimated Time to Solve: 30 Minutes
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
Overview
Chettinad Chaats and Curries (CCC) produces gourmet meals that can be found in the refrigerated section of local grocery stores. It offers a variety of South-Indian favorites, including aloo chaat, dosa batter, chicken dum biryani, and more. CCC operates out of a small kitchen and uses two main appliances for production, which are badly worn and have become difficult to use. The equipment has become such an issue that, as the owner, you must now decide whether to repair or replace the existing appliances. You plan to liquidate and retire in 8 years, so each option will be based on that timeline. The new piece of machinery is specialized and would combine the functions of the existing appliances into one, freeing up floor space that could potentially be leased out. However, the new equipment is significantly more expensive than the repairs, so you decide to run an analysis given the facts below:
Old Appliance Information
- Each appliance was purchased 7 years ago
- Assume straight-line depreciation
- Net book value of each appliance is $3,000
- Assume the market value of each appliance is zero
Repair Information
- A repair of the existing appliances would cost roughly $7,500 in direct labor and parts
- This will likely prolong the useful life an additional 8 years
- For tax purposes, this will be amortized on a straight-line basis over the next 5 years
- Even with the repair, the salvage value of each appliance will still be zero.
New Appliance Information
- Three months ago, you paid $3,000 to an engineer to design the new appliance
- Estimated cost of the new appliance is $45,000
- Estimated sales tax is 7.25% of sales price
- Delivery and installation costs are 4% of sales price
- Estimated initial maintenance is 1.2% of sales price
- Useful life of 8 years with $1,500 salvage value, assuming straight-line depreciation
- Approximately 18% greater capacity than the old appliances without increasing labor or hours of operation
Labor and Maintenance
- Direct labor costs with the current appliances are approximately $25,000 per year
- With the new appliance, labor costs will be reduced to about $17,500 per year
- There will also be a reduction in general overhead costs, which are currently allocated at 10% of direct labor
- Normal annual maintenance for the current appliances has been approximately $2,300 higher than the estimated maintenance costs for the new appliance
Additional Information
- Installing the new appliance will free up 250 square feet of floor space. Someone offered to pay $5 per square foot per year to lease the extra space
- Because of the speed of the new equipment, you’ll need to carry more ingredients on hand.
- Initial investment in extra materials will be $2,500 (Additional NWC)
- Net working capital will increase by 20% from the previous year for years 1-4
- Net working capital will decrease by 10% from the previous year for years 5-8
- At the end of 8 years, the investments in net working capital will be fully recovered
- CCC has a corporate income tax rate of 30% and an estimated after-tax cost of capital of 13%
Instructions
- Download the excel sheet provided.
- Copy the given information into the appropriate cells of arrays N12:N27 and N31:N45.
- Build out a model to represent cash flows from each alternative. Making the model dynamic with formulas may prove useful.
- Use the NPV and IRR formulas to calculate the Net Present Value and Internal Rate of Return for each alternative.
- Taking only NPV and IRR into account, which alternative should CCC move forward with? Why? Feel free to consult Google, ChatGPT, etc. to aid you in your explanation.
- Some of the information we were given wasn’t used. Were there any quantitative or qualitative factors that could potentially change your answer from part 5? Explain.
Data Files
Suggestions and Hints
- Be sure to leave out any sunk costs
- Pay special attention to whether the input is an inflow/savings or outflow of cash
- The initial outlay is the cashflow at time 0. This will include only inflows and outflows that arise as a result of purchasing or repairing equipment
- The cash flows for years 1-7 can be calculated by summing Net Income, Change in Net Working Capital, and Depreciation. Depreciation is not a cash outflow, but it was removed to find Net Income. We add it back to reverse the effect on cashflow
- Because you will be retiring in year 8 and Net Working Capital will be fully recovered, the “Change in NWC” for year 8 should mirror the “Total NWC Needed” in year 7.
- Terminal cash flow is the cash flow in year 8. Because you will be liquidating the business, this will sum Net Income, Change in Net Working Capital, Depreciation, and the Salvage Value of any appliances.
Solution