Business Analytics – ADM 2302
Midterm Exam
商业分析考试代考 QUESTION 1: Graphical Method & Excel Solver Solutions (36 points) An Architect wishes to design the safety deposit room for a bank safe.
QUESTION 1: Graphical Method & Excel Solver Solutions (36 points) 商业分析考试代考
An Architect wishes to design the safety deposit room for a bank safe. He plans to place safety deposit boxes approximately 6 feet high by the approximate length of the room’s walls, 40 feet (i.e., 240 square feet total space available). The bank has asked him to plan for two sizes of box: large and small. Large boxes consume 1 square foot and will rent for $90 per year. Small boxes consume 0.6 square feet and will rent for $60 per year. The bank has asked for at least 80 large boxes and at least 120 small boxes. The bank hopes to maximize its revenue from safety deposit boxes.
A correct formulation for this problem is provided below:
Let L and S denote the number of units of large and small boxes that the architect’s design will provide
MAX Z = 90L + 60S ($)
subject to,
1) Available Space L + 0.6S ≤ 240 square feet
2) Minimum number of large boxes L ≥ 80
3) Minimum number of small boxes S ≥ 120
4) Non-negativity L, S ≥ 0
(a) 商业分析考试代考
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.
(b) Using the graphical method, how many of each size should the architect’s design provide? What is the annual revenue? Report the solution in a managerial statement (i.e. describe verbally the optimal solution and its profit). Provide all necessary calculations to justify your answers. (8 points).
(c) The bank would like to add a mini size box, which will rent for $35 per year and will consume 0.3 square feet of wall space. The bank will now require at least 30 mini boxes, at least 100 small boxes, and at least 40 large boxes. Further, the bank wants the total area of the large boxes to be at least 50% of the available space. How many boxes of each type should the architect include and what is the total annual revenue?
Formulate the linear programming problem on a spreadsheet and SOLVE using Excel Solver (Provide the corresponding “Excel Spreadsheet” and the “Answer Report” on this document). Include “managerial statements” that communicate the results of the analysis. (i.e. describe verbally the results). (16 points)
QUESTION 2: LP Formulation (21 points) 商业分析考试代考
Power Generation Inc. has just announced the March 1, 2022 expansion of the R.H. Saunders Generating Station in the Ottawa/St. Lawrence area. Its personnel department has been directed to determine how many technicians need to be hired and trained for the March – June period. The station currently employs 350 fully trained technicians and projects the following personnel needs:
| MONTH | PERSONNEL HOURS NEEDED |
| March | 40,000 |
| April | 45,000 |
| May | 35.000 |
| June | 50,000 |
By Ontario regulations, a technician can actually work no more than 130 hours per month. Negotiated policy at Power Generation Inc. also dictates that layoffs are not acceptable in the months when the station is overstaffed. So, if more trained employees are available than are needed in any month, each worker is still fully paid, even though he or she is not required to work the 130 hours. Training new employees is an important and costly procedure. It takes one month of one-on-one classroom instruction before a new technician is permitted to work alone in the facility.
Therefore, Power Generation Inc. must hire trainees one month before they are actually needed. Each trainee teams up with a skilled technician and requires 90 hours of that employee’s time, meaning that 90 hours less of the technician’s time are available that month for actual work. Personnel department records indicate a turnover rate of trained technicians at 5% per month. In other words, about 5% of the skilled employees at the start of any month resign by the end of that month. A trained technician earns an average monthly salary of $4,000 (regardless of the number of hours worked, as noted earlier). Trainees are paid $2,000 during their one month of instruction. Power Generation Inc. wants to find the best staffing schedule for the four-month period.
An algebraic formulation is given below: 商业分析考试代考
Let Si = number of trained technicians available at the start of month i
Let Ti = number of trainees (new technician) hired at the start of month i.
Where i=1,2,3,4 for March, April, May and June
Minimize Z = $4,000S1 + 4,000S2 + 4,000S3 + 4,000S4 + 2,000T1 + 2,000T2 + 2,000T3 + 2,000T4
subject to:
130S1 – 90T1 ≥ 40000 Work requirements in March
130S2 – 90T2 ≥ 45000 Work requirements in April
130S3 – 90T3 ≥ 35000 Work requirements in May
130S4 – 90T4 ≥ 50000 Work requirements in June
S1 = 350 Number of trained technicians in March
-.95S1 + S2 – T1 = 0 Compute number of trained technicians in April
-.95S2 + S3 – T2 = 0 Compute number of trained technicians in May
-.95S3 + S4 – T3 = 0 Compute number of trained technicians in June
All variables ≥ 0
Each formulation question below is independent of the others; this means that when you are answering any part, ignore the previous parts. In each part the formulation is altered. You may either write out a complete formulation for the altered problem, or better yet, just clearly state exactly what changes need to be made in the above formulation. Please present your answer using the algebraic format rather than as an EXCEL table.
NOTE: Do not use the optimal values of the decision variables to in any way guide you in your answers to the formulation problems below.
(a) 商业分析考试代考
The turnover rate for trained technicians has decreased to 4% per month. (3 points)
(b) At the start of April, up to 10 trained technicians will be released by a nearby company, and can be hired for the usual cost of $4,000 to start work immediately at the start of April. (5 points)
(c) The number of trainees trained in April and May can differ by at most 20. (4 points)
(d) Only 80% of the trainees in a given month pass the qualification test. The rest are fired at the end of the month of training. (3 points)
(e) The Human Resources Department (HRD) motivational experts feel that a trained technician who is not fully occupied with work will tend to get lazy. To combat this, they have provided life skills training courses for these idle technicians. The cost to the company for these courses is $15 for each hour of idle time. For example, if in a given month all except 2 of the trained technicians are fully occupied, and these two are idle for the whole month, then there would be 260 idle hours in the month, for a cost of $15(260). (6 points)
QUESTION 3: Sensitivity Analysis (24 points)
The Excel spreadsheet formulation and Solver output to Question 2 is provided
below:
Answer all of the questions below. For the purpose of these questions, assume that fractional values of decision variables make sense. Two decimal places accuracy is fine.
Note that each question below is to be considered independently of all others.
(a)
What is the optimal value of the objective function, and what are the optimal values of the decision variables? (3 points)
(b) If the salary/cost to train a trainee during the month of May has increased to $3000, what would be the impact on the optimal solution and its cost? Justify. (5 points)
(c) What is the allowable increase for the variables T2 and T4, the cost to train a trainee during April and June (I have deleted these number from the table)? Why? (4 points)
(d) Power Generation Inc. has underestimated staffing needs in March by 2000 hours, thus staffing needs for March should have been 42,000 hours. What would be the impact on the optimal solution? Justify. (3 points)
(e) As a result of successful sales efforts the staffing needs have increased in April to 48,000 hours. However, in June, it is expected to drop by 1000 hours resulting in a staffing needs of 49,000 hours in June. No other changes have occurred in the formulation. If you can determine what the new optimal value of the objective function is then do so, explaining why your approach is correct. If you cannot determine the new optimal value of the objective function, explain why. (6 points)
(f) Staffing needs in June have increased by 3000 to a value of 53,000. This is the only change in the formulation. Is the new optimal value of the objective function above or below $5,850,000? Defend your answer. (3 points)
QUESTION 4: Transportation Problem (19 points) 商业分析考试代考
The Hardgrave Machine Company produces computer components at its plants in Kingston, ON; Prince George, BC; and Sudbury, ON. These plants supply the demand for orders at Hardgrave’s two warehouses in Hamilton and Regina.
The tables below present the information of: (i) the production cost and capacity for each of the three plants, and the demand at each of the two warehouses
| Production Plant | Monthly Supply (units) | Production Cost per Unit ($) |
| Kingston | 15,000 | $50 |
| Prince George | 20,000 | $55 |
| Sudbury | 25,000 | $45 |
Transportation costs per unit from each plant to each warehouse are summarized in the following table:
| To:
From:
|
Hamilton Regina |
| Kingston
Prince George Sudbury |
$10 $20
$25 $30 $15 $20 |
Recent regulations require that no more than 8,000 units are to be shipped from the plant in Kingston to the Warehouse located in Hamilton. Also, at least 30% of the total computer components shipped to the warehouse in Regina are from the plant in Prince George.
Formulate algebraically the above problem to help Hardgrave satisfy the demand at the warehouses at the lowest production and transportation costs. Define the decision variables, objective function, and constraints. DO NOT SOLVE.
