Ewing Natural Gas is a large energy company with headquarters in Dallas, Texas. The company offers a wide variety of energy products and has annual revenues of approximately $50 billion. Because of the diverse nature of the company, its Manager for Project Development. Cliff Erland, is under continual pressure to manage project proposals from the functional areas of the company. At any point in time, there might be dozens of projects at various stages requiring a wide variety of capital expenditures, promising widely varying future revenue streams, and containing varying degrees of risk. Cliff has a difficult balancing act. The company's CEO. J.R. Bayer, is very concerned about keeping capital expenditures within a fixed budget and managing risk. The heads of the company's functional areas are less worried about budgets and risks; they are most concerned that their pet projects are approved. Cliff also knows that many of the proposed projects, especially those requiring large capital expenditures, must be led by senior project managers with the appropriate experience and skills, and he is keenly aware that the company has only a limited supply of such managers. Cliff is currently about to meet with all parties involved to discuss project proposals for the next three years. He has proposals from the various functional areas for projects they would like to undertake. Each of these is accompanied by a schedule of capital expenditures over the next three years and a financial analysis of the expected revenue streams. These lead to an NPV for each proposal, using the company's discount rate of 12%. (See Table 2.2.) J.R. Bayer has stated in no uncertain terms that the total of capital expenditures for the 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 capital expenditures for the potential list of projects is well over $ 10 billion, so Cliff knows that some of these very promising projects will not be approved. Before the big meeting. Cliff wants to be thoroughly prepared to answer all of the questions he knows he will be asked by the CEO, the functional heads, and other interested parties. As a first step, he wants you to develop an Excel spreadsheet model that provides the following. (You will be asked to extend this analysis in cases in later chapters.) The model should list the given financial information for all potential projects. The model should associate with each proposed project a cell that is I if the project is approved and 0 if it is not approved. Table 2.2 Financial Estimates for Potential Projects (in $ millions) Project Index Functional Area (FA) Partnership Percentage Capex Year 1 Capex Year 2 Capex Year 3 NPV FAI FAI FAI FAI FAI FA2 FA2 FA2 FA2 FA3 FA3 FA3 100% 33% 50% 100% 75% 50% 100% 100% 67% 100% 50% 100% 250 500 100 750 200 00 300 400 60 180 100 300 200 500 80 300 310 220 180 410 280 400 800 1000 300 300 750 750 700 300 600 800 6. 10 400 100 700 1500 600 200 500 400 400 300 400 380 100 260 340 12 Several of the projects, if approved, must be undertaken in joint partnership with another company. For example, if project 3 is approved (see Table 2.2). Ewing will take a 50% share in the project, and the other 50% will be shared by a partner. In this case. Ewing incurs only 50% of the expenditures and receives only 50% of the revenues. Each functional area wants as many of its projects to be approved as possible, but it certainly does not want to be shut out altogether. The model should indicate each of the following: the number of approved projects for each functional area and whether each functional area gets at least one approved project. The latter should be indicated by a 0/1 value: 0 if the functional area is shut out and 1 if it gets at least one. The model should find the sum of capital expenditures for all approved projects, both by year and by the total over all three years. It should also address J.R. Bayer's concerns by indicating whether the total for all three years exceeds $10 billion and, for each year, whether that year's total expenditures exceed $4 billion. As in the previous bullet, these should be indicated by 0/1 values. (Hint: For this bullet and the next one, you might want to avoid complex formulas by creating a table for the company as a whole and for each functional area separately, where each table shows the capital expenditure for each project for each year.) 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 table are probably optimistic estimates, the model should include another input, the possible percentage decrease in each original NPV. Then, there should be a new NPV column that reflects this percentage decrease. The model should list the ROI (return on investment) for each project, defined as the NPV as a percentage of the total capital expenditure for the project. Each ROI should be based on the adjusted NPV from the previous bullet. (Note: The ROI for any project is the same, regardless of whether Ewing undertakes it at the 100% level or in a partnership.) The model should include a one-way data table in which the column input is the common percentage decrease in each NPV mentioned previously, varied from 0% to 30% in increments of 5%. The outputs in the data table should include the following statistics for the ROIS of undertaken projects: the average; the minimum: and the maximum. Of course, the results in this data table should update automatically if the 0/1 values in the second bullet are changed. (Note: Excel has an AVERAGEIF function you can use to find the requested average, but it does not have corresponding MINIF or MAXIF functions. Finding formulas for the requested minimum and maximum is more difficult than it sounds, so you can consider this part a bonus, and you can omit it if necessary.) The model should be accompanied by a line chart of total capital expenditures for all approved projects by year and a separate such line chart for each functional area. I Ewing Natural Gas is a large energy company with headquarters in Dallas, Texas. The company offers a wide variety of energy products and has annual revenues of approximately $50 billion. Because of the diverse nature of the company, its Manager for Project Development. Cliff Erland, is under continual pressure to manage project proposals from the functional areas of the company. At any point in time, there might be dozens of projects at various stages requiring a wide variety of capital expenditures, promising widely varying future revenue streams, and containing varying degrees of risk. Cliff has a difficult balancing act. The company's CEO. J.R. Bayer, is very concerned about keeping capital expenditures within a fixed budget and managing risk. The heads of the company's functional areas are less worried about budgets and risks; they are most concerned that their pet projects are approved. Cliff also knows that many of the proposed projects, especially those requiring large capital expenditures, must be led by senior project managers with the appropriate experience and skills, and he is keenly aware that the company has only a limited supply of such managers. Cliff is currently about to meet with all parties involved to discuss project proposals for the next three years. He has proposals from the various functional areas for projects they would like to undertake. Each of these is accompanied by a schedule of capital expenditures over the next three years and a financial analysis of the expected revenue streams. These lead to an NPV for each proposal, using the company's discount rate of 12%. (See Table 2.2.) J.R. Bayer has stated in no uncertain terms that the total of capital expenditures for the 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 capital expenditures for the potential list of projects is well over $ 10 billion, so Cliff knows that some of these very promising projects will not be approved. Before the big meeting. Cliff wants to be thoroughly prepared to answer all of the questions he knows he will be asked by the CEO, the functional heads, and other interested parties. As a first step, he wants you to develop an Excel spreadsheet model that provides the following. (You will be asked to extend this analysis in cases in later chapters.) The model should list the given financial information for all potential projects. The model should associate with each proposed project a cell that is I if the project is approved and 0 if it is not approved. Table 2.2 Financial Estimates for Potential Projects (in $ millions) Project Index Functional Area (FA) Partnership Percentage Capex Year 1 Capex Year 2 Capex Year 3 NPV FAI FAI FAI FAI FAI FA2 FA2 FA2 FA2 FA3 FA3 FA3 100% 33% 50% 100% 75% 50% 100% 100% 67% 100% 50% 100% 250 500 100 750 200 00 300 400 60 180 100 300 200 500 80 300 310 220 180 410 280 400 800 1000 300 300 750 750 700 300 600 800 6. 10 400 100 700 1500 600 200 500 400 400 300 400 380 100 260 340 12 Several of the projects, if approved, must be undertaken in joint partnership with another company. For example, if project 3 is approved (see Table 2.2). Ewing will take a 50% share in the project, and the other 50% will be shared by a partner. In this case. Ewing incurs only 50% of the expenditures and receives only 50% of the revenues. Each functional area wants as many of its projects to be approved as possible, but it certainly does not want to be shut out altogether. The model should indicate each of the following: the number of approved projects for each functional area and whether each functional area gets at least one approved project. The latter should be indicated by a 0/1 value: 0 if the functional area is shut out and 1 if it gets at least one. The model should find the sum of capital expenditures for all approved projects, both by year and by the total over all three years. It should also address J.R. Bayer's concerns by indicating whether the total for all three years exceeds $10 billion and, for each year, whether that year's total expenditures exceed $4 billion. As in the previous bullet, these should be indicated by 0/1 values. (Hint: For this bullet and the next one, you might want to avoid complex formulas by creating a table for the company as a whole and for each functional area separately, where each table shows the capital expenditure for each project for each year.) 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 table are probably optimistic estimates, the model should include another input, the possible percentage decrease in each original NPV. Then, there should be a new NPV column that reflects this percentage decrease. The model should list the ROI (return on investment) for each project, defined as the NPV as a percentage of the total capital expenditure for the project. Each ROI should be based on the adjusted NPV from the previous bullet. (Note: The ROI for any project is the same, regardless of whether Ewing undertakes it at the 100% level or in a partnership.) The model should include a one-way data table in which the column input is the common percentage decrease in each NPV mentioned previously, varied from 0% to 30% in increments of 5%. The outputs in the data table should include the following statistics for the ROIS of undertaken projects: the average; the minimum: and the maximum. Of course, the results in this data table should update automatically if the 0/1 values in the second bullet are changed. (Note: Excel has an AVERAGEIF function you can use to find the requested average, but it does not have corresponding MINIF or MAXIF functions. Finding formulas for the requested minimum and maximum is more difficult than it sounds, so you can consider this part a bonus, and you can omit it if necessary.) The model should be accompanied by a line chart of total capital expenditures for all approved projects by year and a separate such line chart for each functional area. I