Business Analytics – ADM 2302 Fall 2020
Midterm Exam October 2020
商业分析期中代考 ACME is offered $7 per hour for use of their assembly time by an outside contractor. How much assembly time is it worthwhile to sell?
Time: 2.5 hours or 150 minutes
Last Name: __________ First Name: __________
Student Identification Number: _______ Section: _________
Instructions:
1-Write in your last and first name, your Student ID number, and your section in the spaces above, and sign the Statement of Academic Integrity on page 2.
2-The Midterm exam is an open book exam. However, make sure to prepare as if it is a closed book exam and have a sheet of summary notes ready for quick reference, in order to finish this exam within 2.5 hours.
3-The Midterm exam is to be submitted electronically as a Word Document via However, it is acceptable to plot GRAPHS by hand and to SCAN/INCLUDE them within the Word document file as long as they are large, legible, and properly labeled and that their calculations are typed within the rest of the exam document. Note that there is a 1GB limit on submissions. Therefore, upload the image photos of the graphs with a medium/small resolution. If your file size and graphs photos are more than 1 GB, you can save your word document as a PDF file and upload it. 商业分析期中代考
4-Read each question very carefully: only provide what is asked. You will be required to build a model in Excel to obtain the solutions for one of the questions.
5-You must NOT reproduce or communicate the contents of the Midterm exam in any way. The Midterm exam is an individual effort. You must complete it individually (i.e. alone).Failing to do so is considered academic fraud.
6-I trust that you will act according to the above instructions.
7-Keep smiling.
Questions | 1 | 2 | 3 | 4 | Total |
Points |
|||||
Total Value | 25 | 22 | 33 | 20 | 100 |
Statement of Academic Integrity 商业分析期中代考The Telfer School of Management does not condone academic fraud, an act by a student that may result in a false academic evaluation of that student or of another student. Without limiting the generality of this definition, academic fraud occurs when a student commits any of the following offences: plagiarism or cheating of any kind, use of books, notes, mathematical tables, dictionaries or other study aid unless previously authorized in writing. By submitting my answers electronically, I declare that I have read the text on academic integrity and I pledge not to have committed or attempted to commit academic fraud in this examination. I also declare that I took this Midterm Exam by myself without the assistance of another individual and that I was not aware of the content of this Midterm Exam and I have never discussed it previously with other students or colleagues. Statement to be signed by the studentI have read the text on academic integrity and I pledge not to have committed or attempted to commit academic fraud in this examination. Signed: ___________________ (type your name or insert your electronic signature). Note: An examination without this signed statement will not be graded and will receive a final exam grade of zero. |
QUESTION 1: Graphical Method (25 points) 商业分析期中代考
The RBC (Royal Bank of Canada) uses online banking to market two new banking products. The first product is a home risk insurance that allows buyers to default for up to 6 months on their mortgage payments. The second is a guaranteed mortgage fund that buyers may purchase to leverage their funds without increasing their debt loads. The RBC expects to make profit contributions of $20 per unit on the home risk insurance instrument, and $8 per unit on the guaranteed mortgage fund. The bank has a policy that at least 50% of total sales of the two products are home risk insurance instruments. The bank is now determining sales quotas for its online offerings to maximize total expected contribution to profits based on the product resource requirements, as follows:
Resource Requirements per Product Offering (Hours per Unit)
Bank
Department |
Home Risk Insurance (HRI) | Guaranteed Mortgage (GM) | Resource Availability (Hours) |
Legal | 6 | 4 | 4,800 |
Data Management | 1 | 2 | 2,000 |
Policy Claims | 3 | 0 | 1,800 |
A correct formulation for this problem is provided below:
Let HRI and GM denote the number of units of Home Risk Insurance instruments and Guaranteed Mortgage units to sell online, respectively. 商业分析期中代考
MAX Z = 20 HRI + 8 GM ($)
subject to,
1) Legal Hours 6 HRI + 4 GM 4,800 hrs
2) Data Mgt Hours 1 HRI + 2 GM 2,000 hrs 商业分析期中代考
3) Policy Claims 3 HRI 1,800 hrs
4) Ratio Policy Limits 0.5 HRI – 0.5 GM 0
5) Non-negativity HRI, GM 0
a)Graph the constraint lines and mark them clearly with the numbers (1), (2), (3) and (4) to indicate which line corresponds to which constraint. Darken the feasible region. (12 points)
(b) Determine the optimal solution that will maximize the total expected contribution to profits.
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. (7 points).
(c) Which constraint(s) is (are) redundant? (3 points)
(d) Will there be excess capacity in the Data Management resource? Justify. (3 points).
QUESTION 2: Linear Programming Sensitivity Analysis (22 points) 商业分析期中代考
A salad dressing supplier to Ottawa area restaurants has been using Linear Programming (LP) for years to determine how much dressing they should produce for every season. In particular, they specialize in producing three kinds of dressing: Dijon, Classic Vinaigrette, and Roasted Garlic. All these dressings require some use of olive oil to produce. Taking this into account, the management team has formulated the following LP model that determines the optimal amount they should produce for each dressing.
X : the amount of Dijon
Y : the amount of Classic Vinaigrette
G : the amount of Roasted Garlic
Maximize Z = 1.2X+1.6Y+1.4G (total profit) 商业分析期中代考
subject to
(Olive oil) 6X+5Y+3G <=300 liters
(Labour) 9X+4Y+5G <=280 minutes
(Machine) 2X+8Y+4G <=320 minutes
X,Y,G>=0
The model has been solved using Excel and the following sensitivity report was generated.
Variable Cells | |||||||
Final | Reduced | Objective | Allowable | Allowable | |||
Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
$B$10 | Dijon | 0 | -0.8 | 1.2 | 0.8 | 1E+30 | |
$C$10 | Classic Vinaigrette | 20 | 0 | 1.6 | 0.738461538 | 0.48 | |
$D$10 | Roasted Garlic | 40 | 0 | 1.4 | 0.6 | 0.3 | |
Constraints | |||||||
Final | Shadow | Constraint | Allowable | Allowable | |||
Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
$F$13 | Olive Oil Usage | 220 | 0 | 300 | 1E+30 | A | |
$F$14 | Labour Usage | 280 | 0.2 | 280 | 120 | 120 | |
$F$15 | Machine Usage | B | 0.1 | 320 | 147.6923077 | 96 |
Answer all the questions below ACCORDING TO the above sensitivity report.
(a) What is the optimal profit? (2 points)
(b) Two numbers have been removed from the resource sensitivity table by your professor (the letters A and B appear instead of the numbers). What are the correct values of A and B? Justify. (4 points)
(c) The supplier learned recently that the price of Olive oil has been dropping significantly, and is wondering if they should take this opportunity to purchase more oils. Provide your answer and JUSTIFY it. (3 points)
(d) The profit of Dijon dressing has been found overly estimated. The company however has difficulty determining how much lower the real profit should be. Does this impact the LP optimal solutions? Justify your answer. (3 points)
(e) Some workers are not happy with their salaries and ask for a raise; otherwise, they will quit and this would reduce the available labour minutes from 280 to 240. Should the company consider the raise? If so, what will be a reasonable amount to pay (in total) to these workers in addition to their original salaries? Justify your answer. (5 points)
(f) What if the profit of ALL three dressings (i.e. Dijon, Classic Vinaigrette, and Roasted Garlic) is now $1.5/unit. Do the optimal values of the decision variables change? What will be the impact on the profit? Justify. (5 points)
QUESTION 3: Linear Programming Formulation (33 points) 商业分析期中代考
Acme sells two sprocket assemblies for escalators and elevators. Each type of sprocket must first be machined and then assembled. Unit machining and assembly times, capacity limitations, demand restrictions and revenue-cost data are as follows:
Required | Hrs/unit | Max | Unit | |
Machining | Assembly | Sales | Price ($) | |
Sprocket 1 | 2 | 2 | 15 | 50 |
Sprocket 2 | 4 | 1 | 20 | 60 |
Capacity (hrs/wk) | 90 | 50 | ||
Cost | $ 5/hr | $ 4/hr |
Let x1 and x2 represent the number of units of sprockets 1 and 2 to produce, respectively.
MAX Z = 32×1 + 36×2
Subject to
Max Sales Sp. 1: x1 £ 15
Max Sales Sp. 2: x2 £ 20
Machine Time: 2×1 + 4×2 £ 90
Assembly Time: 2×1 + x2 £ 50
Non-negativity: x1, x2 ³ 0
(a)In November, Acme introduces a third type of sprocket (Sprocket 3).
To produce one sprocket 3 requires 3 hours of machining and three hours of assembly. The maximum sales of sprocket 3 is 10 units. The selling price of a sprocket 3 is $61. The above formulation can be modified to take account of the new product. The new formulation is:
Let x1, x2 and x3 represent the number of units of sprockets 1, 2 and 3 to produce, respectively.
MAX Z = 32×1 + 36×2 _____________
Subject to
Max Sales Sp. 1: x1 £ 15
Max Sales Sp. 2: x2 £ 20
Max Sales Sp. 3: x3 £ _________
Machine Time: 2×1 + 4×2 ________ £ 90
Assembly Time: 2×1 + x2 ________ £ 50
Non-negativity: x1, x2, x3 ³ 0
Complete the formulation above by filling in the four blanks (8 points).
(b) In December, the problem turns out to be the same as the November problem given in part (a), except that a new packaging process has resulted in an additional requirement that at least 2 sprocket 1’s must be produced for every sprocket 2 that is produced. Either include below the LP formulation of this new problem, or clearly state how the formulation in part (a) could be changed to deal with the December situation. (2 points)
(c) In January, the problem turns out to be the same as the November problem given in part (a), except that the selling price per unit for sprockets 1 and 2 have each decreased by 10%. Either included below the LP formulation of this new problem, or clearly state how the formulation in part (a) could be changed to deal with the January situation. (3 points)
(d) In February, the problem turns out to be the same as the November problem given in part (a), except that it is now (in February) possible to purchase up to 10 additional hours of machine time at a price of $6 per hour. The $6 per hour cost applies to only the additional 10 hours. Either include below the LP formulation of this new problem, or clearly state how the formulation in part (a) could be changed to deal with the February situation. (6 points) 商业分析期中代考
Hint: Add a new decision variable (Y: the number of extra machine hours to produce).
(e) Formulate the original linear programming problem on a spreadsheet and SOLVE using Excel Solver (Provide the corresponding “Excel Spreadsheet” and the “Sensitivity Report”). Include “managerial statements” that communicate the results of the analysis. (i.e. describe verbally the results).
(8 points)
Let x1 and x2 represent the number of units of sprockets 1 and 2 to produce, respectively.
MAX Z = 32×1 + 36×2
Subject to
Max Sales Sp. 1: x1 £ 15
Max Sales Sp. 2: x2 £ 20
Machine Time: 2×1 + 4×2 £ 90
Assembly Time: 2×1 + x2 £ 50
Non-negativity: x1, x2 ³ 0
(f) Would it be worthwhile to increase the maximum sales level on Sprocket 2 OR to increase the maximum Machine capacity? Justify. (2 points).
(g) ACME is offered $7 per hour for use of their assembly time by an outside contractor. How much assembly time is it worthwhile to sell? (2 points).
(h) If a machine breakdown reduced machining capacity by 20 hours, how would profits be affected? (2 points)
QUESTION 4: Linear Programming Formulation (20 points) 商业分析期中代考
Niteton Power and Light Company (NPLC) wants to develop an efficient work schedule for its full- and part-time customer service clerks. The number of clerks needed to provide adequate service during each hour the office is open on a weekday is given below:
Hour 8-9AM 9-10 10-11 11-12PM 12-1 1-2 2-3 3-4
Clerks 5 4 6 8 10 9 7 4
A full time clerk works 3 hours, has a 1-hour break, and then works another 3 hours. Part-time clerks work for 4 consecutive hours. Full-timers get paid for their break. All clerks start work on the hour.
NPLC’s office manager insists that at least one full time clerk be on duty during all open hours and that at least 40% of the clerks should be full-time clerks on payroll.
A full-time clerk costs NPLC $20 per hour, and a part-timer costs $15 per hour.
Formulate a linear programming model that will provide a schedule that will meet NPLC’s customer service needs at a minimum labor cost. (Define the decision variables, objective function, and constraints). DO NOT SOLVE.
Hint on the decision variables: there are 2 different full-time shifts and 5 different part-time shifts.
更多代写：sql代写 Gmat代考 英国Economics经济学代写 雪城大学essay代写 音乐论文代写 代写Python作业价格