Page 387 - Computer Software Application TP - Volume 1
P. 387
COMPUTER SOFTWARE APPLICATION - CITS
Related Exercise:
Question1 : Employee Scheduling
You are a manager responsible for scheduling employees for a customer service department. You want to
minimize the total number of hours scheduled while ensuring adequate coverage during peak hours.
Shift Monday Tuesday Wednesday Thursday Friday
9am - 5pm 4 3 4 5 4
1pm - 9pm 5 4 3 4 5
5pm - 1am 3 4 3 4 3
Question: How should you schedule employees for each shift to minimize total hours scheduled while
meeting staffing requirements for each day?
Using Solver:
1 For each example, set up the objective function to maximize or minimize.
2 Define the decision variables and constraints based on the problem.
3 Access the Solver tool in Excel, specify the objective cell, decision variables, and constraints.
4 Run Solver to find the optimal solution.
c Scenarios on data
Excel scenarios allow you to create and save different sets of values that can be quickly substituted into your
worksheet
TASK 1:
1 Create Dataset with Proper Parameters
Suppose, that we are going to sell a book and would like to know how the Sale Units, Price per Unit, and
Variable Cost per Unit can affect the final profits. The profit is dependent on Sale Units (Cell B2), Price per Unit
(Cell B3), and the Variable Cost per Unit (Cell B5). Therefore, type the below formula in cell B7.
= B3*B4-B5-B6*B3
372
CITS : IT & ITES - Computer Software Application - Exercise 65