modeling basics: 3. excel modeling part i by peter woolf ([email protected]) university of michigan...

Modeling Basics: 3. Excel Modeling Part I

By Peter Woolf ([email protected])University of Michigan

Michigan Chemical Process Dynamics and Controls Open Textbook

version 1.0

Creative commons

Scenario

You and a friend have started a new business creating a specialized plastic. The plastic sells for $10 per unit and your inputs only cost $3 dollars per unit, so a profitable company is possible--if you can control it correctly.

• Each reaction is approximately 95% efficient• Each storage container has a maximum

capacity of 100 units• Process is semi-batch, with each reactor run

taking one day• Customer orders are variable

Process Flow Diagram

How can we model this system and develop a controller?

• Aspen: Commercial detailed modeling environment. Steep learning curve, expensive, and not widely available.

• C++, python, C, Java: Write your own code. Extremely powerful, but requires good software development experience & expertise in numerical methods


• Matlab, Mathematica, SAGE*, Octave**,R***: Powerful general purpose mathematical software. Last 3 are free, but have a steeper learning curve.

* http://www.r-project.org/** http://www.sagemath.org/*** http://www.gnu.org/software/octave/


• MS Excel, OpenOffice*: Commonly used spreadsheet tools. Relatively easy to learn, widely available, and good for small problems

* http://www.openoffice.org/

Simple model for R001:

Mass balance model for S001:

€

mB (today) = mB (yesterday) +

+ mB in (yesterday) −mB out (yesterday)€

mB out (today) = mA in (yesterday) * 0.95Here mA in (yesterday) depends on v1.

But is this true??


€


+ mB in (yesterday) −mB out (yesterday)

Total mass that S001 can hold is 100 units, and the minimum is zero, thus the following cases can’t happen

€

mB (today) = 99 +10 − 0 =109

mB (today) =10 +10 − 30 = −10

Excel solution: IF.. THENIF(conditional,true,false)

=IF(SQRT(3)<1,"A","B")

AB

=IF(1+1>2,IF(SUM(3,4)<10,"A","B"),"C")

=IF(1+1=2,"A","B")

C=IF(1+1>=2,IF(SUM(3,4)<10,"A","B"),"C") A=IF(AND(1+2=3,2*2>4), "A","B")

=IF(OR(1+2=3,2*2>4), "A","B")

B

A

IMPORTANT: This is the basis of simple models and of logical control programs


€


+ mB in (yesterday) −mB out (yesterday)

Logical statement?

mBcalc(today)= mB(yesterday)+mBin(yesterday)-mbout(yesterday)

mB(today)=IF(mBcalc(today)>100,100, IF(mBcalc(today)<0,0, mBcalc(today))

See example in plastic.factory.2.xlsConst control tab

Take home messages:

• Simple logical models using IF.. THEN.., AND(), and OR() can be used to model many systems.

• Maybe something other than constant control would work better?

Modeling Basics: 3. Excel Modeling Part II

By Peter Woolf ([email protected])University of Michigan

Michigan Chemical Process Dynamics and Controls Open Textbook

version 1.0

Creative commons

What about noise? What are sources of noise in this system?

• Conversion efficiency

• Customer demand

• Irregularities in flow

How do we model this noise?

Types of noise

• Gaussian noise: symmetric random variation with no time correlation

• Drift: time correlated random movement

• Shot noise: erratic and generally short bursts.

Example: electromagnetic interference on a sensor, splashing in a tank

Example: slow temperature changes outside, fouling, catalyst decay, some kinds of contamination

Example: Partial clogging or jamming, multiphase transients, loose wire

Types of noise

• Gaussian noise: symmetric random variation with no time correlation

• Drift: time correlated random movement

• Shot noise: erratic and generally short bursts.

=$B$2+RAND()-RAND()+RAND()-RAND()

=C2+RAND()-RAND()+RAND()-RAND()

=IF(RAND()>0.9,$D$2+4,$D$2)

=RAND()-RAND()

=RAND()-RAND()+RAND()-RAND()

=RAND()-RAND()+RAND()-RAND() ()+RAND()-RAND()

=RAND()-RAND()+RAND()-RAND()+RAND()-RAND()+ +RAND()-RAND()

Note: total of 2000 samples taken

Aside: Multiple add/subtract cycles of RAND() better approximates a Gaussian distribution (==central limit theorem)

Limit -4 to +4

Limit -1 to +1

True Gaussian:Limit -infinity to +infinity

Interpretation: Gaussian noise is generally caused by the accumulation of many small perturbations. Think thermal fluctuations as an example…

What about noise? What are sources of noise in this system?

• Conversion efficiency

• Customer demand

• Irregularities in flow

Gaussian noise?

Drift?

Shot noise?

Modeling customer orders as a random drift process

Orders(today)=orders(yesterday)+k*(RAND()-RAND())

k determines the scale of the noise from day to day. Determine from historical data.

See example in plastic.factory.2.xlscharts tab, column O

NOTE: Need to add IF.. THEN.. to eliminate the possibility of negative orders!

How can we make the process more profitable?

Control translation:How can we adjust v1,v2, and v3 in response to customer orders to maximize the overall profit?

Control translation:How can we adjust v1,v2, and v3 in response to customer orders to maximize the overall profit?

Option 1: scale requests to v1, v2, and v3 based on current order

BUT, how do we choose k1,k2, and k3?

v1(today)=k1*orders(today) v2(today)=k2*orders(today)v3(today)=k3*orders(today)

OptimizationFitting parameters to fit some objective is

commonly done to parameterize models and find good control settings

Approaches:• Gradient decent and variants• Monte Carlo• Branch and bound• Genetic algorithms+ many many others

Most common and the one we will use in Excel

OptimizationGradient method:1) Choose a point2) Calculate

gradient (slope)3) Move up slope4) Goto 1 until no

improvement

Problems:1) Can get stuck in

local maxima2) Can be unstable

1

2

34

2

*

Solutions:1) Try a few starting points to see

if you can find something near the optimum

OptimizationMonte Carlo

methods:1) Choose a point

at random2) Calculate output

(profit)3) If best, save it4) Goto 1

Problems:1) Can be

computationally slow2) Must specify variable

range to search

1

2 3 7

4

6

5

8



Use Excel Solver function (gradient method) to find k1,k2, and k3 values that maximize profit

See example in plastic.factory.2.xlsOption 1 control tab



Problem: Scaling flow by orders works but often causes the system to overflow or run the storage tanks dry..

How can we solve this?

Option 2: Set v1, v2, and v3 locally to maintain the tank levels in S001, S002, and S003

v1(today)=k1*(LC1set-LC1(today))v2(today)=k2*(LC2set-LC2(today))v3(today)=k3*(LC3set-LC3(today))

Aside: this is a proportional only control system--we will see a lot more of this later!

See example in plastic.factory.2.xlsOption 2 control tab

Effect of control on profit

Optimized based on 10 orders

Effect of control on profit

10 orders

15orders

5 orders

Optimized based on 10 orders

Take home messages:• A controlled system does not always yield

the best result• Control architecture is important• Often, highly optimized systems will perform

well only where they are designed

Better control through design??• Why not just link all of the reactors

together directly?• Is one big surge tank better than many

smaller ones?• Why not just store the material in the

reactor?

How could surge tanks be good?

• Buffer: Can store excess product for low demand times and release extra product for high demand times

• Control: Unit operations can be controlled and operated more independently

How could surge tanks be bad?

• Cost: building, housing, maintaining, and filling large tanks is expensive.

• Cost risk: if process or prices change, you may end up with lots of worthless material. Can impede nimble manufacturing

• Safety risk: in a disaster (e.g. explosion, accident, fire, or earthquake), large quantities of intermediate are not your friend!

ChallengeGiven the excel model, see if you can answer the

surge tank questions posed earlier:• Why not just link all of the reactors together

directly?• Is one big surge tank better than many smaller

ones?• Why not just store the material in the reactor?

What generalizations can you make? Which results only apply to this system and which are universally true?

modeling basics: 3. excel modeling part i by peter woolf ([email protected]) university of michigan...

Documents