Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Business Analytics - ADM 2302 Fall 2022

Midterm Exam November 6, 2022

QUESTION 1 (26 points)

Sandra is a bus driver who also raises turkey for supplemental income. She is trying to decide what to feed her turkeys. She is considering using a combination of turkey feeds available from local suppliers. She would like to feed the turkeys at minimum cost while also making sure each turkey receives a daily adequate supply of calories and vitamins. Sandra has formulated the following Linear Programming (LP) model:

Let       X1 = pounds of Feed Type 1 in diet
X2 = pounds of Feed Type 2 in diet
Minimize Z = $0.3X1 + $0.5X2 (Cost)
subject to

(1) 800X1 + 1,000X2 ≥ 8,000   (requirement of calories per day)

(2) 140X1 + 70X2 ≥ 700 (requirement of units of vitamins)

(3) X1 ≤ (1/3)(X1 + X2) (constraint 3)

(4) X1 ≥ 0, X2 ≥ 0.

(a) Constraint 3 was added since Feed Type 1 contains an ingredient that is toxic if consumed in too large a quantity. In managerial terms (i.e., verbally explain) what does constraint 3 mean? (2 points)

(b)  Briefly explain or define the 140 units in the vitamins constraint. (2 points)

(c) Graph the constraint lines and mark them clearly with the numbers (1), (2) and (3) to indicate which line corresponds to which constraint. Darken the feasible region. (12 points)

You can NOT use a software that automatically does the plotting: you are NOT allowed to use DESMOS or any similar tool. Plotting using Excel, a paint tool or hand plotting is OK. If you wish to use a “graph paper” to assist you with plotting, one is provided below (check page 4).

(d) Using the graphical method, how many pounds of feed Type 1 and feed Type 2 that Sandra should give each turkey? What is the optimal cost? Provide all necessary steps/calculations to justify your answers. (10 points).

QUESTION 2 (28 points)

Let x₁, x₂, and x₃ represent number of units of product A, B, and C and the constraints represent the availability of materials 1, 2, and 3.

Given this LP model

Maximize Z = $100x₁ + $60x₂ + $50x₃      (Profit)

Subject to

2x₁ + 3x₂ + 4x₃  £ 30 (Material 1)

 x₁ + 3x₂ + 2x₃ £ 22 (Material 2)

6x₁ + 3x₂ + 4x₃  £ 30 (Material 3)

       x₁, x₂, x₃  ³ 0

The sensitivity report for this problem above is provided below as Excel Solver output. You will need this output to answer (a) through (g) below each part of which is to be considered independently of all others.

(a) What is the optimal profit? Show your work. (2 points)

(b) Determine the range of optimality for the objective function coefficient of the decision variable x2. (2 points)

(c) If the profit contribution of product A decreased to $75/unit. Without resolving the entire problem, decide if the optimal solutions and the value of the objective function can be determined (if yes, then provide the optimal values). (4 points)

(d) What will be the impact on the profit when we force the production of 2 units of Product C (i.e. x₃= 2)? If you can determine what the new optimal value of the objective function is, then do so. If you cannot determine the new optimal value of the objective function, explain why. (3 points)

(e) Some values in the Sensitivity report were deleted by your professor. Complete the LP table below: Write the corresponding value of each item deleted in the Sensitivity report and provide a brief justification for your answer. (8 points: 2 points each)

(f) The Manager is considering increasing Material 2 and Material 3 values by 2 units EACH. Without resolving the entire problem, decide if the impact of the changes on the optimal solutions and the value of the objective function can be determined (if yes, then provide the optimal values). Show your work. (6 points)

(g) If one additional unit of the third resource (i.e. Material 3 constraint) could be obtained/purchased at a premium of $6, what impact would this have on profit? If you can determine what the new optimal value of the objective function is, then do so. If you cannot determine the new optimal value of the objective function, explain why. (3 points)

QUESTION 3 (25 points): Excel Spreadsheet Formulation and Solutions

Lafarge Paving and Construction (Eastern) Limited is the largest supplier of asphalt, aggregates and concrete in the Ottawa area. Lafarge operates several facilities including sites at Boyce Road in South Ottawa, Merivale Road, and Gloucester. On any summer day, trucks arrive to a designated site, are loaded with paving materials and then proceed to the designated work site. The following Excel table describes the truck transportation problem and the unit costs of delivery for the trucks. Lafarge wants to determine the minimum total cost of how many trucks to assign to each facility to move the paving materials to each of the 3 work sites at (i) Nepean, (ii) Barrhaven, and (iii) Vanier.

(a) Define the decision variables of this problem. (3 points)

(b) Formulate this transportation problem on a spreadsheet and use Excel’s Solver to help Lafarge determine the optimal number of trucks to assign to each facility to move the paving materials to each of the 3 work sites in order to minimize the total delivery costs (Provide the corresponding “Excel Spreadsheet” and the “Sensitivity Report”). Include “managerial statements” that communicate the results of the analyses, (i.e. describe verbally the results). (17 points)

N.B.: To build the Excel Spreadsheet formulation quickly, you can copy/paste the two tables above into your Excel Worksheet.

(c) Does this problem have an alternate optimal solution? Justify your answer. (2 points)

(d) If trucks from Gloucester facility can NOT be assigned to the work site in Vanier, briefly describe how you will adjust the formulation in part b)? (3 points)

QUESTION 4 (20 points)

The BC Apricot Sweet company purchases apricot from local growers and makes dried apricots and apricot jam. It costs $4 to produce a bag of dried apricots and $3 to produce a jar of apricot jam. Dried apricots bag sells for $10 per bag and apricot jam sells for $8 per jar.

The company has a policy that at least 25% but not more than 55% of its total production must be apricot jam.

The company wants to meet but not exceed the demand for each product. The marketing manager estimates that the demand for dried apricots is a maximum of 4,000 bags, plus an additional 2 bags for each $1 spent on advertising dried apricots. The maximum demand for apricot jam is estimated to be 3,000 jars, plus an additional 3 jars for every $1 spent to promote apricot jam.

The BC Apricot Sweet wants to know how many units of each to produce and how much advertising to spend on each to maximize net profit.

Formulate algebraically the linear programming model for this problem. Define the decision variables, objective function, and constraints. DO NOT SOLVE.

Hint: the number of decision variables for this problem is four.