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.


 

Blending Problem 2 (Multi-period)
What rock quarries should be used and how much should they produce to meet a certain
quality of limestone (calcium and magnesium content) and minimize cost? There are 4 quarries with
different qualities, capacity and cost to operate. A different output and quality is required each year.
Due to environmental restrictions, only 3 quarries are allowed to be open each year.  
Information on rock quarries      
  Calcium contents (relative to required quality) Magnesium contents (relative to required quality) Maximum production per year (tons) Cost to keep quarry open per year ($Million)  
Quarry 1 1 2.3 2000 3.5  
Quarry 2 0.7 1.6 2500 4  
Quarry 3 1.5 1.2 1300 4  
Quarry 4 0.7 4.1 3000 2  
Quarries to be used (1=yes, 0=no)      
  Year 1 Year 2 Year 3 Year 4 Year 5
Quarry 1 0 0 0 0 0
Quarry 2 0 0 0 0 0
Quarry 3 0 0 0 0 0
Quarry 4 0 0 0 0 0
Total 0 0 0 0 0
Amounts to produce (tons).        
  Year 1 Year 2 Year 3 Year 4 Year 5
Quarry 1 0 0 0 0 0
Quarry 2 0 0 0 0 0
Quarry 3 0 0 0 0 0
Quarry 4 0 0 0 0 0
Total 0 0 0 0 0
Required 4500 3100 3500 3700 4000
Amounts that can be produced (tons)      
  Year 1 Year 2 Year 3 Year 4 Year 5
Quarry 1 0 0 0 0 0
Quarry 2 0 0 0 0 0
Quarry 3 0 0 0 0 0
Quarry 4 0 0 0 0 0
Calcium restrictions        
  Year 1 Year 2 Year 3 Year 4 Year 5
Total Amount of Calcium 0 0 0 0 0
Total Amount Required 0 0 0 0 0
Calcium Required per Ton (Minimum) 0.9 1.2 1 1.1 0.8
Magnesium restrictions        
  Year 1 Year 2 Year 3 Year 4 Year 5
Total Amount of Magnesium 0 0 0 0 0
Total Amount Required 0 0 0 0 0
Magnesium Required per Ton (Minimum) 1.9 1.7 2.8 1.9 2.1
Cost Year 1 Year 2 Year 3 Year 4 Year 5 Total
  $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
Problem            
A company owns four rock quarries from which it can extract limestone with different qualities. Two
qualities are important, the relative amount of calcium and magnesium in the stone. The company
must produce a certain total amount of limestone, with certain qualities, each year. There is a large
fixed cost to keep a quarry operating for extraction purposes each year. Which quarries should be
used each year, and how much limestone should each one produce each year?  
             
Solution            
The solution is very similar in structure to the one found in worksheet Blend1.  
1) The variables are 0-1 or binary integer variables which determine whether each quarry is open,
and amounts of limestone to be extracted from each quarry. These variables occur in each year.
In worksheet Blend2, these variables the names Quarry_decisions and Amounts_produced.
2) First, there are the logical constraints. These are      
  Amounts_produced >= 0 via the Assume Non-Negative option  
  Quarry_decisions = binary        
Second, there are contraints on the total production and the amount that can be produced at each
quarry. These constraints are:          
  Total_produced >= Total_required      
  Amounts_produced <= Maximum_Production    
The right hand side of the second constraint depends on the binary integer variables.  
Third, there are constraints on the quality (calcium and magnesium content) of the limestone:
  Calcium_production >= Calcium_requirement    
  Magnesium_production >= Magnesium_requirement    
Both the left-hand and right-hand sides of these constraints depend on the Amounts_produced
decision variables.            
Fourth, there is a constraint that limits the number of quarries that can be open each year:  
  Number_of_open_quarries <= 3      
3) The objective is to minimize the cost of operating the quarries. This is defined on the worksheet as
Total_cost.            
             
Remarks            
See the comments on worksheet Blend1 about the characteristics of blending problems, which also
apply here.            

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.