Answer & Explanation:On the attached file you will find 4 questions . Please provide answers on excel fileYou will need to use solver to answers to these questions.Solver can be found on this website .http://www.cengage.com/cgi-wadsworth/course_products_wp.pl?fid=M20b&product_isbn_issn=9781305250901&token
hw_for_s.docx
Unformatted Attachment Preview
1. During each four-hour period, the Smalltown police force requires the following
number of on-duty police officers: eight from midnight to 4am; seven from 4am
to 8am; six from 8am to noon; six from noon to 4pm; five from 4pm to 8pm; and
four from 8pm to midnight. Each police officer works two consecutive four hour
shifts.
a. Determine how to minimize the number of police officers needed to meet Smalltown’s
daily requirements.
b. Use SolverTable to see how the number of police officers needed from midnight to
4am changes.
2. The Grand Prix Car Company manufactures cars in three plants and then ships
them to four regions of the country. The plants can supply the amounts listed in
the right-most column of the table below. The customer demands by region are
listed in the bottom row and the unit costs of shipping a car from each plant to
each region are listed in the middle of the table. Grand Prix wants to find the
lowest-cost shipping plan for meeting the demands of the four regions without
exceeding the capacities of the plant.
Region 1
Region 2
Region 3
Region 4
Capacity
Plant 1
131
218
266
120
450
Plant 2
250
116
263
278
600
Plant 3
178
132
122
180
500
Demand
450
200
300
300
3. Using Solver determine how many people should be surveyed in each age group
and in border/non-border states.
For a particular client Market Research Associates must design a survey that
meets the following requirements:
a. Survey at least 2300 people in US
b. Survey at least 1000 people <= 30 years
c. Survey at least 600 people >=31 and <=50.
d. At least 15% of those surveyed live in a state that borders Mexico
e. At least 50% of those surveyed who are 30 years or younger live in a state
that does not border Mexico.
f. Ensure that no more than 20% of those surveyed who are 51 years or over
live in a state that borders Mexico.
Cost data
Cost per Person Surveyed
Region
Age <=30
Age 31-50
Age >=51
State Bordering Mexico
$7.50
$6.80
$5.50
State Not Bordering Mexico
$6.90
$7.25
$6.10
4. . Using Solver determine how many units of GM3A and GM3B should be
produced in each month.
•
There is no beginning inventory.
Demand:
Model
GM3A
GM3B
January
800
1000
February
700
1200
March
1000
1400
April
1100
1400
Production Costs and Other Information:
Model
GM3A
GM3B
Unit Cost
Jan-Feb
$10
$6
Unit Cost
Mar-Apr
$11
$6.60
Holding
Cost/unit
$0.18
$0.13
Desired Ending
Inventory*
450
300
Labor
Hours/Unit
1.3
0.9
* in April.
•
•
•
•
Maximum inventory limited to 3,300 units because of warehouse space.
2,240 labor regular hours available each month and must be used
320 maximum additional labor hours available each month if needed
Holding Cost: calculated on number of units in inventory at end of a period.
Determine a production schedule (how much to make of each item in each period) that
minimizes total production and inventory costs.
Note:
For all problems, clearly define the decision variables. Write out the objective and the
constraints in terms of the decision variables. Label all constraints to tell me what they
are doing.
Do all work in Excel. Do each problem on a separate tab. Label the tabs.
…
Purchase answer to see full
attachment
Order a plagiarism free paper now. We do not use AI. Use the code SAVE15 to get a 15% Discount
Looking for help with your ASSIGNMENT? Our paper writing service can help you achieve higher grades and meet your deadlines.
Why order from us
We offer plagiarism-free content
We don’t use AI
Confidentiality is guaranteed
We guarantee A+ quality
We offer unlimited revisions