Category: Others

Linear Programming in Excel

Linear Programming in Excel

Linear Programming is a widely used mathematical technique designed to help managers and engineers in planning and decision making relative to resource allocation. Linear programming (LP) model essentially consists of 3 components.

  • Decision Variables
  • Objective function
  • Constraints

This article shows how to develop LP model in Excel.

Multipurpose plant is used to manufacture three products A, B and C from raw materials Y and Z. Amount of raw materials required, batch times and profits per batch are shown as below. Determine number of batches to be processed every week for each product to maximize the profit. Plant is operating for 150 hours per week. Raw material available per week is Y : 216 units and Z : 200 units.

Linear Programming example multipurpose plant

Decision Variables
Number of batches for each product XA , XB and XC

Objective Function
Maximize profit for production.

 Maximize ( 30 XA + 8 XB + 15 XC)

Total time available for production.

 5 XA + 10 XB + 20 XC <= 150

Raw material Y available for production.

 18 XA + 12 XB + 5 XC <= 216

Raw material Z available for production.

 20 XA + 5 XB + 10 XC <= 200

Excel Solver

Excel LP modeling multipurpose plant

Identify Cells E21,F21,G21 which hold values of decision variables. Calculate objective function in cell E23 as


Identify Cells H26,H27,H28 to hold values of constraints.

 Cell H26  =SUMPRODUCT(E26:G26,$E$21:$G$21) 
 Cell H27  =SUMPRODUCT(E27:G27,$E$21:$G$21)
 Cell H28  =SUMPRODUCT(E28:G28,$E$21:$G$21)

Click “Solver” in Data ribbon (Excel 2010) and fill data as in below screenshot.
Excel LP modeling multipurpose plant

Add constraints by clicking “Add” Button. Number of batches needs to be integer, click on add constraint and select cell E21, F21, G21 and select “Int” to make these variable integer. After adding constraints click Solve to get results.

 XA = 8
 XB = 4
 XC = 2
 Profit = 302

A refinery has four type of crude oils available that have the yields shown in the following table. Because of maximum demand, production of gasoline, heating oil, jet fuel and lube oil must be limited as shown in the table. Find the optimum weekly requirement of crude oils to maximize the refinery profit.

Linear Programming example refinery scheduling

Profit from crude oil 1 is obtained by adding value of products formed and subtracting crude and operating cost.

 Crude 1 Profit = 45(0.6) + 30(0.2) + 15(0.1) - (15 + 5) = 14.5 k$

Similarly profits of 8.0, 4.5, 2.0, 8.5 k$ for crude options 2,3,4,5.

Decision Variables
Weekly crude oil requirement X1, X2, X3, X4 and X5

Objective Function
Maximize refinery profit.

 Maximize ( 14.5 X1 + 8 X2 + 4.5 X3 + 2 X4 + 8.5 X5)

Limits on production of gasoline, heating oil, jet fuel and lube oil.

 0.6 X1 + 0.5 X2 + 0.3 X3 + 0.4 X4 + 0.5 X5 <= 170
 0.2 X1 + 0.2 X2 + 0.3 X3 + 0.3 X4 + 0.1 X5 <= 85
 0.1 X1 + 0.2 X2 + 0.3 X3 + 0.2 X4 + 0.2 X5 <= 85
 0.2 X5 <= 20

Limits on availability of crude oils.

 X1 <= 100
 X2 <= 100
 X3 <= 100
 X4 + X5 <= 200

Define the problem in excel solver and get following results.
LP modelling example refinery scheduling

Excel Solver example refinery scheduling

 X1 = 100
 X2 = 100
 X3 = 66.67
 X4 = 0
 X5 = 100
 Profit = 3400 k$/wk

Spreadsheet for above examples on linear programming

Sizing Expansion Tank for Hot Oil System

Sizing Expansion Tank for Hot Oil System

Expansion tank is provided in hot oil system to accommodate for the increased thermic fluid volume due to thermal expansion. It also helps in removal of moisture and non-condensables during start-up. This article will show how to do sizing of an expansion tank.

Sizing Expansion tank for hot oil system

Size an horizontal expansion tank for hot oil system using Therminol VP-1 as thermic fluid. Fluid is heated from 30 °C to 350 °C. System hot oil volume is 7.87 m³. L/D to be used as 2.5. Thermic fluid density is 1056 Kg/m³ at 30 °C and 761 Kg/m³ at 350 °C.

A minimum volume (10% ~ 20%) is considered in expansion tank at start-up in cold conditions. As heating is started volume expands and tank sizing should be such that expanded volume fills (70% ~ 80%) of tank volume.

Consider vessel volume to be V. Initial volume of hot oil at cold conditions is as following.

 Vcold = 10% of V + VSys (in m³)
 Mcold = ( 0.1 V + 7.87 ) ρcold (in Kg)

Volume on expansion is determined by dividing by density at hot conditions.

 Vhot = ( 0.1 V + 7.87 ) ρcold/ ρhot (in m³)

Expansion in volume is obtained as

 Vexpansion = ( 0.1 V + 7.87 )( ρcold/ ρhot - 1 )

Liquid level in expansion tank increases and fills upto 70% of the volume.

 Vexpansion = 0.7 V - 0.1 V

Solving above equations for V provides capacity of expansion tank.

 V = 5.44 m³

Volume for 2:1 elliptical horizontal tank is provided by.

 V = πD²L/4 + πD³/12

With L/D = 2.5, solving above equation provides.

 D = 1.35 m
 L = 3.37 m

Spreadsheet for sizing expansion tank for hot oil system

Gravity Settling Separation

Gravity Settling Separation

This article illustrates determination of terminal velocity required for gravity separation. Performing a force balance on liquid droplet settling out of gas stream results in following relationship.

force balance on liquid droplet

where FB is force due to Buoyancy, FD is force due to Drag and FG is force due to gravity.

 FG = m.g
 FB = m.ρV.g/ρL
 FD = CD.(ρV.Vt²/2).AP

Assuming spherical liquid droplet with diameter DP, above equation gets reduced to

 FD = FG - FB
 FD = CD.(ρV.Vt²/2).(π.DP²/4)
 FG - FB = m.g(ρL - ρV)/ρL
 m = 4/3.(π.DP³/8).ρL

Balancing above equation gives following relation for terminal velocity Vt.

 Vt = [(4gDP/ 3CD).(ρL - ρV)/ρV ]0.5

Drag coefficient depends on shape of particle and Reynold’s number and can be determined from below graph.

Drag coefficient versus Reynold's number

Above curve can be simplified into 3 sections resulting into 3 settling laws.

Stoke’s Law

At low Reynold’s number < 2, a linear relationship exists between CD and Re.

 CD = 24/Re
 Re = DPV.VtV
 Vt = g.DP².(ρL - ρV)/18μV

In English units, with DP in feet, Vt in feet/sec, ρ in lb/ft³, g = 32.2 feet/sec² and μ in centipoise, above equation can be expressed as.

 Vt = 1488.g.DP².(ρL - ρV)/18μV

Criterion K is defined to determine the flow regime as following.

 K = DP[g.ρVL - ρV)/μV²]1/3

Value of K is evaluated for Reynold’s number 2 based on Stoke’s Law.

 Re = 2
 Vt = 2.μV/(ρV.DP)
 K = (36)1/3
 K = 3.3

For K < 3.3 Stoke’s Law is applicable.

Intermediate Law

For 2 < Re < 500, Intermediate law applies.

 CD = 18.5/Re0.6
 Vt = 0.153 g 0.71 DP 1.14L - ρV)0.71/ ( ρV0.29. μV0.43)

In English units, with viscosity in centipoise, above equation can be expressed as

 Vt = 3.49 g 0.71 DP 1.14L - ρV)0.71/ ( ρV0.29. μV0.43)

Newton’s Law

Newton’s law is applicable for Reynold’s number in range of 500 to 200,000.

 CD = 0.44
 Vt = 1.74 (g.DPL - ρV)/ ρV)0.5

Value of K is evaluated for Reynold’s number 500 based on Newton’s Law.

 Re = 500
 K = (500/1.74)2/3
 K = 43.5

For K in the range of 3.3 to 43.5, Intermediate Law is applicable; for K > 43.5, Newton’s Law is applied.

Stoke’s Law is applicable for vapor separation from a continuous liquid phase and dispersed liquid separation from a continuous liquid phase. Intermediate Law is typically used for liquid separation from a continuous vapor phase.

Calculate diameter for a vertical vapor liquid separator based on gravity separation. Gas flowrate is 50,000 lb/h, density is 3.7 lb/ft³ and viscosity is 0.01 cP. Liquid flowrate is 12,000 lb/h, density is 51.5 lb/ft³ and viscosity is 0.42 cP. Assume liquid particle diameter DPL as 250 micron and vapor particle diameter DPV as 150 micron.

Terminal velocity is to be calculated for gravity settling of liquid droplet from vapor phase. K Value is calculated to determine the applicable flow regime.

 K = 41.1

Intermediate Law is applicable for this case. Terminal velocity is calculated based on Intermediate Law.

 Vt = 0.96 feet/sec

Margin of 75% is taken on terminal velocity and vessel diameter is calculated.

 D1 = 2.58 feet

A second terminal velocity is calculated for disentraining vapor from liquid phase. Typically Stoke’s law is applicable for vapor disentrainment. Terminal velocity is calculated based on Stoke’s law.

 Vt = 0.07 feet/sec

With margin of 75% on terminal velocity, vessel diameter is calculated.

 D2 = 1.22 feet

Comparing both diameter values, highest value of 2.58 feet is selected as a minimum diameter required for gravity separation of vapor and liquid.

Spreadsheet for Determining diameter of Vertical Vapor Liquid Separator based on Gravity Separation