Analytics 2: Practice Exam A

You will see the following instructions on the actual exam. Not all apply to the practice exam.

1.   This exam is open-book / open-notes; you may access all course materials posted on Canvas.

2.   You have up to 3 hours to work on the exam. Begin by skimming the exam and planning how to allocate your time.

3.   Submit your answers to the exam questions on Canvas. Also, please be sure to submit your spreadsheet models supporting your answers. You must submit support work to receive

credit for your answers.

4.   Under Tuck's Honor Code, you are responsible for spending no more than three consecutive hours on this exam. You may not discuss any matter related to the form, content, or degree  of difficulty of the exam with any classmate who has not taken the exam. You are not to

exchange messages (on any platform) or phone calls with others while taking this exam. You may not use ChatGPT (or similar tools) on this exam. If you are uncertain about any

administrative or Honor Code issues or encounter any irregularities regarding the administration of this exam, please contact your professor as soon as possible.

[Note: The honor code does not apply to the practice exam. We include the language here so you can be familiar with it before seeing the exam.]

5.   The spreadsheet models you create for this test should, of course, be correct. They should  also be readable, but we do not expect extensive documentation or careful formatting. We should, however, be able to easily find your submitted answers in the supporting

spreadsheets and understand your logic.

6.   Points will be allocated among the exam problems as follows:

Problem             1           2           3          4           Total

Points                25         25         25         25         100

You will be asked to affirm the following Honor Code statement in the online submission system.

I affirm that I will complete this exam in accordance with the exam instructions and the Tuck Honor    Code. In particular, I will not collaborate with anyone during the exam. If any issue arises during the    exam that involves an Honor Code question, I will bring it to the attention of the professors during or immediately after the exam.

Data Files for the Exam

The Excel file(s) needed for the exam questions are available under “Files” in Canvas, under the “Practice Exam A” folder.

File Saving Instructions (these instructions would apply for the actual exam)

For the actual exam, you will be asked to submit multiple files. There should be an Excel file for each spreadsheet-based question. If a question has multiple parts with a different model for each part, you should create a separate worksheet within each Excel file (one for each part).

To avoid losing work, frequently save the files to your laptop.

1. MediDevice Inc.                        [Data file: MediDevice.xlsx]

It is the beginning of 2019, and a U.S. medical device manufacturer, MediDevice Inc., has developed a new blood analyzer for rapid in-the-doctor’s-office testing. The much-anticipated device will

receive final regulatory approval in time for a January 2020 launch. This device is revolutionary and

has worldwide appeal; its target markets are United States, Europe, and Japan. (And, unlike Theranos’s entry into this market, the MediDevice product actually works.)

Demand for this product is largely driven by the fraction of doctor offices adopting the technology. Forecasts indicate 10% of doctor offices in any country would potentially adopt this type of device. The time it takes to achieve this technology adoption is expected to be 2-5 years, depending on several factors, including the effectiveness of the sales force in each country.

The MediDevice board has commissioned a cash flow analysis of this new device starting in 2019

(open MediDevice.xlsx). In this model, cash flows in each column occur at the end of the year.

Assume that the logic and all formulas in the model are correct. In each of the following questions, start with the base case model.

a.   In Excel, plot the relationship between the depreciation lifetime (Cell C40) and the NPV. Consider values for the depreciation lifetime between 5 and 15 years, with axes as shown below.

Reminder: For each question, return to the base case.

b. What value for the unit manufacturing cost (C36) would be required to reduce the NPV by 10% of the base case value?  Write your answer to the nearest tenth (e.g., $2.2 or $4.1 or $5.7, etc.)

c. Vary all parameters in the model according to the Low-Base-High values given in the spreadsheet.

List below the three parameters that have the strongest impact on the NPV. List in order of diminishing impact, from top to bottom.

Parameter #1:                                            

Parameter #2:                                            

Parameter #3:                                            

d.   Independently vary the discount rate (C11) between 5% and 15% and the peak share for market penetration (C17) between 0 and 10%, in steps of 1% for both parameters. List all the

combinations of these inputs that lead to an output NPV (a value from C43) over 350.

Save and upload to Canvas a spreadsheet showing all work supporting your answers.

2. The Northern Cheyenne Bison Farm                                     [There is no data file for this question]

Bison (sometimes called Buffalo) are widespread on the American Plains. Bison meat is becoming a popular food in the U.S. The Northern Cheyenne is a Native American tribe with land for raising

bison. A group of Northern Cheyenne entrepreneurs are starting a bison farm and have arranged for

the free delivery of 200 heifers (mature female bison) and 200 bulls (mature male bison) from a National Park that is overpopulated with bison. The CEO of the new firm describes the tribe’s

relationship with the Bison: “They’ve always taken care of us, and we need to take care of them.”

It is now the beginning of 2020, and the bison will be delivered in one year, at the beginning of 2021. They plan to sell 30% of bulls at the end of each year. The farm will not sell any heifers. After the

initial delivery of 400 bison, no new bison will be acquired from outside.

Bison breeding rates are determined by the following formula:

numbeT CalveS boTnina yeaT = 0.23 ∗ (1 − e一numberbulls) ∗ (numbeT ℎeifeTS)

Use the EXP() function to calculate e to the power (-number of bulls); In Excel,

Number of calves born in a year = c*(1-EXP(-b))*(h)

Where “c” is 0.23, “b” is the number of bulls and h is the number of heifers.

Half of the calves born each year are female and half are male. Calves require 3 years to mature, so,  for example, those born during 2021 are not ready to be sold until the end of 2024. Each year, 3% of heifers that are mature at the beginning of the year die, and 3% of bulls that are mature at the

beginning of the year die. No calves die, and no calves that mature during the year die.

As an example, consider the first year of the farm’s operation, 2021. During that year,

0.23(1 − e一200)(200) = 46 calves will be born: 23 heifers and 23 bulls. These calves will be available for sale at the end of 2024. In addition, (0.03)*(200) = 6 heifers and 6 bulls die. This leaves 194

heifers and 194 bulls available for sale, and therefore the farm will sell (0.3)(194) = 58.2 bulls at the end of 2021.1

At the end of 2021, the farm will receive $1,400 for each mature bull sold. The revenue per bull is

expected to increase by 4% each following year. There are two costs: fencing costs and medical

costs. To start the farm, the entrepreneurs must pay $300,000 now (at the beginning of 2020) for

fencing. At the end of each year of operation, the farm must pay a veterinary cost of $65 per adult

bison. The veterinary cost applies to all heifers and bulls, including bulls that will be sold in that year. There are no costs associated with calves.

They plan to operate the farm until the end of 2030 when they will sell off all remaining mature    bison for that year’s standard revenue per mature bull; you can assume that the revenue per bull applies to heifers as well. Calves at the end of 2030 will not earn any revenue. The entrepreneurs use a discount rate of 6%.

Part I: Influence Diagram

You will be asked to build a spreadsheet model for the Northern Cheyenne Bison Farm (see next  page). First, sketch a complete and clear influence diagram for your model. You may draw it on a blank piece of paper or a whiteboard, take a picture, and upload the picture into Canvas. Or use  Gozinta and paste a copy of the Gozinta diagram into Canvas.

Note: We will not test you on influence diagrams in the actual exam.

Part II: Model Building

Build a spreadsheet model for the tribe. You may use the last page of the exam to sketch your

spreadsheet (although your sketch will not be graded). Your spreadsheet will be graded both for its technical correctness and for its adherence to the principles of spreadsheet engineering. In

particular, design your model so that the logic is easy for an outsider to understand. Note that  documentation of each calculation is not required, nor is extensive formatting for appearance.

However, if you need to make any assumptions beyond the problem description given above, you should include an explanation of these assumptions at the top of the spreadsheet.

Evaluate the NPV of the bison farm.

Base case NPV:        _________________

3. Awarding Supplier Contracts                                                                [Data file: Contracts data.xlsx]

An automobile manufacturer wants to award contracts for the supply of five different

components. Five contractors have submitted bids on the components; the following table summarizes the prices bid per unit as well as the demand for each component. Note that    where an entry is $0, the contractor submitted no bid.

The entire demand for a component does not have to be supplied completely by one    contractor. In fact, certain contractors have indicated maximum quantities that can be

supplied at the bid price. Contractor 1 can supply no more than 18,000 of component 4,

contractor 3 no more than 8,000 of component 3, and contractor 4 no more than 10,000

units of component 2. In addition, the manufacturer wishes to avoid awarding contracts for over $1.5 million in total to any one contractor.

There is no provision that awards must go to the low bidder. The automobile manufacturer

wants to determine how many units of each component should be awarded to each contractor in order to minimize the total costs for the five components.

a.   Record the best award allocation for the manufacturer in the table below. What is the corresponding total cost?

Component                                 

Contractor                1              2              3              4              5

1

2

3

4

5

Total cost?                                                               

b.   The ceiling on the award to any one contractor ($1.5 million) is an arbitrary managerial    constraint. Sketch an accurate graph showing the relationship between the optimal total cost and the size of the ceiling. Your analysis should cover the range from $1,100,000 to  $1,800,000 in steps of $20,000.

c.   What is the shadow price on demand for component 2 given the current demand? Over what range of demands for component 2 does this shadow price apply? (Reset the ceiling for each contractor to $1.5M before answering this question.)

4. Airplane Refueling                                                    [Data file: Airplane data.xlsx]

TGA wants to optimize its purchases of jet fuel in the cities it serves around the world. Since the fuel efficiency of an airplane is related to its weight, an airplane carrying more fuel than  needed to reach its destination will waste fuel. This fact suggests that a plane should take

off with just enough fuel to reach its next destination, plus a safety buffer in case the plane has to circle before landing or has to be diverted to another airport. However, since fuel

prices vary from city to city, a policy of minimal fuel purchases may be more costly than filling the plane to capacity at inexpensive cities.

To illustrate TGA’s problem, consider an airplane that each day flies a so-called rotation that consists of the following four flight segments:

1.   New York (JFK) – Los Angeles (LAX)

2.   Los Angeles (LAX) – San Francisco (SFO)

3.   San Francisco (SFO) – Seattle (SEA)

4.   Seattle (SEA) – New York (JFK)

Upon its arrival back in New York, the airplane repeats the rotation. For each flight

segment, the table below displays the minimum and maximum fuel levels at takeoff, a    linear function that relates fuel consumption to the fuel level at takeoff (G), and the fuel prices at the departure city. To illustrate the fuel consumption relationship, suppose the plane departs New York (JFK) with 25 thousand gallons of fuel and is heading to Los

Angeles (LAX), as in segment one. Then the plane will consume 4.7+(0.40)(25)=14.7 thousand gallons of fuel during the flight to Los Angeles (LAX).

*Here, G represents the amount of fuel onboard at the start of the flight.

You should assume that there are 5,000 gallons of fuel in the plane at the start of the rotation before the purchase of fuel. This fuel has already been paid for.

The fuel on board, when the plane lands in New York, must equal the 5,000 gallons at the start of the rotation.

Formulate and solve an optimization model with which TGA can determine how much fuel

to purchase at each of the four cities in order to minimize the total fueling cost for a rotation.