Frontline Systems, Excel Solver, optimization software, Solver Excel, simulation software
Solver.com
From Frontline Systems, developers of the Excel Solver.

Solver tutorials

Learn to use optimization for resource allocation, and Monte Carlo simulation for risk analysis of your models.


 

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            
  Cost of lay-off Available to be hired per year Cost of hiring Retraining of employees Cost
Highly Trained $700 500 $250 Untrained -> Moderately trained $400
Moderately Trained $500 800 $150 Moderately trained-> Highly trained $500
Untrained $350 1200 $100        
Estimated number of employees that are required.
  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.  
  Number of employees trained  
  Year 1 Year 2 Year 3 Cost
Untrained -> Moderately trained 0 0 0 $0
Moderately trained-> Highly trained 0 0 0 $0
  Number of employees hired  
  Year 1 Year 2 Year 3 Cost
Highly Trained 0 0 0 $0
Moderately Trained 0 0 0 $0
Untrained 0 0 0 $0
  Number of employees laid off  
  Year 1 Year 2 Year 3 Cost
Highly Trained 0 0 0 $0
Moderately Trained 0 0 0 $0
Untrained 0 0 0 $0
   
  Total number of employees laid off 0  
           
    Number of employees working  
  Year 1 Year 2 Year 3  
Highly Trained 800 800 800  
Moderately Trained 1500 1500 1500  
Untrained 2000 2000 2000  
    Total cost of reorganizing $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:  
  Trainees >= 0          
  Employees_hired >= 0          
  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:        
  C22 <= B17            
  C23 <= B16            
  C26 : C28 <= C9 : C11          
  C31 : C33 <= B15 : B17          
  C40 : E42 = C15 : E17          
  D22 <= C41          
  D23 <= C42          
  D26 : D28 <= C9 : C11          
  D31 : D33 <= C40 : C42          
  E22 <= D41          
  E23 <= D42          
  E26 : E28 <= C9 : C11          
  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.    

To Learn More:

For instant access to our white papers, example models, full-text User Guides, and to download free 15-day trial versions of our software products whenever you're ready, register now with no obligation.
User type
Email address
Name
First Last
Company
University
Phone

Trial version passwords are sent to the above email address. Our Privacy Policy protects you.