Financial Model (Excel homework) - Eng. Economics (EGN 3615)

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 NPV's and IRR's. Why the difference between them? Is this still a good investment based on the NPV and IRR values?

D67 X V fx 1 2 A B C D E F G H J K L M N 0 1 In-House Manufacturing: Unit Average Selling Price $ 200.00 Cells with formulas already Sales & Marketing % of Revenue 10% Cells where the student enters data or a formula General & Admin % of Revenue 3% Income tax % provision 35% Unit Cost As per unit cost per year calculated MARR 8.50% 10 11 12 Units per year calculation 13 2022 2023 2024 2025 2026 14 2027 2028 2029 2030 2031 2032 Worldwide Market (Units 1,000,000 1,020,000 1,040,400 15 1,061,208 1,082,432 1,104,081 1,126,162 1,148,686 1,171,659 1,195,093 % Sneakers LLC share 1,218,994 09 5% 10% 16 15%% 20% 25% 25%% 25% 25% 25% 25% Sneakers, LLC units 50,000 51,000 104,040 159,181 216,486 17 276,020 281,541 287,171 292,915 298,773 304,749 18 19 20 21 Free Cash Flow Model 2022 2023 2024 2025 2026 22 2027 2028 2029 1030 P&L 2031 2032 23 Revenue $10,000,000 $10,200,000 $20,808,000 $31,836,240 $43,297,286 24 $55,204,040 $56,308,121 $57,434,283 $58,582,969 $59,754,628 $60,949,721 Avg Unit Selling Price 200 $ 200 $ 200 $ 200 $ 200 $ 200 25 200 $ 200 $ 200 $ 200 $ 200 Units 50,000 1,000 104,040 159,181 216,486 276,020 26 281,541 287,171 292,915 298,773 304,749 27 28 Cost of Goods Sold $ 622,214,936 $ 622,277,936 $ 625,619,456 $ 629,093,352 $ 632,703,581 $ 636,454,209 $ 636,801,994 $ 637,156,735 $ 637,518,571 $ 637,887,644 $ 638,264,098 29 Cost Per Units (line 32 next tab) 12,444 $12,202 $6,013 3,952 2,923 $2,306 2,262 30 2,219 $2,176 $2,135 2,094 Units 50,000 51,000 104,040 159,181 216,486 276,020 281,541 287,171 292,915 298,773 304,749 Gross Profit 33 $ (612,214,936) $ (612,077,936) $ (604,811,456) $ (597,257,112) $ (589,406,295) $ (581,250,168) $ (580,493,873) $ (579,722,452) $ (578,935,602) $ (578,133,016) $(577,314,377) GP % 61229 -6001% -2907% -1876% -1361% 1053% -1031% 34 -1009% 988% 968% -947% 35 Operating Expenses 36 Research & Development $ 3,000,000 $ 3,000,000 $ 1,500,000 $ 500,000 $ 500,000 $ 500,000 $ 500,000 $ 500,000 $ 500,000 $ 500,000 | $ 500,000 38 SG&A 1,300,000 $ 1,326,000 $ 39 2,705,040 $ 4,138,711 $ 5,628,647 $ 7,176,525 $ 7,320,056 $ 7,466,457 $ 7,615,786 $ 7,768,102 $ 7,923,464 10% Sales & Marketing 1,000,000 $ 1,020,000 $ 2,080,800 $ 3,183,624 $ 4,329,729 $ 5,520,404 $ 40 5,630,812 $ 5,743,428 $ 5,858,297 $ 3% General & Admin 5,975,463 $ 6,094,972 300,000 $ 306,000 $ 624,240 $ 41 955,087 $ 1,298,919 $ 1,656,121 1,689,244 $ 1,723,029 $ 1,757,489 $ 1,792,639 $ 1,828,492 42 Acquisition Depreciation 43 8,750,000 $ 8,166,667 $ 7,622,222 $ 7,114,074 $ 6,639,802 $ 6,197,149 $ 5,784,006 $ 5,398,405 $ 5,038,512 $ 4,702,611 $ 4,389,103 44 BT (Income Before Tax) 45 $ (615,214,936) $ (615,077,936) $ (606,311,456) $ (597,757,112) $ (589,906,295) $ (581,750,168) $ (580,993,873) $ (580,222,452) $ (579,435,602) $ (578,633,016) $(577,814,377) 46 35% - Tax 47 $ (215,325,228) $ (215,277,278) $ (212,209,010) $ (209,214,989) $ (206,467,203) $ (203,612,559) $ (203,347,856) $ (203,077,858) $ (202,802,461) $ (202,521,555) $(202,235,032) 48 Net Income 49 $ (399,889,708) $ (399,800,658) $ (394,102,446) $ (388,542,123) $ (383,439,092) $ (378,137,610) $ (377,646,018) $ (377,144,594) $ (376,633,141) $ (376,111,460) $(375,579,345) 50 8.5% NPV $260,876,620.27 51 IRR 52