Final OR
pdf
keyboard_arrow_up
School
George Mason University *
*We aren’t endorsed by this school
Course
531
Subject
Industrial Engineering
Date
Jan 9, 2024
Type
Pages
5
Uploaded by CorporalHamster3468
OR 531
Analytics and Decision Analysis
Fall
2023
Final Exam
December 12, 2023
NAME
Instructions:
1. You may use books, notes, excel sheets, Python codes, but all work is to be done
on your own.
The following statement must be typed in the beginning of your
Excel/Python file in order for your exam to be graded:
“I understand that it is considered cheating to give or receive any unauthorized
assistance on this test.”
2. You may ask Dr. Mohebbi any questions and post them on the discussion board,
but you may not discuss the test or compare answers with anyone else.
3. The test is untimed, but due before 6:00 p.m. on Tuesday, December 12, 2023.
4. Submit the answers for all problems on BB.
5. Good Luck!
1
1.
(25 points) Spreadsheet Design
The St. Mary (‘Mary’) and Mt. Sinai (‘Sinai’) hospitals are planning to merge.
Together, they must design a new employee benefits plan that will cover all employ-
ees in the merged hospital. To save on administration costs, they have decided to
adopt for the entire merged hospital either Mary’s plan or Sinai’s current plan. The
table below shows that Sinai’s average benefits are more generous than Mary’s. For
example, an employee with a family receives, on average,
550 in annual benefits
under Mary’s plan and
830 in benefits under Sinai’s plan. In total, the employee
with a family would receive an average of
550+ 90+ 40+ 15= 695 under Mary’s
plan and significantly more under Sinai’s. Overall, 65% of all employees at both
hospitals enroll under a family plan and the remainder enrolls under the individual
plan.
The table displays average benefits, but either decision will significantly reduce ben-
efits for some employees. Mary has 1200 employees, and if Sinai’s plan is adopted
then 12% of Mary’s employees are expected to leave because of the reduction in
benefits. Sinai has 750 employees, and if Mary’s benefits are adopted then 30% are
expected to leave Sinai. On average, it will cost
45,000 to replace an employee
who leaves either hospital. Replacement employees are similar to current workers:
average costs are equal to those in the table and 65% enroll in a family plan. The
hospitals would like to assess the total cost over the next 8 years of applying each
plan to the entire merged hospital.
Assume that the following events will occur
now: the new plan is announced, some employees leave because of a reduction in
benefits, and these employees are immediately replaced. Then, the costs described
in the table above will be booked after 1 year.
During years 2-8, total benefits
costs will grow by 8% annually. Management uses a discount rate of 5%.
Build a spreadsheet model to answer the following questions.
(a) Which benefits plan has the lower cost (expressed as an NPV)?
(b) At what growth rate in benefits costs do the two plans have an equal NPV?
2
2.
(25 points) Linear Optimization
You are the operations manager of a chemical company which produces five prod-
ucts in a common production facility. The following table gives potential sales for
each product, along with variables costs and revenues.
Production in any year
cannot exceed the sales potential.
Your production facility rotates through the product line because it can produce
only one product at a time. The production rates differ for the various products.
It takes 0.3 hours to make 1 ton of A, 0.5 hour for a ton of B, and 1 hour each for
a ton of C, D, or E. The facility can be operated up to 4000 hours per year.
The file EnviroPlanning.xlsx contains all the information given above in a format
designed for optimization. Use this template to answer the following questions.
(a) Determine the maximum profit the company can achieve from its product line
in the coming year.
(b) What is the shadow price on the sales potential for product C?
3
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
3.
(25 points) Network Models
An automobile manufacturer wants to award contracts for the supply of five differ-
ent engine components. Some of the components are used in most models, while
other components are specialized to one or two models—therefore, the volumes
differ. Five contractors have submitted bids on the components, and the following
table summarizes the prices bid per unit. Where no entry appears, the contractor
did not bid.
The manufacturer wants to determine how many units of each component should
be awarded to each contractor in order to minimize total cost for the purchases.
(For these purposes, fractional solutions in the model are acceptable.)
(a) What is the minimum total cost for the manufacturer, if no other conditions
are imposed?
(b) In fact, Supplier 2 is capacity-constrained and cannot provide more than
25,000 units in total. In addition, the manufacturer does not want more that
2 million allocated to any one of the suppliers. Recognizing these limitations,
what is the minimum total cost for the manufacturer?
4
4.
(25 points) Classification and Prediciton Models
The dataset Boston Housing.xlsx contains information on 506 census tracts around
the city of Boston.
The data include housing-related information (e.g., average
rooms per dwelling) as well as demographic information (e.g., per capita crime
rate). The goal is to create Classification Tree and Logistic Regression models to
predict which census tracts have a median home price above
30,000.
Interpret
the results and choose the best model.
5