In-House Manufacturing:
Unit Average Selling Price
Sales & Marketing % of Revenue
General & Admin % of Revenue
Income tax % provision
Unit Cost
MARR
$
200.00
10%
3%
35%
–
As per unit cost per year calculated
8.50%
Units per year calculation
Worldwide Market (Units)
% Sneakers LLC share
Sneakers, LLC units
+
Free Cash Flow Model
P&L
Revenue
Avg Unit Selling Price
Units
2022
1,000,000
0%
50,000
2023
1,020,000
5%
51,000
2022
2023
$0
$0
50,000
51,000
–
Cost of Goods Sold
Cost Per Units (line 32 next tab)
Units
$
–
$
–
=
Gross Profit
GP %
$
–
$
–
–
0%
0%
Operating Expenses
Research & Development
–
SG&A
Sales & Marketing
General & Admin
$
$
$
–
$
$
$
–
–
Acquisition Depreciation
$
–
$
–
=
IBT (Income Before Tax)
10%
3%
35% –
=
8.5%
Tax
Net Income
NPV
IRR
+
–
=
0.085
Non-Cash Expenses
Depreciation
$
–
$
Non-P&L Cash Outlays
Acquisition Cost
Annual Depreciation
$
25,000,000
Free Cash Flow
Cumulative Cash Flow
$
$
(25,000,000) $
(25,000,000) $
–
(25,000,000)
NPV
IRR
1
Payback Years Calculation
2.0
Outsourcing:
Unit Average Selling Price
Sales & Marketing % of Revenue
General & Admin % of Revenue
Income tax % provision
Purchase Price
Unit Cost
+
Free Cash Flow Model
P&L
Revenue
Avg Unit Selling Price
$
200.00
10%
3%
35%
–
As per Outsourcing model (next tab)
2022
2023
$0
$0
Units
51,000
–
Cost of Goods Sold
Cost Per Units
Units
$
–
$
–
=
Gross Profit
GP %
$
–
$
–
–
0%
0%
Operating Expenses
Research & Development
–
SG&A
Sales & Marketing
General & Admin
$
$
$
–
$
$
$
–
–
Acquisition Depreciation
$
–
$
–
=
IBT (Income Before Tax)
$
–
$
–
Tax
$
–
$
–
Net Income
$
–
$
–
$
–
$
–
Non-P&L Cash Outlays
Acquisition Cost
Annual Depreciation
$
–
Free Cash Flow
Cumulative Cash Flow
$
$
10%
3%
35% –
=
8.5%
NPV
IRR
+
–
=
0.085
50,000
Non-Cash Expenses
Depreciation
NPV
IRR
–
–
$
$
–
Payback Years Calculation
1
1.0
Cells with formulas already
Cells where the student enters data or a formula
unit cost per year calculated
2024
1,040,400
2025
1,061,208
2026
1,082,432
2027
1,104,081
2028
1,126,162
2029
1,148,686
2024
2025
2026
2027
2028
2029
$0
$0
$0
$0
$0
$0
–
–
–
–
–
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
0%
0%
0%
0%
0%
0%
$
$
$
–
$
$
$
–
$
$
$
–
$
$
$
–
$
$
$
–
$
$
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
$
$
–
$
–
$
–
$
–
$
–
$
–
$
$
$
$
$
(25,000,000) $ (25,000,000) $ (25,000,000) $ (25,000,000) $ (25,000,000) $ (25,000,000)
3.0
4.0
5.0
6.0
7.0
8.0
$46.5MM
Outsourcing model (next tab)
2024
2025
$0
2026
$0
2027
$0
2028
$0
2029
$0
$0
–
–
–
–
–
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
0%
0%
0%
0%
0%
0%
$
$
$
–
$
$
$
–
$
$
$
–
$
$
$
–
$
$
$
–
$
$
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
–
$
$
–
–
$
$
–
–
$
$
–
–
$
$
–
–
$
$
–
–
$
$
–
1.0
1.0
1.0
1.0
1.0
1.0
2030
1,171,659
2031
1,195,093
2032
1,218,994
2030
2031
2032
$0
$0
$0
–
–
–
$
–
$
–
$
–
$
–
$
–
$
–
0%
0%
0%
$
$
$
–
$
$
$
–
$
$
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
$
$
$ (25,000,000) $ (25,000,000) $ (25,000,000)
9.0
2030
10.0
2031
$0
11.0
2032
$0
$0
–
–
–
$
–
$
–
$
–
$
–
$
–
$
–
0%
0%
0%
$
$
$
–
$
$
$
–
$
$
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
–
$
$
–
–
$
$
–
–
$
$
–
1.0
1.0
1.0
Cells with formulas already
Cells where the student enters data or formu
Units per year calculation
2022
1,000,000
0%
50,000
Worldwide Market (Units)
% Sneakers LLC share
Sneakers, LLC units
2022
Per Unit
$35
$8
Units Per Employee
Units Per Employee
2,000
7,500
Raw Materials
STD Materials
Materials Usage / Scrap Variance
Volume (Units)
$
–
Direct Labor
Annual Employee Rate
# of Direct Labor Headcount
$
$
75,000
Indirect Labor
Engineer Cost
Anuual Employee Rate
# of Engineers
$
$
$
85,000
Quality Inspector Cost
Annual Employee Rate
# of Quality Inspectors
$
$
80,000
$
–
Repairs & Maintenance
OH Rate Per Unit
$20
Plant Overhead Allocation
Depreciation
Total Cost of Goods Manufactured
Cost Per Unit
Non-Cash Expenses
–
Depreciation
8.5%
Free Cash Flow
NPV of Total Cost of Goods Mfg
NPV of Free Cash Flow
$
–
rmulas already
the student enters data or formula
2023
1,020,000
5%
51,000
2024
1,040,400
2025
1,061,208
2026
1,082,432
2027
1,104,081
2028
1,126,162
In-House Manufacturing (after acquisition of mfg plant)
2023
2024
$
2025
$
$
$
$
$
77,250 $
$
79,568 $
$
81,955 $
$
84,413 $
$
86,946 $
89,554
$
$
$
$
$
86,700 $
$
$
88,434 $
$
$
90,203 $
$
$
92,007 $
$
$
93,847 $
95,724
$
$
$
82,400 $
$
84,872 $
$
87,418 $
$
90,041 $
$
92,742 $
95,524
$
–
–
–
–
–
–
$
–
$
2028
–
$
–
2027
$
$
–
2026
$
–
$
$
–
$
–
$
–
$
–
$
–
$
–
$
–
2029
1,148,686
2030
1,171,659
2031
1,195,093
2032
1,218,994
2029
2030
2031
2032
mfg plant)
$
–
$
–
$
–
$
–
$
$
$
92,241 $
$
95,008 $
$
97,858 $
100,794
$
$
$
$
$
97,638 $
$
$
99,591 $
$
$
101,583 $
103,615
$
$
$
98,390 $
$
101,342 $
$
104,382 $
107,513
$
–
–
–
–
$
$
$
Purchase Price
Purchase price increase per year
$
–
$
–
$
–
$
–
2022
Per Unit
Purchase Price
Purchase price increase per year
Raw Materials
STD Materials
Materials Usage / Scrap Variance
Volume (Units)
$
$
–
$
$
$
110.00
75,000
$
$
$
85,000
2
Indirect Labor
Engineer Cost
Anuual Employee Rate
# of Engineers
$
$
80,000
25,000
Quality Inspector Cost
Annual Employee Rate
# of Quality Inspectors
$
–
1.01
# of Engineers
Units Per Employee
Repairs & Maint
OH Rate Per Unit
$
Direct Labor
Annual Employee Rate
# of Direct Labor Headcount
–
Repairs & Maintenance
$0
Plant Overhead Allocation
Depreciation
Total Cost of Goods Manufactured
Cost Per Unit
Non-Cash Expenses
–
Depreciation
8.5%
Free Cash Flow
NPV of Total Cost of Goods Mfg
NPV of Free Cash Flow
$
–
Outsource Proposal
2023
2024
$
$
2025
$
$
$
$
$
$
$
111.10 $
$
77,250 $
112.21 $
$
79,568 $
113.33 $
$
81,955 $
114.47 $
$
84,413 $
115.61 $
$
86,946 $
116.77
89,554
$
$
$
$
$
86,700 $
$
$
88,434 $
$
$
90,203 $
$
$
92,007 $
$
$
93,847 $
95,724
$
$
$
82,400 $
$
84,872 $
$
87,418 $
$
90,041 $
$
92,742 $
95,524
$
–
–
–
–
–
–
$
–
$
$
2028
–
$
–
2027
$
$
$
–
2026
$
–
$
$
$
–
$
–
$
–
$
–
$
–
$
–
$
–
2029
2030
$
$
2031
$
$
–
$
$
$
117.93 $
$
92,241 $
119.11 $
$
95,008 $
120.31 $
$
97,858 $
121.51
100,794
$
$
$
$
$
97,638 $
$
$
99,591 $
$
$
101,583 $
103,615
$
$
$
98,390 $
$
101,342 $
$
104,382 $
107,513
$
–
–
–
–
$
–
$
$
2032
$
–
$
$
$
–
$
–
$
–
$
–
$
–
Answers:
1. Calculate the unit cost/year for the option A, µying a manufacturing plant(Use the anufacturing/Outsourc
2022
2023
2024
2025
Cost Per Unit
2. Calculate the unit cost/year for the option B õtsource(Use the anufacturing/Outsourcing Model4ab in e
2022
2023
2024
2025
Cost Per Unit
3. Calculate the NPV for the ïst of goods manufactured&or both options and compare. Which options is preferr
Buy a mfg. plant
NPV of Total Cost of Goods Mfg
Option recommended:
4. Calculate the NPV for the ²ee Cash Flow&or both options and compare. Which options is preferred? Use Inco
Buy a mfg. plant
NPV of Free Cash Flow
Option recommended:
5. Use the Unit cost of the option selected (Manufacturing Vs Outsource) to calculate the NPV and IRR for the fte
Recommended option:
NPV (net income)
IRR
6. Use the Unit cost of the option selected (Manufacturing Vs Outsource) and calculate the NPV and IRR for the ²
Recommended option:
NPV (Free Cash flow)
IRR
7. Compare both set of NPVàand IRRà(from questions 5 & 6 above). Is there a difference between them? Why?
Manufacturing/Outsourcing model4ab in excel)
2026
2027
2028
2029
2030
2031
2032
tsourcing Model4ab in excel)
2026
2027
2029
2030
2031
2032
2028
Which options is preferred? Use the Manufacturing/Outsourcing Model tab in excel
Outsourcing
NPV of Total Cost of Goods Mfg
ns is preferred? Use Income Statement Model tab in excel
Outsourcing
NPV of Free Cash Flow
NPV and IRR for the fter tax Income (Net Income). Use the ncome Statement Model4ab.
e NPV and IRR for the ²ee Cash Flow Use the ncome Statement Model
e between them? Why? Is this still a good investment based on the NPV and IRR values?

Purchase answer to see full
attachment