Ewing Natural Gas is a large energy company with headquarters in Dallas, Texas. The company offers...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 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
Expert Answer:
Answer rating: 100% (QA)
1 Deterministic case We Linear programming approach Dec... View the full answer
Related Book For
College Mathematics for Business Economics Life Sciences and Social Sciences
ISBN: 978-0321614001
12th edition
Authors: Raymond A. Barnett, Michael R. Ziegler, Karl E. Byleen
Posted Date:
Students also viewed these accounting questions
-
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...
-
A large energy company produces electricity, natural gas, and oil. The production of a dollar's worth of electricity requires inputs of $0.30 from electricity, $0.10 from natural gas, and $0.20 from...
-
Johnson & Johnson is a health-care company with headquarters in New Brunswick, New Jersey. Its revenues in the fiscal year ended January 1, 2012, were more than $65 billion. Following are items from...
-
You are the manager of a fast food restaurant. Part of your job is to report to the boss at the end of the day which special is selling best. Use your vast knowledge of descriptive statistics and...
-
Hurette Inc. (Hurette) decided to exercise its option to redeem its outstanding bond issue before the bonds matured in 2022. The bonds had a face value of $10,000,000 and Hurette paid $10,500,000 to...
-
The Sarbanes-Oxley Act will fundamentally change the way public companies do business and how the accounting profession performs its statutorily required audit functions. True 2 False?
-
General ledger accounts cannot be deleted unless the account balance is zero. (True/False)
-
Trout Company uses a perpetual inventory system and made purchases and sales of a particular product in 2014 as follows:. Required: 1. Calculate the total goods available for sale (in units and...
-
Miller Industries has 9,000 units in beginning finished goods. The sales budget shows expected sales to be 30,000 units. If the production budget shows that 35,000 units are required for production,...
-
Peanut Company acquired 90 percent of Snoopy Company's outstanding common stock for $270,000 on January 1, 20X8, when the book value of Snoopy's net assets was equal to $300,000. Problem 3-34...
-
14. A company paid $750 for this month's rent. a. Debit rent expense $750, credit accounts payable $750 Debit cash $750, credit accounts payable $750 c. Debit rent expense $750, credit cash $750 d....
-
A company is considering two alternative machines with different net cash flows and salvage values. Present value amounts are calculated using Excel and the results follow. Potential Machine...
-
What is the difference between arteries and veins structurally?What differences are seen between large and small arteries andlarge and small veins?what do the types of arteries and veins...
-
Discuss the potential risks and ethical considerations associated with the use of CRISPR-Cas9 in gene editing, including unintended consequences, unequal access to genetic technologies, and the...
-
The distance to Saturn from our planet is constantly changing asboth of the planets travel through space. The current distance of this planet from Earth is about 1,484,000,000 kilometers(9.92 AU or...
-
Every year about 20,000 pounds of stone, sand, and gravel aremined for each person in the United States. Calculate how many pounds of stone, sand, and gravel will beneeded for an individual during an...
-
"I know headquarters wants us to add that new product line," said Dell Havasi, manager of Billings Company's Office Products Division. "But I want to see the numbers before I make any move. Our...
-
Show that, given a maximum flow in a network with m edges, a minimum cut of N can be computed in O(m) time.
-
In Problems 21-26, use a definite integral to find the area bounded by the graphs of the indicated equations over the given interval. Then check your answer by finding the area without using a...
-
Interpret the results of Problem 44 with both a graph and a description of the graph.
-
In Problems 37-52, find the area bounded by the graphs of the indicated equations over the given intervals (when stated). Compute answers to three decimal places. y = -x + 1; y = 0; -1 x 2
-
The following data was collected for Molton Ltd for the year ended 31 March 19X3. Calculate price, volume and mix variances for \(19 \times 3\). Product Budget Budget Selling sales price units %...
-
Singleton has been operating for some years as a manufacturer of a single product, and after several years' growth has decided to form a company Singleton Ltd. His accountant advised him that in an...
-
A (Note: this question covers material from both Chapters 43 and 44.) HGW Limited produces a product called a Lexton. The standard selling price and the manufacturing costs of this product are as...
Study smarter with the SolutionInn App