Homework Set 1
1. Enrollment Management. An engineering department chair wants to maximize the use of scholarship dollars to shape the enrollment of the incoming freshmen. All applicants take an engineering aptitude test and receive a scholarship based on their score. Here is the scholarship award amount:
|
Test Score
|
80
|
85
|
90
|
95
|
100
|
|
Scholarship Amount
|
$2,290
|
$2,590
|
$3,250
|
$3,880
|
$4,150
|
The department chair wants to
- award scholarships to 100 students
- with at least 5 scholarships given to students in each test score
- and a total of $350,000 spent on the 100 scholarships.
What is the maximum average test score the freshmen class can have?
2. Make or Buy. A sudden increase in the demand for smoke detectors has left Acme Alarms with insufficient capacity to meet demand. The company has seen monthly demand from its retailers for its AC and battery-operated detectors rise to 20,000 and 10,000 respectively. Acme's production process involves three departments: fabrication, assembly, and shipping. The relevant quantitative data on production and prices are summarized as follows:
|
Monthly Hours Available
|
Hours/Unit (AC)
|
Hours/Unit (Battery)
|
|
|
Fabrication
|
2,800
|
0.1
|
0.14
|
|
|
Assembly
|
3,000
|
0.11
|
0.15
|
|
|
Shipping
|
2,300
|
0.1
|
0.13
|
|
|
|
|
|
|
|
In House
|
Subcontracted
|
|
|
AC
|
Battery
|
AC
|
Battery
|
|
Variable cost/unit
|
$19.90
|
$17.50
|
$21.30
|
$21.30
|
|
Retail Price
|
$31.90
|
$28.10
|
$31.90
|
$28.10
|
The company also has the option to obtain additional units from a subcontractor, who has offered to supply up to 20,000 units per month in any combination of outlet and battery-operated models at the charge shown in the chart. For this price, the subcontractor will test and ship its models directly to the retailers without using Acme's production process. What is the maximum profit? How many of each type should they fabricate themselves, and how many should they buy from the subcontractor? (fractional decisions are acceptable)?
NOTE: I want these answers in excel sheet using solver of linear programming and description of steps in excel sheet.
Attachment:- 1577527_1_Tools-Homework-2016-su-1.docx