CFIN6
Spreadsheet Problem Solution
Chapter 10
a. The expansion project should be purchased.
INPUT DATA:
KEY OUTPUT:
Base price
($260,000)
NPV
Modifications
($15,000)
57,186
Increase in NWC
($22,500)
Increase in sales revenue
125,000
Operating costs
55,000
Salvage value
12,500
Required rate of return
Tax rate
MACRS class life (years)
Useful life (years)
MODEL-GENERATED DATA:
Initial investment at t=0:
Base price
($260,000)
Modification
Increase in NWC
($22,500)
Initial investment outlay
($297,500)
($15,000)
Depreciation schedule:
Terminal cash flow:
Depr. basis =
$275,000
Salvage value
12,500
Ending
Tax on sale of asset
(5,000)
Year
MACRS
Depreciation
Book
Reverse of NWC
22,500
Rate
Allowance
Value
Terminal CF
30,000
1
0.20
55,000
220,000
2
0.32
88,000
132,000
3
0.19
52,250
79,750
4
0.12
33,000
46,750
5
0.11
30,250
16,500
6
0.06
16,500
CFIN6
Annual cash flows:
0
1
2
3
4
5
6
7
8
9
10
Initial invest.
(297,500)
Sales increase
125,000
125,000
125,000
125,000
125,000
125,000
125,000
125,000
125,000
125,000
Operating costs
(55,000)
(55,000)
(55,000)
(55,000)
(55,000)
(55,000)
(55,000)
(55,000)
(55,000)
(55,000)
Depreciation
(55,000)
(88,000)
(52,250)
(33,000)
(30,250)
(16,500)
0
0
0
0
Earn. b/f taxes
15,000
(18,000)
17,750
37,000
39,750
53,500
70,000
70,000
70,000
Taxes
7,200
(14,800)
(15,900)
(21,400)
(28,000)
(28,000)
(28,000)
(28,000)
Net income
9,000
(10,800)
10,650
22,200
23,850
32,100
42,000
42,000
42,000
Add back deprec.
55,000
88,000
52,250
33,000
30,250
16,500
0
0
0
0
Supplemental oper. CF
64,000
77,200
62,900
55,200
54,100
48,600
42,000
42,000
42,000
Net cash flow
(297,500)
64,000
77,200
62,900
55,200
54,100
48,600
42,000
42,000
72,000
b. The expansion project should not be purchased.
INPUT DATA:
KEY OUTPUT:
Base price
($260,000)
NPV
Modifications
($15,000)
–3,254
Increase in NWC
($22,500)
Increase in sales revenue
125,000
Salvage value
Required rate of return
Tax rate
MACRS class life (years)
Useful life (years)
MODEL-GENERATED DATA:
Initial investment at t=0:
Base price
($260,000)
Modification
Increase in NWC
($22,500)
Initial investment outlay
($297,500)
($15,000)
CFIN6
Depreciation schedule:
Terminal cash flow:
Depr. basis =
$275,000
Salvage value
12,500
Ending
Tax on sale of asset
(5,000)
Year
MACRS
Depreciation
Book
Reverse of NWC
22,500
Rate
Allowance
Value
Terminal CF
30,000
1
0.20
55,000
220,000
2
0.32
88,000
132,000
3
0.19
52,250
79,750
4
0.12
33,000
46,750
5
0.11
30,250
16,500
6
0.06
16,500
Annual cash flows:
0
1
2
3
4
5
6
7
8
9
10
Initial invest.
(297,500)
Sales increase
125,000
125,000
125,000
125,000
125,000
125,000
125,000
125,000
125,000
125,000
Operating costs
(65,000)
(65,000)
(65,000)
(65,000)
(65,000)
(65,000)
(65,000)
(65,000)
(65,000)
(65,000)
Depreciation
(55,000)
(88,000)
(52,250)
(33,000)
(30,250)
(16,500)
0
0
0
0
Earn. b/f taxes
5,000
(28,000)
7,750
27,000
29,750
43,500
60,000
60,000
60,000
60,000
Taxes
11,200
(10,800)
(11,900)
(17,400)
(24,000)
(24,000)
(24,000)
(24,000)
Net income
3,000
(16,800)
4,650
16,200
17,850
26,100
36,000
36,000
36,000
36,000
Add back deprec.
55,000
88,000
52,250
33,000
30,250
16,500
0
0
0
0
c. The expansion project should not be purchased.
INPUT DATA:
KEY OUTPUT:
Base price
($260,000)
NPV
Modifications
($15,000)
-27,254
Increase in NWC
($22,500)
Increase in sales revenue
125,000
Operating costs
Salvage value
Required rate of return
Tax rate
MACRS class life (years)
Useful life (years)
CFIN6
MODEL-GENERATED DATA:
Initial investment at t=0:
Base price
($260,000)
Modification
($15,000)
Increase in NWC
($22,500)
($297,500)
Depreciation schedule:
Terminal cash flow:
Depr. basis =
$275,000
Salvage value
Ending
Tax on sale of asset
Year
MACRS
Depreciation
Book
Reverse of NWC
Rate
Allowance
Value
Terminal CF
1
0.20
55,000
220,000
2
0.32
88,000
132,000
3
0.19
52,250
4
0.12
33,000
5
0.11
30,250
6
0.06
16,500
Annual cash flows:
0
1
2
3
4
5
Initial invest.
(297,500)
Sales increase
125,000
125,000
125,000
125,000
125,000
Operating costs
(55,000)
(55,000)
(55,000)
(55,000)
(55,000)
Depreciation
(55,000)
(88,000)
(52,250)
(33,000)
(30,250)
Earn. b/f taxes
15,000
(18,000)
17,750
39,750
Taxes
7,200
(7,100)
(14,800)
(15,900)
Net income
9,000
(10,800)
10,650
23,850
Add back deprec.
55,000
52,250
30,250
Supplemental oper. CF
64,000
62,900
54,100
Salvage AT
Net cash flow
(297,500)
64,000
62,900
101,950
d. The expansion project should not be purchased.
INPUT DATA:
KEY OUTPUT:
Base price
($260,000)
NPV
Modifications
Increase in NWC
Increase in sales revenue
CFIN6
Operating costs
55,000
Salvage value
Required rate of return
Tax rate
MACRS class life (years)
Useful life (years)
12,500
MODEL-GENERATED DATA:
Initial investment at t=0:
Base price
($260,000)
Modification
Increase in NWC
($297,500)
Depreciation schedule:
Terminal cash flow:
Depr. basis =
$275,000
Salvage value
12,500
Ending
Tax on sale of asset
(5,000)
Year
MACRS
Depreciation
Book
Reverse of NWC
22,500
Rate
Allowance
Value
Terminal CF
30,000
1
0.20
55,000
220,000
2
0.32
88,000
132,000
3
0.19
52,250
4
0.12
33,000
5
0.11
30,250
6
0.06
16,500
Annual cash flows:
0
1
2
3
4
5
6
7
8
9
10
Initial invest.
(297,500)
Sales increase
105,000
105,000
105,000
105,000
105,000
105,000
105,000
105,000
105,000
105,000
Operating costs
(55,000)
(55,000)
(55,000)
(55,000)
(55,000)
(55,000)
(55,000)
(55,000)
(55,000)
(55,000)
Depreciation
(55,000)
(88,000)
(52,250)
(33,000)
(30,250)
(16,500)
0
0
0
0
Earn. b/f taxes
(38,000)
(2,250)
17,000
19,750
33,500
50,000
50,000
50,000
50,000
Taxes
15,200
(6,800)
(7,900)
(13,400)
(20,000)
(20,000)
(20,000)
(20,000)
Net income
(22,800)
(1,350)
10,200
11,850
20,100
30,000
30,000
30,000
30,000
Add back deprec.
88,000
52,250
33,000
30,250
16,500
0
0
0
0
Supplemental oper. CF
65,200
50,900
43,200
42,100
36,600
30,000
30,000
30,000
30,000
Salvage AT
0
0
0
0
0
0
0
0
30,000
Net cash flow
(297,500)
65,200
50,900
43,200
42,100
36,600
30,000
30,000
30,000
60,000
CFIN6
e. The expansion project should be purchased.
INPUT DATA:
KEY OUTPUT:
Base price
($260,000)
NPV
Modifications
($15,000)
20,319
Increase in NWC
($22,500)
Increase in sales revenue
125,000
Operating costs
65,000
Salvage value
12,500
Required rate of return
Tax rate
MACRS class life (years)
Useful life (years)
MODEL-GENERATED DATA:
Initial investment at t=0:
Base price
($260,000)
Modification
($15,000)
Increase in NWC
($22,500)
Initial investment outlay
($297,500)
Depreciation schedule:
Terminal cash flow:
Depr. basis =
$275,000
Salvage value
12,500
Ending
Tax on sale of asset
(5,000)
Year
MACRS
Depreciation
Book
Reverse of NWC
22,500
Rate
Allowance
Value
Terminal CF
30,000
1
0.20
55,000
220,000
2
0.32
88,000
132,000
3
0.19
52,250
79,750
4
0.12
33,000
46,750
5
0.11
30,250
16,500
6
0.06
16,500
CFIN6
Annual cash flows:
0
1
2
3
4
5
6
7
8
9
10
Initial invest.
(297,500)
Sales increase
125,000
125,000
125,000
125,000
125,000
125,000
125,000
125,000
125,000
125,000
Operating costs
(65,000)
(65,000)
(65,000)
(65,000)
(65,000)
(65,000)
(65,000)
(65,000)
(65,000)
(65,000)
Depreciation
(55,000)
(88,000)
(52,250)
(33,000)
(30,250)
(16,500)
0
0
0
0
Earn. b/f taxes
5,000
(28,000)
7,750
27,000
29,750
43,500
60,000
60,000
60,000
60,000
Taxes
(2,000)
11,200
(3,100)
(10,800)
(11,900)
(17,400)
(24,000)
(24,000)
(24,000)
(24,000)
Net income
3,000
(16,800)
4,650
16,200
17,850
26,100
36,000
36,000
36,000
36,000
Add back deprec.
55,000
88,000
52,250
33,000
30,250
16,500
0
0
0
0
Supplemental oper. CF
58,000
71,200
56,900
49,200
48,100
42,600
36,000
36,000
36,000
36,000
Net cash flow
(297,500)
58,000
71,200
56,900
49,200
48,100
42,600
36,000
36,000
36,000
66,000