DSCI 3870.

For Problem 2, enclose an Excel file with Solvers solution to the problem. Note that it is imperative that Excel file and Solver are set up so the grader can run the model with Solver and obtain a solution immediately.

Problem 2 (60 pt) A medical facility operates between 11 am and 10 pm. Their staff is made of part-time employees who work in shifts of four hours (i.e. an employee that starts at noon will finish at 4 pm). On average, the wage for these employees is $7.60 per hour. In view of the number of patients that this facility usually has to serve, management considers that they will need the following employees: 8 employees between 11 am and noon; 8 employees between noon and 1 pm; 7 employees between 1 pm and 2 pm; 1 employee between 2 pm and 3 pm; 2 employees between 3 pm and 4 pm; 1 employee between 4 pm and 5 pm; 5 employees between 5 pm and 6 pm; 10 employees between 6 pm and 7 pm; 10 employees between 7 pm and 8 pm; 6 employees between 8 pm and 9 pm; and 6 employees between 9 pm and 10 pm. Management is asking you to come up with a schedule for part time-employees (i.e. they want to know how many employees should start shifts and 11 am, noon, 1 pm, etc.) such that the total cost in wages is minimized. a) (25pt) Develop a linear programming model that helps management. As seen in class, follow the following steps: . Define decision variables. Define objective function. Define constraints. Write down the entire formulation. . . b) (25pt) Solve this problem with Microsoft Excel. What is the total payroll per day? What is the optimal schedule? c) (10pt) Comment on the desirability of scheduling at least some of the employees for three-hour shifts. (Tip: use the surplus variables for this). Problem 2 (60 pt) A medical facility operates between 11 am and 10 pm. Their staff is made of part-time employees who work in shifts of four hours (i.e. an employee that starts at noon will finish at 4 pm). On average, the wage for these employees is $7.60 per hour. In view of the number of patients that this facility usually has to serve, management considers that they will need the following employees: 8 employees between 11 am and noon; 8 employees between noon and 1 pm; 7 employees between 1 pm and 2 pm; 1 employee between 2 pm and 3 pm; 2 employees between 3 pm and 4 pm; 1 employee between 4 pm and 5 pm; 5 employees between 5 pm and 6 pm; 10 employees between 6 pm and 7 pm; 10 employees between 7 pm and 8 pm; 6 employees between 8 pm and 9 pm; and 6 employees between 9 pm and 10 pm. Management is asking you to come up with a schedule for part time-employees (i.e. they want to know how many employees should start shifts and 11 am, noon, 1 pm, etc.) such that the total cost in wages is minimized. a) (25pt) Develop a linear programming model that helps management. As seen in class, follow the following steps: . Define decision variables. Define objective function. Define constraints. Write down the entire formulation. . . b) (25pt) Solve this problem with Microsoft Excel. What is the total payroll per day? What is the optimal schedule? c) (10pt) Comment on the desirability of scheduling at least some of the employees for three-hour shifts. (Tip: use the surplus variables for this)