Please answer the following three questions utilizing Excels Solver add in funct
ID: 3130453 • Letter: P
Question
Please answer the following three questions utilizing Excels Solver add in function.
8-3
The famous Y.S Chang Rstaurant is open 24
hours a day. Waiters and busboys report for duty
at 3 am 7am 11am 3pm 7pm 11pm. And each works
an 8 hour shift. The following table shows the minimum
number of workers need during the six periods into which
the day is divided. Changs scheduling problem is to determine
how many waiters and busboys should report for work
at the start of each time to minimize the total staff required
for one days operation.
Period
Time
Number of Employees Req.
1
3a-7a
3
2
7a-11a
12
3
11a-3p
16
4
3p-7p
9
5
7p-11p
11
6
11p-3a
4
8-4
The table on this page shows these minimum requirements units of each
ingredient per pound of feed mix and costs for the three mixes.
In addition the stable owner is aware that an overfed horse is a
sluggish worker. Consequently he determines that 6 pounds of feed per
day are the most that any horse needs to function properly. Formulate
this problem and solve for the optimal daily mix of the three feeds.
Diet Requirment
Oat Product
Enrihced Grain
Mineral Product
Minimum Daily Requirment
A
2
3
1
6
B
0.5
1
0.5
2
C
3
5
6
9
D
1
1.5
2
8
E
0.5
0.5
1.5
5
Cost/LB
0.09
0.14
0.17
8-6
Eddie Kelly is running for reelection as mayor of a
small town in Alabama. Jesica Martinez Kelly's campaign
manager during this election is planning the markeing
campaign and there is some stiff competition. Martinez
has selected four ways to advertise televeision ads radio ads
bllboards and newspaper ads. The costs of these the audience
reached by each type of ad and the maximum number
available is shown in the following table.
Type of AD
Cost Per AD
Audience Reached AD
Maximum Number
TV
800
30000
10
Radio
400
22000
10
Billboards
500
24000
10
Newspapers
100
8000
10
In addition Martinez has decided that there should
be a least six ads on TV or radio or some combination
of those two. The amount spent on billboards and newspapers
together must not exceed the amount spend on TV ads.
While fundrasing is still continuing the monthly budget for
advertising has been set a 15000. How many ads of each type should
be placed to maximize the total number of the people rached.
11p-3a
4
Please answer the following three questions utilizing Excels Solver add in function.
8-3
The famous Y.S Chang Rstaurant is open 24
hours a day. Waiters and busboys report for duty
at 3 am 7am 11am 3pm 7pm 11pm. And each works
an 8 hour shift. The following table shows the minimum
number of workers need during the six periods into which
the day is divided. Changs scheduling problem is to determine
how many waiters and busboys should report for work
at the start of each time to minimize the total staff required
for one days operation.
Period
Time
Number of Employees Req.
1
3a-7a
3
2
7a-11a
12
3
11a-3p
16
4
3p-7p
9
5
7p-11p
11
6
11p-3a
4
8-4
The table on this page shows these minimum requirements units of each
ingredient per pound of feed mix and costs for the three mixes.
In addition the stable owner is aware that an overfed horse is a
sluggish worker. Consequently he determines that 6 pounds of feed per
day are the most that any horse needs to function properly. Formulate
this problem and solve for the optimal daily mix of the three feeds.
Diet Requirment
Oat Product
Enrihced Grain
Mineral Product
Minimum Daily Requirment
A
2
3
1
6
B
0.5
1
0.5
2
C
3
5
6
9
D
1
1.5
2
8
E
0.5
0.5
1.5
5
Cost/LB
0.09
0.14
0.17
8-6
Eddie Kelly is running for reelection as mayor of a
small town in Alabama. Jesica Martinez Kelly's campaign
manager during this election is planning the markeing
campaign and there is some stiff competition. Martinez
has selected four ways to advertise televeision ads radio ads
bllboards and newspaper ads. The costs of these the audience
reached by each type of ad and the maximum number
available is shown in the following table.
Type of AD
Cost Per AD
Audience Reached AD
Maximum Number
TV
800
30000
10
Radio
400
22000
10
Billboards
500
24000
10
Newspapers
100
8000
10
In addition Martinez has decided that there should
be a least six ads on TV or radio or some combination
of those two. The amount spent on billboards and newspapers
together must not exceed the amount spend on TV ads.
While fundrasing is still continuing the monthly budget for
advertising has been set a 15000. How many ads of each type should
be placed to maximize the total number of the people rached.
11p-3a
4
Explanation / Answer
Since everything had to be done in excel, I will write the answer here and at the end provide the link to download the original excel file on which the work is done.
8-3
The optimal number of employee require is 30.
8-4
The optimal ratio of Oat Product : Enriched grain : Mineral Product is 2 : 0 : 3
8-6
The optimal number of ads would be,
TV - 7
Radio - 10
Billboards - 9
Newspapers - 9
Link for excel file.
https://www.dropbox.com/s/1g866ntv31nsmse/Chapter%208.xlsx?dl=0