Refer to the inputs below concerning a 4-year project. t=0 t=1 t=2 t=3 t=4 Inves
ID: 2720321 • Letter: R
Question
Refer to the inputs below concerning a 4-year project.
t=0
t=1
t=2
t=3
t=4
Investment
1,000,000
Units
2,000
2,500
2,500
2,000
Price/unit
250
Cost/unit
40
SGA/year
125,000
Depreciation is straight line
Taxes
34%
Dis. Rate
9%
Net Working Capital is 30% of next year's sales
With your team:
Calculate project cash flows (OCF, ICF, CF) and NPV.
Assuming that units sold per year are constant, what is NPV BE?
Develop and submit a spreadsheet with clearly labeled material that is formatted to be printable on 2 pages maximum.
(Note: you may want to use the Excel function goal seek to solve for BE. Alternatively, you can use the formula given in the text or slides.)
t=0
t=1
t=2
t=3
t=4
Investment
1,000,000
Units
2,000
2,500
2,500
2,000
Price/unit
250
Cost/unit
40
SGA/year
125,000
Depreciation is straight line
Taxes
34%
Dis. Rate
9%
Net Working Capital is 30% of next year's sales
Explanation / Answer
1)
2)
Using Excel function goal seek to solve for BE , We get units sold per year are constant = 2274 Unit approx
t=0 t=1 t=2 t=3 t=4 Investment [a] 1,000,000 Units [b] 2,000 2,500 2,500 2,000 Price/unit [c] 250 250 250 250 Cost/unit [d] 40 40 40 40 SGA/year [e] 125,000 125,000 125,000 125,000 Depreciation [f = 1000000/4] 250,000 250,000 250,000 250,000 Net income [g =( (c-d)*b - e-f)*(1-tax rate)] 29700 99000 99000 29700 Working Capital [ h = 30%*Next Year Sale] 150,000.00 187,500.00 187,500.00 150,000.00 - Change in Working Capital [i] 150,000.00 37,500.00 - (37,500.00) (150,000.00) Cash Flow [j = g +f -a-i]] (1,150,000.00) 242,200.00 349,000.00 386,500.00 429,700.00 Discount Factor [k] 1 0.91743 0.84168 0.77218 0.70843 Present value [l= j*k] (1,150,000.00) 222,201.55 293,746.32 298,447.57 304,412.37 Net Present Value [m= sum of l] (31,192.19)Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.