- In addition, Quality inspectors (Indirect labor) are needed where One inspec- tor 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 yearfor 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 associ- ated costs are: - Raw materials cost (Purchase price) : Silo/unit the rst year and will increase by 1% every year. -The manufacturing process produces waste (scrap/material usage): $0.00 6) (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 Alloca- tion (0H allocation) are also included in the purchase price. Tasks: (Log your answers in the spreadsheet. There is a tab named " An- swers") ' 32"F II I 5 I Mostly sunny I' ,0 team 7 .Calculate the unit cost/year for the option A, \"buying a manufacturing plant" (Use the \"Manufacturing/Outsourcing model" tab in excel) . Calculate the unit cost/yearfor the option B "Outsource" (Use the \"Manu- l\" facturing/Outsourcing Mode tab in excel) . Calculate the NPV for the "Cost of goods manufactured\" for both options and compare. Which options is preferred? Use the Manufacturing/Out- sourcing Model tab in excel . Calculate the NPV for the \"Free Cash Flow\" for both options and compare. Which options is preferred? Use Income Statement Model tab in excel . 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. . 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 . Compare both set of NPV's and IRR's. Why the difference between them? Is this still a good investment based on the NPV and IRR values? 2159 PM 6'? QB) '3 11/26/2022 Q 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 ofa 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 5200 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 fr rn 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/mate- rial 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 bene- ts)). 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 $35,000/yr. Assume that their salary will increase at a rate of 2% per year 2158 PM 11/26/2022 ' 32"F Mostly sunny II I, ,0 Search I re :1) to