## Production planning & linear programming

Henko
Posts: 790
Joined: Tue Apr 09, 2013 12:23 pm
Windows
Location: Groningen, Netherlands
Flag: ### Production planning & linear programming

Some weeks ago, i posted a "linear programming" function. It is used in various bussiness areas to find optimal solutions for processes where objectives and restrictions can be expressed as linear functions of the relevant decision variables.
A practical example is production planning in a factory, where some different products are fabricated, that use the same raw materials, machines and personnel. Depending of the possible selling prices of each ptoduct type, and the actual costs of resources to fabricate them, the total profit varies which the decision how much to produce of each product within the availability limits.

In the accompanying program, such a process is coded in the form of a simple game: you are the plant manager, who is responsible for the production plan in each period. There are three products, P1, P2, and P3. On the left the selling prices for the coming period are given. In the mid section, the recepy of each product is shown: how many units of raw material (4 kinds of them) and machine groups (also 4 of them) are needed for one unit of each product. Machinehours ask for personnel.
The availability and cost of raw materials vary for each new period. Together with the fluctuating selling prices of the end products, this accounts for different optimal production quantities for each period. The margins of the products for the current period are shown at the right side.

The production quantities must be entered using the three buttons with the blue tokens. Each entry causes the spreadsheet to be recalculated. You can re-enter quantities until you are satisfied with the result, leaving each avalability of resources zero or greater than zero.
Touching the "next period" button shows the optimal quantities in th left under corner. Also shown is the optimal cumulative result, which can be compared with your results.
Touching the "Go-on" button starts of the next period.

Code: Select all

``````option base 1
set orientation 2 ! set toolbar off
graphics ! graphics clear ! draw color 0,0,0
sw=screen_width() ! sh=screen_height()
'
nprod=3 ! nraw=4 ! nmach=4
init_dim(nprod,nraw,nmach)
init_screen(nprod,nraw,nmach)
b_costprices()
'
new_round: init_round()
do
for i=1 to nprod
if b_p("sales"&i) then
psale(i)=numpad(0,1000) ! button "sales"&i text psale(i)
calc(nprod,nraw,nmach)
end if
next i
if b_p("nextp") and avail_ok() then new_round
if b_p("help") then help()
if b_p("debug") then debug pause
until b_p("stop")
set orientation 1 ! set toolbar on ! stop
end

def init_dim(np,nr,nm)
dim .pname\$(np),.rname\$(nr),.mname\$(nm)     ' names
dim .r_use(np,nr),.m_use(np,nm),.s_use(nm)  ' recepy
dim .r_tot(nr),.m_tot(nm),.s_tot(nm)        ' total used res.
dim .av_price(np),.price(np)                ' product price
dim .av_rcost(nr),.r_cost(nr),.m_cost(nm)   ' resource tariffs
dim .t_rcost(nr),.t_mcost(nm)               ' total costs
dim .psale(np)                              ' production quant.
dim .r_av_avail(nr),.r_avail(nr),.r_max(nr) ' max. raw avail.
dim .m_avail(nm),.m_max(nm)                 ' max. mach. avail.
dim .b_costprice(np),costprice(np)          ' costprice info
dim .plname\$(7),.pl(7,2),.cu(7,2)           ' profit&loss account
dim .vr(np),.slack(nr+nm+1)                 ' for LinP function
dim .opt(np)
end def

def init_round()
dim marge(.nprod)
.period+=1
if .period>1 then
for i=1 to 7
.cu(i,1)+=.pl(i,1) ! if i=1 then fac=max(1,.cu(1,1)/100)
.cu(i,2)=int(.cu(i,1)/fac)
field "pl"&i&1 text .pl(i,1) ! field "pl"&i&2 text .pl(i,2)
field "cu"&i&1 text .cu(i,1) ! field "cu"&i&2 text .cu(i,2)&" %"
next i
for i=1 to 3 ! field "opp"&i text opt(i) ! next i
field "op"&1 text .optcumprofit
field "op"&2 text optperc & " %"
button "goon" show
do slowdown ! until b_p("goon")
button "goon" hide
for i=1 to 3 ! field "opp"&i text "" ! next i
field "op"&1 text "" ! field "op"&2 text ""
for i=1 to .nprod
.psale(i)=0 ! button "sales"&i text "  "&0
next i
end if
' raw availability and prices
for j=1 to .nraw
av=.r_av_avail(j) ! ampv=.3*av
.r_max(j)=int(av+ampv*sin(.9*period+2*j)+rnd(ampv)-ampv/2)
field "ravail"&j text .r_max(j)
ap=.av_rcost(j) ! ampp=.4*ap
.r_cost(j)=int(ap+rnd(ampp)-ampp/2)
field "rcost"&j text .r_cost(j)
next j
for j=1 to .nmach
field "mavail"&j text .m_max(j)
next j
costprices()
' product prices
sum=0 ! for i=1 to .nprod ! sum+=.costprice(i) ! next i
for i=1 to .nprod
.price(i)=int(sum/3+rnd(400)+50*floor(i/3))
marge(i)=max(3+rnd(10),.price(i)-.costprice(i))
field "price"&i text .price(i)
field "margin"&i text marge(i)
next i
nr=.nraw+.nmach+1
dim rc(nr,.nprod),rm(nr)
for i=1 to .nraw ! for j=1 to .nprod
rc(i,j)=.r_use(j,i)
next j ! next i
for i=1 to .nmach ! for j=1 to .nprod
rc(.nraw+i,j)=.m_use(j,i)
next j ! next i
for i=1 to .nprod ! sum=0
for j=1 to .nmach ! sum+=.s_use(j)*.m_use(i,j) ! next j
rc(nr,i)=sum
next i
for i=1 to .nraw  ! rm(i)=.r_max(i) ! next i
for i=1 to .nmach ! rm(.nraw+i)=.m_max(i) ! next i
rm(nr)=.s_max
linP(.nprod,marge,nr,rc,rm,.vr,.slack)

for i=1 to 3
opt(i)=floor(.vr(i)) ! if opt(i)>400 then opt(i)=0
.optsales+=opt(i)*.price(i)! optprofit+=opt(i)*marge(i)
next i
.optcumprofit+=optprofit
optperc=int(100*.optcumprofit/.optsales)
calc(nprod,nraw,nmach)
end def

def avail_ok()
ok=1
for j=1 to .nraw
if val(field_text\$("ravail"&j))<0 then ok=0
next j
for j=1 to nmach
if val(field_text\$("mavail"&j))<0 then ok=0
next j
if av_staff<0 then ok=0
return ok
end def

def costprices()
for i=1 to .nprod
.costprice(i)=.b_costprice(i)
for j=1 to .nraw
.costprice(i)+=.r_use(i,j)*.r_cost(j)
next j
next i
end def

def calc(np,nr,nm)
for j=1 to nr ! s=0
for i=1 to np ! s+=.psale(i)*.r_use(i,j) ! next i
.r_tot(j)=s ! field "rtot"&j text s
next j
for j=1 to nm ! s=0
for i=1 to np ! s+=.psale(i)*.m_use(i,j) ! next i
.m_tot(j)=s ! field "mtot"&j text s
next j
.tot_s=0
for j=1 to nm
.s_tot(j)=.m_tot(j)*.s_use(j) ! .tot_s+=.s_tot(j)
field "st"&j text .s_tot(j)
next j
field "tots" text .tot_s
for j=1 to nr
.r_avail(j)=.r_max(j)-.r_tot(j)
if .r_avail(j)>=0 then red=0 else red=1
field "ravail"&j font color red,0,0
field "ravail"&j text .r_avail(j)
next j
for j=1 to nm
.m_avail(j)=.m_max(j)-.m_tot(j)
if .m_avail(j)>=0 then red=0 else red=1
field "mavail"&j font color red,0,0
field "mavail"&j text .m_avail(j)
next j
.av_staff=.s_max-.tot_s
if .av_staff>=0 then red=0 else red=1
field "savail" font color red,0,0
field "savail" text .av_staff
for j=1 to nr
.t_rcost(j)=.r_tot(j)*.r_cost(j)
field "rtcost"&j text .t_rcost(j)
next j
for j=1 to nm
.t_mcost(j)=.m_tot(j)*.m_cost(j)
field "mtcost"&j text .t_mcost(j)
next j
.t_scost=.tot_s*.s_cost
field "stcost" text .t_scost

for i=1 to 7 ! .pl(i,1)=0 ! next i
for i=1 to np ! .pl(1,1)+=.psale(i)*.price(i) ! next i
for j=1 to nr ! .pl(2,1)+=.t_rcost(j) ! next j
for j=1 to nm ! .pl(3,1)+=.t_mcost(j) ! next j
.pl(4,1)=.t_scost
.pl(5,1)=.pl(2,1)+.pl(3,1)+.pl(4,1)
.pl(7,1)=.pl(1,1)-.pl(5,1)-.pl(6,1)
fac=max(1,.pl(1,1)/100)
for i=1 to 7 ! .pl(i,2)=int(.pl(i,1)/fac)
' field "pl"&i&1 font size 12
field "pl"&i&1 text int(.pl(i,1))
field "pl"&i&2 text .pl(i,2)&" %"
if fac=1 then field "pl"&i&2 text ""
next i
end def

for i=1 to np ! read .pname\$(i) ! next i
for i=1 to nr ! read .rname\$(i) ! next i
for i=1 to nm ! read .mname\$(i) ! next i
for i=1 to np ! read .av_price(i) ! next i
for i=1 to np ! for j=1 to nr
read .r_use(i,j) ! next j ! next i
for i=1 to np ! for j=1 to nm
read .m_use(i,j) ! next j ! next i
for i=1 to nm ! read .s_use(i) ! next i
for i=1 to nr ! read .r_av_avail(i) ! next i
for i=1 to nr ! read .av_rcost(i) ! next i
for i=1 to nm ! read .m_max(i) ! next i
for i=1 to nm ! read .m_cost(i) ! next i
for i=1 to 7  ! read .plname\$(i) ! next i
data "product1","product2","product3"
data "rawmat1","rawmat2","rawmat3","rawmat4"
data "machine1","machine2","machine3","machine;"
data 900, 900, 900
data 3, 2, 4, 0
data 5, 1, 1, 4
data 1, 3, 2, 4
data 2, 0, 2, 1
data 1, 2, 1, 3
data 2, 1, 3, 1
data 1, 2, 1, 3
data 700, 520, 800, 900
data 32, 44, 28, 22
data 600,500,600,300
data 40, 20, 40, 30
data 3600, 20, 50000
data "Sales...........","  Materials.....","  Tooling......."
data "  Labour........"
end def

def init_screen(np,nr,nm)
help_page()
dw=floor((.sw-310)/9) ! st=320-dw
h1=35 ! dh=50 ! h2=100+np*dh ! period=0
.optsales=0 ! .optcumprofit=0
draw text "Raw Materials" at st+2*dw,10
draw text "Machines" at st+6*dw,10
draw text "Product" at 0,20
draw text "Price" at 100,20
draw text "Quantity" at 180,20
draw text "Margin" at 940,20
draw text "Staff" at 945,310
for j=1 to nr ! draw text "R"&j at st+j*(dw+1),40 ! next j
for j=1 to nm ! draw text "M"&j at st+(j+4)*(dw+1),40 ! next j
for i=1 to np
y=h1+i*dh
draw text "P"&i at 30,y+5 ! .price(i)=.av_price(i) ! .psale(i)=0
field "price"&i text "    "&.av_price(i) at 95,y size 70,30 RO
button "sales"&i text "  "&.psale(i) at 195,y size 70,30
field "margin"&i text "" at 940,y size 70,30 RO
next i
for i=1 to np ! for j=1 to nr
x=st-25+j*dw ! y=h1+i*dh
field "pr"&i&j text "       "&.r_use(i,j) at x,y size 70,30 RO
next j ! next i
for i=1 to np ! for j=1 to nm
x=st-20+(j+4)*dw ! y=h1+i*dh
field "pm"&i&j text "       "&.m_use(i,j) at x,y size 70,30 RO
next j ! next i
draw text "Used resources" at 95,h2+5
for j=1 to nr
x=st-25+j*dw ! y=h2
field "rtot"&j text .r_tot(j) at x,y size 70,30 RO
next j
for j=1 to nm
x=st-20+(j+4)*dw ! y=h2
field "mtot"&j text .m_tot(j) at x,y size 70,30 RO
next j
draw text "Staff per machine hr." at 15,h2+dh+5
for j=1 to nm
x=st-20+(j+4)*dw ! y=h2+dh
field "ms"&j text .s_use(j) at x,y size 70,30 RO
next j
draw text "Staff totals" at 120,h2+2*dh+5
for j=1 to nm
x=st-20+(j+4)*dw ! y=h2+2*dh
field "st"&j text .s_tot(j) at x,y size 70,30 RO
next j
field "tots" text .tot_s at 940,h2+2*dh size 70,30 RO
draw text "Availability" at 115,h2+3*dh+5
for j=1 to nr
x=st-25+j*dw ! y=h2+3*dh
field "ravail"&j text .r_av_avail(j) at x,y size 70,30 RO
next j
for j=1 to nm
x=st-20+(j+4)*dw
field "mavail"&j text .m_max(j) at x,y size 70,30 RO
next j
field "savail" text .s_max at 940,y size 70,30 RO
draw text "Cost / unit" at 125,h2+4*dh+5
for j=1 to nr
x=st-25+j*dw ! y=h2+4*dh ! .r_cost(j)=.av_rcost(j)
field "rcost"&j text .r_cost(j) at x,y size 70,30 RO
next j
for j=1 to nm
x=st-20+(j+4)*dw
field "mcost"&j text .m_cost(j) at x,y size 70,30 RO
next j
field "scost" text .s_cost at 940,y size 70,30 RO
draw text "Total costs" at 125,h2+5*dh+5
for j=1 to nr
x=st-25+j*dw ! y=h2+5*dh
field "rtcost"&j text .t_rcost(j) at x,y size 70,30 RO
next j
for j=1 to nm
x=st-20+(j+4)*dw
field "mtcost"&j text .t_mcost(j) at x,y size 70,30 RO
next j
field "stcost" text .t_scost at 940,y size 70,30 RO
button "nextp" text "Next period" at 30,574 size 160,40
button "help" text "Help" at 30,645 size 160,40
button "stop" text "Stop" at 30,715 size 160,40
' button "debug" text "Debug" at 945,650 size 70,50
button "goon" text "Go on" at 945,630 size 70,50
button "goon" hide
for i=1 to 7 ! read ypl(i) ! next i
data 575,605,630,655,685,710,740
for i=1 to 7 ! draw text .plname\$(i) at 223,ypl(i) ! next i
draw text "this period" at 448,ypl(1)-25
for i=1 to 7 ! for j=1 to 2
field "pl"&i&j text "" at 430+(j-1)*90,ypl(i) size 80,20 RO
field "pl"&i&j font size 15
next j ! next i
draw text "cumulative" at 645,ypl(1)-25
for i=1 to 7 ! for j=1 to 2
field "cu"&i&j text "" at 620+(j-1)*90,ypl(i) size 80,20 RO
field "cu"&i&j font size 15
next j ! next i
draw text "optimal" at 840,ypl(1)-25
for i=1 to 3
draw text "P"&i at 815,545+50*i
field "opp"&i text "" at 853,540+50*i size 70,30 RO
next i
for j=1 to 2
field "op"&j text "" at 803+(j-1)*90,ypl(7) size 80,20 RO
next j
draw size 3
draw line 420,680 to 600,680 ! draw line 420,735 to 600,735
draw line 610,680 to 790,680 ! draw line 610,735 to 790,735
draw size 1
b_costprices()
calc(np,nr,nm)
end def

def b_costprices()
for i=1 to .nprod ! s=0
for j=1 to .nraw
s+=.m_use(i,j)*(.m_cost(j)+.s_use(j)*.s_cost)
next j
.b_costprice(i)=int(s)
next i
end def

'
' produce a simple keypad to quickly enter a number in an app
' upon entry, the keypad disappears
' initialize once, multiple use after
' left upper corner is placed at "xtop,ytop"
' "bs" is the button size (keypad becomes 4.3 times larger)
' size of number is accepted between "minval" and "maxval"
' if both "minval" and "maxval" are zero, then no restrictions
' max number of tokens in the number is 10 (minus and dot included)
' works for option base 0 and 1
'
page name\$ set
page name\$ frame xtop,ytop,0,0
set buttons custom
if bs<20 then bs=20
sp=4 ! th=.5*bs+4 ! ww=4*bs+5*sp ! hh=th+4*bs+6*sp
fsize=.5*bs
draw font size fsize ! set buttons font size fsize
draw color 1,1,1 ! fill color .7,.7,.7
button "rec" title "" at 0,0 size ww,hh
button "res" title "" at 0,0 size ww,th+4
fill color R,G,B ! fill alpha alpha
button "0" title "0" at sp,th+3*bs+5*sp size bs,bs
for k=1 to 9
x=(k-1)%3 ! y=2-floor((k-1)/3)
button k title k at (x+1)*sp+x*bs,th+y*bs+(y+2)*sp size bs,bs
next k
button "-" title "-" at 2*sp+bs,th+3*bs+5*sp size bs,bs
button "." title "." at 3*sp+2*bs,th+3*bs+5*sp size bs,bs
button "Cl" title "C" at 4*sp+3*bs,th+2*sp size bs,bs
button "del" title "<-" at 4*sp+3*bs,th+bs+3*sp size bs,bs
button "ok" title "ok" at 4*sp+3*bs,th+2*bs+4*sp size bs,2*bs+sp
page name\$ hide
page name\$ frame xtop,ytop,ww,hh ! page "" set
set buttons default
draw font size 20 ! set buttons font size 20 ! draw color 0,0,0
end def

a\$="" ! pflag=0 ! sflag=0 ! ob=1-option_base()
nump1:
if b_p("ok") then
number=val(a\$) ! a\$="" ! button "res" text ""
if minval<>0 or maxval<>0 then
if number<minval or number>maxval then
button "res" text "range error"
pflag=0 ! a\$="" ! pause 1
button "res" text ""
goto nump1
end if
end if
page "numpad" hide ! page "" set
return number
end if
if b_p("Cl") then
a\$ = "" ! pflag=0 ! sflag=0 ! goto nump3
end if
if b_p("del") and len(a\$) then
ll=len(a\$) ! if substr\$(a\$,ll-ob,ll-ob)="." then pflag=0
a\$ = left\$(a\$,ll-1) ! sflag=0 ! goto nump3
end if
if b_p("-") then
a\$ = "-" ! pflag=0 ! sflag=0 ! goto nump3
end if
if b_p(".") and not pflag and not sflag then
a\$ &= "." ! pflag=1 ! goto nump3
end if
for k=0 to 9
t\$=k
if b_p(t\$) and not sflag then
a\$ &= t\$ ! goto nump3
end if
next k
goto nump1
nump3:
if len(a\$)>10 then ! sflag=1 ! goto nump1 ! end if
button "res" text a\$
goto nump1
end def

def help_page()
xs=100 ! ys=50 ! w=.sw-2*xs ! h=.sh-2*ys-180
h\$="This is a simulation program with which the production planning of a little plant can be simulated. "&chr\$(10)&"For each priod, the production quantities of three different product may be entered. "&chr\$(10)&"However, the amount of raw material and the needed machine capacity and labour are all limited. "&chr\$(10)&"Given the selling price of each of the products and the cost of the resources, one must try to reach an optimum production program with maximum profit. "&chr\$(10)&"Each period, the selling price and the availability of raw materials and its cost will change. "&chr\$(10)&"The program will keep track of the optimum quantities using the simplex algorithm and keep track of the cumulative results. "&chr\$(10)&"One can try different quantities with the entry buttons in the left upper corner (blue numbers). When a satisfactory result is reached, press the ""next period"" button. The current planning is not accepted if any of the resources has a negative availability. "&chr\$(10)&"At the bottom of the screen, a profit/loss account is shown, based upon the current entries. "&chr\$(10)&"Calculation takes place immediatly upon each individual entry. "&chr\$(10)&"In the auxil.(liary) fields in the right upper corner, the actual profit margin is shown for each of the three products."
n\$="help" ! page n\$ set ! page n\$ frame xs,ys,w,h
field "hh" text h\$ at 0,0 size w,h ML RO
field "hh" font color 0,0,1 ! field "hh" font size 20
button "h_stop" text "Close" at w/2-30,h-40 size 60,25
page n\$ hide
page "" set
end def

def help()
n\$="help" ! page n\$ set ! page n\$ alpha 0 ! page n\$ show
for i=0 to 1 step 0.01 ! page n\$ alpha i ! pause 0.01 ! next i
do slowdown ! until b_p("h_stop")
for i=1 to 0 step -0.01 ! page n\$ alpha i ! pause 0.01 ! next i
page n\$ hide ! page "" set
end def

def b_p(a\$) = button_pressed(a\$)

' Lineair Programming function
' simple version: only "<=" constraints
' nv = # of variables
' v_coef() = coefficients in the objective function
' nres = # of contraints
' r_coef(,) = coefficients in the constraint relations
' r_max() = right hand side (max. values) of the constraints
' v_ result() = the resulting optimal variable values
' slack() = final value of the slack varables ("leftovers")
' the function returns the value of the objective function
'
def linP(nv,v_coef(),nres,r_coef(,),r_max(),v_result(),slack())
nr=nres+1 ! nc=nv+nres+1
dim w(nr,nc)
'******   initialize the simplex tableau
for j=1 to nv
w(1,j)=-v_coef(j)
for i=1 to nres ! w(i+1,j)=r_coef(i,j) ! next i
next j
for i=1 to nr
if i=1 then w(i,nc)=0 else w(i,nc)=r_max(i-1)
for j=1 to nres
if i=j+1 then w(i,nv+j)=1 else w(i,nv+j)=0
next j
next i
'******   process the tableau
do
'****   find pivot column, if any (if not, we're done)
m=0
for j=1 to nc-1
if w(1,j)<m then ! pc=j ! m=w(1,j) ! end if
next j
if m=0 then break
'****   find pivot row
m=1e10
for i=2 to nr
fac=w(i,pc) ! if fac=0 then continue
aux=w(i,nc)/w(i,pc)
if aux>0 and aux<m then ! m=aux ! pr=i ! end if
next i
'****   optimalization step
fac=w(pr,pc)
for j=1 to nc ! w(pr,j)/=fac ! next j
for i=1 to nr
if not i=pr then
fac=w(i,pc)
for j=1 to nc ! w(i,j)-=fac*w(pr,j) ! next j
end if
next i
until forever
'******   extract the results from the tableau
profit=w(1,nc)
for j=1 to nv ! v_result(j)=0
for i=2 to nr ! if w(i,j)=1 then v_result(j)=w(i,nc) ! next i
next j
for j=1 to nres ! slack(j)=0
for i=2 to nr ! if w(i,nv+j)=1 then slack(j)=w(i,nc) ! next i
next j
return profit
end def
`````` IMG_1469.PNG (1.2 MiB) Viewed 5538 times