## 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

Example
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.

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)``

Constraints
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``

Featured Resources :

Hydrocarbon Processing the premier magazine providing job-help information to technical and management personnel in petroleum refining, gas processing, petrochemical/chemical and engineer/constructor companies throughout the world – since 1922.

### Excel Solver

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

`` =SUMPRODUCT(E22:G22,E21:G21)``

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.

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````

Example
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.

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)``

Constraints
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.

```` 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

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.

Example
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)````

Featured Resources :

Serves chemical engineering professionals in the chemical process industry including manufacturing, engineering, government, academia, financial institutions and others allied to the field serving the global chemical process industry.

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

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.

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.

Featured Resources :

Serves chemical engineering professionals in the chemical process industry including manufacturing, engineering, government, academia, financial institutions and others allied to the field serving the global chemical process industry.

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 = DP.ρV.Vt/μV`
` 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.ρV(ρL - ρ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.14 (ρL - ρ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.14 (ρL - ρ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.DP(ρL - ρ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.

Example
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