Bluejay Natural Gas Financials (in millions)
Given:
Project Index Functional Area Partnership % Capex Year 1 Capex Year 2
1 FA 1 100% $ 250 $ 100
2 FA 1 33% $ 500 $ 300
3 FA 1 50% $ 100 $ 200
4 FA 1 100% $ 750 $ 500
5 FA 1 75% $ 200 $ 400
6 FA 2 50% $ 1,000 $ 300
7 FA 2 100% $ 750 $ 750
8 FA 2 100% $ 800 $ 700
9 FA 2 67% $ 400 $ 600
10 FA 3 100% $ 100 $ 200
11 FA 3 50% $ 700 $ 500
12 FA 3 100% $ 1,500 $ 400
Adjusted based on % Partnership:
Project Index Functional Area Partnership % Cap Ex. Yr1 Cap Ex. Yr2 Cap Ex. Yr3
1 FA 1 100% $ 250 $ 100 $ 100
2 FA 1 33% $ 165 $ 99 $ 99
3 FA 1 50% $ 50 $ 100 $ 200
4 FA 1 100% $ 750 $ 500 $ 300
5 FA 1 75% $ 150 $ 300 $ 600
FA 1 Approved Totals: $ 1,115 $ 999 $ 1,199
6 FA 2 50% $ 500 $ 150 $ 150
7 FA 2 100% $ 750 $ 750 $ 300
8 FA 2 100% $ 800 $ 700 $ 600
9 FA 2 67% $ 268 $ 402 $ 536
FA 2 Approved Totals: $ 1,518 $ 1,302 $ 986
10 FA 3 100% $ 100 $ 200 $ 400
11 FA 3 50% $ 350 $ 250 $ 150
12 FA 3 100% $ 1,500 $ 400 $ 400
FA 3 Approved Totals: $ 1,850 $ 650 $ 550
Cap Ex. Yr1 Cap Ex. Yr2 Cap Ex. Yr3
Overall Totals for Approved Projects: $ 4,483 $ 2,951 $ 2,735
ROI
% Change Average Minimum
15% 10%
0%
5%
10%
15%
20%
25%
30%
Financials (in millions)
Capex Year 3 NPV
$ 100 60
$ 300 180
$ 400 80
$ 300 310
$ 800 220
$ 300 180
$ 300 410
$ 600 280
$ 800 380
$ 400 100 Insert 0 or 1 here:
$ 300 260
$ 400 340
Partnership:
Total Cost NPV New NPV ROI Approval Rating
$ 450 $ 60 $ 53 0
$ 363 $ 180 $ 158 14% 1
$ 350 $ 80 $ 70 10% 1
$ 1,550 $ 310 $ 273 18% 1
$ 1,050 $ 220 $ 194 14% 1
$ 3,313
$ 800 $ 180 $ 158 10% 1
$ 1,800 $ 410 $ 361 20% 1
$ 2,100 $ 280 $ 246 0
$ 1,206 $ 380 $ 334 19% 1
$ 3,806
$ 700 $ 100 $ 88 0
$ 750 $ 260 $ 229 15% 1
$ 2,300 $ 340 $ 299 13% 1
$ 3,050
Total Cost Total Approved NPV:
$ 10,169 $ 2,077
ROI
Maximum
20%
23%
22%
21%
19%
18%
17%
16%
Inputs
Annual Revenue (millions) $ 50,000
Expenditure (millions) $ 10,000
Yearly Cap (millions) $ 4,000 Insert Discount
NPV Discount Rate % decrease: 12% Rate Reduction
Here:
Requirement Checks
Capital Ex. Under 4000 (Y1) 0
Capital Ex. under 4000 (Y2) 1
Capital Ex. under 4000 (Y3) 1
Under $10,000 (year 1-3) 0
Each FA has at least 1 project approved 1
Number of Approved Project for FA 1 4
Number of Approved Project for FA 2 3
Number of Approved Project for FA 3 2
Total Number of Approved Projects 9
Are the Conditions Met? 0
Approved Cap Ex. Year Totals
Year 1 Year 2
FA 1 $ 1,115 $ 999
Functional Area: FA 2 $ 1,518 $ 1,302
FA 3 $ 1,850 $ 650
Discount
eduction
ere:
x. Year Totals
Year 3
$ 1,199
$ 986
$ 550
Scenario
Bluejay Natural Gas is a large energy company based out of Baltimore, MD. The company offers a wid
Because of the diverse nature of the company, its Manager for Project Development, Julie Kavoli, is unde
company. At any point in time, there might be dozens of projects at various stages requiring a wide varie
containing varying degrees of risk. Julie has a difficult balancing act. The company's CEO, Cordelia Kareen
managing risk. The heads of the company's functional areas are less worried about budgets and risks; the
many of the proposed projects, especially those requiring large capital expenditures, must be led by seni
aware that the company has only a limited supply of such managers.
Julie is currently preparing to meet with all parties involved to discuss project proposals for the next t
would like to undertake. Each of these is accompanied by a schedule of capital expenditures over the ne
a net present value (NPV) for each proposal, using the company's discount rate of 12%.
Several of the projects, if approved, must be undertaken in joint partnership with another company.
project, and the other 50% will be shared by a partner. In this case, Bluejay Natural Gas will only incur 5
Cordelia has stated in no uncertain terms that the total of capital expenditures for the approved projects can be n
Note to user: This project is cumulative and uses all of the skills of the course so far! Read the questions multiple
1
Question 1
Before the big meeting, Julie wants to be thoroughly prepared to answer all the questions she knows she will be a
The model should list the given financial information for all potential parties.
The model should associate with each proposed project a cell that is 1 if the project is approved, 0 if not approved
Each functional area wants as many of its projects to be approved as possible, but it certainly does not want to be
The model should find the sum of capital expenditures for all approved projects, both by year and by the total ove
The model should find the sum of capital expenditures for all approved projects, by year, for each functional area.
Recognizing that the NPVs in the attached spreadsheet are probably optimistic estimates, the model should includ
The model should list the ROI (Return on investment) for each project, defined as the NPV as a percentage of the
The model should include a one-way data table in which the column input is the common percentage decrease in
The model should be accompanied by a line chart of total capital expenditures for all approved projects by year an
The company offers a wide range of energy products and has annual revenues of approximately $50 billion.
ment, Julie Kavoli, is under continual pressure to manage project proposals from the functional areas of the
ges requiring a wide variety of capital expenditures, promising widely varying future revenue streams, and
ny's CEO, Cordelia Kareeni, is very concerned about keeping capital expenditures within a fixed budget and
out budgets and risks; they are most concerned that their pet projects are approved. Julie also knows that
ures, must be led by senior project managers with the appropriate experience and skills, and she is keenly
ct proposals for the next three years. She has proposals from the various functional areas for projects they
expenditures over the next three years and a financial analysis of the expected revenue streams. These lead to
of 12%.
with another company. For example, if project 3 is approved, Bluejay Natural Gas will take a 50% share in the
ural Gas will only incur 50% of the expenditures and receive only 50% of the revenues.
approved projects can be no more than $10 billion and that no more than $4 billion should be spent in any single year. Unfortunately, the
ead the questions multiple time to understand what is being asked. Do your best to model the project. Upon completion, the a solution sp
ons she knows she will be asked by the CEO, the functional heads, and other interested parties. As a first step, she wants to develop an Ex
approved, 0 if not approved. For now, you can insert 0 or 1s randomly. Try to manually find a solution of projects to assign where all condi
rtainly does not want to be shut out altogether. The model should indicate each of the following: the number of approved projects for eac
by year and by the total over all three years. It should address Cordelia's concerns by indicating whether the total for all three years excee
ar, for each functional area.
es, the model should include another input, the possible percentage decrease in each original NPV. Then there should be a new NPV colum
NPV as a percentage of the total capital expenditure for the project. Each ROI should be based on the adjusted NPV from the previous dire
mon percentage decrease in each NPV mentioned previously, varied from 0% to 30% in increments of 5%. The outputs in the data table sho
pproved projects by year and a separate such line chart for each functional area.
gle year. Unfortunately, the capital expenditures for the potential list of projects is well over $10 billion, so Julie knows that not all of these
ompletion, the a solution spreadsheet will be provided. If you are stuck on a particular part, that is ok. Review the solution and any prior m
she wants to develop an Excel spreadsheet model including the following information. Check off each item as you incorporate it into the m
ts to assign where all conditions are satisfied. Later in the final project we will optimize this.
of approved projects for each function area and whether each function area gets at least one approved project. The latter should be indica
tal for all three years exceeds $10 billion and, for each year, whether that year's total expenditures exceed $4 billion. As in the previous dir
should be a new NPV column that reflects this percentage decrease.
NPV from the previous direction. The ROI for any project is the same, regardless of whether Bluejay undertakes it at the 100% level or in a
utputs in the data table should be the ROIs of undertaken projects: the average; the minimum; and the maximum. Of course, the results in
e knows that not all of these promising projects will be approved.
he solution and any prior modules if needed to understand the solution.
ou incorporate it into the model.
. The latter should be indicated by a 0/1 value: 0 if the function area is shut out, 1 if it gets at least one project approved.
billion. As in the previous direction, these should be indicated by 0/1 values.
s it at the 100% level or in a partnership.
um. Of course, the results in this data table should update automatically if the 0/1 values in the second bullet are changed.
Capital Expenditure for all Functional Area Approved Projects Y1-Y3
$2,000
$1,800
$1,600
Capital Expenditure ($ - in millions)
$1,400
$1,200
$1,000
$800
$600
$400
$200
$-
Year 1 Year 2 Year 3
Number of Years
FA 1 FA 2 FA 3
Capital Expenditure for FA 2 Approved Projects Y1-Y3
$1,600
$1,400
$1,200
Capital Expenditure ($ - in millions)
$1,000
$800
$600
$400
$200
$-
Year 1 Year 2 Year 3
Number of Years
$-
Year 1 Year 2 Year 3
Number of Years
oved Projects Y1-Y3 Capital Expenditure for FA 1 A
$1,400
$1,200
Capital Expenditure ($ - millions)
$1,000
$800
$600
$400
$200
Year 3
$-
Year 1
Number
rojects Y1-Y3 Capital Expenditure for FA 3 A
$2,000
$1,800
$1,600
Capital Expenditure ($ - in millions)
$1,400
$1,200
$1,000
$800
$600
$400
$200
$-
Year 3 Year 1
Numbe
$200
$-
Year 3 Year 1
Numbe
Capital Expenditure for FA 1 Approved Projects Y1-Y3
Year 1 Year 2 Year 3
Number of Years
Capital Expenditure for FA 3 Approved Projects Y1 - Y3
Year 1 Year 2 Year 3
Number of Years
Year 1 Year 2 Year 3
Number of Years
