Financial Model (Excel homework) Eng. Economics (EGN 3615) 50 pts

Sneakers For All, LLC. is having success selling casual sneaker shoes for everyday use. The company is looking to grow the annual sales and for that has put a comprehensive strategy that includes aggressive marketing and a potential acquisition of a manufacturing plant. Currently they are outsourcing the sneakers manufacturing, but they wonder if their unit cost will be better if they do it themselves.

Their selling price is $200 per unit, and they are on track to sell 50,000 units by year end. The total market size they play in is estimated to be 1,000,000 units, therefore in year 1 they are targeting to capture 5% of the market for a total of 51,000 units. If they are successful implementing their marketing strategy, they should capture share every year as follows:

2022: 0% (= 50,000 units)

2023: 5% (= 51,000 units)

2024: 10%

2025: 15%

2026: 20%

2027: 25%

2028: 25%

And maintain 25% share from there on.

Note: The total market will also grow at a rate of 2% every year.

The company is also investing in Research and Development every year as follows:

2022: $3,000,000

2023: $3,000,000

2024: $1,500,000

2025: $500,000

And keep investing $500,000 from there on.

Sneakers, LLC spends 10% of their sales in Sales and Marketing expenses plus another 3% in General and Administrative expenses.

If they decide to buy the manufacturing plant, they will have to incur in $25,000,000 capital investment to be depreciated in 15 years. (Use straight line method to depreciate the asset) and consider a 35% provision for income taxes.

If they were to buy the manufacturing plant here are the assumptions to consider:

• Raw materials: std cost is $35/unit
• The manufacturing process produces waste at a rate of $8/unit (scrap/material usage)
• One production operator (Direct labor) is capable of producing 2,000 units per year.
• Each production operator earns $75,000/yr (fully burden (means with benefits)). Assume that their salary will increase by 3% every year
• They will also need 2 process engineers (Indirect labor) per year and each engineer makes $85,000/yr. Assume that their salary will increase at a rate of 2% per year
• In addition, Quality inspectors (Indirect labor) are needed where One inspector is capable of inspecting 7,500 units/yr. Each inspector makes $80,000/yr and their salary will increase at a rate of 3% per year too.
• The plant will need $750,000 per year for normal maintenance and repair.
• Their plant Overhead Allocation (OH allocation) is $20 per unit and they are also carrying a depreciation of $64,286 per year from previous investments.

If the company decides to continue outsourcing the manufacturing the associated costs are:

• Raw materials cost (Purchase price) : $112/unit the first 6 years and will increase by $2.00 in year 7, then 1% every year thereafter.
• The manufacturing process produces waste (scrap/material usage): $0.00 (Included in purchase price per unit)
• Direct labor: $0.00 (Included in purchase price per unit)
• They will also need 2 process engineers (Indirect labor) per year and each engineer makes $85,000/yr. Assume that their salary will increase at a rate of 2% per year
• Quality inspectors (Indirect labor) = Each inspector is capable to process 25,000 units and earns $80,000/yr. Their salary will increase at a rate of 3% per year.
• Other costs like Repair and Maintenance, Depreciation and Overhead Allocation (OH allocation) are also included in the purchase price.

Tasks: (Log your answers in the spreadsheet. There is a tab named Answers)

1. Calculate the unit cost/year for the option A, buying a manufacturing plant (Use the Manufacturing/Outsourcing model tab in excel)
2. Calculate the unit cost/year for the option B Outsource (Use the Manufacturing/Outsourcing Model tab in excel)
3. Calculate the NPV for the Cost of goods manufactured for both options and compare. Which options is preferred? Use the Manufacturing/Outsourcing Model tab in excel
4. Calculate the NPV for the Free Cash Flow for both options and compare. Which options is preferred? Use Income Statement Model tab in excel
5. Use the Unit cost of the option selected (Manufacturing Vs Outsource) to calculate the NPV and IRR for the after tax Income (Net Income). Use the Income Statement Model tab.
6. Use the Unit cost of the option selected (Manufacturing Vs Outsource) and calculate the NPV and IRR for the Free Cash Flow. Use the Income Statement Model tab
7. Compare both set of NPVs and IRRs. Why the difference between them? Is this still a good investment based on the NPV and IRR values?

Could you let me know if I am doing this correctly? Check my work plz. I am also having trouble finding IRR.

Transcribed image text