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 calan will increase hu 2% avon wear 75.F O 29 99+ 7:46 PM 31 Partly cloudy Search 11/26/2022 30If 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) : $110/unit the first year and will increase by 1% every year. 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. Tarbe. 1 75.F 99+ Search 29 N 7:46 PM 31 30 Partly cloudy 11/26/2022In 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) : $110/unit the first year and will increase by 1% every year. 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) B. 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 OneDrive . . . X and IRR for the "Free Cash Flow". Use the "Income Statement Model" tab 7. Compare both set of NPV's and IRR's. Why the difference between them? Is this still a good Screenshot saved investment based on the NPV and IRR values? The screenshot was added to your OneDrive. 75.F 7:47 PM Search 29 199+ N 31 Partly cloudy 11/26/2022 30