Chapter 13: Applying Excel YOUR NAME HERE
can work either of the 2
Data solutions listed below.
Cost of equipment needed $60,000
Working capital needed $100,000
Overhaul of equipment in 4 years $5,000
Salvage value of the equipment in 5 years $10,000
Annual revenues and costs:
Sales revenues $200,000
Cost of goods sold $125,000
Out-of-pocket operating costs $35,000
Discount rate 14%
Enter a formula into each of the cells marked with a ? below
Sales revenues ?
Less cost of goods sold ?
Less out-of-pocket costs ?
Annual net cash inflows ?
Amount of ? Present Value
Year(s) Cash Flow Factor* of Cash Flows
Purchase of equipment Now ? 1.000 ?
Working capital needed Now ? 1.000 ?
Overhaul of equipment ? ? ? ?
Annual net cash inflows from sales of the product line 1- ? ? ? ?
Salvage value of equipment ? ? ? ?
Working capital released ? ? ? ?
Net present value ?
*Use the formulas from Appendix 13B:
Present value of $1 = 1/(1+r)^n
Present value of an annuity of $1 = (1/r)*(1-(1/(1+r)^n))
where n is the number of years and r is the discount rate
Alternate solution, using Excel =NPV, =IRR functions: Amount of
Year(s) Cash Flow
PV of FCF ?
Net PV ?