Solution
Hari Kiran answered on
Sep 15 2022
Sneaker 2013
Sneaker 2013
Assumptions
Sometimes in spreadsheets you'll want to put your assumptions at the top, so I'm leaving space here if you'd like to set your up that way
Research and Development Cost $ 200,000
Factory Building $ 1,500,000
Salvage Value of Factory Building $ 150,000
Equipment $ 500,000
Salvage Value of Equipment $ 50,000
Net Working Capital $ 200,000
Depreciation shall be charged as per MACR 5 Years table
Revenue per year $ 800,000
Purchase and other Cost per year $ 300,000
Particulars Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Net Present Value
2012 2013 2014 2015 2016 2017 2018
Initial Cash Outflows (2,000,000) - 0 - 0 - 0 - 0 - 0 - 0
Revenue - 0 800000 800000 800000 800000 800000 800000
(Note: put relevant costs in this line and the ones below)
Purchase and Other Costs 300000 300000 300000 300000 300000 300000
Depreciation - 0 360000 576000 342000 216000 198000 108000
Research and Development Cost (Sunk Cost) - 0 - 0 - 0 - 0 - 0 - 0 - 0
EBIT - 0 140000 -76000 158000 284000 302000 392000
Taxes (40%) - 0 56000 -30400 63200 113600 120800 156800
Net Income (ignoring interest) - 0 84000 -45600 94800 170400 181200 235200
Add: Depreciation - 0 360000 576000 342000 216000 198000 108000
After tax Annual Cashflows - 0 444000 530400 436800 386400 379200 343200
Terminal Cash Inflows
Factory Building $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ 150,000
Equipment $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ 50,000
NWC Changes $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ 200,000
Free Cash flows $ (2,000,000) 444000 530400 436800 386400 379200 743200
Present Value Discount Factore @ 10% 1.00 0.91 0.83 0.75 0.68 0.62 0.56
Present Values - Discount Factor at 10% (2,000,000) 403,636 438,347 328,174 263,916 235,453 419,517 89,045
Present Value Discount Factore @ 15% 1.00 0.87 0.76 0.66 0.57 0.50 0.43
Present Values - Discount Factor at 15% (2,000,000) 386,087 401,059 287,203 220,925 188,529 321,306 (194,891) (105,846)
NPV of Project - at 10% Discount Factor is $ 89,045.
NPV of Project - at 15% Discount Factor is ($ 105,846).
IRR - NPV at Lower Discount Rate 10% = ((rl+((NPVl)/(NPVl-NPVh)) *( rh-rl))
=(0.10 + ((89045 /(89045-(-1055846)))*(0.15-0.10))
=0.1+(89045/194891)*0.05)
=0.1 + ((0.4569))*0.05
= 0.1 +0.0228
0.1228
12.28%
IRR - NPV at higher Discount Rate 15% = ((rh+((NPVh)/(NPVl-NPVh)) *( rh-rl))
=(0.15 + ((-105846 /(89045-(-105846)))*(0.15-0.10))
=0.15+(-105846/194891)*0.05)
=0.15 + ((-0.5431))*0.05
= 0.15 -0.0272
0.1228
12.28%
Payback Period = Year(n-1) + (Initial Investment - Cumulative Cash Inflows upto Year(n-1) ) / Net Cash Inflow of Yearn
Where, Yearn = Year in which Initial Investment is recovered fully
= 5+(2000000-1669528)/419517
Payback Period in years 5.79
Payback period is 5 years 6 months and 18 days
Case_Study
1
a Building Factory
It is an Opportunity cost and can be included in cash flows for capital budgeting analyses.
b Research and Development costs
It is a Sunk Costs. It is i
elevant for future decision making since it is already incu
ed.
c Cannibalization of other sneaker sales
It can be included in cashflows for the analysis of Capital Budgeting
d interest costs
Shall not be included in cashflows for capital budgeting, since it is included in cost of capital along with dividends payable on Equity funds. Cost of Capital used to discount the cash flows to
ing at present value.
e Changes in Net Working Capital
It shall be incuded in cash flows used in (from) the project
f Taxes
It shall be included in cash flows. Tax liabilities will arise on operating profits made and also on profits made on sale of assets or in case of loss on sale of assets and on depreciation tax shield will be available.
g Cost of Goods Sold (or Variable cost)
It is opportunity Cost. It shall be included in cash flows.
h Advertising and Promotion expenses
These are Sunk Costs. Advertising and promotion expenses are already incu
ed and now it is i
elevant for decision making. Hence, these costs shall not be included in cash flows.
i Depreciation
We must consider the effect of 'Depreciation tax Shield' while calculating Profit after tax. Hence it is included in Cash Flows.
2
a
Project’s year zero cash flow is $ 2000,000.
Net operating cash flows is $ 920,000.
C
2018 terminal non-operating cash flows is $ 400,000
d
Sneaker is viable project as its NPV is positive $ 89,045.
IRR of the project is 12.28% and required rate of return in this case is 10%....