MSDA 628 Supply Chain Business Analytics
Module 4 Assignment
General requirement
- Make sure that each problem is on its own worksheet.
- Your Excel model must be executable. If the excel model is ran, it should generate the same results you submit. That means the spreadsheet has correct embedded formulae and Solver dialog boxes have all the inputs. If ‘Solve’ is clicked and your spreadsheet does not produce the reported results, a zero score will be assigned to that problem.
- You should use lecture examples as templates to solve the assignment problems. • Name the Excel file as “Homework 4_Last Name.xlsx”.
- Please contact the instructor if you have any questions regarding the assignment.
Problem 1
Gimli Dwarven, the manager of the Parks and Recreation Department for the Village of Durin, is considering plans for her town’s new recreation center. Her recommendations will be presented to the members of the Village Council at its regular monthly meeting next week.
Community meetings were conducted to gather public input, which was utilized by the department planners to identify desired facilities and estimate their potential usage. The table below presents information regarding construction costs, annual operating expenses, space requirements, and expected daily usage for each facility.
| Facility | Basketball
Courts, each |
Indoor
running track |
Skate
Park |
Fitness
Center |
Multipurpose fields | Multipurpose gymnasium |
| Construction Cost | $20,000 | $700,000 | $400,000 | $300,000 | $450,000 | $800,000 |
| Annual
Operating Expense |
$4,000 | $100,000 | $60,000 | $40,000 | $70,000 | $120,000 |
| Required
Space (ft2) |
3,500 | 25,000 | 15,000 | 10,000 | 18,000 | 30,000 |
| Average
Estimated Usage Residents/day |
20 | 250 | 150 | 100 | 700 | 800 |
The recreation center must include either an indoor track, a fitness center, or a skate park. Additionally, the facility will offer amenities like lockers, showers, and change rooms, serving the entire recreation center. While either a multipurpose fields or a multipurpose gymnasium
may be constructed, building both is not feasible. Furthermore, the recreation center can accommodate up to a maximum of four basketball courts.
The total amount of space available for the facilities is 50,000 square feet. Voters have approved a bond issue to provide up to $12.5 million to pay for constructing the facilities. The department’s annual operating expenses are limited to $250,000.
Use excel model to determine what facilities should be built to maximize total resident usage.
Problem 2
Quantel Devices (QD) is an electronics manufacturer that produces three products: cellphones, smartwatches, and monitors, with various profit margins. The production of each product requires three resources: microchips, assembly labor hours, and testing and quality assurance time. The variable cost, the selling price, and the resources needed to manufacture each type of electronic device are given below:
| Sales Price | Variable
Cost |
Microchips | Assembly
Hours |
Testing and quality assurance hours | |
| Cellphones | $850 | $450 | 2 | 2.5 | 1 |
| Smartwatches | $550 | $300 | 1 | 2 | 1 |
| Monitors | $950 | $550 | 3 | 3 | 1.5 |
Each week QD has 3,000 microchips, 5,000 assembly hours, and 2,500 testing hours available for production.
Each product must be assembled on its own dedicated machinery. The weekly rent for each machinery is: $22,500/week for a cellphone assembly line, $21,000/week for a smartwatch assembly line, and $16,000/week for a monitor assembly line.
In addition, considering the economy of scale, QD requires that if any type of product is produced, it must produce at a minimum level of 100.
Use excel model to determine the optimal plan that maximizes QD’s weekly profit.