Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Page 1
Chapter_8_Applying_Excel.xls
5
?
?
?
?
?
?
?
?
Page 2
Chapter_8_Applying_Excel.xls
Chapter 8: 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 8-6
Years
Enter a formula into each of the cells marked with a ? below
Chapter_8_Applying_Excel.xls
5
200,000$
(125,000)$
(35,000)$
10,000$
100,000$
150,000$
0.519
77,850$
Page 4
Chapter_8_Applying_Excel.xls
Chapter 8: 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 8-6
Years
Enter a formula into each of the cells marked with a ? below
Page 5
Chapter_8_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
Chapter_8_Applying_Excel.xls
Chapter 8: 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 10%
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.909 0.826 0.751 0.683
Present value of cash flows (a) x (b) (160,000)$ 36,360$ 33,040$ 30,040$ 23,905$
Net present value 56,495$
Present value of $1 = 1/(1+r)^n
Exhibit 8-6
Years
Enter a formula into each of the cells marked with a ? below
Chapter_8_Applying_Excel.xls
5
200,000$
(125,000)$
(35,000)$
10,000$
100,000$
150,000$
0.621
93,150$
Page 8
