Chapter_11_Applying_Excel.xls
Chapter 11: Applying Excel
Data
Example E
Cost of equipment needed $60,000
Working capital needed $100,000
Overhaul of equipment in four years $5,000
Salvage value of the equipment in five 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%
Now 1 2 3 4
Purchase of equipment ?
Investment in working capital ?
Sales ? ? ? ?
Cost of goods sold ? ? ? ?
Out-of-pocket operating costs ? ? ? ?
Overhaul of equipment ?
Salvage value of the equipment
Working capital released
Total cash flows (a) ? ? ? ? ?
Discount factor (14%) (b) ? ? ? ? ?
Present value of cash flows (a) x (b) ? ? ? ? ?
Net present value ?
Present value of $1 = 1/(1+r)^n
Exhibit 11-7
Years
Enter a formula into each of the cells marked with a ? below
Chapter_11_Applying_Excel.xls
5
?
?
?
?
?
?
?
?
Page 2
Chapter_11_Applying_Excel.xls
Chapter 11: Applying Excel
Data
Example E
Cost of equipment needed $60,000
Working capital needed $100,000
Overhaul of equipment in four years $5,000
Salvage value of the equipment in five 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%
Now 1 2 3 4
Purchase of equipment (60,000)$
Investment in working capital (100,000)$
Sales 200,000$ 200,000$ 200,000$ 200,000$
Cost of goods sold (125,000)$ (125,000)$ (125,000)$ (125,000)$
Out-of-pocket operating costs (35,000)$ (35,000)$ (35,000)$ (35,000)$
Overhaul of equipment (5,000)$
Salvage value of the equipment
Working capital released
Total cash flows (a) (160,000)$ 40,000$ 40,000$ 40,000$ 35,000$
Discount factor (14%) (b) 1.000 0.877 0.769 0.675 0.592
Exhibit 11-7
Years
Enter a formula into each of the cells marked with a ? below
Chapter_11_Applying_Excel.xls
5
200,000$
(125,000)$
(35,000)$
10,000$
100,000$
150,000$
0.519
77,850$
Page 4
Chapter_11_Applying_Excel.xls
Chapter 11: Applying Excel
Data
Example E
Cost of equipment needed 60000
Working capital needed 100000
Overhaul of equipment in four years 5000
Salvage value of the equipment in five years 10000
Annual revenues and costs:
Sales revenues 200000
Cost of goods sold 125000
Out-of-pocket operating costs 35000
Discount rate 0.14
Now 1 2 3
Purchase of equipment =-B5
Investment in working capital =-B6
Sales =B10 =C21 =D21
Cost of goods sold =-B11 =C22 =D22
Out-of-pocket operating costs =-B12 =C23 =D23
Overhaul of equipment
Salvage value of the equipment
Working capital released
Total cash flows (a) =SUM(B19:B26) =SUM(C19:C26) =SUM(D19:D26) =SUM(E19:E26)
Discount factor (14%) (b) 1 =1/(1+$B$13)^C18 =1/(1+$B$13)^D18 =1/(1+$B$13)^E18
Present value of cash flows (a) x (b) =B27*B28 =C27*C28 =D27*D28 =E27*E28
Net present value =SUM(B29:G29)
Present value of $1 = 1/(1+r)^n
Exhibit 11-7
Years
Enter a formula into each of the cells marked with a ? below
Chapter_11_Applying_Excel.xls
4 5
=E21 =F21
=E22 =F22
=E23 =F23
=-B7
=B8
=B6
=SUM(F19:F26) =SUM(G19:G26)
=1/(1+$B$13)^F18 =1/(1+$B$13)^G18
=F27*F28 =G27*G28
Page 6