A B C D E F G H

1 Analysis of an New Project: Inputs and Key Results (Dollars in Thousands)

2 Part 1. Inputs and Key Results

3
4 Inputs Base-Case
5 Equipment cost $7,750
6 Salvage value, equipment, Year 4 $639
7 Units sold, Year 1 11,000
8 Annual change in units sold, after Year 1 15%
9 Sales price per unit, Year 1 $1.50
10 Annual change in sales price, after Year 1 4%
11 Variable cost per unit (VC), Year 1 $1.07
12 Annual change in VC, after Year 1 3%
13 Nonvariable cost (Non-VC), Year 1 $2,120
14 Annual change in Non-VC, after Year 1 3%
15 Project cost of capital, r 10%
16 Tax rate 40%
17 Working capital as % of next year's sales 15%
18
19
20
Analysis of a New Project: Cash Flows and Performance Measures
21 (Dollars in Thousands)
22
23 Part 2. Cash Flows and Performance Measures
24 Intermediate Calculations 0 1 2 3
25 Unit sales 11,000 12,650 14,547
26 Sales price per unit $1.50 $1.56 $1.62
27 Variable cost per unit (excl. depr.) $1.07 $1.10 $1.14
28 Nonvariable costs (excl. depr.) $2,120 $2,184 $2,249
29 Sales revenues = Units × Price/unit $16,500 $19,734 $23,602
30 NOWCt = 15%(Revenuest+1) $2,475 $2,960 $3,540 $4,234
31 Equipment cost $7,750
32 Annual depreciation rate (MACRS) 33.33% 44.45% 14.81%
33 Annual depreciation expense $2,583 $3,445 $1,148
34 Remaining undepreciated value $5,167 $1,722 $574
35 Cash Flow Forecast Cash Flows at End of Year
36 0 1 2 3
37 Sales revenues = Units × Price/unit $16,500 $19,734 $23,602
38 Variable costs = Units × Cost/unit $11,770 $13,942 $16,514
39 Nonvariable costs (excluding depr.) $2,120 $2,184 $2,249
40 Depreciation $2,583 $3,445 $1,148
41 Earnings before int. and taxes (EBIT) $27 $164 $3,691
42 Taxes on operating profit (40% rate) $11 $66 $1,476
43 Net operating profit after taxes $16 $98 $2,215
44 Add back depreciation $2,583 $3,445 $1,148
45 Equipment purchases −$7,750
46 Salvage value
47 Cash flow due to tax on salv. val.
48 Cash flow due to change in WC −$2,475 −$485 −$580 −$694
49 Project net cash flows: Time Line −$10,225 $2,114 $2,963 $2,669
50 Project Evaluation Measures
A B C D E F G H
51 NPV $2,064 =E49+NPV(E15,F49:I49)
52 IRR 17.13% =IRR(E49:I49)
53
I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 4
25 16,730
26 $1.69
27 $1.17
28 $2,317
29 $28,228
30 $0
31
32 7.41%
33 $574
34 $0
Cash Flows at End of35
Year
36 4
37 $28,228
38 $19,561
39 $2,317
40 $574
41 $5,776
42 $2,311
43 $3,466
44 $574
45
46 $639
47 $256
48 $4,234
49 $8,658
50
I
51
52
53
A B C D E F G H
1
2
3
4 Inflation Omitted
5 $7,750
6 $639
7 10,000
8 15%
9 $1.50
10 0%
11 $1.07
12 0%
13 $2,120
14 0%
15 10%
16 40%
17 15%
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
A B C D E F G H
1
2
3
4 Base-Case
5 $7,750
6 $639
7 10,000
8 15%
9 $1.50
10 4%
11 $1.07
12 3%
13 $2,120
14 3%
15 10%
16 40%
17 15%
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
A B C D E F G H
1
2
3
4 Project S
5 $8,750
6 $433
7 10,000
8 0%
9 $2.50
10 -5%
11 $1.64
12 6%
13 $1,815
14 4%
15 10%
16 40%
17 5%
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 Annual depreciation rate (MACRS) 33.33% 44.45% 14.81%
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 7.41%
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 Annual depreciation rate (Straight-Line) 16.67% 33.33% 33.33%
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 16.66%
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
A B C D E F G H
1 Analysis of an New Project: Inputs and Key Results (Dollars in Thousands)

2 Part 1. Inputs and Key Results

3
4 Inputs Base-Case
5 Equipment cost $7,750
6 Salvage value, equipment, Year 4 $639
7 Units sold, Year 1 11,000
8 Annual change in units sold, after Year 1 15%
9 Sales price per unit, Year 1 $1.50
10 Annual change in sales price, after Year 1 4%
11 Variable cost per unit (VC), Year 1 $1.07
12 Annual change in VC, after Year 1 3%
13 Nonvariable cost (Non-VC), Year 1 $2,120
14 Annual change in Non-VC, after Year 1 3%
15 Project cost of capital, r 10%
16 Tax rate 40%
17 Working capital as % of next year's sales 15%
18
19
20
Analysis of a New Project: Cash Flows and Performance Measures
21 (Dollars in Thousands)
22
23 Part 2. Cash Flows and Performance Measures
24 Intermediate Calculations 0 1 2 3
25 Unit sales 11,000 12,650 14,547
26 Sales price per unit $1.50 $1.56 $1.62
27 Variable cost per unit (excl. depr.) $1.07 $1.10 $1.14
28 Nonvariable costs (excl. depr.) $2,120 $2,184 $2,249
29 Sales revenues = Units × Price/unit $16,500 $19,734 $23,602
30 NOWCt = 15%(Revenuest+1) $2,475 $2,960 $3,540 $4,234
31 Equipment cost $7,750
32 Annual depreciation rate (MACRS) 33.33% 44.45% 14.81%
33 Annual depreciation expense $2,583 $3,445 $1,148
34 Remaining undepreciated value $5,167 $1,722 $574
35 Cash Flow Forecast Cash Flows at End of Year
36 0 1 2 3
37 Sales revenues = Units × Price/unit $16,500 $19,734 $23,602
38 Variable costs = Units × Cost/unit $11,770 $13,942 $16,514
39 Nonvariable costs (excluding depr.) $2,120 $2,184 $2,249
40 Depreciation $2,583 $3,445 $1,148
41 Earnings before int. and taxes (EBIT) $27 $164 $3,691
42 Taxes on operating profit (40% rate) $11 $66 $1,476
43 Net operating profit after taxes $16 $98 $2,215
44 Add back depreciation $2,583 $3,445 $1,148
45 Equipment purchases −$7,750
46 Salvage value
47 Cash flow due to tax on salv. val.
48 Cash flow due to change in WC −$2,475 −$485 −$580 −$694
49 Project net cash flows: Time Line −$10,225 $2,114 $2,963 $2,669
50 Project Evaluation Measures
A B C D E F G H
51 NPV $2,064 =E49+NPV(E15,F49:I49)
52 IRR 17.13% =IRR(E49:I49)
53
I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 4
25 16,730
26 $1.69
27 $1.17
28 $2,317
29 $28,228
30 $0
31
32 7.41%
33 $574
34 $0
Cash Flows at End of35
Year
36 4
37 $28,228
38 $19,561
39 $2,317
40 $574
41 $5,776
42 $2,311
43 $3,466
44 $574
45
46 $639
47 $256
48 $4,234
49 $8,658
50
I
51
52
53
A B C D E F G H
1
2
3
4 Inflation Omitted
5 $7,750
6 $639
7 10,000
8 15%
9 $1.50
10 0%
11 $1.07
12 0%
13 $2,120
14 0%
15 10%
16 40%
17 15%
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
A B C D E F G H
1
2
3
4 Base-Case
5 $7,750
6 $639
7 10,000
8 15%
9 $1.50
10 4%
11 $1.07
12 3%
13 $2,120
14 3%
15 10%
16 40%
17 15%
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
A B C D E F G H
1
2
3
4 Project S
5 $8,750
6 $433
7 10,000
8 0%
9 $2.50
10 -5%
11 $1.64
12 6%
13 $1,815
14 4%
15 10%
16 40%
17 5%
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 Annual depreciation rate (MACRS) 33.33% 44.45% 14.81%
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 7.41%
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 Annual depreciation rate (Straight-Line) 16.67% 33.33% 33.33%
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 16.66%
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
Data for Sensitivity Graph
NPV with Variables at Different Deviations from Base
Deviation from Base Equipment Sales Price/unit Unit Sales VC/Unit Non-VC r
−30%
0%
30%
Range