Business Analytics – ADM 2302 Fall 2020
Midterm Exam October 2020
Time: 2.5 hours or 150 minutes
Business Analytics代考 Acme sells two sprocket assemblies for escalators and elevators. Each type of sprocket must first be machined and then assembled.
Instructions: Business Analytics代考
1-Write in your last and first name, your Student ID number, and your section in the spacesabove, and sign the Statement of Academic Integrity on page 2. Please check if the student did sign it
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 finishthis 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 limiton 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. Business Analytics代考
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 academicfraud.
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 student Business Analytics代考
I 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:
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)LegalHours 6 HRI + 4 GM £ 4,800 hrs
2)DataMgt Hours 1 HRI + 2 GM £ 2,000 hrs Business Analytics代考
3)PolicyClaims 3 HRI £ 1,800 hrs
4)Ratio Policy Limits 5 HRI – 0.5 GM ³0
5)Non-negativity HRI, GM ³0
a)Graphthe 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 thesolution 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? (3points)
d)Will there be excess capacity in the Data Management resource? (3points).
Yes, they will be an excess capacity in the Data management resource. Because there is a slack = 2000
– (600 – 2(300)) = 2000-1200 = 800 hours. Or saying there is 800 hours of excess capacity. Or indicating that the left hand side is not equal to the right hand of the constraint. Or saying the constraint is non- binding.
QUESTION 2: Linear Programming Sensitivity Analysis (22 points) Business Analytics代考
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.
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 lettersA 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 wonderingif they should take this opportunity to purchase more Provide your answer and JUSTIFY it. (3 points) Business Analytics代考
(d)The profit of Dijon dressing has been found overly estimated. The company however has difficulty determininghow much lower the real profit should 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. (5points)
(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. (5points)
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 Sales | Unit Price ($) | ||
| Machining | Assembly | |||
| Sprocket 1
Sprocket 2 |
2
4 |
2
1 |
15
20 |
50
60 |
| Capacity (hrs/wk)
Cost |
90
$ 5/hr |
50
$ 4/hr |
||
Let x1 and x2 represent the number of units of sprockets 1 and 2 to produce, respectively.
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 sprocket3 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: Max Sales Sp. 2:
Max Sales Sp. 3: |
x1 |
x2 |
x3 |
≤ 15
≤ 20 ≤ _10 |
|
| Machine Time: | 2×1 | + | 4×2 | _+ 3×3 | ≤ 90 |
| Assembly Time: | 2×1 | + | x2 | + 3×3 | ≤ 50 |
| Non-negativity: | x1, | x2, | x3 | ≥ 0 |
Complete the formulation above by filling in the four blanks (8 points).
(b)InDecember, 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 theLP 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) Business Analytics代考
(d)InFebruary, 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).
Y = number of extra machine hours to produce.
New objective function: 32×1 + 36×2 + 34×3
[1 is the additional cost per hour over that already charged in LP]
New Machine Time constraint: 2×1 + 4×2 + 3×3 ≤ 90 Business Analytics代考
New constraint: y ≤ 10
Non-negativity: y ≥ 0
(e)Formulatethe 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 |
Excel Spreadsheet (5 points)
2 points: For writing description (Refer to column A and Row 6).
It is ok if they put X1 and X2 in Row 6 instead of Sprocket 1 and Sprocket 2.
(f)Would it be worthwhile to increase the maximum sales level on Sprocket 2 OR to increase the maximum Machine capacity? Justify. (2points).
(g)ACME is offered $7 per hour for use of their assembly time by an outside contractor. Howmuch assembly time is it worthwhile to sell? (2 points).
(h)If a machine breakdown reduced machining capacity by 20 hours, how would profits beaffected?(2 points).
QUESTION 4: Linear Programming Formulation (20 points) Business Analytics代考
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.
更多代写：racket程序代写 GRE代考 英语作业代写 英文简历修改 计算机论文翻译 计算机论文代写
合作平台：essay代写 论文代写 写手招聘 英国留学生代写
