Please show work on Excel and Show Formulas
Year 1 Year 2 Year 3 Year 4 Year 5 Units Sales Electron Americas is a midsized electronics manufacturer located in Key West, Florida. The company president is Michael Scott, who inherited the company. When it was founded over 70 years ago, the company originally repaired radios and other household appliances. Over the years, the company expanded into manufacturing and is now a reputable manufacturer of various electronic items. Recently, you have been hired by the company's finance department One of the major revenue-producing items manufactured by Electron Americas is a smartphone. Electron Americas currently has one smartphone model on the market, and sales have been excellent. The smartphone is a unique item in that it comes in a variety of tropical colors and is preprogrammed to play Jimmy Buffett music. However, as with any electronic item, technology changes rapidly, and the current smartphone has limited features in comparison with newer models. Electron Americas can manufacture the new smartphones for $300 each in variable costs. Fixed costs for the operation are estimated to run $4.3 million per year. The estimated sales volume is 75,000, 95,000, 125,000, 130,000, and 140,000 per year for the next five years, respectively. The unit price of the new smartphone will be $650. The necessary equipment can be purchased for $61 million and will be depreciated on a seven-year MACRS schedule. It is believed the value of the equipment in five years will be $3.4 million. The effective tax rate for the company is 35%. The project requires no initial NWC investment, and it requires an NWC balance equal to 15% of sales, after that. The required return for the project is 12%. Equipment Cost Salvage value 6 7 Units Price Variable cost (per unit) 9 Fixed costs (per year) 10 11 Tax rate 12 NWC (% of sales) 13 Required return 14 Required Payback Period (years) 15 16 MACRS Schedule 17 3-year 18 5-year 19 7-year Year 5 Year 6 Year 7 Year 8 Year 1 33.33% 20.00% 14.29% Year 2 44.45% 32.00% 24.49% Year 3 14.81% 19.20% 17.49% Year 4 7.41% 11.52% 12.49% 11.52% 8.93% 5.76% 8.92% 8.93% 4.46% Tax rate NWC (% of sales) Required return Required Payback Period (years) 35% 15% 12% Year 1 Year 2 Year 3 Year 4 Year 5 Units Sales Electron Americas is a midsized electronics manufacturer located in Key West, Florida. The company president is Michael Scott, who inherited the company. When it was founded over 70 years ago, the company originally repaired radios and other household appliances. Over the years, the company expanded into manufacturing and is now a reputable manufacturer of various electronic items. Recently, you have been hired by the company's finance department One of the major revenue-producing items manufactured by Electron Americas is a smartphone. Electron Americas currently has one smartphone model on the market, and sales have been excellent. The smartphone is a unique item in that it comes in a variety of tropical colors and is preprogrammed to play Jimmy Buffett music. However, as with any electronic item, technology changes rapidly, and the current smartphone has limited features in comparison with newer models. Electron Americas can manufacture the new smartphones for $300 each in variable costs. Fixed costs for the operation are estimated to run $4.3 million per year. The estimated sales volume is 75,000, 95,000, 125,000, 130,000, and 140,000 per year for the next five years, respectively. The unit price of the new smartphone will be $650. The necessary equipment can be purchased for $61 million and will be depreciated on a seven-year MACRS schedule. It is believed the value of the equipment in five years will be $3.4 million. The effective tax rate for the company is 35%. The project requires no initial NWC investment, and it requires an NWC balance equal to 15% of sales, after that. The required return for the project is 12%. Equipment Cost Salvage value 6 7 Units Price Variable cost (per unit) 9 Fixed costs (per year) 10 11 Tax rate 12 NWC (% of sales) 13 Required return 14 Required Payback Period (years) 15 16 MACRS Schedule 17 3-year 18 5-year 19 7-year Year 5 Year 6 Year 7 Year 8 Year 1 33.33% 20.00% 14.29% Year 2 44.45% 32.00% 24.49% Year 3 14.81% 19.20% 17.49% Year 4 7.41% 11.52% 12.49% 11.52% 8.93% 5.76% 8.92% 8.93% 4.46% Tax rate NWC (% of sales) Required return Required Payback Period (years) 35% 15% 12%
