Question

How do I use Excel Solver to solve this problem? I need a step by step process please. The famous YS Chang restaurant is open 24 hours a day. Waiters and busboys report for duty at 3am, 7am, 11am, 3pm, 7pm, or 11pm, and each works an 8 hour shift. The following table shows the minimum number of workers needed during the six periods into which the day is divided. Chang's scheduling problem is to determine how many waiters and busboys should report for work at the start of each time period to minimize the total staff required for one day's operation.(Let Xi equal number of waiters and busboys beginning work in time period i, where i = 1,2,3,4,5,6.) Period Time Number of waiters and busboys required 1 3a-7a 3 2 7a-11a 12 3 11a-3p 16 4 3p-7p 9 5 7p-11p 11 6 11p-3a 4

Answer 1

Formulation in Excel

Solution

	3 AM	7 AM	11 AM	3 PM	7 PM	11 PM
Report at -->	3	14	2	7	4	0	30		Requirement
3 AM - 7 AM	1					1	3	>=	3
7 AM - 11 AM	1	1					17	>=	12
11 AM - 3 PM		1	1				16	>=	16
3 PM - 7 PM			1	1			9	>=	9
7 PM - 11 PM				1	1		11	>=	11
11 PM - 3 AM					1	1	4	>=	4

So, the total number of employees required is 30 and the reporting schedule is shown above (bold rows).