Project Selection at Ewing Natural Gas

Inputs All values in $ millions, assuming 100% involvement

Project index Functional Area (FA) Partnership % Capex Year 1 Capex Year 2 Capex Year 3
1 FA1 100% 250 100 100
2 FA1 33% 500 300 300
3 FA1Answers to the questions50%appear in yellow.100You can 200 400
enter any 0/1 values in the boxed-in range to see
4 FA1how everything updates.
100% 750 500 300
5 FA1 75% 200 400 800
Note the tricky formulas in cells I36 and J36. These
6 FA2are called array formulas.
50%When you enter 1000
each of 300 300
7 FA2them, you should press100% 750
Ctrl+Shift+Enter, not just 750 300
8 FA2Enter. 100% 800 700 600
9 FA2 67% 400 600 800
10 FA3 100% 100 200 400
11 FA3 50% 700 500 300
12 FA3 100% 1500 400 400

Decisions Capex for company

Project index Undertake project? Year 1 Year 2 Year 3
1 1 250 100 100
2 1 165 99 99
3 1 50 100 200
4 1 750 500 300
5 1 150 300 600
6 1 500 150 150
7 0 0 0 0
8 0 0 0 0
9 1 268 402 536
10 1 100 200 400
11 1 350 250 150
12 1 1500 400 400
Totals 10 4083 2501 2935

Number of projects undertaken in functional areas

Total Any for FA?
FA1 5 1
FA2 2 1
FA3 3 1

Limits for company capital expenditures

Year 1 Year 2 Year 3 Total
Limit 4000 4000 4000 10000
Exceeds limit? 1 0 0 0

Summary of capex for projects undertaken (useful for creating chart)

Capex for
4500
4000
3500
Year 1 Year 2 Year 3 Capex for
Company 4083 2501 2935 4500
FA1 1365 1099 1299 4000
FA2 768 552 686 3500
FA3 1950 850 950 3000
2500
2000
1500
1000
500
0
Yea r 1
Company
% decrease 0%

100% involvement Calculations

NPV Adjusted NPV ROI
60 60 13%
180 180 16%
80 80 11%
310 310 20%
220 220 16%
180 180 11%
410 410 23%
280 280 13%
380 380 21%
100 100 14%
260 260 17%
340 340 15%

Capex for FA1 Capex for FA2

Year 1 Year 2 Year 3 Year 1 Year 2 Year 3
250 100 100 0 0 0
165 99 99 0 0 0
50 100 200 0 0 0
750 500 300 0 0 0
150 300 600 0 0 0
0 0 0 500 150 150
0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 268 402 536
0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 0 0 0
1365 1099 1299 768 552 686

Data table for ROI for undertaken projects as function of % decrease in optimistic NPV
% decrease Avg ROI Min ROI Max ROI
15.56% 11.25% 21.11%
0% 21.11%
5% 20.06%
10% 19.00%
15% 17.94%
20% 16.89%
25% 15.83%
30% 14.78%

Capex for Projects Undertaken

0
0
0
Capex for Projects Undertaken
0
0
0
0
0
0
0
0
0
0
ea r 1 Year 2 Yea r 3
Company FA1 FA2 FA3
Capex for FA3
Year 1 Year 2 Year 3
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
100 200 400
350 250 150
1500 400 400
1950 850 950