Reference no: EM133306569
Question: Develop an Excel spreadsheet model to analyze the scenarios within the case.
- Show After-Tax Cash Flow Analysis for each option (Find at least Present Worth).
- Show one graph with breakeven analysis for all options.
You work at an avionics manufacturing company and are part of a team that will evaluate the company's investment alternatives. The company can invest in the following:
- Invest $6 million for a super computer system to simulate airflow on new aircraft designs
o Implementation and training of employees costs $80,000
o 1st year savings is $1,200,000. The amount goes up each year by 3.5% inflation.
o 8 year life
o $200,000 Year 8
o Use MACRS GDS 5 year recovery
• Update current manufacturing, use one of the following 2 options:
o Buy 11 new programmable robots for a flexible manufacturing work cell to replace old machinery
• Buy from German Company for 305,000 Euro's each
• Other information for all II robots combined below:
• Shipping Handling Insurance: $10000
• Installation: $30000
• Specialized Training: $20000
• Market Value $200.000 year 8.
• Savings in labor and energy costs $700,000 per year in Is' year. Assume inflation rate of 3.5% for labor and energy costs per year.
• Sell old machines for $100,000 now.
• Use MACRS GDS 7 year recovery for robots and old machines
• Life 8 years
o Upgrade current machinery
• Current machines have a 8 year life and were purchased 5 years ago at $3,000,000
• Invest $990,000 in upgrade costs to extend the life of the machines for another 8 years
• Savings in labor resulting from upgrade, $150,000 per year in Is' year dollars. Assume 3.5% inflation rate on labor rate.
• Market Value $200,000 year 8.
• For depreciation for this option complete the depreciation for the last 3 years of the original machine and add to it a new depreciation for the $990,000 upgrade investment.
• Use MACRS GDS 7 year recovery the old machines and start a new 7 year depreciation for the upgrade investment.
• Invest $9.5 million to establish manufacturing capability to build company's next generation unmanned craft
o Increase of revenue in lst year is $1,300,000. This amount goes up each year by 3.5% inflation rate.
o Market Value of $900,000 year 8
o Life 8 years
o Use MACRS GDS 7 year
From Finance Committee:
• Committee has found investors willing to invest $10.5 million.
• Projects must meet after tax MARR 10%
• M an exchange rate, use 1 dollar = 1.01 euro
• State tax rate 7.6%
• Federal tax rate 21%
• Use MACRS GDS depreciation
You're given the following Strategic Direction from Executive Team made up of the CEO, CFO, Chief of Technology:
• Establishing manufacturing capability for next generation unmanned craft is the top priority. Companies that can deliver these as production aircraft will have competitive advantage 10-15 years from now.