Supply Chain Modeling On Excel

Chapter 2: Introduction to Spreadsheet Modeling: Case 2.1 Project Selection at Ewing Natural Gas Book Title: Practical Management Science Printed By: Alixen Sanchez (alisensc@gmail.com) © 2019 Cengage Learning, Cengage Learning

Chapter Review

Case 2.1 Project Selection at Ewing Natural Gas

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.

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

1 FA1 100% 250 100 100 60

2 FA1 33% 500 300 300 180

javascript://
javascript://
Project Index

Functional Area (FA)

Partnership Percentage

Capex Year 1

Capex Year 2

Capex Year 3

NPV

3 FA1 50% 100 200 400 80

4 FA1 100% 750 500 300 310

5 FA1 75% 200 400 800 220

6 FA2 50% 1000 300 300 180

7 FA2 100% 750 750 300 410

8 FA2 100% 800 700 600 280

9 FA2 67% 400 600 800 380

10 FA3 100% 100 200 400 100

11 FA3 50% 700 500 300 260

12 FA3 100% 1500 400 400 340

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 1 if the project is approved and 0 if it is not approved.

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

javascript://
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.

Chapter 2: Introduction to Spreadsheet Modeling: Case 2.1 Project Selection at Ewing Natural Gas Book Title: Practical Management Science Printed By: Alixen Sanchez (alisensc@gmail.com) © 2019 Cengage Learning, Cengage Learning

© 2021 Cengage Learning Inc. All rights reserved. No part of this work may by reproduced or used in any form or by any means – graphic, electronic, or mechanical, or in any other manner – without the written permission of the copyright holder.

 
"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"
Looking for a Similar Assignment? Our Experts can help. Use the coupon code SAVE30 to get your first order at 30% off!

Hi there! Click one of our representatives below and we will get back to you as soon as possible.

Chat with us on WhatsApp