Chapter_8_Applying_Excel.xls
Chapter 8: 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$
Exhibit 8-6
Years
Enter a formula into each of the cells marked with a ? below
Chapter_8_Applying_Excel.xls
5
255,000$
(160,000)$
(50,000)$
20,000$
80,000$
145,000$
0.519
75,255$
Page 10
Chapter_8_Applying_Excel.xls
Chapter 8: 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 8-6
Years
Enter a formula into each of the cells marked with a ? below
Chapter_8_Applying_Excel.xls
5
255,000$
(160,000)$
(50,000)$
20,000$
80,000$
145,000$
0.621
90,045$
Page 12
Chapter_8_Applying_Excel.xls
Chapter 8: 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 11%
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.901 0.812 0.731 0.659
Present value of cash flows (a) x (b) (200,000)$ 40,545$ 36,540$ 32,895$ 3,295$
Net present value (740)$
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
255,000$
(160,000)$
(50,000)$
20,000$
80,000$
145,000$
0.593
85,985$
Page 14
Chapter_8_Applying_Excel.xls
Chapter 8: 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 $53,410
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 –$
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
255,000$
(160,000)$
(50,000)$
53,410$
80,000$
178,410$
0.519
92,595$
Page 16