{"text_format":"3","workbook":"xls","n_sheets":8,"sheets":["Summary","Crew","Offices","Sched1","Sched2","Sched3","Hirefire","Troops"],"not_rendered":{"defined_names":809},"read":"whole","date_system":"1900","formulas":"cached_only"}

{"sheet":"Summary","index":0,"n_rows":28,"n_cols":1,"rows_kept":28,"flags":["formulas_cached_only"]}
["Scheduling Examples"]
[]
["On each example worksheet, read the comments at the bottom of the sheet, then"]
["click Tools Solver... to examine the decision variables, constraints, and objective."]
["To find the optimal solution, click the Solve button."]
[]
["In this series of models we will see how the Solver can help in staff scheduling with employee "]
["preferences, assigning people to offices, and in larger-scale problems such as airline crew"]
["scheduling and employee hiring, firing and training."]
[]
["In the Crew worksheet, we look at a very simplified application where a small airline needs to"]
["schedule crews to operate various flights.  A crew 'rotation' must begin and end in the same city."]
[]
["In the Offices worksheet, we look at the common problem of assigning employees to offices,"]
["taking into account employee preferences."]
[]
["In the three Scheduling worksheets, an amusement park needs to assign employees to"]
["different work schedules to meet the demand for operation of the various rides on busy days."]
["In Sched2, we take into account employee preferences in scheduling. In Sched3, we also take"]
["into account seniority and assign greater weight to senior employees' preferences."]
[]
["In the HireFire worksheet, we consider a company that has to change the composition of its"]
["workforce towards more highly trained employees.  It can hire, fire and train employees at"]
["various costs.  Different objectives are possible, such as minimizing costs or employee turnover."]
[]
["In the Troops worksheet, an army needs to move troops from 3 different training camps to 4"]
["bases.  The army can minimize cost or time by using this transportation model.  Similar models"]
["occur in the Logistics Examples workbook."]

{"sheet":"Crew","index":1,"n_rows":74,"n_cols":6,"rows_kept":74,"flags":["formulas_cached_only"]}
["Crew Scheduling"]
["A small airline company maintains 2 daily flights between Salt Lake City, Chicago and Dallas."]
["How should the company schedule the crews to minimize cost?"]
[]
["Flight Schedule"]
["From","To","Departure","Arrival","Departure","Arrival"]
["Salt Lake City","Dallas","09:00:00","12:00:00","14:00:00","17:00:00"]
["Salt Lake City","Chicago","10:00:00","14:00:00","15:00:00","19:00:00"]
["Dallas","Salt Lake City","08:00:00","11:00:00","14:00:00","17:00:00"]
["Dallas","Chicago","09:00:00","11:00:00","15:00:00","17:00:00"]
["Chicago","Salt Lake City","08:00:00","12:00:00","14:00:00","18:00:00"]
["Chicago","Dallas","10:00:00","12:00:00","16:00:00","18:00:00"]
[]
["A crew must leave and arrive in the same city. It is possible to fly the crew back aboard another "]
["airline. This would always be on a 8:00 PM flight. There are 6 airplanes in use."]
["When a crew is actually flying a plane, the entire crew is paid $200 per hour. The other time spent "]
["(waiting between flights or flying aboard another airplane) costs the company $75 per hour."]
[""]
["Possible Crew Rotations "]
["(S=Salt Lake City, D=Dallas, C=Chicago, ( )=Back with other company)"]
[null,"Flying Hours","Other Hours","Cost","Decision"]
["SD+DS",6,2,{"v":1350,"d":"$1,350"},0]
["SD+(DS)",3,11,{"v":1425,"d":"$1,425"},0]
["SD+DC+(CS)",5,10,{"v":1750,"d":"$1,750"},0]
["SC+(CS)",4,10,{"v":1550,"d":"$1,550"},0]
["SC+CD+(DS)",6,5,{"v":1575,"d":"$1,575"},0]
["DS+SD",6,3,{"v":1425,"d":"$1,425"},0]
["DS+(SD)",3,12,{"v":1500,"d":"$1,500"},0]
["DS+SC+(CD)",7,7,{"v":1925,"d":"$1,925"},0]
["DC+CS+(SD)",6,5,{"v":1575,"d":"$1,575"},0]
["DC+CD",4,5,{"v":1175,"d":"$1,175"},0]
["CS+SD+(DC)",7,7,{"v":1925,"d":"$1,925"},0]
["CS+SC",8,3,{"v":1825,"d":"$1,825"},0]
["CD+DC",4,3,{"v":1025,"d":"$1,025"},0]
["CD+DS+(SC)",7,9,{"v":2075,"d":"$2,075"},0]
[null,null,"Total Cost",{"v":0,"d":"$0"}]
[]
["Twelve Flight Constraints"]
["Flight","Number of crews"]
["SD 1",0]
["SD 2",0]
["SC 1",0]
["SC 2",0]
["DS 1",0]
["DS 2",0]
["DC 1",0]
["DC 2",0]
["CS 1",0]
["CS 2",0]
["CD 1",0]
["CD 2",0]
[]
[]
["Problem"]
["An airline company maintains a schedule of two daily flights between Salt Lake City, Dallas and "]
["Chicago. A crew that leaves a city in the morning has to return there at night. The crew can be "]
["brought back on another airline. There are 6 airplanes in use. When a crew is flying, the cost is $200 "]
["per hour. When a crew is waiting or being flown back, the cost is $75. How should the company "]
["schedule its crews to minimize cost?"]
[]
["Solution"]
["1) The airline has already determined what all the possible crew rotations can be. The variables are "]
["the binary integer decisions to accept rotations. In worksheet Crew these are defined as "]
["Rotation_decisions."]
["2) The constraints are simple. We want only one crew per flight. This gives"]
[null,"Crews_on_flight = 1"]
["and the logical constraint gives"]
[null,"Rotation_decisions = binary"]
["3) The objective is to minimize total cost.  On worksheet Crew this cell is given the name Total_cost."]
[]
["Remarks"]
["Please confirm for yourself that the crew rotations chosen meet the required schedule.  More "]
["sphisticated versions of this model are widely used in the airline industry, but the same approach "]
["can be used in scheduling truck drivers, boat crews, etc."]

{"sheet":"Offices","index":2,"n_rows":64,"n_cols":14,"rows_kept":64,"flags":["formulas_cached_only"]}
["Office Assignment"]
["A company wants to assign 14 employees to 10 offices. Each employee has a preference for a certain office. "]
["How should the company assign employees to the offices to maximize the preference of all employees?"]
[]
["Preferences (1=first, 10=last choice)"]
[null,"Office 1","Office 2","Office 3","Office 4","Office 5","Office 6","Office 7","Office 8","Office 9","Office 10"]
["Employee 1",3,2,1,4,6,5,8,9,10,7]
["Employee 2",5,3,2,6,1,7,9,8,4,10]
["Employee 3",10,8,1,9,7,4,3,6,2,5]
["Employee 4",7,3,2,9,5,4,8,6,1,10]
["Employee 5",1,3,6,8,5,2,9,10,7,4]
["Employee 6",4,9,1,5,6,8,2,7,10,3]
["Employee 7",2,1,10,9,5,3,6,8,4,7]
["Employee 8",6,5,1,3,2,4,7,8,9,10]
["Employee 9",8,9,10,5,4,3,2,1,6,7]
["Employee 10",9,10,3,2,5,4,1,7,8,6]
["Employee 11",7,3,5,2,9,8,1,10,4,6]
["Employee 12",6,5,1,9,10,2,3,4,7,8]
["Employee 13",6,8,10,9,1,2,3,4,5,7]
["Employee 14",6,3,5,9,1,2,10,4,8,7]
[]
["Assignments","Office 1","Office 2","Office 3","Office 4","Office 5","Office 6","Office 7","Office 8","Office 9","Office 10","Total",null,"Preference"]
["Employee 1",0,0,0,0,0,0,0,0,0,0,0,null,0]
["Employee 2",0,0,0,0,0,0,0,0,0,0,0,null,0]
["Employee 3",0,0,0,0,0,0,0,0,0,0,0,null,0]
["Employee 4",0,0,0,0,0,0,0,0,0,0,0,null,0]
["Employee 5",0,0,0,0,0,0,0,0,0,0,0,null,0]
["Employee 6",0,0,0,0,0,0,0,0,0,0,0,null,0]
["Employee 7",0,0,0,0,0,0,0,0,0,0,0,null,0]
["Employee 8",0,0,0,0,0,0,0,0,0,0,0,null,0]
["Employee 9",0,0,0,0,0,0,0,0,0,0,0,null,0]
["Employee 10",0,0,0,0,0,0,0,0,0,0,0,null,0]
["Employee 11",0,0,0,0,0,0,0,0,0,0,0,null,0]
["Employee 12",0,0,0,0,0,0,0,0,0,0,0,null,0]
["Employee 13",0,0,0,0,0,0,0,0,0,0,0,null,0]
["Employee 14",0,0,0,0,0,0,0,0,0,0,0,null,0]
["Total",0,0,0,0,0,0,0,0,0,0,null,null,0]
[]
["Required",1,1,1,1,2,1,2,2,2,1]
[]
[]
["Problem"]
["A company wants to assign 14 employees to 10 offices. There are four offices that require 2 people. Each "]
["employee has given their preference.  A 1 means first choice, 2 means second choice, etc. How should the "]
["company assign the people to the offices to optimize the preferences of the employees?"]
[]
["Solution"]
["1) The variables are the assignments of the people to different offices. On worksheet Offices these are "]
["given the name Assignments."]
[" 2) There are the following logical constraints"]
[null,"Assignments = binary"]
["and the other constraints"]
[null,"Assignments_per_employee = 1"]
[null,"Total_employees = Required_employees"]
["3) The objective is to optimize the preference of the employees. That means we have to minimize the sum "]
["of the total preferences given to the assigned offices, defined on the worksheet as Total_preference."]
[]
["Remarks"]
["When everybody wants a different office, there will be no problems. If all employees prefer the same "]
["office (more likely!), the problem gets more difficult and it might be necessary to give an employee 7th or "]
["8th choice. It might be wise, in that case, to add a constraint to say that no assignment worse than 5th "]
["choice is given, for instance. This may cause the problem to be infeasible, i.e., there is no possible "]
["solution. If this happens, you will have to relax the constraint on the assignments, e.g. no worse than 6th "]
["or even 7th choice."]

{"sheet":"Sched1","index":3,"n_rows":42,"n_cols":12,"rows_kept":42,"flags":["formulas_cached_only"]}
["Personnel scheduling for an Amusement Park 1"]
["For employees working five consecutive days with two days off, find the schedule"]
["which meets demand from attendance levels while minimizing payroll costs."]
[]
[]
["Sch.","   Days off",null,"Employees",null,"Sun","Mon","Tue","Wed","Thu","Fri","Sat"]
["  A","Sunday, Monday",null,3,null,0,0,1,1,1,1,1]
["  B","Monday, Tuesday",null,5,null,1,0,0,1,1,1,1]
["  C","Tuesday, Wed.",null,6,null,1,1,0,0,1,1,1]
["  D","Wed., Thursday",null,4,null,1,1,1,0,0,1,1]
["  E","Thursday, Friday",null,6,null,1,1,1,1,0,0,1]
["  F","Friday, Saturday",null,1,null,1,1,1,1,1,0,0]
["  G","Saturday, Sunday",null,0,null,0,1,1,1,1,1,0]
[]
[null,null,"Schedule Totals:",25,null,22,17,14,15,15,18,24]
[]
[null,null,"Total Demand:",null,null,22,17,13,14,15,18,24]
[]
[null,"Pay/Employee/Day:",null,{"v":40,"d":"$40"}]
[null,"Payroll/Week:",null,{"v":5000,"d":"$5,000"}]
[]
[]
["Problem"]
["An amusement park needs a certain number of employees each day of the week."]
["Every employee must be on a schedule that gives him/her two consecutive days off. "]
["How many employees should the park hire and what schedule should they be on to "]
["minimize total payroll cost?"]
[]
["Solution"]
["1) The variables are the number of people hired for each of the 7 possible schedules. "]
["On worksheet Sched1 these are given the name Employees_per_schedule."]
["2) The logical constraints are"]
[null,"Employees_per_schedule >= 0  via the Assume Non-Negative option"]
[null,"Employees_per_schedule = integer"]
["There is also the constraint to have enough employees to operate the rides each day:"]
[null,"Employees_per_day >= Required_per_day"]
["3) The objective is to minimize payroll. This is defined on the worksheet as Payroll."]
[]
["Remarks"]
["This is an example of a simple, but classic personnel scheduling problem. Hospitals, "]
["schools, police forces, etc., can all use a model like this to optimize their personnel "]
["scheduling."]

{"sheet":"Sched2","index":4,"n_rows":75,"n_cols":21,"rows_kept":75,"flags":["formulas_cached_only"]}
["Personnel scheduling for an Amusement Park 2"]
["Maximize the preference of the employees' schedules while maintaining the schedule "]
["that minimizes payroll costs."]
[]
[]
["Sch.","   Days off",null,"Employees",null,"Sun","Mon","Tue","Wed","Thu","Fri","Sat"]
["  A","Sunday, Monday",null,3,null,0,0,1,1,1,1,1]
["  B","Monday, Tuesday",null,5,null,1,0,0,1,1,1,1]
["  C","Tuesday, Wed.",null,7,null,1,1,0,0,1,1,1]
["  D","Wed., Thursday",null,3,null,1,1,1,0,0,1,1]
["  E","Thursday, Friday",null,7,null,1,1,1,1,0,0,1]
["  F","Friday, Saturday",null,0,null,1,1,1,1,1,0,0]
["  G","Saturday, Sunday",null,0,null,0,1,1,1,1,1,0]
[]
[null,null,"Schedule Totals:",25,null,22,17,13,15,15,18,25]
[]
[null,null,"Total Demand:",null,null,22,17,13,14,15,18,24]
[]
[null,"Pay/Employee/Day:",null,{"v":40,"d":"$40"}]
[null,"Payroll/Week:",null,{"v":5000,"d":"$5,000"}]
[]
[null,null,"Preference of employees (7=first ,1=last choice)",{"$nulls":9},"Schedule assigned to"]
[{"$nulls":4},"A","B","C","D","E","F","G",null,"A","B","C","D","E","F","G","Total","Preference"]
[null,null,"Employee 1",null,4,6,5,3,2,7,1,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 2",null,3,4,5,2,1,7,6,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 3",null,6,5,2,7,4,3,1,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 4",null,4,5,3,6,2,7,1,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 5",null,5,4,2,6,7,3,1,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 6",null,1,2,3,4,5,6,7,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 7",null,7,5,3,1,6,4,2,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 8",null,4,3,2,5,6,1,7,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 9",null,5,4,3,2,7,6,1,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 10",null,1,3,2,5,6,7,4,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 11",null,6,7,2,1,3,4,5,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 12",null,5,4,6,7,3,2,1,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 13",null,1,2,3,4,5,6,7,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 14",null,4,6,7,3,2,5,1,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 15",null,4,5,7,6,3,2,1,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 16",null,4,3,2,5,6,7,1,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 17",null,6,5,7,3,2,1,4,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 18",null,7,6,5,4,3,2,1,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 19",null,6,5,3,4,2,7,1,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 20",null,4,5,3,6,7,2,1,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 21",null,7,6,5,3,4,1,2,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 22",null,7,6,2,5,3,4,1,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 23",null,6,5,7,3,4,2,1,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 24",null,5,6,4,3,1,2,7,null,0,0,0,0,0,0,0,0,0]
[null,null,"Employee 25",null,6,4,7,5,3,2,1,null,0,0,0,0,0,0,0,0,0]
[]
[{"$nulls":8},"Totals",{"$nulls":3},0,0,0,0,0,0,0,null,0]
[]
[{"$nulls":8},"Demand",{"$nulls":3},3,5,7,3,7,0,0]
[]
[]
[]
["Problem"]
["The amusement park discussed in model Sched1 has hired the necessary employees. It now needs to decide "]
["which employee goes on which schedule. Each employee has given a list with his/her preferences. A 7 means "]
["the most desired schedule, a 1means the least desired. How should the park divide the schedules among the "]
["employees?"]
[]
["Solution"]
["1) The variables are the schedules to which each employee is assigned.  On worksheet Sched2 these are given "]
["the name Schedules."]
["2) The logical constraints are"]
[null,"Schedules = binary"]
["The other constraints are"]
[null,"Schedule_per_employee =1"]
[null,"Employees_scheduled = Employees_required"]
["3) The objective is to optimize employee preference, and in this case that means maximize the sum of the "]
["preferences. This sum is defined on the worksheet as Total_preference."]
[]
["Remarks"]
["In this model we solved the problem after finding the optimal solution of  Sched1. It is possible to find both "]
["solutions automatically. You can write a VBA macro to do this. "]

{"sheet":"Sched3","index":5,"n_rows":68,"n_cols":17,"rows_kept":68,"flags":["formulas_cached_only"]}
["Personnel scheduling for an Amusement Park 3"]
["Maximize the preference of the employees' schedules, taking into account seniority,"]
["while maintaining the schedule that minimizes payroll costs."]
[]
[]
["Sch.","   Days off",null,"Employees",null,"Sun","Mon","Tue","Wed","Thu","Fri","Sat"]
["  A","Sunday, Monday",null,3,null,0,0,1,1,1,1,1]
["  B","Monday, Tuesday",null,5,null,1,0,0,1,1,1,1]
["  C","Tuesday, Wed.",null,7,null,1,1,0,0,1,1,1]
["  D","Wed., Thursday",null,3,null,1,1,1,0,0,1,1]
["  E","Thursday, Friday",null,7,null,1,1,1,1,0,0,1]
["  F","Friday, Saturday",null,0,null,1,1,1,1,1,0,0]
["  G","Saturday, Sunday",null,0,null,0,1,1,1,1,1,0]
[]
[null,null,"Schedule Totals:",25,null,22,17,13,15,15,18,25]
[]
[null,null,"Total Demand:",null,null,22,17,13,14,15,18,24]
[]
[null,"Pay/Employee/Day:",null,{"v":40,"d":"$40"}]
[null,"Payroll/Week:",null,{"v":5000,"d":"$5,000"}]
[]
[{"$nulls":3},"Preference of employees",{"$nulls":6},"Schedule assigned to"]
[null,null,"Seniority (Years)",null,"A","B","C","D","E",null,"A","B","C","D","E","Total","Preference"]
["Employee 1",null,5,null,5,3,2,4,1,null,0,0,0,0,0,0,0]
["Employee 2",null,6,null,3,4,5,2,1,null,0,0,0,0,0,0,0]
["Employee 3",null,4,null,4,3,2,1,5,null,0,0,0,0,0,0,0]
["Employee 4",null,7,null,3,4,2,1,5,null,0,0,0,0,0,0,0]
["Employee 5",null,3,null,1,3,4,2,5,null,0,0,0,0,0,0,0]
["Employee 6",null,2,null,4,5,3,1,2,null,0,0,0,0,0,0,0]
["Employee 7",null,7,null,3,4,2,5,1,null,0,0,0,0,0,0,0]
["Employee 8",null,5,null,2,4,3,1,5,null,0,0,0,0,0,0,0]
["Employee 9",null,3,null,4,3,2,5,1,null,0,0,0,0,0,0,0]
["Employee 10",null,2,null,3,4,2,5,1,null,0,0,0,0,0,0,0]
["Employee 11",null,5,null,4,2,5,1,3,null,0,0,0,0,0,0,0]
["Employee 12",null,7,null,5,3,2,4,1,null,0,0,0,0,0,0,0]
["Employee 13",null,5,null,1,2,3,4,5,null,0,0,0,0,0,0,0]
["Employee 14",null,4,null,4,2,5,3,1,null,0,0,0,0,0,0,0]
["Employee 15",null,9,null,5,4,3,1,2,null,0,0,0,0,0,0,0]
["Employee 16",null,5,null,3,5,1,4,2,null,0,0,0,0,0,0,0]
["Employee 17",null,6,null,4,3,5,2,1,null,0,0,0,0,0,0,0]
["Employee 18",null,7,null,3,4,2,5,1,null,0,0,0,0,0,0,0]
["Employee 19",null,6,null,5,4,3,2,1,null,0,0,0,0,0,0,0]
["Employee 20",null,4,null,3,5,4,2,1,null,0,0,0,0,0,0,0]
["Employee 21",null,3,null,2,5,1,3,4,null,0,0,0,0,0,0,0]
["Employee 22",null,4,null,2,5,1,3,4,null,0,0,0,0,0,0,0]
["Employee 23",null,6,null,5,2,4,3,1,null,0,0,0,0,0,0,0]
["Employee 24",null,6,null,3,1,5,2,4,null,0,0,0,0,0,0,0]
["Employee 25",null,7,null,1,4,3,5,2,null,0,0,0,0,0,0,0]
[]
[{"$nulls":8},"Totals",null,0,0,0,0,0,null,0]
[]
[{"$nulls":8},"Demand",null,3,5,7,3,7]
[]
[]
[]
["Problem"]
["We now extend the model as seen in Sched2 by adding another factor. When deciding which employee "]
["goes on which schedule, the amusement park decides to let the seniority of the employees affect the "]
["decision. The seniority is simply measured in years of employment at the park. How should the company "]
["assign the schedules to the employees?"]
[]
["Solution"]
["The solution is almost identical to the one in Sched2. The difference is that instead of just adding the "]
["preferences, we now weight them by multiplying each preference by the employee's seniority. "]
[]
["Remarks"]
["There are many ways of adjusting for seniority and other factors. By increasing and decreasing the size of "]
["the seniority factors, you can adjust the importance of the seniority to the desired level."]

{"sheet":"Hirefire","index":6,"n_rows":87,"n_cols":8,"rows_kept":87,"flags":["formulas_cached_only"]}
["Company Reorganization"]
["A company wants to reorganize its labour force. It currently has 3 different kind of employees; untrained,"]
["moderately trained and highly trained. Over the next 3 years, the company expects a necessary shift to "]
["more trained employees. How should the company reorganize to minimize cost? Or minimize the number"]
["of employees that have to be laid off?"]
[]
["Employee information"]
[null,"Cost of lay-off","Available to be hired per year","Cost of hiring","Retraining of employees",null,null,"Cost"]
["Highly Trained",{"v":700,"d":"$700"},500,{"v":250,"d":"$250"},"Untrained -> Moderately trained",null,null,{"v":400,"d":"$400"}]
["Moderately Trained",{"v":500,"d":"$500"},800,{"v":150,"d":"$150"},"Moderately trained-> Highly trained",null,null,{"v":500,"d":"$500"}]
["Untrained",{"v":350,"d":"$350"},1200,{"v":100,"d":"$100"}]
[]
["Estimated number of employees that are required."]
[null,"Current","Year 1","Year 2","Year 3"]
["Highly Trained",800,1200,1500,2000]
["Moderately Trained",1500,1500,2000,2500]
["Untrained",2000,1600,1000,0]
[]
["Number of employees that are trained, hired or laid off."]
[null,null,"Number of employees trained"]
[null,null,"Year 1","Year 2","Year 3","Cost"]
["Untrained -> Moderately trained",null,0,0,0,{"v":0,"d":"$0"}]
["Moderately trained-> Highly trained",null,0,0,0,{"v":0,"d":"$0"}]
[null,null,"Number of employees hired"]
[null,null,"Year 1","Year 2","Year 3","Cost"]
["Highly Trained",null,0,0,0,{"v":0,"d":"$0"}]
["Moderately Trained",null,0,0,0,{"v":0,"d":"$0"}]
["Untrained",null,0,0,0,{"v":0,"d":"$0"}]
[null,null,"Number of employees laid off"]
[null,null,"Year 1","Year 2","Year 3","Cost"]
["Highly Trained",null,0,0,0,{"v":0,"d":"$0"}]
["Moderately Trained",null,0,0,0,{"v":0,"d":"$0"}]
["Untrained",null,0,0,0,{"v":0,"d":"$0"}]
[]
[null,"Total number of employees laid off",null,null,0]
[]
[]
[null,null,"Number of employees working"]
[null,null,"Year 1","Year 2","Year 3"]
["Highly Trained",null,800,800,800]
["Moderately Trained",null,1500,1500,1500]
["Untrained",null,2000,2000,2000]
[null,null,"Total cost of reorganizing",null,null,{"v":0,"d":"$0"}]
[]
[]
["Problem"]
["A company has three different kinds of employees. These are highly trained, moderately trained and untrained "]
["workers. The company expects a shift towards more highly trained employees necessary over the next few "]
["years. It is possible to train people at a certain cost Laying people off also costs a certain amount. How should "]
["the company reorganize to save costs and/or have as few lay-offs as possible?"]
[]
["Solution"]
["1) The variables are the number of people that are trained, hired and laid off.  On worksheet HireFire these are "]
["given the names Trainees, Employees_hired, and Employees_laid_off."]
["2) The constraints can be divided into 2 parts."]
["First, there are the logical constraints, all of which are defined via the Assume Non-Negative option:"]
[null,"Trainees >= 0"]
[null,"Employees_hired >= 0"]
[null,"Employees_laid_off >= 0"]
["Second, we have the training, laying off and hiring constraints. These do not use defined names, but are "]
["represented on the worksheet by the following cells:"]
[null,"C22 <= B17"]
[null,"C23 <= B16"]
[null,"C26 : C28 <= C9 : C11"]
[null,"C31 : C33 <= B15 : B17"]
[null,"C40 : E42 = C15 : E17"]
[null,"D22 <= C41"]
[null,"D23 <= C42"]
[null,"D26 : D28 <= C9 : C11"]
[null,"D31 : D33 <= C40 : C42"]
[null,"E22 <= D41"]
[null,"E23 <= D42"]
[null,"E26 : E28 <= C9 : C11"]
[null,"E31 : E33 <= D40 : D42"]
["In general, these constraints reflect the movement of employees from being hired untrained to becoming "]
["moderately trained or highly trained."]
[]
["3) The main objective is to minimize cost. This is defined on the worksheet as Total_cost."]
[]
["Remarks"]
["The model as presented here will find the method of organization that has the lowest cost. It can involve large "]
["lay-offs. It is even possible that there are alternate solutions that require fewer lay-offs! To check this, you can "]
["add the constraint Total_cost = Solution, where Solution is the amount previously found by the solver. Then "]
["change the objective to minimize lay-offs. This way you are sure to find the solution that is least expensive and "]
["involves the fewest layoffs. If the number of lay-offs is still unacceptable, you could solve the original problem "]
["again and this time include a constraint like total_laid_off = 0, or * 1000. When this problem is solved you "]
["can use the sensitivity analysis report to see how much an extra lay off would cost. "]

{"sheet":"Troops","index":7,"n_rows":40,"n_cols":7,"rows_kept":40,"flags":["formulas_cached_only"]}
["Troop Movement"]
["An army wants to move troops from 3 training camps to 4 different bases. How should "]
["the troops be moved to minimize cost?"]
[]
["Moving Cost Per Man"]
[null,"Base 1","Base 2","Base 3","Base 4"]
["Camp 1",{"v":34,"d":"$34"},{"v":26,"d":"$26"},{"v":29,"d":"$29"},{"v":31,"d":"$31"}]
["Camp 2",{"v":42,"d":"$42"},{"v":33,"d":"$33"},{"v":28,"d":"$28"},{"v":35,"d":"$35"}]
["Camp 3",{"v":36,"d":"$36"},{"v":29,"d":"$29"},{"v":32,"d":"$32"},{"v":38,"d":"$38"}]
[]
["Number Of Troops Moved"]
[null,"Base 1","Base 2","Base 3","Base 4","Total","Available"]
["Camp 1",100,100,100,100,400,500]
["Camp 2",100,100,100,100,400,400]
["Camp 3",100,100,100,100,400,400]
["Total",300,300,300,300]
["Required",200,250,350,300]
["Cost",{"v":11200,"d":"$11,200"},{"v":8800,"d":"$8,800"},{"v":8900,"d":"$8,900"},{"v":10400,"d":"$10,400"},{"v":39300,"d":"$39,300"}]
[]
[]
["Problem"]
["An army wants to move troops from 3 training camps to 4 different bases. All costs of moving a "]
["soldier from any camp to any base are known. How should the army move the troops to"]
["minimize cost?"]
[]
["Solution"]
["1) The variables are the number of soldiers that are moved from each camp to each base.  On "]
["worksheet Troops these are given the name Troops_moved."]
["2) The constraints are"]
[null,"Troops_moved >= 0  via the Assume Non-Negative option"]
[null,"Troops_per_camp <= Troops_available"]
[null,"Troops_per_base = Troops_required"]
["3) The objective is to minimize the total cost. This is defined on the worksheet as Total_cost."]
[]
["Remarks"]
["This model is a transportation model, like those shown in the Logistics Examples workbook. You "]
["might wonder why there is no constraint to assure that the numbers of troops moved are integers."]
["It is a mathematical property of these types of problems that if the constants in the constraints are "]
["integers, the solution values for the variables are always integers. It is beyond the scope of these "]
["examples to further explore this."]