I need work in excel with all labels and highlighted answer just like template i posted

6. (25 pts.) The Country Rose Truck Stop at the crossroads of Interstates 70 and 57 near Effingham, Illinois is a large full-service independent truck stop serving truckers with fuel, ample overnight parking, wireless internet access, showers, a game room with pool tables, and a 24/7 restaurant. The restaurant, one of the largest in the Midwest, serves a simple but wide menu including burgers, club and Reuben sandwiches, catfish, and \"breakfast anytime\". Scheduling wait staff in the restaurant has long been a challenge due to the hour-by-hour change in restaurant traffic. Mae Allen, the owner-manager of the truck stop's restaurant, has looked at her typical customer traffic over a 24-hour period. Reviewing traffic in one-hour increments Mae came up with the table below showing how many persons should generally be scheduled to wait tables through the day. Hou Staff r 00:0 6 0 01:0 4 0 02:0 3 0 03:0 2 0 04:0 3 0 05:0 5 0 06:0 14 0 07:0 17 0 08:0 20 0 09:0 15 0 10:0 10 0 11:00 15 Hour Staff 12:00 25 13:00 20 14:00 12 15:00 8 16:00 8 17:00 15 18:00 23 Mae could employ both full- and part-time staffers. Full-timers work 8 hour shifts and part-timers work 19:00 19 shifts of anything under 8 hours in length. For either class of wait staff shifts may begin at any time of the 20:00 14 day Mae chooses. For example, a full-time staffer waiting tables could work from midnight to 8 AM, 21:00 10 from 2:00 AM to 10:00 AM, 4:00 AM to noon, etc. Full-time wait staff are paid $9.55 per hour, with 22:00 7 every hour worked from 10:00 PM till 6:00 AM paying an additional $2.50 premium. Part-timers are 23:00 8 paid a base rate of $8.35 per hour. \"I take care of my good people. I pay better'n anybody around here for this kind of work,\" says Mae proudly. She remembers how hard it can be to support a family by waiting tables. Mae recognizes part time staff save Country Rose on wages, but she prefers full-timers. \"Them part-time people's just a pain in the neck!\" she says with exasperation. \"They don't know the job as well, and they don't show up to work, and they'll quit without tellin' you! Can you believe it? I've even had 'em come in knee-walkin' drunk and still expectin' to work! You can just count on full-timers better.\" a. Formulate and solve the mathematical programming model which will show Mae how to schedule the restaurant in the truck stop such that she meets her minimum staffing levels in each 1-hour block of time while minimizing daily wage expense. Mae would like no part-time staff on duty at any time. b. You'd like to convince Mae to use part-time staffers. She agrees to consider part-timers in her scheduling, but with two stipulations. These are 1) no more than eight part time wait staff may be in her restaurant at any given time, and 2) part-time staff can only be on the premises between 5 AM and 7 PM. These are Mae's usual working hours, so having part-timers work in this 14-hour stretch allows Mae to watch them. Following Mae's stipulations how much could be saved in daily wage expense if part-time staffers were used? Thidwick Products, LLC Nizzard Gross Requirements Scheduled Receipts On Hand Beg. Net Requirements PO Receipts PO Releases Sneetch Gross Requirements Scheduled Receipts On Hand Beg. Net Requirements PO Receipts PO Releases Wocket Gross Requirements Scheduled Receipts On Hand Beg. Net Requirements PO Receipts PO Releases Item A Gross Requirements Scheduled Receipts On Hand Beg. Net Requirements PO Receipts PO Releases Item G LT=1 26000 1000 LT=1 33000 1000 LT=1 19000 1000 LT=1 14000 5000 LT=1 Gross Requirements Scheduled Receipts On Hand Beg. Net Requirements PO Receipts PO Releases Item C Gross Requirements Scheduled Receipts 6000 3500 LT=1 -2 -1 0 1 2 0 0 0 0 0 26000 0 0 0 26000 0 0 0 26000 0 0 0 26000 0 0 0 26000 0 0 0 -2 -1 0 1 2 0 0 0 0 0 33000 0 0 0 33000 0 0 0 33000 0 0 0 33000 0 0 0 33000 0 0 0 -2 -1 0 1 2 0 0 0 0 0 19000 0 0 0 19000 0 0 0 19000 0 0 0 19000 0 0 0 19000 0 0 2000 -2 -1 0 1 2 0 0 0 0 14000 0 0 0 14000 0 0 0 14000 0 0 0 0 12000 14000 0 0 0 26000 0 0 0 -2 -1 0 1 2 0 0 0 0 6000 6000 0 0 0 6000 0 0 0 6000 0 0 0 6000 0 0 0 6000 0 0 73500 -2 -1 0 1 2 0 0 0 175000 397500 On Hand Beg. Net Requirements PO Receipts PO Releases Item B Gross Requirements Scheduled Receipts On Hand Beg. Net Requirements PO Receipts PO Releases Item D Gross Requirements Scheduled Receipts On Hand Beg. Net Requirements PO Receipts PO Releases Item E Gross Requirements Scheduled Receipts On Hand Beg. Net Requirements PO Receipts PO Releases Item F GR Doses from G GR Doses from B GR Combined fl. oz. GR Gallons GR Gallons w/ Loss GR Containers GR Pallets GR Pallets Scheduled Receipts On Hand Beg. Net Requirements PO Receipts PO Releases Fl. Oz. F for G Fl. Oz. F for B 280000 100000 LT=1 280000 0 0 0 280000 0 0 0 280000 0 0 0 -2 -1 0 1 2 0 0 0 900000 900000 1000000 0 0 500000 2400000 900000 1000000 500000 500000 2400000 1000000 1000000 1000000 1000000 0 0 0 100000 0 0 0 0 0 0 280000 0 0 300000 105000 292500 300000 800000 -2 -1 0 1 2 0 0 0 600000 1606000 1500000 1500000 1500000 1500000 0 0 0 1500000 0 900000 706000 LT=1 10000 LT=1 900000 1000000 LT=0 5 1 0.35 0.25 0 0 0 0 0 0 0 710000 710000 1700000 -2 -1 0 1 2 0 0 0 2630000 7500000 900000 900000 0 0 0 0 0 2000000 900000 1730000 2000000 8,000,000 270000 7230000 8000000 8,000,000 1 2 900000 0 0 0 -2 -1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 73500 500000 2400000 125000 625725 976.5625 4888.4765625 1054.6875 5313.561481 210.9375 1062.7122962 10.546875 53.13561481 10.546875 53.13561481 5 0 0 0 5 0 0 0 5 0 0 0 5 0.453125 5.546875 52.68248981 6 53 6 53 Size of Container (gal) Containers on Pallet Loss Percentage C Spare Part Demand 5 20 8% 175000 3 4 5 6 7 8 9 12000 10000 8000 11000 11500 13000 12000 26000 0 0 0 14000 0 0 4000 4000 4000 4000 11000 0 11000 11000 12000 0 11500 12000 13000 500 12500 13000 12000 500 11500 12000 9000 3 4 5 6 7 8 9 15000 17000 18000 20000 21500 23000 24000 33000 0 0 0 18000 0 0 17000 1000 17000 17000 20000 0 20000 20000 22000 0 21500 22000 23000 500 22500 23000 24000 500 23500 24000 25000 3 4 5 6 7 8 9 21000 24000 26500 27500 29000 30500 32000 19000 2000 2000 24000 0 24000 24000 27000 0 26500 27000 27000 500 27000 27000 29000 0 29000 29000 31000 0 30500 31000 32000 500 31500 32000 35000 3 4 5 6 7 8 9 0 54000 95000 104000 111000 108000 95000 26000 0 0 30000 26000 28000 30000 95000 2000 93000 95000 105000 2000 102000 105000 110000 3000 108000 110000 110000 2000 106000 110000 95000 4000 91000 95000 100000 3 4 5 6 7 8 9 72000 183000 201000 219000 231000 240000 255000 0 72000 73500 182000 1500 181500 182000 203000 500 200500 203000 217000 2500 216500 217000 231000 500 230500 231000 241500 500 239500 241500 255500 2000 253000 255500 252000 3 4 5 6 7 8 9 745000 823000 886000 933000 969500 1009500 993000 7500 737500 800000 800000 62500 760500 800000 900000 39500 846500 900000 900000 53500 879500 900000 1000000 20500 949000 1000000 1000000 51000 958500 1000000 1000000 41500 951500 1000000 700000 3 4 5 6 7 8 9 2400000 2700000 2700000 3000000 3000000 3000000 2100000 0 2400000 2400000 2700000 0 2700000 2700000 2700000 0 2700000 2700000 3000000 0 3000000 3000000 3000000 0 3000000 3000000 3000000 0 3000000 3000000 2100000 0 2100000 2100000 600000 3 4 5 6 7 8 9 1702000 1980000 1997000 2209000 2216000 2203000 1614000 4000 1698000 2000 1978000 2000 1995000 5000 2204000 6000 2210000 0 2203000 7000 1607000 1700000 1980000 1980000 2000000 2000000 2210000 2210000 2210000 2210000 2210000 2210000 1610000 1610000 610000 3 4 5 6 7 8 9 8640000 8700000 9630000 9630000 9630000 6930000 2430000 770000 7870000 8000000 9,000,000 130000 8570000 9000000 10,000,000 430000 9200000 10000000 9,000,000 800000 8830000 9000000 10,000,000 170000 9460000 10000000 7,000,000 540000 6390000 7000000 2,000,000 610000 1820000 2000000 2,000,000 3 4 5 6 7 8 9 182000 203000 217000 231000 241500 255500 252000 2700000 2700000 3000000 3000000 3000000 2100000 600000 738700 746050 825950 830850 834525 614425 238200 5771.09375 5828.515625 6452.734375 6491.015625 6519.7265625 4800.1953125 1860.9375 6272.9279891 6335.3430707 7013.841712 7055.4517663 7086.6593071 5217.6036005 2022.7581522 1254.5855978 1267.0686141 1402.7683424 1411.0903533 1417.3318614 1043.5207201 404.55163043 62.729279891 63.353430707 70.13841712 70.554517663 70.866593071 52.176036005 20.227581522 62.729279891 63.353430707 70.13841712 70.554517663 70.866593071 52.176036005 20.227581522 0.3175101902 0.5882302989 0.2347995924 0.0963824728 0.5418648098 0.6752717391 0.4992357337 62.411769701 62.765200408 69.903617527 70.45813519 70.324728261 51.500764266 19.728345788 63 63 70 71 71 52 20 63 63 70 71 71 52 20 10 11 12 9000 10000 10500 500 8500 9000 10000 500 9500 10000 10000 500 10000 10000 0 10 11 12 25000 27000 26000 500 24500 25000 27000 500 26500 27000 26000 500 25500 26000 0 10 11 12 35000 30000 0 500 34500 35000 30000 500 29500 30000 0 500 0 0 0 10 11 12 104000 102000 0 4000 100000 100000 105000 0 102000 105000 0 3000 0 0 0 10 11 12 252000 156000 0 2500 249500 252000 154000 2500 153500 154000 0 500 0 0 0 10 11 12 697000 205000 175000 48500 648500 700000 200000 51500 153500 200000 200000 46500 128500 200000 0 10 11 12 600000 600000 0 0 600000 600000 600000 0 600000 600000 0 0 0 0 0 10 11 12 605000 410000 0 3000 602000 8000 402000 8000 0 610000 410000 410000 0 0 0 10 11 12 1830000 0 0 180000 1650000 2000000 - 350000 0 0 - 350000 0 0 - 10 11 12 154000 600000 203900 1592.96875 1731.4877717 346.29755435 17.314877717 17.314877717 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.271654212 0.956776495 0.956776495 17.043223505 0 0 18 0 0 18 0 0 Beg Inv SS Lot Size LT Sch. Recp Nizzard Sneetch Wocket 28000 35000 21000 2000 2000 2000 1000 1000 1000 1 1 1 B takes .25 fl. oz. of F G takes .35 fl. oz. of F F comes in 5 gallon containers F comes 20 containers on a pallet C needs spare part demand A B C D E F 17000 1000000 300000 1500000 1000000 3000 0 20000 0 100000 5000 100000 100000 10000 100000 1 1 1 1 1 12K in 1 900K in 1 & 2 100K in 1, 2, 3 5 5 1 0 G 9000 3000 3500 1 9K in 1

Answer rating: 100% (QA)

Objective Minimize daily wage expenses while meeting minimum staffing levels Decision Variables Let x i xi xi be the number of fulltime staff starting at hour i i i for i 0 1 23 i 0 1 ldots 23 i0123 C... View the full answer