Maha Natural Gas is a large energy company based out of Omaha,…

Masunzu18 Maha Natural Gas is a large energy company based out of Omaha,…Maha Natural Gas is a large energy company based out of Omaha, MN.  The company offers a wide range of energy products and has annual revenues of approximately $50 billion.  Because of the diverse nature of the company, its Manager for Project Development, Bobby B., 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.  Bobby has a difficult balancing act. The company’s CEO, Josiah G., 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.  Bobby 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. Bobby is currently about to meet with all parties involved to discuss project proposals for the next three years.  She has proposed 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%. Image transcription textFinancial Estimates for Potential Projects [in Smilliuns} Functional Area {FA} Partnership Percentage Cape):Year 1 Caper: Year 2 Caper: Year 3 Project Index … Show moreJosiah 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 Bobby knows that not all of these promising projects will be approved. Before the big meeting, Bobby wants to be thoroughly prepared to answer all the questions she knows she will be the CEO, the functional heads, an other interested parties.  As a first step, she wants to develop an Excel spreadsheet model that provides the following: The model should list the given financial information for all potential partiesThe model should associate with each proposed project a cell that is 1 if the project is approved, 0 if not approvedSeveral of the projects, if approved, must be undertaken in joint partnership with another company. For example, if project 3 is approved, maha Gas will take a 50% share in the project, and the other 50% will be shared by a partner.  In this case, maha Gas will only incur 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 all together. The model should indicate each of the following: the number of approved projects for each function area and whether each function area gets at least one approved project.  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.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 all address Josiah’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 direction, these should be indicated by 0/1 values.The model should find the sum of capital expenditures for all approved projects, by year, for each functional areaRecognizing that the NPV’s in the attached spreadsheet 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 direction.  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 be 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.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.BusinessBusiness – Other