Assignment7.pdf

1

Instructions

1. The exam is a take home exam. It is due electronically (via the course website) by 48 hours after
receipt.

2. You may ONLY refer to the following material in doing the exam: course text, course handouts
(slides, cases, etc), course spreadsheets, your own notes, all your own homeworks, all
homework solutions (i.e., all material on the course website).

3. You MAY NOT communicate with anyone in connection with this exam. Failure to comply with
this requirement will result in a grade of XF in this course.

4. If you have any questions about the wording in the questions, and are unable to proceed with
answering the question, you can e-mail your Section instructor (DO NOT post your questions on
the website). Depending on the nature of the question, the instructor may respond at their
discretion to the question posed. If you do not get a response regarding your question, make
the assumptions you feel are necessary, state your assumptions, and answer the question.

5. Submission instructions: Create a separate spreadsheet file for each question. If dif ferent parts
of a problem require you to rerun your optimization (changing objective or constraints), please
place each of the models in a separate worksheet in the same spreadsheet file. Take care to
ensure your submission CONTAINS your Solver model, Precision Tree model, @RISK Simulation
model (and results). Additionally create one word file with your answers (unless they are
contained in each spreadsheet). SUBMIT ALL YOUR FILES on the exam submission page on the
course website. It is YOUR RESPONSIBILITY to check that you have correctly submitted all the
files you in tended. Please understand, we cannot accept any missing files after the submission
deadline!

6. Submitting the exam indicates you have signed and agreed to the following pledge. “I pledge on
my honor that I have not given or received any unauthorized assistance on the final exam for
BUSO716 Decision Modeling.”

7. Goodluck! We hope you have enjoyed this class.

Submission instructions: Create a separate spreadsheet file for each question. If dif ferent parts of a
problem require you to rerun your optimization (changing objective or constraints), please place each of
the models in a separate worksheet in the same spreadsheet file. Take care to ensure your submission
CONTAINS your Solver model, Precision Tree model, @RISK Simulation model (and results). Additionally
create one word file with your answers (unless they are contained in each spreadsheet).

2

Problem 1. Drof Automobiles produces four types of cars: subcompact, compact, intermedi ate, and
luxury. Drof also produces trucks and vans. Vendor capacities limit total production capacity to at most
1.2 million vehicles per year. Subcompacts and compacts are built together in a facility with a total
annual capacity of 620,000 cars. Intermediate and luxury cars are produced in another facility with
capacity of 400,000; and the truck/van facility has a capacity of 275,000. Profit margins, market
potential (in terms of maximum sales), and fuel efficiencies are summarized as follows.

Type Profit Margin
($/vehicle)

Market Potential
(sales in ‘000)

Fuel Economy
(mpg)

Subcompact 150 600 40
Compact 225 400 34

Intermediate 250 300 15
Luxury 500 225 12
Truck 400 325 20
Van 200 100 25

Drof would like to determine the optimal vehicle mix so as to maximize profit. (In this problem do not
worry about integer restrictions, it is okay if the solution contains a non-integer value in the production
mix).

(a) Write down a Linear Program (i.e., mathematical/algebraic) formulation for the above
optimization problem. Make your formulation clear by defining the decision variables,
constraints, objective function etc. clearly.

(b) Implement and solve your linear programming model in Solver. Submit your Excel
spreadsheet Solver Model for this part (i.e., the actual Excel file).

(c) What is the optimal profit? State in words.

(d) What is the optimal combination of vehicles to produce? State in words.

(e) The US government has just passed legislation that requires the average fuel economy of the
vehicles manufactured by an automobile manufacturer to be at least 28 miles per gallon. (Note.
The legislation by itself does not eliminate any vehicle from being manufactured. It simply
requires the average fuel economy taken over all vehicles manufactured by an automobile
manufacturer to be at least 28 miles per gallon). Will this affect the current production plan? If
so, i) explain mathematically how to modify your model, and ii) implement your modification
(do this in a separate sheet containing its own Solver Model) to determine the new optimal
profit and production plan. (To obtain full credit the modified model should be linear). Submit
your Excel Spreadsheet Solver Model for this part (i.e., the actual Excel file) along with your
answers.

3

Problem 2. The Prudential Bank offers a special low interest loan program for small business owners.
The rate is fixed for all loans made under this program, but the bank has the discretion whether or not
to approve a particular loan. Ann, a loan officer at the Bank, is reviewing an application under this
program from Bob, a small business owner. If Bob repays the loan as per the terms of the loan, the Bank
will earn a profit of $15,000. (All earning are reported as present values.) However, if Bob defaults on
the loan, the Bank will lose $10,000. Based on a quick examination of Bob’s application, Ann assesses
Bob’s likelihood of default at 5%. Ann can either issue a quick decision to approve or deny the loan, or
send Bob’s application to Credit Services Inc., a firm that conducts in-depth credit analysis and issues a
rating of ”A”, ”B” or ”C” for the prospective borrower. Each application sent to Credit Services costs the
Bank $2,000. Historically, the default rates for Credit Services’ three ratings categories have been as
follows:

Rating Default Probability
A 1%
B 10%
C 90%

Ann believes that if she sends Bob’s case to Credit Services, there is a 40% chance that he will get an ”A”
rating, a 50% chance that he will get a ”B” rating, and a 10% chance that he will get a ”C” rating.

(a) Structure Ann’s problem as a decision tree. Draw this tree by hand (or electronically) and
include it along with your submission.

(b) Implement your tree using Precision Tree. Submit your Precision Tree implementation.

(c) What is your recommended decision strategy? Make sure to state the best strategy in words;
otherwise no credit will be given.

4

Problem 3. Northeast Airlines recently began service from Los Angeles (LAX) to LaGuardia Airport (LGA)
near New York. Passenger demand for this flight has been high, but its overall travel time has been
unreliable. The Director of Operations at Northeast Airlines is particularly concerned about delays. The
scheduled travel time from LAX to LGA is 300 minutes. En route delays are not uncommon, due to
weather or air traffic control restrictions, and this delay is uniformly distributed between 0 and 60
minutes.

Excessive delays have been caused by the flight crew’s inability to reach the flight on time, due to tight
connections from another inbound flight. The Director of Operations has found that the delay added by
the crew is exponentially distributed with a 15-minute mean1. Worse yet, whenever the crew delay
exceeds 45 minutes, the crew will “time out”, meaning that they cannot board the flight because their
overall flying time will exceed legal limits. When this happens, Northeast dispatch has to call for an
alternate crew, which adds 90 minutes of delay (in addition to whatever delay the original crew has
incurred).

(a) Use @RISK to create a spreadsheet simulation that models the total travel time of the flight.
(Run it for 1,000 iterations, and show the resulting histogram of total travel time.)

(b) What is the mean and standard deviation of the travel time?

(c) The Department of Transportation (DoT) considers a flight to be on time if it arrives within 15
minutes of its scheduled arrival time. What is the probability that the flight will arrive on time
(that is, total travel time is 315 minutes or less)?

(d) The Director of Operations considers any delay over 60 minutes (travel time is 360 minutes or
more) to be excessive. What is the probability of an excessive delay?

(e) The Director of Operations would like to improve the on-time performance by eliminating only
one of the two sources of delay (en route delays or crew delay). Which should it be? To
determine this, rerun the simulation two ways: first with the en route delay set to zero and the
crew delay preserved, then with the en route delay preserved and the crew delay set to zero.
Then for each case, look at and report on the probability of travel time being less than 315
minutes.

1 The exponential distribution takes only one parameter, its mean. So, in @RISK, use =riskExpon(m), where m is the
mean.