Student ID ________________

Semester / Year: Semester 1 2021

Faculty / Dept: Management and Marketing

Subject Code: MGMT90280

Subject Name: Managerial Decision Analytics

Writing Time: 3 hrs

Reading Time: 15 minutes

Open Book Status: Yes

Number of Pages (including this page): 9

Instructions to Students:
This examination contributes 50% to the final subject mark.
This examination paper includes 1 section.

Section 1: Contains 5 questions. You are required to answer 5 questions. This section accounts
for 100 marks.

Examination answers must be submitted in PDF file via Canvas assignment/file upload.


2

SECTION 1
This section contains 5 questions. You are required to answer 5 questions. This section accounts
for 100 marks.

Question 1 (20 marks)

Garrett McNeil needs to determine how to invest all of his $200,000 in the following collection of
bonds to maximise the annual return.

Bond Annual Return Maturity Risk Tax-Free
A 10% Long High No
B 6% Long Low Yes
C 5% Short High Yes
D 7% Short Low Yes

Garrett wants to invest at least 40% of the money in short-term maturity bonds and no more than
60% in low-risk bonds. At least 50% of the funds should go into tax-free investments. And lastly, at
least 80% of the total annual return should be tax-free.

a. Formulate a linear programming (LP) model to determine how much Garrett should be
investing in each type of bond if he wants to maximise his annual return.
(8 marks)

The LP model has been solved and the sensitivity analysis report generated. For the following
questions, please refer to the sensitivity analysis report below and you do not need to solve the
model formulated in part (a).


b. What is the amount of money that Garrett will be investing into each bond? Clearly label
your answers. (1 mark)

c. Define Reduced Cost in your own words. Why is there a Reduced Cost of 0 for Bonds A, C
and D? (2 marks)

3

d. Would the optimal solution change if the annual return for Bond C increases from 5% to
6.5%? (2 marks)

e. Write down the range of feasibility for the “investing $200,000” and “at least 40% in short-
term maturity bonds”. Indicate your answers clearly. (2 marks)

f. Garrett received $15,000 from his parents. Garrett would like to know how much his annual
returns will increase by if Garrett decides to invest an extra $15,000. (2 marks)

g. Would the solution change if Garrett decides to increase the money invested in the short-
term maturity bonds from 40% to 60%? Explain your answer. (3 marks)
4

Question 2 (20 marks)

a. Glenn Sturgis’ manufacturing company has a contract to produce 10,000 18W Power Bank
with LED Display for a technology chain store in Australia. Glenn has four different machines
in their warehouse that can produce the 18W Power Bank with LED Display. Because these
machines are from different manufacturers and use slightly differing technologies, their
specifications are not the same. Any of the machines can be utilised, but not necessary all
the machines.

Machine
Fixed Cost to Set
Up Production Run ($)
Variable Cost per
18W Power Bank with
LED Display ($)
Capacity
1 500 7.2 5,000
2 700 5.4 7,000
3 900 4.2 6,000
4 200 9.5 8,000

Formulate an Integer Linear Programming (ILP) model that will minimise total costs
(including fixed and variable costs). Also include constraints to ensure that if Machine 1 is
used, Machine 2 cannot be, vice versa and only a maximum of 3 machines can be used. You
do not need to solve the model.
(12 marks)


b. In Glenn Sturgis’ manufacturing company, there exist a Research and Design (R&D)
department. His R&D department recently developed a new type of carpet that is
waterproof and resists dirt a lot better than the current carpets in the market. Several carpet
retailers in Melbourne want to sell Glenn Sturgis’ new type of carpet. The locations of the
carpet retailers are summarised in the following table.

Carpet Retailer Locations X-Coordinate Y-Coordinate
Sunshine 42 8
Preston 7 22
Moonee Ponds 20 18
Moorabbin 2 4

Glenn expects to make 100, 150, 70, and 90 carpet deliveries to the retailers in Sunshine,
Preston, Bullen, and Moorabbin in Melbourne, respectively.

Glen wants to build a new plant in the location that would minimise the annual shipping
distance to the carpet retailers listed above. However, Glenn also wants to be within 30
kilometres of each of the retailers so that it will be easy to provide on-site quality control
services if problems occur.

Formulate a Non-Linear Programming (NLP) model for this problem that will minimise the
shipping distance. You do not need to solve the model.
(8 marks)

5

Question 3 (20 marks)

Dina Fox, who is a building contractor, is preparing a bid on a new construction project. There are
two other contractors who will be submitting bids for the same project. Based on past bidding
practices, bids from the other contractors can be described by the following probability
distributions:

Contractor Probability Distribution of Bid
A Uniform probability distribution between $500,000 and $700,000
B
Normal probability distribution with a mean bid of $610,000 and a
standard deviation of $30,000

a. What is the expected value of Contractor A’s bid? (1 mark)

b. What is the probability that Contractor A will have a bid of less than $630,000? (2 marks)

c. What is the probability that Contractor B will have a bid between $600,000 and $630,000?
(3 marks)

d. If Dina submits a bid of $620,000, what is the probability that Dina will obtain the winning
bid (i.e., the highest bid)? Using the random numbers below, simulate 5 trials of the contract
bidding process, one by one. Note: You must use the random numbers from left to right to
simulate Contractor A and B’s bids (i.e., the 1st, 3rd, …, 9th numbers are for A’s bids and the
2nd, 4th, …, 10th numbers are for B’s bids).

0.42 0.39 0.56 0.89 0.71 0.85 0.60 0.60 0.19 0.85
(14 marks)





















6

Question 4 (20 marks)

a. Explain the purpose of the training, validation, and test data sets in data mining.
(3 marks)


b. A home improvement retail store selling products that are needed by homeowners to repair,
remodel, and redecorate their homes. Cheyenne Thompson, the manager of this home
improvement retailer store is analysing buying patterns of its customers to evaluate the
layout of its stores. Products within this store are organised into the following categories:
Paint, Wallpaper, Lawn Care, Flooring, Hardware, Plumbing, Tools, Electrical, Building
Materials, Cleaning, Appliances. Cheyenne would like to determine what, if any, categories
of products tend to be purchased together. There have been 1,500 recent transactions from
the store.

A portion of the table of Association Rules for the data using a minimum support of 150
records and a 50% minimum confidence percent was produced below. Please use this
output to answer the following questions.

Row
ID
Confidence % Antecedent (A)
Consequent
(C)
Support for
A
Support for
C
Support for A &
C
Lift Ratio
1 ? Paint & Plumbing Tools 254 432 165 ?
2 54.27631579 Paint & Tools Plumbing 304 376 165 2.165278555
3 54.09252669 Paint & Flooring Plumbing 281 376 152 2.157946543
4 61.17647059 Paint & Wallpaper Tools 255 432 156 2.124183007
5 59.84251969 Paint & Plumbing Flooring 254 427 152 2.102196242
6 58.00711744 Paint & Flooring Tools 281 432 163 2.014136022
7 57.89473684 Paint & Lawn Care Tools 266 432 154 2.010233918
8 51.31578947 Paint & Tools Wallpaper 304 384 156 2.004523026
9 56.76691729 Paint & Lawn Care Flooring 266 427 151 1.994154003
10 54.78723404 Plumbing Tools 376 432 206 1.902334515

i. Calculate the confidence and lift ratio for the rule “If a customer buys Paint & Plumbing, then
they buy Tools”. Interpret these quantities. (4 marks)

ii. What managerial recommendation might be suggested by the rules with a minimum lift ratio
of 2? What do you observe? (3 marks)



P.T.O


7

c. The program director of the MBA program at University of Melbourne wants to develop a
procedure to determine which applicants to admit to the MBA program. The program
director believes that an applicant’s undergraduate grade point average (GPA) and score on
the GMAT exam are helpful in predicting which applicants will be good students. To assist in
this endeavour, the director asked a committee of faculty members to classify 70 of the
recent students in the MBA program into two groups: (1) good students and (2) weak
students.

A logistic regression was used to create a classifier for this data, with the student group as
the target variable and GPA and GMAT as the input variables. Using the Excel printout below,
answer the following questions.



i. Using the Confusion Matrix, calculate the Sensitivity, Specificity, Precision and F1 score.
(4 marks)

ii. Write out the estimated Logistic Regression model function. (2 marks)

iii. How accurate is this procedure? (2 marks)

iv. Do the relationships suggested by the Logistic regression model make sense?
(2 marks)
8

Question 5 (20 marks)

The data in the table below represent quarterly data on the number of 4WDs (four-wheel drive)
sold by a local Melbourne car dealer during the past 3 years.

Year Quarter Units Sold
2017 1 23
2 25
3 36
4 31
2018 1 26
2 28
3 48
4 36
2019 1 31
2 42
3 53
4 43

a. Without drawing a Time Series graph, do you observe any patterns in the data in the table
above? Explain your answer. (2 marks)

b. Develop a Four-Quarters moving average for this time series. Compute MSE and a forecast
for 2020 Quarter 1. (7 marks)

Excel was used to fit a regression model that accounts for seasonal effects in the data. Please use
the following output to answer the following questions.

c. Write out the estimated regression model that relates Sales of the 4WDs with the trend, and
quarters of a year. Ensure to define each of the variables clearly. (2 marks)
9

d. What is your opinion about the overall model fit? (2 marks)

e. Is Quarter 3 a significant factor for the regression model? Use α = 0.05. (2 marks)

f. Interpret the coefficient of Quarter 3. (2 marks)

g. Based on the model that has been developed, compute the first three quarterly forecasts
for 2020. (3 marks)

END OF SECTION

END OF EXAMINATION PAPER