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 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 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.621
93,150$
Page 8
Chapter_11_Applying_Excel.xls
Chapter 11: Applying Excel
Data
Example E
Cost of equipment needed $120,000
Working capital needed $80,000
Overhaul of equipment in four years $40,000
Salvage value of the equipment in five years $20,000
Annual revenues and costs:
Sales revenues $255,000
Cost of goods sold $160,000
Out-of-pocket operating costs $50,000
Discount rate 14%
Now 1 2 3 4
Purchase of equipment (120,000)$
Investment in working capital (80,000)$
Sales 255,000$ 255,000$ 255,000$ 255,000$
Cost of goods sold (160,000)$ (160,000)$ (160,000)$ (160,000)$
Out-of-pocket operating costs (50,000)$ (50,000)$ (50,000)$ (50,000)$
Overhaul of equipment (40,000)$
Salvage value of the equipment
Working capital released
Total cash flows (a) (200,000)$ 45,000$ 45,000$ 45,000$ 5,000$
Discount factor (14%) (b) 1.000 0.877 0.769 0.675 0.592
Present value of cash flows (a) x (b) (200,000)$ 39,465$ 34,605$ 30,375$ 2,960$
Net present value (17,340)$
Present value of $1 = 1/(1+r)^n
Exhibit 11-7
Years
Enter a formula into each of the cells marked with a ? below
Page 9
Chapter_11_Applying_Excel.xls
5
255,000$
(160,000)$
(50,000)$
20,000$
80,000$
145,000$
0.519
75,255$
Page 10
Chapter_11_Applying_Excel.xls
Chapter 11: Applying Excel
Data
Example E
Cost of equipment needed $120,000
Working capital needed $80,000
Overhaul of equipment in four years $40,000
Salvage value of the equipment in five years $20,000
Annual revenues and costs:
Sales revenues $255,000
Cost of goods sold $160,000
Out-of-pocket operating costs $50,000
Discount rate 10%
Now 1 2 3 4
Purchase of equipment (120,000)$
Investment in working capital (80,000)$
Sales 255,000$ 255,000$ 255,000$ 255,000$
Cost of goods sold (160,000)$ (160,000)$ (160,000)$ (160,000)$
Out-of-pocket operating costs (50,000)$ (50,000)$ (50,000)$ (50,000)$
Overhaul of equipment (40,000)$
Salvage value of the equipment
Working capital released
Total cash flows (a) (200,000)$ 45,000$ 45,000$ 45,000$ 5,000$
Discount factor (14%) (b) 1.000 0.909 0.826 0.751 0.683
Present value of cash flows (a) x (b) (200,000)$ 40,905$ 37,170$ 33,795$ 3,415$
Net present value 5,330$
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
255,000$
(160,000)$
(50,000)$
20,000$
80,000$
145,000$
0.621
90,045$
Page 12