Business Analytics Midterm ASAP!!

ADM2302-Business Analytics-spring/summer 2021

Midterm

June 15, 11:30 AM – 2:00 PM

4 Questions, Total marks: 100

Question 1. Graphical method (25 marks)

Consider the following mathematical model:

1 2

Min 2

subject to

3 2 12

4 8

0, 0

Z x y

x y

x y

x x

= −

+ 

+ 

 

a. Draw each constraint boundary line by generating arbitrary points, and report these points

in your answer. Label each line on the graph. (5 marks)

b. Show each constraint on the graph by a line arrow on the corresponding constraint

boundary line. It means that you should show which side of a constraint boundary line

corresponds to the constraint. (2 marks)

c. Show the feasible region. (5 marks)

d. Find all corner points. You should show how you calculate each corner point. (3 marks)

e. Show the objective function line on the graph, (3 marks)

f. Show the direction of improving the objective function on the graph, (2 marks)

g. Show the optimal solution on the graph, and explain how you have found it. (5 marks)

Question 2. Mathematical model (25 marks)

Nik wants to go on a diet with eating only 3 foods of A, B, and C. Consider the data provided in the following table.

Ingredient

Grams of each ingredient per unit of each food

A B C

N1 5 8 2

N2 8 2 4

N3 4 10 6

Cost per unit 3$ 2$ 4$

The specific requirements per day are:

- To gain at most 40 gram of ingredient N1, - To gain exactly 30 gram of ingredient N2, - To gain no more than 60 gram of ingredient N3, and - Sum of the number of units of Food A and B must be no less than a half of that of C.

How many numbers of units of each food should be eaten to minimize the costs while meeting

the requirements? Fractional numbers are allowable.

a. Define the decision variables. (5 marks)

b. Write the objective function and constraints. The objective function and constraints in the

mathematical model should be numbered. (15 marks)

c. Explain each line of the mathematical model after the model by referring to its number. (5

marks)

Attention: No need to solve this model.

Question 3. Spreadsheet model (25 marks)

Consider the following mathematical model. Prepare a spreadsheet model for this model, and

solve it using Excel solver to get an optimal solution.

1 2 3 4

1 2 3 4

1 3 4

1 2 3 4

1 2 3 4

Max 2 4 3

subject to

3 2 4

1

2 2 6

0, 0, 0, 0

Z x x x x

x x x x

x x x

x x x x

x x x x

= + + +

+ + + 

− + 

+ + + =

   

a. Use meaningful labels in your spreadsheet model. Labels include x1, x2, x3, and x4 for the

name of the variables, constraint 1, 2, and 3 for the name of the functional constraints,

right-hand side values, objective function coefficients, and objective function value. (5

marks)

b. Copy each formula you have used in a cell of your spreadsheet model and paste it and its

address here. For example, G8=SUMPRODUCT(C8:F8,C11:F11). (5 marks)

c. Report a screenshot of your spreadsheet model that has been solved and the optimal

solution can be seen in it. (15 marks)

Question 4. What-if analysis (25 marks)

Answer the following questions considering the sensitivity analysis report given below:

a. What is the optimal solution? (2 marks)

b. What is the optimal objective function value? (3 marks)

c. What is the allowable range for the objective function coefficient of “Variable 1”? (3 marks)

d. What is the allowable range for the Right-hand side value of “Constraint 1”? (3 marks)

e. Does the optimal solution change if we change the objective function coefficient of

“Variable 2” to 800? Why? (3 marks)

f. Based on the available information, can we find out if the optimal solution changes when

we change the objective function coefficients of “Variable 2” and “Variable 3” to 650 and

600 respectively? Why? (4 marks)

g. Based on the available information, can we determine the optimal objective function value

after the RHS of “Constraint 3” changes to 10? Why? If yes, what is the optimal objective

function value after this change? (3 marks)

h. Based on the available information, can we determine the optimal objective function value

when we change the RHS of “Constraint 1” and “Constraint 2” to 4800 and 900

respectively? Why? If yes, what is the optimal objective function value after this change? (4

marks)

Good luck