UBC Theses and Dissertations

UBC Theses Logo

UBC Theses and Dissertations

Computer aided cost estimation of steel structures a case study of operational approach Shing, W.Y. Albert 1986

Your browser doesn't seem to have a PDF viewer, please download the PDF to view this item.

Item Metadata

Download

Media
831-UBC_1986_A7 S56.pdf [ 4.09MB ]
Metadata
JSON: 831-1.0062932.json
JSON-LD: 831-1.0062932-ld.json
RDF/XML (Pretty): 831-1.0062932-rdf.xml
RDF/JSON: 831-1.0062932-rdf.json
Turtle: 831-1.0062932-turtle.txt
N-Triples: 831-1.0062932-rdf-ntriples.txt
Original Record: 831-1.0062932-source.json
Full Text
831-1.0062932-fulltext.txt
Citation
831-1.0062932.ris

Full Text

COMPUTER AIDED COST ESTIMATION OF STEEL  STRUCTURES  A CASE STUDY OF OPERATIONAL APPROACH  by W.Y.  ALBERT SHING  B . A . S c , The U n i v e r s i t y  of B r i t i s h Columbia,  1984  A THESIS SUBMITTED IN PARTIAL FULFILMENT OF THE REQUIREMENTS FOR THE DEGREE OF MASTER OF APPLIED SCIENCE  in  THE FACULTY OF GRADUATE STUDIES (Dept. o f C i v i l  Engineering)  We accept t h i s t h e s i s as conforming to the required  standard  THE UNIVERSITY OF BRITISH COLUMBIA July ©  W.Y.  1986  ALBERT SHING, 1986  In presenting  this  thesis  in  requirements  for an advanced  partial degree at  B r i t i s h Columbia, I agree that the freely  available  for reference  purposes may  the  Library  and  study.  that permission f o r e x t e n s i v e copying scholarly  fulfilment  of  be granted by the  Engineering Department or by h i s or her  of  the  University shall  make  of it  I f u r t h e r agree this  thesis  Head of the  representatives.  for Civil It  i s understood that copying or p u b l i c a t i o n of t h i s t h e s i s f o r f i n a n c i a l gain  shall  not  be  allowed  permission.  The U n i v e r s i t y of B r i t i s h 2075 Wesbrook Place Vancouver, Canada V6T 1W5  Date: J u l y  1986  Columbia  without  my  written  ABSTRACT An of  arrangement  British  C o l u m b i a and  Fabricators, estimation  of  to  of  research process  steel  is of  to  to  Steel  the  this  and  data  the  given  files  Basic program  runs  is  estimating  hour  and  the  cost  the  allow  the  variables;  2.  enter  inputs  take-offs;  3.  compute  4.  calculate  estimating  has  labour cost,  p r o g r a m has  the  following developed  investigated by  to  be  their  efficiency. access  the  between  updrated  program  users  The  of  the  reduced the  that overall  written system.  in The  to:  weights;  material cost  reported  i i  is  computer  l a b o u r h o u r s and m a t e r i a l  Fabricators  by  proven  and f r e e  MAI-Four  l a b o u r hour  Steel  method  study.  store  from  a  required  a higher  experience  1.  Coast  the  p r o g r a m was  firm  this  estimating  used  made  Fabricators  estimating  under  the  cost  cost  of  The u n i q u e a r r a n g e m e n t  achieved  menu-driven to  the  objective  variables  Steel  computer  variables  r e s e a r c h has  Steel  throughout  and  of  current  hour  industrial  hands-on  The c o m p u t e r  The  the  program.  process  was  topic  ,Coast  a p p r o a c h and a method was  Coast  estimating author  This  labour  estimating  successful.  the  firm  University  F a b r i c a t o r s and i n v e s t i g a t e  computer  the  study  industrial  labour  operational  generate  computer  the  A  up between t h e  structures.  program.  contributions:  an  set  computerize  Coast  estimating  been  investigate  generating  using  has  and  and t o t a l  the  computer  estimating  cost. cost time  by  30%. The  each  labour  hour  operation.  computer  As  v a r i a b l e s a r e d e f i n e d as the time  for  these  the  variables  are  vital  e s t i m a t i n g program, a method has been d e v e l o p e d  o b t a i n t h e s e v a r i a b l e s by c o l l e c t i n g s h o p d a t a a t level  to  through  an  i n f o r m a t i o n system.  (MLR)  An o p t i m u n  using a spreadsheet  s i z e o f 200  a monthly  average.  the users  to:  The  global  The  g l o b a l shop  of  Multiple  Linear  program (Lotus  1-2-3).  w i l l t h e n be a n a l y z e d by t h e t e c h n i q u e Regression  a  o b s e r v a t i o n s was  data  recommended to  give  program i s a l s o menu-driven to  allow  1.  convert g l o b a l shop data  2.  sort the piece f i l e  3.  p e r f o r m MLR  a n a l y s i s on t h e c o m p o n e n t f i l e t o o b t a i n  labour hour  v a r i a b l e s ; and  into piece  i n t o component  4.  p l o t the standard e r r o r of each  The  labour hour  time  studies  variables will in  p l a n t and w i l l  to  files; files; the  labour hour v a r i a b l e .  serve as a powerful  tool  for  c a s e o f t h e s t a r t - u p o f a new f a b r i c a t i o n  provide  feedback  to  the  cost  estimation  p r o g r a m o f an o n g o i n g f a b r i c a t o r . Since the a p p l i c a t i o n s are w r i t t e n spreadsheet for  environments,  in  the  p r o g r a m s c a n be e a s i l y  u s e by a number o f s t e e l f a b r i c a t o r s .  Basic  and  customized  TABLE OF CONTENTS  ABSTRACT LIST OF FIGURES ACKNOWLEDGEMENTS  i i vi viii  1.  INTRODUCTION  1  2.  COMPUTER ESTIMATING PROGRAM  3  3.  LABOUR HOUR VARIABLES  12  3.1  Introduction  12  3.2  Current Shop P r a c t i c e and G l o b a l Shop Data  12  3.3  C o l l e c t i n g G l o b a l Shop Data, Approach  #1  14  3.4  C o l l e c t i n g G l o b a l Shop Data, Approach  #2  16  3.5  C o l l e c t i n g G l o b a l Shop Data, Approach  #3  17  3.6  C o l l e c t i n g G l o b a l Shop Data, Approach  #4  18  4.  5.  SORTING AND  MULTIPLE LINEAR REGRESSION ON LOTUS 123  .29  4.1  Introduction  29  4.2  Lotus 1-2-3  Environment  30  4.3  Lotus 1-2-3  Macro Program  31  4.3.1  Conversion Routine  36  4.3.2  S o r t i n g Routine  38  4.3.3  MLR  41  4.3.4  P l o t t i n g Routine  Routine  42  THEORY OF MLR  64  5.1  System of Equations  64  5.2  Regression A n a l y s i s  65  5.3  A l g o r i t h m of MLR  66  6.  CONCLUSIONS  71  7.  FUTURE RESEARCH  73  7.1  Computer Cost E s t i m a t i o n Program on Lotus Spreadsheet  7.2  Input From Drawings  1-2-3  73 73  iv  REFERENCES APPENDIX  LIST OF FIGURES FIGURE  PAGE  2.1  The Page by Page Summary  9  2.2  The Formula and Single Unit R e t r i e v i n g Labour Hour V a r i a b l e s  2.3  The R e l a t i o n s h i p Between T h i s Research and I n d u s t r i a l Firm  3.1  Four Approaches  3.2  Material B i l l  3.3  G l o b a l and L o c a l Shop Data  23  3.4  An Example of Approach  #1  24  3.5  An Example of Approach  #2  25  3.6  A Comparison  3.7  An Example of Approach  3.8  A Scheme of Approach  4.1  The Opening Menu  45  4.2  The G l o b a l Data F i l e  46  4.3  The F u n c t i o n of the Conversion Routine  47  4.4  An Example of the S i n g l e Piece Data F i l e  48  4.5  An Example of the Double Piece Data F i l e  49  4.6  The Menus and Macros of the Conversion Routine  4.7  The Help Messages of the Conversion Routine  51  4.8  The F u n c t i o n of the S o r t i n g Routine  52  4.9  The Menus and Macros of the S o r t i n g Routine  53  4.10  The Help Messages of the S o r t i n g Routine  54  4.11  The Menus and Macros of the A n a l y z i n g Routine  4.12  The Help Messages of the A n a l y z i n g Routine  4.13  The Menus and Macros of the P l o t t i n g Routine ....57  4.14  The Help Messages of the P l o t t i n g Routine  58  4.15  The R e s u l t s of the C200X15 Component F i l e  59  Methods  of the  of C o l l e c t i n g Shop Data  and Route Card  of Approach  #1 and #2  #3  #4  vi  10 11 21 22  26 27 28  ..50  ...55 56  4.16  The R e s u l t s  o f t h e PL8 Component  File  60  4.17  The R e s u l t s  o f t h e W150X24 Component  File  61  4.18  The R e s u l t s  o f t h e PL8PL10 Component  File  62  4.19  The R e s u l t s  o f t h e PL10W610X113 Component  5.1  A System o f A l g e b r a i c E q u a t i o n s  5.2  A Graphical Illustration Showing M i n i m i z a t i o n of the R e s i d u a l E r r o r  File  ..63 68  the  f o r t h e MLR A n a l y s i s  69  5.3  A Flow C h a r t  7.1  A Flow Chart f o r Computer Program i n L o t u s 123  7.2  An Example Showing t h e S p r e a d s h e e t s and Menus ...76  7.3  A Scheme U t i l i z i n g  Computer G r a p h i c s  77  7.4  A Scheme U t i l i z i n g  C.A.D.  78  vii  Cost  Technology  70 Estimating  75  ACKNOWLEDGEMENTS The  i n v e s t i g a t i o n s and r e s e a r c h were made p o s s i b l e by a  Natural  Science  (NSERC)  and E n g i n e e r i n g Research C o u n c i l of Canada  Postgraduate  scholarship  G.R.E.A.T.-Award f o r the author and  a financial  Ltd.,  Port  contribution  Coquitlam.  and  by the B.C. by  Coast  The  author  Victor  Dick,  through  a  Science C o u n c i l  Steel  Fabricators  i s grateful  for this  support. Mike  Liska  and  assistant  e s t i m a t o r , and Herb H o r n s t e i n , head manager of Fabricators  to  Coast  L t d . are due s i n c e r e thanks f o r t h e i r  and c o o p e r a t i o n . thanks  and  The author  P r o f e s s o r S.F.  wishes to express Stiemer,  chief Steel  guidance  h i s sincere  h i s s u p e r v i s o r , f o r the  s u p e r v i s i o n and a d v i c e . I  would  l i k e to thank my wife, Fern, for her patience  throughout my r e s e a r c h .  Trademarks: IBM  is a  trademark  of  International  Business  Machines  Corporat ion Lotus  1-2-3 i s a trademark of Lotus Development  MAI-Four i s a trademark of MAI Canada L t d .  viii  Corporation  Chapter 1 INTRODUCTION New general s t r a t e g i e s have r e c e n t l y been at  U.B.C.  and the p o s s i b i l i t y of developing a computerized  e s t i m a t i o n program f o r Early  investigated  results  from  steel  structures  exists  these r e s e a r c h programs were promising  and i n v i t e d an implementation of  these  workplace.  fabricator,  A  local  F a b r i c a t o r s Ltd.,was  [1] [ 2 ] .  steel  trying  to  findings  upgrade  into  Coast  their  the Steel  estimating  process to take advantage of the new s t r a t e g i e s and computer technologies. computer  This  aided  research  estimation  included  using  a  case  study  of  an o p e r a t i o n a l approach.  The study was composed of two p a r t s .  In the f i r s t  part,  a  computer e s t i m a t i n g program was developed on a mini-computer in B a s i c .  T h i s program has been completed and  use  as a d a i l y o p e r a t i o n t o o l  The  computer  estimating  was  put  to  i n the e s t i m a t i n g department.  program  has  reduced  the  total  e s t i m a t i n g time by 30%. The second part of the r e s e a r c h p r o j e c t  was  concerned  with the g e n e r a t i o n of the labour hour v a r i a b l e s used i n the e s t i m a t i n g program at the f a b r i c a t i n g  shop.  These v a r i a b l e s  are needed to c a l c u l a t e the labour hours and thus the labour cost.  They  performance  are  important  because  they  of the f a b r i c a t i n g shop. shop  different  of shop management.  were reduced  by  multiple  have  linear  1  the  Several approaches of  c o l l e c t i n g global levels  data  reflect  been  investigated  for  The g l o b a l shop data  regression  utilizing  a  2  program  written  shop d a t a  served  well  as  a  curve  studies  research  [3].  should  Lotus  123 macro l a n g u a g e .  as f e e d b a c k f o r t h e e s t i m a t i n g  database  mechanism w h i c h from  in  be  for efficiency,  Finally,  directed  simplifies  shop d r a w i n g s .  this  the  The  program  c o v e r a g e and  project  global  learning  showed t h a t  future  towards the development process  of  data  as  of a  retrieval  Chapter 2 COMPUTER ESTIMATING PROGRAM A computer e s t i m a t i n g language utility  f o r the program  deletion.  The  the access  program has been w r i t t e n  "MAI-Four"  was  added  estimating  computer system. to  handle  Recently, storage  a  and  program i s menu-driven a l l o w i n g  of four major sub-programs. of the labour  file  i n Basic  1.  storage  2.  input of t a k e - o f f  3.  computation of the labour hours  Their  functions are:  hour v a r i a b l e s ;  sheets; and  material  weights;  and 4.  calculation total To  of  the  costs,  m a t e r i a l c o s t s and  costs. run the computer e s t i m a t i n g  drawings must be f i r s t then  labour  entered  known as  the  estimating  recorded  on the t a k e - o f f  i n t o the computer. take-off  program.  summary,  "HOURS",  that each l i n e structural  will  be  issued  and  file  by  except  the  f o r the  that  the bottom part are missing.  take-off  and  4.1  the  summary  operations  will  computer  the Note  represent  a  a s s o c i a t e d with i t .  The t a k e - o f f summary i s checked and then used as data  and  The t a k e - o f f summary i s s i m i l a r t o the  on the  member  sheets  A hardcopy of the e n t r i e s ,  page by page summary shown i n f i g u r e "WEIGHT",  program, d e t a i l s of the  estimating  the  program  input which  c a l c u l a t e s the m a t e r i a l weights and the labour hours of the required operations. by page summary.  The r e s u l t s a r e summarized by the page  The values  under the "WEIGHT" and 3  "HOURS"  4  headings labour  in  hours.  operations  2.1  figure The  and  are the c a l c u l a t e d weights and  bottom part g i v e s  the  the  total  r e s u l t s of the page by page summary w i l l  in  terms  labour  are  activity,  agreement  to  with  the computer protect The  accurate the  total  program  operational previously  multiplying  cost.  summary.  cannot  be  i t by a c o s t  the  of  the  t o t a l cost  low.  labour  technologies  summing  up  the  steel the  The  products  for  total  handling  c o s t s , an advanced estimation.  material  material, cost  overhead are  f i x e d c o s t s and  The can  taken as a percentage of the t o t a l of the v a r i a b l e c o s t s and  labour.  can  of the i n d i v i d u a l  the corresponding m a t e r i a l c o s t s .  f o r equipment and  was  However,  c o s t s on  i s composed of the  overhead [ 1 ] .  of  factor [ 4 ] . This  be employed f o r s t e e l s t r u c t u r e  m a t e r i a l weights and  material  to  program i s  estimating  relatively  importance  equipment and by  of  discussed  Costs by  data a s s o c i a t e d with labour  In g e n e r a l ,  found  calculated  the a v a i l a b i l i t y of new  approach can  profit  Because  approach.  c o s t s were  increasing  massive  costs  material  the  secrets.  when labour  c o s t s and  be  the  estimating  the  and  labour,  shape to give  approach used i n the computer e s t i m a t i n g as  weight  and  sorted  Coast S t e e l F a b r i c a t o r s , f u r t h e r d e t a i l s of  s t r u c t u r e s were  the  give  t h e i r trade  known  grouping  then be  the u n i t m a t e r i a l c o s t s , wages, overhead and  entered  with  d e s c r i p t i o n , and  grouping, m a t e r i a l  Finally,  of  corresponding time of performing them.  The  of  number  the  Labour c o s t s are the most  be of  difficult  5 items  to  amounts as  estimate.  of  labour time.  shearing,  The  key  to  structural  However,  estimating of  these  variables.  the  basic  etc.  are  labour  component and t h e  hour  operations  punching, d r i l l i n g ,  identification  labour  Different  require  different  operations relatively  fixed.  lies  in  the  common  operations  for  each  proper  application  This  hours  such  approach  is  of  known  known as  the  operational approach. On  the  other  formulas  to  example,  a general  DR = n{T  cover  n + I s ^_  hand,  all  1  Leung  operations  formula  (T ). a i  for  [2] for  has  suggested  all  drilling  general  components,  for  is  + t *N, + t *N. }/N d h c h '  where  n = ^— and ^— i s i n t e g e r d i v i s i o n s s DR = t i m e f o r o p e r a t i o n ( i . e . d r i l l i n g ) T  g  = setup  time  T  g  = alignment  t^ = d r i l l i n g  time per  N, = number of h t  = time  to  g  of of  one p u n c h h o l e  i d e n t i c a l members  = maximum number of  The a d v a n t a g e  of  the  formula  components. does  plates  formula  l a b o u r hour v a r i a b l e s all  hole  holes  center  N = number of N  time  The  in a  method  a r e needed  is  the  that  only a small  to cover  disadvantage  not c o v e r a l l  stack.  is  parameters,  the  that this  set  operations since  the  method may  6 not  give  good  operation  results  on a  independent  given  unit,  these  units.  that  each  hour e s t i m a t e .  In  as  the  However,  if  set  to  of  all  individual  f o r m u l a method  store  the  appropriate data  in  formulas through shows the  have their  how  been studies  the  period labour  when  is  there  used are  hour v a r i a b l e s .  office  for  more o f t e n large  i n the  two methods  f o r m u l a method  cost  retrieving  base these  It  a  of  the  method  operation  methods  unit  is  on an labour must  A full  by the  used  for  the  obtain  it  set  estimating shop.  no  previous  can a l s o  be  used  and  then  labour  department 2.2  general,  records  in  a  unit  already  of  design  method  is  possesses  a  l a b o u r hour v a r i a b l e s . variables  not  fabrication  virtually  manufacturer  is  of  In  start-up  The s i n g l e  used  Figure  together.  to  program.  variable  to  file.  used be  estimating  hour  are  are  method w i l l  fabricating  l a b o u r hour  using  estimation.  used  optimization.  u s e d when  data  is  compiled  single  a more a c c u r a t e  labour  the  unit  the  computer  available, it  single  both  The s i n g l e the  a  each  l a b o u r hour v a r i a b l e s  project,  mechanism of  as  if  c a n be d e r i v e d  reflect  p e r f o r m the  other.  an  costs this  However,  treated  and hence g i v e s  reported  basic  of  best  A full  each  is  labour  will  in order  the  complement  component  the  component  be o b t a i n e d  some c o m p o n e n t s .  The a d v a n t a g e unit  individual  for  Methods of  will  be  has  been  discussed  later. The  computer  substantially  and  estimating was  put  program into  use  for  over  tested  a year.  The  7  management total  of  Coast S t e e l  estimation  estimating their  has  accepted  by a l l  program  not  additional  of  all  completed given  of  changes.  the the  shop  more r e l u c t a n t system lack part  is  of of  program.  to  accept  adequate  this  estimating readily  estimating allowed  before.  an a r e a where  of  ,  only  greatest  the  Continual  are  they  hi-tech work.  cost  c a n be  changes.  and d i s l i k e  shown by t h e  r e s e a r c h was  the shop  it.  structural computer considered  graduates  of  willing  and t h i n k t h e Hence,  estimating  feedback  other  saving to  a r e more  the  first was  advice  was  the  success  h a n d , management  They  use  program,  and  which c o n t r i b u t e d to On t h e  For  saving.  estimators  their  one  w i t h the  versions  t o have a l o w e r a c a d e m i c  interest  to maintain  and was  performed  institutes,  enchance  estimators  seem  tool  However,  They welcome  research  estimating  the  be a b l e  involved,  the  the  on s c h e d u l e .  by t h e  efficiency,  computer  identified  a variety  or t e c h n i c a l  this  their  w o r k l o a d but a l s o  not  calculations  a computer w i l l  part  reduced  suggested a s t r u c t u r a l equivalent  which produces  Universities  has  The computer  The  their  w h i c h was  program,  Since  of  he  to  indispensible  c o u l d be c o n s i d e r e d .  determine  accept  staff.  an e s t i m a t o r  the  estimating  to  observed  relieved  it  With h i g h e r  estimators.  analysis  of  an  the  only  be made,  equivalent  to  become  when  Because  was  with a smaller  program  could  by 30%.  department  service  example,  time  reported that  of  from  b a c k g r o u n d and a r e think  computer.  the Because  management,  behind schedule.  current  the  Input  of  the  second from  the  8 shop  management  was  minimal  done  through observations  the  findings  and v e r i f i e d The firm  of  the  i n the  technical  support  issues.  The  key  estimating  of  and r e s u l t s  will  is  ignore  only  are  steel  Steel  data  the  is  as  material  trade  secrets  possible  must of  tested  the  lies  in.  Care  be d i s c l o s e d industrial  the  their  topic  can be  since  data  of  industrial  are doing, collect  still  cost  co-ordinated  the  they  may  as  much  w o r k i n g on  areas  should also or w i t h h e l d firm.  to  interest.  must is  estimating  i n a common  in  student  other  their  support.  efforts  he  provides  facility  work t h e y  while  industrial  author  access  and  However,  i n the The  interested  what  be  was Thus  in computerizing  allows  interested  be s h a r e d .  and t h e  researching  structures,  studies.  research  shop.  cannot  The  some f i n a n c i a l  interested  other  firm  2.3.  such an a r r a n g e m e n t  both p a r t i e s  research  i n the  study  Steel  in  In r e t u r n , C o a s t  and c o n t r i b u t e s  firm  figure  Coast  and  file  Since  between t h i s  to  process  to  author  the  shop.  summarized i n  estimating  by the  of  l a b o u r hour v a r i a b l e s  arrangement  can be  and most  be to  taken  as  to  protect  the  f FILE *AME = SAMPLE DIVISION :  PAGE  OTY  DESCRIPTION  0  COAST STEEL FA3*l£ATQ*  OAIEJ  08/15/84  PAGE 8V PASE SJtlArttf  PASE t  1  * MORMAL *  SH  H/LB LENGTH AEIGHI  IN-LINE OPErfAl13*3 A B C 0 El F  CODE  G  CUT BEV P*E COP P.M ORl SIO CJT CJT HJL HOL 1/1  1/2 1/S 1/4 1/D  1  r  1 o  16 21 18 i)  fa<i/8 J.5X1/2 18X1/2 6X1/2 3X3X1/4  1/S 1// irs™ 1/9 HX4X1/4 1/10 T7TT r~ _g 1/12 1 1UX1 1/13 0 U iv n — Z T 0 1/15 0 l/lf> 0 a T7T7 3—  ~ H —  w o  40 "55 55 0  -pr u PL PL  0 0 -pxr~tr L 4.9 L 6.6 - c — 11.5 PL 0 0 0 SP~ -vs-— 0 ' 0 0 0 -o  iO 1201) 25 I3T521.ii 1183 0 0 ~TB— " T T 28 1. S >7 2.5 -2?~ .5 4 lb 99 -TT543 1.3 0 0 -.-5'-- -12-0 0 0 0 -o—  I DIAL 1E13H1:  -$TT-  2 Z 2 0 2A 2A -2A~ 2A 2A -2TT2A 0 -2A0 0 U—  0 -  2 0 —0~ 0 0  —o~ 0 0  —tr o  0  0  o—o0 0  0  2  21  o2—o—nr—o  0 0  v—-tr  0 0 ~0 0 o  0 U  0 0 -IT — 0 0 0 0 0 ~u— "2 o u 0 u "D— o 0 0 0 0 - « — -o  0 0  0  1 0  T B — 3  ADOHIONAL OPEKAIIONS H I J K L 1 N O P " ' /L8 END 3 U GU3 EXT CLP CON SIB BUG EX PLS END PCS o o o -o—0"—0 0 0 0 0 0 0 -o o~ 0 0 0 1 -9--—f/-  3  r — 1.5 0 r  0 0 4 0 -j o— a a 0 0 q 0o a 0 0 o—0 0 0 0 0 a o-  0 1 1l 0  0  0  o—o 0 0  -o oo o o o -T)—oo o o o -TJ 0-  0 0  1 o  0  ......  0 0 -o— 0 0 0 0 0 0  o  o—i  o o —o  o o  o  TOTAL H'JJHSs  4102 OPS HOJHS OPS  H3JRS  2.2 I. o I .6 0  ~t— 2  1.1 I -  .0  o o o  1.5 1 1.3 1 a  IB  2 2  3  2  1.7  .&  2 .3  2 .3  34 1.9  4 .5  0 0  0 0  0 0  0 0  0 0  3 0  0 0  0 0  0 0  0 5.4  2A 2A  15 1.9  0 0  0 0  2 .4  38  4 .4  3 1.5  4 I.B  1 .9  1 .8  2 .6  1 1.2  2 1  1 .7  1 1  0 12.6  2.1  The  TOTAL HOOKS:  PC3 =  CODE:  Figure  MOJH3  Page  by  Page  Summary  IB  10  INSIDE COMPUTER  ask f o r labour hour v a r i a b l e  ESTIMATING PROGRAM  ^  r e t u r n the r e q u i r e d labour hour v a r i a b l e  DATA BASE c o n t a i n i n g a l l the labour hour variables available (Single Unit Method) 1\  missing labour hour v a r i a b l e , prompt user f o r input  input the labour v a r i a b l e i n the computer f  USER look up the general formula and c a l c u l a t e the labour hour v a r i a b l e by feeding in the various parameters by hand (Formula Method)  F i g u r e 2.2  The Formula and S i n g l e U n i t Methods of R e t r i e v i n g Labour Hour V a r i a b l e s  INDUSTRIAL FIRM:  U.B.C. provides t e c h n i c a l supports, research and development  COAST STEEL THIS RESEARCH  FABRICATORS access of t h e i r data f i l e , finance and f a c i l i t i e s  Figure 2.3  The R e l a t i o n s h i p Between T h i s Research and the I n d u s t r i a l Firm  Chapter LABOUR HOUR  3. 1  approach.  hour  chapters. global  1-2-3) error  This  is  using of  each  data  this  have  approaches which  of  find  in  discussed  the  is  programming  been are  study,  examined  the  used  to  these  following analyze  spreadsheet  technique.  the The  (Lotus  The s t a n d a r d  be p l o t t e d  four approaches (figure  of  3.1).  Only  in d e t a i l .  The  a fundamental only  obtain  to  show  collecting  shop  analysis.  investigated  will  in  operational  l a b o u r hour v a r i a b l e s .  micro-computer  the  the  a method t o  regression  give a  the  case  change  be d i s c u s s e d  of  as  the  the  first  last  approach  fabricating  a theoretical  three  shop  option.  CURRENT SHOP PRACTICE AND GLOBAL SHOP DATA Currently,  materials  i n the  are  sequences are The  to  used  l a b o u r hour v a r i a b l e w i l l  involves  management  on  macro  precision In  to  are  be  linear  data done  is  will  Multiple  shop  analysis  variables  The o b j e c t i v e  variables.  3.2  VARIABLES  INTRODUCTION Labour  the  3  labour  manner t h a t  handled  scheduled hours  are  of  by  Coast  material  by the recorded  route i n the  Steel  Fabricators,  bills cards time  and  some s o r t i n g .  time  a  basis  of  In g e n e r a l ,  the  classification  cards  12  workers which  the  (figure  no l a b o u r h o u r v a r i a b l e can be o b t a i n e d  without on  case  job 3.2).  i n such a directly  record  separates  their all  1 3 components  into  component  basis  example, of  beams,  a  such  worker  the  components punching  such as  The c u r r e n t long  as  with  2 hours  20 h o l e s  the  as  2 hours  the  into  required  for  Formerly, labour  time  s y s t e m of  by t i m e  the  hour are  the  example,  the  plate  "global  is  In  fact  However,  not  it  the  hour  on  as  the  to  seems t o  data  are  just  are  to  observation time  for  be of  found.  hour  p r o g r a m were  found  be more e f f i c i e n t the  data  to punch are  labour  hour v a r i a b l e s . the  to  obtain  combinations  of  the  The g l o b a l local  local labour  shop  data  data,  for  a  10 mm  individual  time  the  "local  shop d a t a " ,  i.e.  Multiple  linear  regression  is  local  to  production.  and p u n c h i n g of  shop d a t a " whereas t h e  the  labour  several  shearing  data  determine  current  estimating  shop  variables.  o r p a r t i c u l a r shop d a t a a r e  that  calculate  global  l a b o u r hour used  labour  classification.  i n d i v i d u a l worker and h i s  shop  total  the  shop d a t a and a n a l y z e  and t i m e  to  had  in  under e a c h  are  computer  shear  used  1  total  be  global  or  of  different  the  interested  to  The l o c a l  sum  on  is  studies  i n the  variables  instead  adequate  the  global  shop d a t a .  numbers  is  of  p e r f o r m a n c e of The  classification  W150X27 and  determination  studies.  collect the  used  For  shop m o n i t o r i n g  records  hour v a r i a b l e s .  variables  of  on P L 8 .  shop  the  under t h e  1 h o u r on sawing  instead etc.  individual operations  fabricator  these  etc.  L75X75X6,  drawing  h o u r s and i n d i v i d u a l l a b o u r h o u r s However,  frames,  W150X27,  records  "beam" t o g e t h e r  breaking  columns,  shop d a t a ,  i.e.  to the then  l a b o u r hour  14  variables labour  from  the  program.  the  shop management  for  the the  has  in figure  other  in-line most  of  small  3.3  of  of  them  Because  shop  global  shop d a t a .  enough  of  the  put the made  data  will  the  must  never  not  p e r i o d of in  operations  drawings  Moreover,  11,  yet  been  APPROACH  and  that 14,  However,  departments.  they  of  to  assign  of  developed.  #1 s t u d i e d and are  For  sorted  required  o p e r a t i o n they  have  shop  flow  data  the done  forget  Hence, a d e t a i l e d job  record  instance,  sometimes even  global  to this  w o r k e r s do not  card.  while  experience  sorting  o p e r a t i o n and s e q u e n c e of  (figure  Assume  type  drawing numbers.  order  both  shop  operations  constraint  be c a r e f u l l y  time  r e c o r d what  the  system  directly  fit.  require  c u r r e n t job s e t - u p the  time c a r d .  down  d e p a r t m e n t and  can be u s e d  by  time  Studies  i n f o r m a t i o n on t h e  workers  computer  information  and  used  a l i n k a g e p r o g r a m has  i n the  These  l a b o u r hour v a r i a b l e s a r e c o m p a t i b l e  "Raw"  because  the  additional  weld  are  3.3).  U n f o r t u n a t e l y , the  the  COLLECTING GLOBAL SHOP DATA,  into  on  2 . 1 i n terms  portion  research,  same  program.  operations  interpolate.  update  these values  all  (figure  estimating  have a d o p t e d t h e  classified  the  to  both the  computer e s t i m a t i n g  shown  a  If  labour operations,  management  Thus,  shop d a t a  hour v a r i a b l e s can be used  estimating  in  global  for  to  study  of  must  be  certain  3.4). the  17 a r e  global to  be  shop d a t a on c o n t r a c t found.  From t h e  5441  time c a r d s  and of  1 5  figure 1  3.4,  worker number 9 and 12 have  hour a t  last  one  However,  do n o t  see  these drawings, has  angle  shear  From  the  that  a set  times  and is  saw,  drill  on  and t h e  be  is  fit  general will time.  is  operation  and  usually Worker  yet.  To to  there  must  by the usually  perform number 9,  required in The  be  conclude  a  set  are  However,  be  of  number of  the  the  exact  3 angle  shears  performed and f i n a l l y  the  actual  number of  indicates  indicated  of  sets  it,  the  plus  performed.  individual  give  route  by the route  and  together  both  first  and  fit.  This  arrangement card.  shop d i c t a t e s the  same  that  sort  12 and 25 a r e  by t h e  of  From  fitter.  certain operation  known  as  6  that  and punch a r e p e r f o r m e d t o g e t h e r  handled  i n the  have  one c a n  find  drill  on  they  on W150X24  A l s o the  at  since  operations.  two  the  known  p e r f o r m e d and  clear.  columns  shear are  practice  not  shear  flow  drill  cards,  The j o b s e q u e n c e  sequence of  angle  been  these  example,  followed  experience,  have  are  should  time  referenced  angle  job  and f i t  in p e r f o r m i n g the  on L75X75X10. and  operations  L75X75X10  and  hours  operation  that  known  for  of  s h o u l d be c h e c k e d .  between  not  operation,  the  kind  not  card  independently,  of  the  d i d is  4.0  route  times  should  saw  actually  operations  relationship  drawings  they  spent  of  card  observed  has  hour  number 25 who i s  kind  and punch  of  operations  the  the  route  are  punches  Worker  0.75  what  indicated  operations  each  down  To  card  what  put  performed.  the  respectively.  w o r k i n g on t h e s e d r a w i n g s  1 p.m. they  7 a.m.  spent  the  and The  workers all  the  shear  16 man,  saw man and f i t t e r  the  global  detailed  shop  study  data  general,  global  shop  knowledge, plays  of  last this  data  effort  this  needed  into  in  figure  the  l a b o u r hour v a r i a b l e  3.4  COLLECTING GLOBAL SHOP DATA,  simple  For i n s t a n c e ,  drawings  time  total the  particular  important. is  operations.  No  the  fewer  needed  uses the hours  the  to  referencing  the  individual to  give  experience  the  raw shop d a t a  time  14  or e x p e r i e n c e  and  left  give  the  Figure  3.5  are  not  card  a  for  the  number  of  is  magnitude  required of  17 c a n be o b t a i n e d The  hand s i d e  operations of  a  same  to  to  with the  these hours  assigned  drawings. the  #2  added  The  that  hour.  r e f e r r i n g to  sum of  data.  11,  1/3  are  easier  conclude  raw shop d a t a  be  to  advantage  same d r a w i n g s .  logic  shop  drawings  up  be  deduction,  global  obtain  be d i f f i c u l t  W150X24 i s  APPROACH  R a t h e r the  on  summed  will  can c o r r e c t l y  will  on  operations  then  it  i n d i v i d u a l h o u r s on e a c h  same d r a w i n g s  find  cards  are  fabrication  since  However,  sawing  All  hours  shows t h a t  to  for  routine.  on the  individual  one  approach a l s o  sorting  to  shop d a t a w h i c h a r e a l s o  analyze.  second  data  suited  Also,  relatively  to  The  deduce  through a  shop  extensive  approach,  global  can  3.4.  poorly  program.  one  shop d a t a  global  involves  in t h i s  the  raw  patience.  that  Hence,  These  it  a computer  deduce  the  approach is  as  approach i s  to  box  and  a major r o l e  implement  from  and s o r t i n g .  summarized by the In  respectively.  the  the by are  equation  17  shown  in  drawing w i l l equation  be  the  represents  The  results  summarized global  3.5.  figure  in  The t o t a l  right the  hand s i d e  global  between the  shop d a t a  generated  first  by t h e  and  Nevertheless,  more raw shop d a t a  use  of  older  raw  records.  a p p r o a c h do not  represent  readily  by t h e  not  solved  want  approach  hour  3.5  can  approaches  easily  shop  see  that  approach is data  his  is  recommended  Thus,  monitoring because  i n t o a computer program to  with  of  this  they  are  a fabricator  does  system,  the  the  needed.  obtained  because  if  are  fairly  are  c a n be e a s i l y  a major o b s t a c l e  change  COLLECTING GLOBAL SHOP DATA, third  current  possibility  shop p r a c t i c e  information Operations route  the spent  system on the  card  without  will  This  the  second  procedure produce  can  the  be  labour  variables.  A the  and s e c o n d  second  computer.  each  equation.  The c o m p l i c a t e d e q u a t i o n s  to  implemented  the  to  data.  One  complicated  the  more  of  shop  3.6.  figure  hours corresponding  alone  referencing  operations on the give  shop d a t a  on  will the the  operation.  the will  LHS of  requires but  for  route  the  APPROACH  in  collecting should  give  the  drawings. route  global  deviation a  much  global be  RHS of  the  equation.  route  better  shop  global  The w o r k e r s  hours  from  data.  quantified.  the  inside  circle  time  the  will  they  circle  Therefore,  c a r d s and  The  equation  must  c a r d and r e p o r t  Now t h e  a p p e a r on t h e  a slight  results  cards  #3  global  then  be  18 entered  into  the  Since summed  the  daily  from t h e s e  needed.  less  and  There  #2).  management cards.  as  of  the  minimal.  As  appropriate  the  data  data  can  controlling  the  an  of  card  card. raw  global  all  sorting  of is  be  passes  locations  for a c e r t a i n W i t h the  of  be a s s i g n e d  time of  can the  global  APPROACH  proposed  such t h a t  the  can be  is in  only an shop  global shop  forecasting,  i.e.  the  equation.  at  Hence,  of  known,  stations  which a  the this  the  time  global duration  representing  a global  job  The d u r a t i o n  represent  RHS  operation  collecting  control time  will  job sequence being that  #4  recorded.  work s t a t i o n s  to  these  i d e a l a p p r o a c h of  has  operation,  equation.  replace  global  accounting,  an  stations  subsequent  compile  route  purposes.  Forde [ l ]  through these  between two taken  shop  the  the  Moreover, these g l o b a l  for  f o u r t h approach i s  critical  effort  formulated  become  on  on t h e will  extra  longer  actually  load  information  r e q u i r e d to  and s c h e d u l i n g  work  are  no  be  ( A p p r o a c h #1  be q u a n t i f i e d  the  can  are  than b e f o r e  them w i l l  used  cards are  themselves  data  MLR a n a l y s i s .  shop d a t a .  time  Hence,  shop  analysis.  i n d i v i d u a l worker  increased must  route  analytical  approach.  the  COLLECTING GLOBAL SHOP DATA, The  LHS  the  workers  operations  the  also  of  this  cards,  be  time  for  of  the  on work r e p o r t s  may  way,  Little  shop  at  route  time  The e n t r y  data.  hours  So b a s i c a l l y ,  spending  3.6  for  3 . 7 shows an example  Figure  that  computer  equation  the will  19 be  set  up  mechanism  and  will  behind  of  the  third  the  fourth  approach  the  approach.  depends  be  approach  utilizes  time  a job  is  shown  both  in  workers  route be  all  cards.  material  recorded located this  at  number  the  the  other  material  passed  exit  until  time  is  the  from one the  the  the  the next  location  is  finished  recorded.  flow  an  the  approach  The  by t h e  the  shop  computer  most  optimal  material  indicator an e n t r y bar code  on time  will  be  operation.  the  are  leaves  it. is  to  passed  When  another,  to  detector  performed  recorded.  of  unnecessary  The  material  component  and  eliminated.  shop,  to  fourth  workload  also  optimal  put  strip  The  fourth  information  again  that  daily  but  issue  the  suggests  the  computer.  to  third  data.  taken-off  the  the  magnetic  still  s u c h as  material  occurrence the  [5]  Some o p e r a t i o n s a r e  for  with  t i m e of  repeated  device  and when f i n i s h e d  workers  is  into  while  be  and  worker  brought  entrance.  together  the  is  that  will  selects  the  is  register  will  the  information  a sensing  material,  on t o  is  by  is  into  instructs  between them  and  reduced  entry  The computer  u s e d and  Once t h e  and  this  be  drawings  management and e n t e r e d records  only  and management data  that  A scheme of  Not  The  to  shop  to  data.  similar  Berry  the  devices  shop.  3.8.  from the  very  bar-codes  collect  i n the  is  shop  automatic  input.  as  sensing  human e r r o r d u r i n g information  human  to  figure  approach  fully  such  used  global  The d i f f e r e n c e  is on  media  devices  of  fourth  approach  pre-encoded  represent  the  worker  registered  The  sequence  the  shop and  20  Once unaware  of  the the  monitored.  system fact  Since  misinformation approach  may  that  will  be  involve  installation  of  approach  is  of  set  their  little  and t h e this  is  up,  the  performance  human i n p u t reduced.  changes the  proposed  workers  in  is  is  current  sensing hardware. for  the  future.  soon  be  continuously  involved,  However, the  will  errors  this shop  or  ideal practice  A case  study  21  APPROACH: #1  RAW SHOP DATA  DETAIL STUDY AND SORTING INVOLVING DEDUCTION, LOGIC AND EXPERIENCE  #2 RAW SHOP DATA  SIMPLE SORTING  GLOBAL SHOP DATA  #3  MODIFIED SHOP DATA  manual  input >  #4  IDEAL SHOP DATA  Figure  3.1  automatic  input 1  F o u r A p p r o a c h e s of  Collecting  Shop D a t a  ETC  ETC  ETC  ANGLE SHEAR MATERIAL  PUNCH  BILL  O e s c r . O t y . Cut/M W150X27 1 12 C200X2 1 1 3 L75X75X6 1 5  ROUTE CARD Contract: Drawings: 0 1 1 1  F i g u r e 3.2  Material  Bill  OPERATIONS D e s c r . L .sh P u n c h Saw X W150X27 C200X21 L75X75X6  and Route Card  Fit X X X  to to  GLOBAL SHOP DATA  MULTIPLE LINEAR REGRESSION (MLR) /  LABOUR HOUR VARIABLE (LOCAL SHOP DATA)  Figure 3 . 3 G l o b a l and L o c a l Shop Data  ro CA)  24  TIME CARDS: Worker No.: 9 Date: J u l y 8,86 Time i n : 7:01 Code Contr/Dwg. Time beam 5441/11,14 17 .75  Worker No.: 12 Date: J u l y 8,86 Time i n : 7:00 Code Contr/Dwg. Time beam 5441/11,14, 17 1  Worker No.: 25 Date: J u l y 8,86 Time i n : 7:03 Code Contr/Dwg. Time frame 41/115,160 2 beam 5441/11,14,17 4  C o n t r a c t : 5441 Drawing: 11,14,17 Q Descr. Code L.sh Punch Saw 3 W150X24 beam X 3 L75X75X10 .. X X  DRAWINGS:  Drill X  OPERATIONS Wl50X24:SAW DRILL FIT L75X75X10:L.SH FIT  SEQUENCE OF JOB i  W150X24:3 SAW 9 DRILL 12 FIT L75X75X10 :3 L.SH 6 PUNCH  GLOBAL SHOP DATA: f(OPERATIONS)=TIME 3(W150X24 SAW)=1 3(L75X75X10 L.SH)+ 6(W150X24 PUNCH)=.75 9(W150X24 DRILL)+ 12(W150X24 FIT)=4  Figure 3.4  TIME(HRS)  LOGIC AND EXPERIENCE  An Example of Approach #1  .75 4  V  1  25  TIME CARDS: Worker N o . : 9 D a t e : J u l y 8,86 Time i n : 7:01 Code C o n t r / D w g . Time beam 5 4 4 1 / 1 1 , 1 4 17 .75  Worker N o . : 12 Date: J u l y 8,86 Time i n : 7:00 Code C o n t r / D w g . Time beam 5 4 4 1 / 1 1 , 1 4 17 1  Worker N o . : 25 D a t e : J u l y 8,86 Time i n : 7:03 Code C o n t r / D w g . Time frame 4 1 / 1 1 5 , 1 6 0 2 beam 5 4 4 1 / 1 1 , 1 4 , 1 7 4  Wl50X24:SAW DRILL FIT L75X75X10:L.SH FIT DRAWINGS:  V  Wl50X24:3 SAW 9 DRILL 12 F I T  ADD  L75X75X10:3 L . S H 6 PUNCH GLOBAL SHOP DATA: 3(W150X24 SAW)+9(W150X24 DRILL)+12(W150X24 3CL75X75X10 L.SH)+6(L75X75X10 PNCH)  Figure  3.5  An Example  of A p p r o a c h  #2  FIT)+ =5.75  26  3(W150X24 SAW)=1 3(L75X75X10 6(L75X75X10 APPROACH #1  MLR=>W150X24  L.SH)+ PUNCH)=.75  9(W150X24 D R I L D + 12(W150X24 FIT)=4  SAW=l/3  REQUIRES ONE EXTRA EQUATION TO SOLVE REQUIRES ONE EXTRA EQUATION TO SOLVE  RAW SHOP DATA  3(W150X24 SAW)+ 9(W150X24 DRILL)+ REQUIRES FOUR 12(W150X24 F I T ) + H-tEXTRA EQUATIONS 3(L75X75X10 L . S H ) + TO SOLVE 6(L75X75X10 PUNCH)=5.75  APPROACH #2  Figure  3.6  A Comparison of  A p p r o a c h #1  and  #2  27  ROUTE CARD: CONTRACT: 5441 DRAWING: 1 1 , 1 4 , 1 7  3(W150X24 SAW)=1 3CL75X75X10 L.SH)+6(L75X75X10 9(W150X24 DRILL)+12(W150X24  Note:  9/.75  PUNCH)=.75  FIT)=4  => Worker n o . / H o u r s  Figure  3.7  An Example  spent  of A p p r o a c h  #3  Figure  3.8  A Scheme o f  Approach  #4  Chapter  4  SORTING AND MULTIPLE LINEAR REGRESSION ON LOTUS  4.1  INTRODUCTION The  program  previously in chapter  limited  the  flexibility points,  1-2-3  of  its  of  a  maximum of  two p i e c e s .  component.  Subsequently are analyzed  labour  hour  (MLR).  Finally  their The  the  standard  hour  the  will  to  variables  regular  Lotus  to  derived. Lotus  1-2-3  but  shop  shop  data  pieces  routine  sorts  the of  same  the  data  same i.e.  Regression  analyzed  plotting  the  Lotus  component  are  accuracy  and  routine. of  The  macro  program  1-2-3  menu.  The  can a l s o  spreadsheet.  29  global  shop d a t a local  the  plotting  Multiple Linear  users  automatically  using  and  containing  g r a p h e d by t h e  the  these  program  The s o r t i n g  global  the  on  and  macro  analyzing  l a b o u r hour v a r i a b l e s  indicate  start  This  files  through  m e n u - d r i v e n and r e s e m b l e s menu  improve  converts  and r e d u c e d  e r r o r s are  This  compatibility  same number of  the  variables  graph w i l l  labour  routine  into  language.  been d e v e l o p e d  sorting,  the  to  [6].  of  data  use,  estimating  programming l a n g u a g e ,  feature  conversion,  shop  of  In o r d e r  consisting  global  component  ease  level  cost  in Basic  p r o g r a m has  The c o n v e r s i o n  files  computer  written  program.  macro  the  routines.  the  the  most p o p u l a r h i g h  consists  to  two was  analytical  and  into  described  availability,  an  current  up  123  the is  opening  be a c c e s s e d  from  30 4.2  LOTUS  1-2-3  The  reasons  from  Basic  e a s e of  use,  the  market.  the  for  is  other  purposes.  Basic  different  the  software  is  is  a  common  will  compatibility  of  The allow  made  to  research  on of  copied  and  macro  program  implement  the  the  as  IBM P C ' s  are  ones  inside  to  take  the  most  instance, the  cost  described system.  language  Basic. run L o t u s  is  some  Basic  Basic  Lotus  1-2-3 all  1-2-3.  The  and  definitely of  may  Almost  1-2-3,  advantage  on  estimating  previously  Lotus  program  different  problem  computers.  than  able  macro  on  Even  Mai-Four  IBM c o m p a t i b l e  reside  software  adapt  environment  the  the not  a  results  project.  flexibility to  just  For  the  in  T h u s more u s e r s c a n  users  package  easily each  1-2-3"  composed  compatibility  computer  for  micro-computers  macro p r o g r a m  this  is  problem.  hardwares.  designed  on o t h e r  a has  IBM p e r s o n a l  run even  of  can  certain  always  be  not  problem.  of  programs w r i t t e n  more  software  be  Since  user  language  must  program to  Moreover,  is  computer  adjustments  business  the  "Lotus  developed can  availability,  needs.  hardware  popular  business  environment  are  and f l e x i b i l i t y .  which  compatibility  computer  programming  macro l a n g u a g e  program  routines  his  the  most p o p u l a r  independent,  which f i t s The  1-2-3  The macro  program  routine  switching  compatibility  currently  assembled  for  to L o t u s  is  short  ENVIRONMENT  associated  customize  the  w i t h macro programming  macro p r o g r a m t o  best  fit  will their  31  needs.  The e f f e c t  screen  such t h a t  the  macro p r o g r a m s . 1-2-3.  The  program  e a c h command w i l l  user  Input  users  and  scheduling  of  and o u t p u t  can  can  can always  expand  create  program r o u t i n e s  be d i s p l a c e d on  follow  are  and  debug  taken c a r e  and  improve  accounting, which share  the  the  of  the  by L o t u s  the  existing  forecasting  and  same g l o b a l  shop  data.  4.3  LOTUS The  and  1-2-3  MACRO PROGRAM  macro p r o g r a m i s  r u n s on t h e  IBM PC w i t h  program developed conversion, routine and  o p e n i n g menu a s  ways t o point  select to  a  numerical "return"  pad t o key.  menu i t e m  user  can  typing  the  selected, and  the  in  plot  subsequent The  figure  from t h e by  moving  the way  all  the for  is  to  menu  will  a u t o - e x e c u t e macro "\0" w h i c h w i l l  own menus  way  arrow  the  is  to  key on  the  pressing first  just  when t h e  the  letter  of  is  the  by p o i n t i n g "plot"  be a u t o m a t i c a l l y the  users  obtained  by two  menu-driven such t h a t  prompt  inter-connection  Each  There are  then  operations  will  [7].  the  type  the  inter-connected  The f i r s t  i t e m and  example,  worksheet  4.1  menu.  The p r o g r a m i s  menu i t e m s , the  selection.  shown  namely  its  2  The  routines.  which c o n t a i n s are  release 2.1.  f o u r main p a r t s ;  worksheets  The s e c o n d  perform  of  These  item  1-2-3  K memory and DOS  itself  highlight  [8].  for Lotus  a n a l y z i n g and p l o t t i n g  an i t e m menu  512  composed  a worksheet  macro commands.  the  the  is  sorting,  is  designed  item  is  retrieved for  by u s i n g  be a u t o m a t i c a l l y  or  next the  invoked  32  once the appear four be  worksheet as  a  as  the  spreadsheet  "M" k e y ,  macro than  the  program the  menus  the  macro  1-2-3  worksheet  or  A2000.  The menus  cell  Since  BA1 and t h e the  closely. a typical  of In  of  the  arbitrarily  shop p e r f o r m a n c e  data  is  of  file.  Coast  be  user  and Steel  of  the  can combine  productivity.  are very  important  to  enhances  the  design  program. freedom  With  data  placed  either  i.e. at  written  data  data  file  should  at will  do not  the  starts the  cell  home A1000  programmed cell  file  global  necessary  CA1.  affect  be examined  shop d a t a  that  the  memory.  always  worksheet  the  only  random a c c e s s  input  Lotus  in designing  because  in  global  Note  pressing  feature  and macro commands a r e  the  accessed  other  the  input  menus  the  saved the  Lotus  can  other  space  are  the  4.2,  program  of  w o r k i n g on s o m e t h i n g  more  are  composed  key w h i l e  the  the  to  data  derived of  "ALT"  format  p r o g r a m , the  figure  input  regular  h e l p messages a r e  format  workability  the  increase  of  hand s i d e  output  cell  macro  good w o r k s h e e t  regard  upper l e f t  the  use  is  and d a t a  and the  are  he  menu t o  position  at  to  macro p r o g r a m r o u t i n e s ,  the  the  Moreover,  cells  is  menu c a n a l s o  free  A  it  opening  there  loaded  macro p r o g r a m w i l l  to  and o r g a n i z a t i o n 2,  the  feature  when  design  without  individual  at  is  opening  release  In the  user  program.  readability  hand,  by h o l d i n g  even  Worksheet the  the  MLR a n a l y s i s .  with  other  an a d v a n c e d Since  Hence,  program eventhough  On the  and f u n c t i o n s . from the  loaded.  complete  worksheets. used  is  more  shown  shop reflect  Fabricators Limited.  the  is  data the These  33 data are  used o n l y  of  macro  the  to  illustrate  program.  d a t a a r e d e s c r i b e d as contract the  the  The f i e l d  follows.  number and t h e  i n d i v i d u a l worker.  names  Column  d r a w i n g number. Column C  pieces  used  to  create  one  number  either  one  or  two  since  temporarily reasons  restricted  to  two  for  this  restriction.  only  allow  file  names  will  be v e r y  component  to  difficult  files  of  to  eventhough  correct names for  this  to  and  represent  are  different will  only  function. total  number  members allow  is  number of  this  limitation the  above  operations not  is  to  Lotus  of  retard  have  are  two will  Thus  the  A method  Next, the  members,  Lotus  QUERY  increase as  the there  overcome  function such a  speed  of 1-2-3  /DATA  A way t o  However,  Since  range  limit  /DATA QUERY  and  to  numerical  full  member w i l l  power  file  PL 1OPL16W200X22  a  in  it  the  same  PL 1OPL16W61 OX 113.  instead. the  is  different  different  thirty-two  the  of This  1-2-3  a s y s t e m of  e a c h member.  to  For instance,  represent  third  use  the  piece  There  different.  fields  the  for  /DATA SORT f u n c t i o n  drastically  member  represented.  the  to  number  the  represent  thirty-two  the  track  c o m b i n a t i o n of  be  shop  Column B r e f e r s  long.  numbers a v a i l a b l e ,  can  global  characters  develop  12345678 w i l l  fields  is  piece to  the  An a d d i t i o n o f  thirteen  will  third  all  100,000,000  are  use  the  number 23456789 w i l l  there  refers  more t h a n two members.  limitation  example,  A  most.  PL10PL16W200X22 and PL 1OPL16W610X113 w i l l name  the  the  Firstly,  keep  procedures  s t r u c t u r a l member.  at  be e i g h t  and  of  indicates  different is  fuctions  of  but  switch the  34 conversion to  routine  emulate  justified such  a  one  at  present  of  s u c h as  No s p a c e  is  because  this  each  file  name  a file  as  or to  member(s). member the  they  AE  are  The f i r s t  the  must  eight  a double  operations  will  piece.  angle  PL8 as  plate  bend,  fit,  the  total  later it  hours  in  refer  the  spent  a  file  first  eight  have  refer  piece.  punch,  w e l d and c l i p  worksheet  the  same  first  eight  to  as  f or  the  t  the  first  operations  will  Similarly,  second are  member  the  burn,  those  the of  template,  saw,  respectively.  on a l l  ensure  distinct.  performed  operations  PL10  from PL8W610X113.  a single  shear,  and  description  and t h e s e  to  the  PL8PL10.  a  the  3  double  be p a i d t o  operations  The t h i r t e e n  shear,  the  piece  thirteen  be  characters  distinish  thirteen  for  column AF i s  will  take  and  a  space  operations  operations  punch,  only  total  means  PL8W610X i n t h e i r  c a n change  only  layout,  any  the  of  later  not  remove  PL8 E  names  be u s e d  attention  PL8W610217 t o  i n c a s e of  double  two  first  2  In c a s e  description  will  the  are  is  well  Column  permit  b o t h have  The u s e r  P8W610X217 Columns F  name,  functions  Column D means  PL8.  will  not  may  there  the  Lotus  has  1-2-3  PL8W610X113 and PL8W610X217 w i l l  characters.  line  since  description example,  the  description  as  For  next  between  SORT  Such a t r a d e - o f f  QTY=2+3=5.  PL8 and P L 1 0 , t h e  Also,  characters  if  member e . g .  allowed  several  future.  e.g.  name and L o t u s w i l l  name.  be  in the  and  the  takes  because L o t u s  PIECE=2  description  it  function.  members u s e d ,  W150X24,  file  QUERY  limitation  number of  piece  because  drill, Finally,  operations  35 together. their  The p o s i t i o n  size  program. all  the  will  Lotus  1-2-3  the  novice  user  Lotus  should give  2 system  and  Lotus  1-2-3  manual  enough  guidance located  at  that  the  CA1.  the  Note  slash,  the  order.  user  should  and s t a r t on  cursor  names  of  to  to at  the cell  the  BA1.  /RANGE NAME LABEL RIGHT for  the  range  range names  column  t e s t e d by p r e s s i n g Since  individual  worksheets  four  BA.  the  macro  to  plotting master,  names,  i.e.  routines sort,  analyze  and  be  For  the  such  as [9]  help  and  messages before  and n u m e r i c a l  labels.  the  user  s h o u l d move  then  to  the  range  by  using  be p r o m p t e d  cover  all  the  T h e s e menus and macros can see  if  the  range  p r o g r a m has SORT,  converting,  should  menus  typed  pointer  be MASTER,  the  be  will  c o r r e s p o n d i n g menus and macros must file  the  menus  be a c t i v a t e d  The u s e r  "ALT" "M" t o  activated.  The  [7].  run  1-2-3  The column BA c o n t a i n s  and he c a n move t h e on  The  must  menus and macros and must  of  appropriate  market  BA1 and t h e  formula  size  routines.  user.  an a p o s t r o p h e  backslash,  cell  the  but macro  the  Using Lotus  Once t h e s e menus and macros a r e c o p i e d , the  typing  any book on t h e and t h e  the  the  their  individual  [8]  important  can change  messages  1-2-3,  are  w o r k a b i l i t y of  change  i n the  macro commands a r e cell  user  disk  help  indicated  of  the  s h o u l d not  release  as  the  fields  macro p r o g r a m ,  commands  locations  the  affect  words,  columns but  ,macro  the  not  In o t h e r  To copy  of  saved  plot  be  names  assumed  the  ANALYZE and saved  sorting, under t h e  under  be are  four PLOT. these  a n a l y z i n g and file  respectively.  names The  of  user  36 s h o u l d a l s o copy  the g l o b a l  data  file  to  test  the  macro  program.  4.3.1  CONVERSION The  the  conversion  opening  into  piece  different to  routine  menu w i l l files  To  of  start  RETRIEVE  routine  is  saved  function.  menu w i l l  automatically  menu w i l l  Once t h e  When  ask the user  the  global  will  prompt t h e u s e r  a  single  records  of  conversion  the  input  conversion  Figures routine  conversion  manipulate  the  4.4  is  data the  is  into  "DOUBLE"  uses  If  show  the  menu  item  called  will  data  be  input  of  the  file.  t h e /DATA QUERY f u n c t i o n  The i n p u t  is  piece  i n the  results  as  Either  file  file  a  next  such  single  records  shop d a t a  for  files.  the  the  opening  the s i n g l e  output  the  t h e next  an o u t p u t  piece  the  file  output  in  user  Then  the  conversion  chosen,  data  convert  file  and 4 . 5  routine  of  allowed.  on t h e g l o b a l  data.  the  t o be c o n v e r t e d .  by g r o u p i n g t h e d o u b l e  file.  retrieved,  t o name an i n p u t  file  the  should by  described  item  for the c h o i c e  Likewise,  converted  The  file  as  is  limited  the user  that  file  number o f  spreadsheet  and prompt  t h e macro p r o g r a m w i l l  "SINGLE".  data  data  or double  selected,  appear  the  Lotus  file  data  maximum  routine,  file  through  temporarily  Remember  as t h e MASTER  section.  item.  record is  into  global  The  the c o n v e r s i o n  previous  menu  the master 4.3).  each  t h e "MASTER" w o r k s h e e t  /FILE  w h i c h c a n be a c c e s s e d  convert  (figure  components  two.  load  ROUTINE  a r e assumed  to  t o be a t  37  the  upper  single those In  left  piece of  records  the  figure  double 4.6,  auto-execute for  it  is  BA3  typing  convert,  sort,  are  further  as  shown  figure  in  item  is  by  /fr  program t o  information  the  the  the  file.  user  plot,  to  the  is  the  A2000.  the  The  located  again  with range  an name  auto-execute  at  is  loaded  cell  the  invoke  i n MENUO.  BA5.  of  row i . e . the  item  BB7 is  123  cell  p r o g r a m as  input a data  to  Finally, CA1  to  shown  in  typed as  is  another  file  "home" p o s i t i o n  menu  of  the  combine the  be  items  BG7  is  If  the  invoke or  retrieved  exit the  figure  row  analyze  give  long  such as  will  sort,  item w i l l  spreadsheet.  MENU1  menu  chosen.  will  the  The  the  by  menu  They a r e  command /xmMENU1~ w i l l  The L o t u s  to  the  123 and h e l p .  functions  The n e x t  The command / f c c e { ? } ~ at  zero".  Lotus  messages s h o u l d be  c o l u m n CA o n l y .  the  file  about  that  i n d i v i d u a l worksheet  screen  cell  starts  S i m i l a r l y , when the  regular  the  Note  menu i t e m s  selected,  the  while  "MASTER" w o r k s h e e t  e x e c u t e d when  command. the  move  that  BA1.  the  A1000  be put a t  routine  macro w i l l  describe  menu MENU1.  chosen,  the  will  is  cell  of  macro " \ M " w h i c h can be a c c e s s e d  4.1.  commands b e i n g  The o u t p u t  at  will  "backslash  six  analyze,  BB6 w i l l  plot  is  This  at  next  cell  shows a n o t h e r  There  the  records  menu MENUO w h i c h  "ALT" " M " .  convert  placed  conversion at  which  the  MENUO.  the  be  piece  the  spreadsheet.  be e x e c u t e d once the  invokes  Cell  the  will  macro  "\0"  macro w i l l and  c o r n e r of  the  macro  help  item  user  more  4.7.  Note  labels  occupying  which  will  ask  global  shop  data  the  worksheet  input  data  and t h e  next  38 command  /xmCONVERT~  located The  below.  cells  There are  below  the  single  piece  file"  respectively.  will  invoke  data  the  DOUBLE l o c a t e d The  set  at  PIECE and t h e the  shorter output  replaced  by t h e  name  of  piece  data  set  the  data  fields.  one.  double  piece  ranges the  comments  those  piece  at  to  data  cell  BA20  and  fields  at  cell  Next,  the  field output  same  the  piece  the of  file  fashion.  macro commands w h i l e  the  be  will  saved  file that  are  input,  be  under opening  Similarly,  data Note  S are  will  r e t u r n the  executed. input  the  output  is  is  records  data  the  The macro w i l l fully  field  When a l l  set,  criterion  columns A to  single  records. are  the  The c r i t e r i o n  the  last  convert  in the  the  "Convert  double  ouput  Only  because  commands a r e  BB c o n t a i n s  4.3.2  range  will  are  menu.  respectively.  "HOURS" and t h e  file  of  is  "SINGLE".  DOUBLE  CONVERT  CONVERT  items  located  AT1 and A T 2 .  Finally,  menu when t h e macro  input  and c r i t e r i o n  extracted.  the  BA33  criterion  than the  menu  i n the  and d o u b l e  SINGLE  cell  cells  input  items  next  The commands / x g S I N G L E ~ and /xgDOUBLE~  macro SINGLE w i l l  is  used as  three  the  and " C o n v e r t t o  macros at  invoke  single  file"  A1000 by c o p y i n g the range  will  the  into  double  the  column  column BE h o l d s  the  commands.  SORTING ROUTINE The  sorting  containing instance,  routine  the  piece  the  same  description.  s o r t i n g program w i l l  sort  the  components the  sorts of  file  into  single  files For piece  39 file  into  W150X24, one  files etc  containing  as  component  description components  of  reduce  contain 1000  system  is  two  the of  are  itself  should give  are  been  for  this  large  100  The  a  there  as  PL8,  a r e more  piece  than  files,  in ascending  the  order,  the e.g.  enough  reason  the  of  10 e q u a t i o n s a  the  no  Meanwhile,  relationship  members,  say  In o t h e r Note  example,  PL8 and  words, that  in  determining  for  that  double and  for  both  PL8  It  seems  that  the  labour  hour  sometimes a p a r t i c u l a r  attached  to  other  The l a b o u r hour  members variables  be d e t e r m i n e d  through  the  for  some o t h e r  members,  s u c h as  the  piece  and I - s h a p e s ,  the  PL8W150X24 by  data are c o l l e c t e d .  is  be  10X10 m a t r i x and  s t r u c t u r a l members  For  member.  solve  10  decoupled.  is  that  with  is  piece  individual  to  is  same p r o b l e m can  independent. be  files  inefficient  there  single  a small angle,  member c a n o n l y  channels  if  routines  smaller  l a b o u r hour v a r i a b l e s  double piece  data.  into  solve  independent. the  and s o r t i n g  very  sets  to  can as  is  Since  are  repeated  s u c h as  form  piece  if  It  easier  treated  also  W150X24  to  such  be a c o m b i n a t i o n of  file  unknowns  into  equations  variables.  names  data  1000  equations  hence  work has  If  will  conversion  individual  data  member,  the  with  piece  and  files  more a c c u r a t e .  between any W150X24,  of  master  It  is  4.8. double  component  and s o r t e d  unknowns. result  the  same unknowns.  equations  converted  as  figure  only  a description.  the  the  such  the  The p u r p o s e to  in  these data  with  PL8W150X24 as  shown  one component  single  data  double  will  the be  40 easier file  to  obtain.  is  to  different will  group  members  then  members.  there  only  look  at  figure use  the  of This  The  been  limited  the  range  keep t r a c k  of  mixed  the  in  routine  will over  deleting  of  the  the  input  and o v e r the  records  have  at  BB15.  used  Finally,  been used  to  at  by the  files file  Next,  the  used.  Since  data  if  the  used  as  shown  in c e l l  program w i l l command  BA15 a l s o a  few  are  output  BA28.  in  no  description  description  efficiency  many  seen  files  and the  A  routines.  but  output  Such a r e d u n d a n c y  the  cell  name the  the  the  records  the  are  as  manipulation,  used  reduced  again.  there  different  only  file,  of  routine.  and s o r t i n g  and d o u b l e  descriptions  files  data  sorting  that  the  by c o p y i n g  be g r e a t l y  the  data  be  data  of  MLR a n a l y s i s .  First,  must  number  descriptions  macro l o c a t e d  name DESCRIP a t  / d q d d q ~ command.  cell  single  been a c h i e v e d  the  sorted  to  for  the  data  Such a r e d u c e d d a t a  conversion  sorting  to  master  many s m a l l  indicates  added.  member(s)  has  the  h e l p messages a r e q u i t e  have  the  for  the  converted  by  unknowns.  listing  /DATA QUERY f u n c t i o n  longer  The  eventually  files  between  4.10.  tricks  to  are  convert  according  shows a l i s t i n g  this  similarities  data  sorted  a few  input  4.9  way t o  contained.  Hence,  Figure  However,  it  further  become t h e  close  the  be  which c o n t a i n will  A logical  at  files. cell  E2  p r o g r a m must  of  are  usally  the  sorting  records is  avoided  BB30 i . e .  check  if  all  are by the the  /xi(C2=0)~/xmMENU20~  41  4 . 3 . 3 MLR ROUTINE W i t h e a c h component analysis and  obtain  a l g o r i t h m of  next the  to  be  r e q u i r e d by the divided cell  BB15 t o  empty  two  "operation"  operation said  to  will  done  from t h e  i.e.  column  F  deleted.  The with  done  using  by  second  part  the  only  maximum  operation  sixteen  after  requirement  thirteen  only  the  will few  example,  not  both.  sixteen  columns empty  must  be  The  because  BB45,  be  No  less are as  member  some o p e r a t i o n s  the  the  is  is  from  of  the  of  each  column  is is  first  one  result  in  not  being  of  course  regression  is  Since  this  variables,  the  than  or e q u a l  to  deleted.  This  each  member  will  have  all  will are  be p u n c h e d or  intercept  as be  deletion  function.  fulfilled  operations.  the  hence  independent  columns  usually  the  MLR r o u t i n e  itself.  a member can e i t h e r In c e l l  and  /DATA REGRESSION  allows  operations  for  regression  the  starts  way r o u n d w i l l  summed  this  can  entries  column AE t o  other  of  deletion  that  and  "ANALYZE"  the  zero,  the  that  which  sum i s  theory in  routine  thirty  MLR  listing  part  Note  i.e.  the  being  function  the  operation  not  the  with  deleted.  because  columns  concerned  If  name  MLR  The f i r s t  be summed.  last  show the  file  The  concerned  is  the  the The  Remember  The f i r s t  columns.  some empty  a  program.  be empty and  perform  be d i s c u s s e d  and 4.12  under  parts.  BB40 i s  will  MLR r o u t i n e .  saved  macro  into  4.11  the  can  l a b o u r hour v a r i a b l e s .  Figures  should  one  MLR a n a l y s i s  h e l p messages of  routine  have  the  the  chapter.  file,  in  conflict;  drilled  forced  to  be  but zero  42  by  the  command / i z .  the  case  of  program labour  would  The  true  for  and  fit,  be  labour  4.3.4  to  distribute  rather that  such as if  be the  the  the  because  s e t - u p time  explicit  of  drill  for  say  and w e l d .  s e t - u p time  is  the  set-up will  saw,  other  in the  into  s e t - u p time  operations  be a p p l i c a b l e  saw,  zero  as  t h a n h a v i n g an e x p l i c i t  combinations not  to  s e t - u p time  be  drill  combinations  Such  a  problem  distributed  into  the  variables.  plotting  for  each  routine  routine  will  then  hour v a r i a b l e s  should  help  needed.  messages  display  BB12  to  dividing  Figures  4.13  to  BB20 w i l l  calculate  absolute  plot  a  bar  shown  in  figures  the  degree  Similarly,  the  chart  of  routine.  4.15  to  4.17.  precision  macro ABS a t  on t o p of These  where  cell  the  graphs  attention  listing  The commands a t  cells  standard  by the  relative  error  cell  BA26  i.e. will  standard error  quantitative BA38 w i l l  by  coefficient  The s t a n d a r d e r r o r in  errors  help  relative  the  percentage.  and  The macro REL a t  showing  of  standard  standard  places  show the  standard error  l a b o u r hour v a r i a b l e .  i n terms  bar c h a r t .  pin-point  the  relative  standard error  and 4.14  plotting  the  relative  in a stacked  the  the  the  absolute  management  of  calculates  l a b o u r hour v a r i a b l e  a bar c h a r t and the  the  assumed  o r the  PLOTTING ROUTINE  labour  is  is  is  but w i l l  hour  error  in  certain  avoided  The  The  like  reason  operations  can  study  hour v a r i a b l e s  time.  of  this  The i n t e r c e p t  plot  as  indicates  terms a  [10]. stacked  43 bar  chart  labour  showing  hour  on  The upper after it  gives  items  4.15  to  the  component  right  the  the  columns  results  the  term at  mentioned  before.  standard error The  coefficient term  means  general, the  the  of  adequate. month,  200  shows  it  The p a n e l  right  below  number of  equations.  macro p r o g r a m . The  Their  units  of  are hours.  practical  compare.  of  to  To  to  the  Observations" used. the an  average  In  better infinite  should  be  p e r day and twenty d a y s  the  which a r e  standard error  their  the  total  refers  have  labour  d e g r e e of  The X c o e f f i c i e n t s  the  as  to  the  being  to  The  time  refers  give meaningful  refers  Since  magnitudes, to  A1003  the  standard e r r o r s are the  it  i.e.  "No.  monthly  l a b o u r hour v a r i a b l e s  follows.  observations,  records  item  cell  individual  set-up  records  Assuming  the  difficult  of  a  The n e x t  the  below  The  should  as  variable  term a t  ten  to  item  not  The  described  observations,  are  different  analysis.  refers  next  is  PL8 and W150X24.  deleted.  number  the  plotting  data  dependent  the  C200X15,  the  file  A1001  squared"  of  component  are  the  Since  s y s t e m of  variables  the  the  output  The  results  files  MLR  cell  larger  the  them.  the  the  observations  variables.  s t a n d a r d e r r o r s on t o p of  determination.  the  result.  number  of "R  of  are  are  of  MLR  "Constant"  hours.  4.17  hand p a n e l  empty  of  absolute  variables.  Figures routine  the  at  freedom  objective  and t h e  of  right  overcome  this,  the  the  below  labour  errors  of  A1007  l a b o u r hour v a r i a b l e s standard  hour  cell  listed  a  hour  are  are  of  quite  relative  44  s t a n d a r d e r r o r s a r e computed i n terms of give  a  better  picture  The b a r and s t a c k e d b a r should  the  Figure  4.18  help  of  the  charts  management  and 4.19  give  the  than  can  number unknowns  of  observations  in order  the  of  4.15  figures  evaluate  results  Since  unknowns, no s o l u t i o n  p r e c i s i o n of  to  PL8PL10 and PL 10W61 OX 113.  there be  of  found.  solution.  alone  to  variables. to  4.17  shop p e r f o r m a n c e .  the  are  must be g r e a t e r  t o have a  percentage  component  less  files  observations  Note than the  that  the  number of  Al:  Convert Sort Convert master A B  Analyze Plot Lotus123 Help global data f i l e into piece f i l e s C D E F G  A1 : Plot Lotus123 Help Convert l S o r t | Analyze S o r t p i e c e d a t a f i l e i n t o component d a t a f i l e s C D E F G A B  A1 : Convert Analyze A  A1 : Convert P l o t the A  A n a l y ze Plot Lotus123 Help Sort file user s p e c i f i e d component d a t a B C D E F G  Sort Analyze Plot residual error B C D E  A1: Convert Sort E x i t to Lotus A B  A1: Convert Sort H e l p menu A B  Figure  Lotus123  Help  Analyze 123 C D  Plot  Lotusl23  Help  Analyze  Plot  Lotusl23  Help  C  4.1  D  E  F  The O p e n i n g Menu  G  A  B  C  D  E  1 CQNTR-DU6 HAN PIEC QTY DESCRIPTION 1 2 11150X24 7 2 5432-50 1 4 PL8 21 3 5432-58 4 5432-58  14  |  4 PL8  5 5432-46 6 5432-17  I 1  4 H150X24 4 PL8  7 5432-17  7 29 29  8 5445-2 9 5445-2 10 5432-9 11 5432-40  9 21 7 14  1 I 1 1 1  4 PL8 4 C200I15 4 C200X15 6 U150I24 7 U150X24  12 5432-36  29  1  3 W1S0X24  13 5432-60 14 5432-5  22 1  14 PL8PL10 5 W150K24  15 5432-51 16 5442-23  7 29  17 5442-4  12 14  18 5432-9  22  19 5436-601 20 5442-34  2 14  1  F  G  H  I  J  2  4 PL8 16 PL10U610XU3  2 2  92 PL8PL10  2  20 PL8PL10  0  0  0  24  0 0  0 0  0 0  0 0  2 1  3 PL10U610H13  6 PL10U610I113 7 C200X15  K  L  H  N  O  P  Q  R  S  T  ITEN ILAY 1LSH IPSH 1PUN 1BUR ISAM 1DRI 1LIN 1BEN 1FIT 1HEL ICLI 0 0 0 4 32 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 24 0 32 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 8 0 0 44 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 24 0 0 16 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 8 0 0 0 0 0 0 0 0 0 0 6 0 16 0 0 0 0 0 0 0 0 0 24 12 0 68 0 0 0 0 0 0 72 0 0 0 212 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 6 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 20 0 0 0 0 92 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 12 0 0 0  0 0  0 0  0  0  0  0 0  0 2  14  0  0 0  0  0  0  56  0  0 B 0  0 0 6  0 0 0  0  2TEN 2LAY 1.5 2.25 0.5 8 1.25 1.75 1 3 12 12 8 0  0  0  0 0  0 0  0 0  0 0  0 0  0 0  3.5 7.25  2.75  0 0  0  0 0  0 0  0  0  0  0  0  0 0  0  0 0  0 0  0 16  0 0  0 0  0 4.25  1  4 PL8  0  0  0  0  0  0  0  0  0  0  16  0  0  0  0  112  0  0  0  0  0  0  0  5 PL8PL10  0  0  0  6  0 B  0 0  1.75  56 PL8  0  0  0  0  0  0  0  0  0  1  6 C200X15  0  0  0  0  0  24 0  0 0  0 0  0 0 0  32  0 0  16 0 0  0  0 0  0 0 0  6  4 PL8 4 PL8 4 C200X15 8 C200.15 3 PL8PL10  0 0 0  0  1 1 1 1  0 0  0 16  0 0  6 1.5 1.5  0 0 0  0 0 0  0 0  0 0  0 0 0  0 0 0  0 0 0  0 0  3  4 8 0  4 52  0  0 0 3  0 0 0 0  0  0  0 0 0  3 6 0  The G l o b a l  12 3  0  0 0 64  2 1 0  0 0  4.2  0 0  0 0 0  1  Figure  0 0  0 0 56  0  0  0 0  0  0 0 0  0 0 0  22  2  0 0 0  0 0  23 5432-56  21 23 25  3.5  0 0  0 0  9  29 29 29  0  0  29  25 5436-513' 26 5432-31 27 5432-31 28 5442-37 29 5442-33 30 5442-63  0  0  22 5432-51  21  0  0  21 5436-513  24 5442-6  0  8  0 0  16  0  0  0 0  0 56  0  0  8 1.5  0 0  0  U V U i Y I AA AB AC AD AE AF 2LSH 2PSH 2PUN 2BUR 2SAU 2DRI 2LIN 2BEN 2FIT 2HEL 2CLI HOURS  0  0  32  0  0  40 0  0  0  1  0 0  0  0  4  4  0  0  0  0  0  0  0  0  0  0  t  0  0  0  0  0  0  0  0  0  7  5  Data  File  1  47  Operations: Cont/Dwg  1st  • • •/ • •  •  set  ••••••* •»•••*•  SINGLE PIECE DATA F I L E CONVERSION PROGRAM  Operations: Cont/Dwg  1st  set  2nd  set  • •/ • •  • •/ ••  mm/ mm • m / mm • m / mm • •/ ••  m m/ m m MASTER GLOBAL DATA F I L E  Operations: Cont/Dwg • • • / mm • ••/••  1st  set  •  •••••••  •  2nd  ••••••• •>••••••  DOUBLE PIECE DATA F I L E  Figure  4.3  The F u n c t i o n of  the  Conversion  set  Routine  A B C D £ I CONTR-DHG HAN PII X QTY DESCRIPTION 2 5432-50 7 2 U150I24 3 5432-58 21 1 4 PLB 4 PLB 4 5432-58 14 5 5432-46 7 1 4 H150I24 4 PL8 6 5432-17 29 7 5432-17 29 1 4 PLB 4 C200I15 8 5445-2 9 9 5445-2 21 1 4 C200J15 6 U150X24 10 5432-9 7 11 5432-40 14 1 7 U1S0X24 3 U1S0X24 12 5432-36 29 13 5432-5 7 1 5 H150X24 14 5432-51 29 1 4 PL8 15 5436-513 9 I 7 C200J15 4 PLB 16 5432-51 29 17 5432-56 22 1 56 PLB 18 5436-513'29 6 C200115 19 5432-31 29 1 4 PLB 20 5432-31 29 4 PLB 21 5442-37 21 1 4 C200I15 22 5442-33 23 8 C200I15  Figure  4.4  F S H I J K L M N O P Q R S 1TEH 1LAY 1LSH 1PSH 1PUN 1BUR ISAM 1DRI1LIN 1BEN lFIT 1UEL 1CLI HOURS 0 0 0 0 0 0 4 32 0 0 0 0 0 1.5 0 32 0 0 24 0 0 0 0 0 0 0 0 2.25 0 0 0 0 0 0 0 0 0 0 0 0 0 0.5 0 0 0 0 0 0 0 B 0 0 44 0 0 B 0 0 0 0 24 0 0 0 0 0 0 0 0 1.25 0 0 0 0 0 0 0 0 0 0 16 0 0 1.75 0 0 0 0 0 8 0 0 0 0 0 0 0 1 0 0 0 0 0 6 0 16 0 0 0 0 0 3 0 0 0 0 0 12 0 68 0 0 24 0 0 12 0 0 0 0 0 0 0 212 0 0 72 0 0 12 0 0 0 0 0 6 0 0 0 0 0 0 0 8 0 0 0 0 0 0 0 0 0 0 92 0 0 8 0 0 0 0 12 0 0 0 0 0 0 0 0 1.5 0 0 0 0 0 6 0 16 0 0 16 0 0 4.25 0 0 0 0 0 0 0 0 0 0 16 0 0 1.75 0 0 0 112 0 0 0 0 0 0 0 0 0 5 0 0 0 0 0 6 0 16 0 0 32 0 0 6 0 0 0 24 0 0 0 0 0 0 0 0 0 1.5 0 0 0 0 0 0 0 0 0 0 16 0 0 1.5 0 0 0 0 0 4 0 4 0 0 0 0 0 3 0 0 0 0 0 8 0 52 0 0 0 0 0 6  An Example  of  the  Single  Piece  Data F i l e  F G H I J K L M N O P Q R A B C D E 1 CONTR-DUG MAN PIEC BTY DESCRIPTION ITEM 1LAY 1LSH 1PSH IPUN (BUR ISAM 1DRI 1LIN 1BEN 1FIT 1HEI ICLI 2 5432-60 22 2 14 PL8PL10 0 0 0 0 20 0 0 0 0 0 0 0 0 16 PL10W610X113 12 2 0 0 0 14 0 0 0 0 3 5442-23 0 0 0 0 0 14 4 5442-4 3 PL 101(6101113 0 0 0 2 0 0 0 0 0 0 0 0 0 2 5 5432-9 6 5436-601 7 5442-34 8 5442-6 9 5442-63  22 2 14  2 2  92 PL8PL10 20 PL8PL10  0 0  0 0  0 0  56 24  0 0  2  6 PL10H610X113  0  0  0  0  8  21  2 2  0 0  0  25  5 PL8PL10 3 PL8PL10  0 0  6 3  8 3  0  0  0  0 0  0 0  0  0  0  0  0 0  0  0 0  0  0  0 0  0  0 0  0 0  0  0 0 0 0 0  0 0  56 0  0  0  0  0 0  0  T U V U I Y I AA AB AC AO A£ AF 2TEH 2LAY 2LSK 2PSH 2PUN 2BUR 2SAM 2DRI 2LIN 2BEN 2FIT 2MEL 2CLI HOURS 3.5 0 0 0 0 0 0 0 0 0 0 0 8 0 12 0 0 0 0 0 0 0 0 0 0 0 0 2 3 0 0 0 0 0 0 0 0 0 0 0 1 0 7 0 64 0 0 0 0 0 0 0 0 0 56 0 3.5 0 0 0 0 0 0 32 0 0 0 40 0 0 0 0 7.25 0 0 0 0 0 0 0 0 1 0 0 0 0  0  0  4  4  0  0  0  0  0  0  0  0  2.75  0  0  1  0  0  0  0  0  0  0  0  0  1  Note: These shop data are a r b i t r a r i l y d e r i v e d and do not necessary r e f l e c t the shop performance of Coast S t e e l F a b r i c a t o r s L t d .  Figure 4 . 5  An Example of the Double P i e c e Data F i l e  50  1 2 3 4 5 6 7 8 9 10 11 12  BA \0  BB BC /xsHENUO*  \M  /xiHENUO*  MENUO  Convert Sort Analyze Plot Lotus123 Help Convert aasSort piece Analyze coiPlot residuExit to LotHelp ienu /xsMENUl* /frSORT* /frANALYZE*/frSORT* /xq CgotoJCAP /xiHENUO* Data f i l e ? Hit return to continue (hone} /fcce<?>*  NENU1  13  14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42  BO  BE  BF  BG  /XBCONVERT*  CONVERT Single Double Help Convert to Convert to Help ienu /xgSINGLE* /xgOOUBLE* {goto)CA34* /xiCONVERT* SINGLE  /cAl..Sl*A1000* /cCl..Cl*ATl* {goto)AT2*l* {goto}Sl*{endXdown}* /dqri..{ho«e)* cATl..AT2* oA1000..S1900*eq {goto)S1000* HOURS" /fxvSlNGLE* A1000..Sl900*r(esc} /xiHENUO*  DOUBLE  /cAl..AF1*A2000* /cCl..C1*AT1* {goto>AT2 2* /dqrilhoieKendKdown}.. {hoieXendJCright}* cATl..AT2* oA2000..AT2900*eq /fxvDOUBLE* A2000..AT2900*r(esc} /xaHENUO* %  F i g u r e 4.6  . set: output f i e l d at A1000 c r i t e r i a range at ATI..AT2 copy 1 to AT2 anchor at the lower right corner data querry: input range c r i t e r i a range output range go to 2TENP replace 2TEMP with HOURS f i l e extract: SINGLE extract range back to H E N U O * set: output f i e l d at A2000 c r i t e r i a range at ATI..AT2 copy 2 to AT2 data querry: input range c r i t e r i a range output range f i l e extract: DOUBLE extract range back to MENUO*  The Menus a n d M a c r o s o f t h e C o n v e r s i o n  Routine  51  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 28 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52  Figure  CA HELP 0 : OPENING HENU - Can be accessed froa Lotus spreadsheet by holding "ALT" key while pressing "H" EXIT HENU  By choosing "Lotus 123" option or h i t t i n g "ESC"  TO START -  You should have prepared a data f i l e which have the sane foraat as the GLOBAL data f i l e resided in this disk.  GLOBAL DATA —  The global data f i l e contains f i e l d s : CONTR-DHG = Contract-Drawings KAN = Labour nuiber PIECE = No. of different aeabers QTY = Total nuiber of aeabers DESCRIPTION = Description of the aeaber(s) 1OPERATION = Refers to the f i r s t aeiber 2QPERATI0N = Refers to the second aeabers OPERATION = Labour operation performed on the aeaber TEH = Teiplate LAY = Layout LSH = Angle Shear PSH = Plate shear PUN = Punch BUR = Burn cut SAW -- Saw DRI = D r i l l LIN = Line punch BEN = Bend FIT = F i t t i n g UEL = Melding CLI = Clipping HOURS = Tiae to per f o r i the operation  HELP 1: CONVERT  Converting the GLOBAL data f i l e into SINGLE and DOUBLE data f i l e s  DATA FILE  Enter the naie of the data f i l e to be converted  SINGLE  Single f i l e i s a f i l e generated f r o i the global data f i l e which contains data with ONE type of aeiber only  DOUBLE  Double f i l e i s a f i l e generated froa the global data f i l e which contains data with TWO types of aeabers only  PATH:  4.7  HASTER  The  I I 1 I I  SINGLE  SORT  ANALYZE  PLOT  DOUBLE  SORT  ANALYZE  PLOT  H e l p M e s s a g e s of  the  Conversion  Routine  52  Operations: 1st set Cont/Dwg L a y L . S h . . D r i l l  SORTING PROGRAM Operations: 1st set Cont/Dwg Temp L a y . . . F i t • ••  /  •• •/  •• •/  •••  • • • ••• ••• •••  •••  Hrs  •• •/ •• •/  ••• •••  •••  L75X75X6 (only required are l i s t e d )  operations  •••  Operations: 1st set Cont/Dwg Temp L a y . . P u n c h H r s • ••  • • •  •••  Hrs  •• • • •  PL8  (only  •••  required operations are l i s t e d )  SINGLE PIECE DATA F I L E ( a l l o p e r a t i o n s are listed )  Operations: 1st set C o n t / D w g Saw D r i l l . . F i t —7*  • »•  • • •  W150X24 (only required are l i s t e d )  Figure  4.8  The F u n c t i o n  of  the  Sorting  Routine  Hrs  • » •  operations  53  I 2 3 4 5 6 7 8 9 10 II 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31  8A \0  BB BC /xiHENim*  \H  /xiHENU2Q*  MENU20  Convert Sort Analyze Plot Lotusl23 Help Convert lasSort piece Analyze coiPlot residuExit to LotHelp ienu /frHASTER* /xiHENU2P /frANALYZE""/frPLOT* /xq {goto)CAP /XBMENU20" Data f i l e ? Hit return to continue {hotel /fccei?)* /xgSORr  HENU21  SORT  BD  BE  BF  B6  /xi^Or/xirOWO" {H0HE}/cAl..AFPAl000* {60T05E1* /cfENDHDOHNPATP (goto)E2* ^"OESCRIP* /dqiCHOKE} ..{ENDKR16HTKENDKDQWNP CAT1..AT2* OA1000..AF2000* eq {GOTO}A1000* /fxv  i f C2 = 0, end loop and MENU20 copy output fields at A1000 go to description copy description to colmn E go to description copy description into DESCRIP data querry: input range  *A1000..AF2000V{esc} /dqddq* /xgSQRT*  extract range delete the extracted records start the loop again  c r i t e r i a range output range extract; quit go to output range f i l e extract by DESCRIPtion  DESCRIP  Figure  4.9  The  Menus and  M a c r o s of t h e  Sorting  Routine  54  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31  CA HELP 20: SORT  Sort the SIN6LE or DOUBLE data f i l e into component f i l e s containing the sane type of aeaber(s) only  PURPOSE  The purpose of the CONVERT and SORT routines i s to reduce the global shop data into saaller and simpler f i l e s containing the sane type of leibers. It i s nore efficient to solve a 100 sets of 10x10 tatrix than to solve a 1000 xlOOO uatrix.  METHOD  The DATA QUERRY function i s used to sort the piece f i l e . The first description of the data records ( i . e . entry E2) i s used as the CRITERIA to EXTRACT the records out. These records will becoae a data f i l e theiselves by the FILE XTRACT function. The extracted records will be deleted by the DATA QUERRY DELETE function. Thus the next c r i t e r i a will be replaced by a new one and the process repeats again u n t i l l a l l the records are extracted. The QTY (i.e. entry C2) i s used to deteraine the end of the loop.  ERROR  One of the possible errors i s the component f i l e lay have already existed f r o i previous run. Hence, the user should renaie existed component f i l e s with a different naies.  OPENING MENU - can be accessed froa Lotus spreadsheet by holding "ALT' key while pressing "N" FIELDS  Figure  4.10  There are a HI. of 32 fields i n DATA QUERRY  The H e l p M e s s a g e s of  the  Sorting  Routine  55  I 2 3 4 5 6 7 8 9 10 II 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51  BA \0  BB BC /xiHENU3P  \H  /x»r1EMU30*  HENU30  Convert Sort Analyze Plot Lotusl23 Help Convert aasSort piece Analyze coaPlot residuExit to LotHelp ienu /frHASTER* /frSORT* /xaHENU3P /frPLOT* /xq {gotoJCAP /xaHENU30* Data f i l e ? Hit return to continue (hote) /fcce{?P /xgDELETE*  HENU31  DELETE  BD  /xilSU«(AEl..AE30)<=0 /«dcAEr /xifJSUH(ADi..AD30X=0 7wdcADP /xilSUN(ACl..AC30X=0 7ydcACP /xi§SUr1(ABl..AB30X=0*/wdcABP /xiiSUM(AAl..AA30K=0 7wdcAAP /xi«SUH(Zl..Z30X=0VwdcZP /xi§SUM(Yl..Y30X=0VwdcYP /xi6SUH(Xl.J30X-0VwdcXP /xi§SUH(Wl..W30X=0 7wdcWP /xilSUH(Vl..V30X=0*/wdcVP /xi§SUH(Ul..U30X=0'7vdcUP /xi§SUH(Tl..T30X=0*/wdcTP /xiiSUH(Sl..S30X=0 /wdcSP /xi«SUH(Rl..R30X=0"7vdcRP /xi§SUH(Ql..Q30X=0VwdcQP /xi@SuH(Pl..P30X=0VvdcPP /xiiSUH(Q1..030X=0Vwdc0P /xi§SuH(Nl..N30X=0*/vdcNP /xi§SUH(Hl..M30X=0'7vdcHP /xi6SU(1(Ll..L30X=0VwdcLP /xilSUH(Kl..K30X=0VwdcKP /xi«SUH(Jl..J30X=:0VwdcJP /xilSUH(Il..I30X=0 /wdcIP /xi8SUH(Hl..H30X=0VwdcHP /xi§SUH(G1..630X=0Vwdc6P /xi8SUH(Fl..F30X=0VwdcFP {goto}F2 /drrx..{endXright) UeftKendXdownP yCendXrightL-IendlCdown}'* oAlOOO'Mzg {gotolAlOOO* /cE2*DESCRIP* /fXV %  BE  BF  B6  delete eapty coluans:  ,  ,  use 8SUH function to check i f the colunn i s eapty  ,  i f yes, delete coluan i f no, check the next one on the l e f t  ,  note the deleting starts froa the right end and proceed to the left  ,,  %  ,>  DESCRIP  F i g u r e 4.11  *Al..AT1500*r{esc} /xaMENOr  go to the f i r s t operation DATA REGRESSION RESET X-range Y-range Output;go go to OUTPUT range copy description into DESCRIP FILE EXTRACT VALUE description as f i l e naae extract RANGE back to NENU30  The Menus and Macros of the A n a l y z i n g Routine  56  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30  Figure  CA HELP 30: ANALYZE  The ANALYZE routine i s composed of two parts. The f i r s t part i s to delete the eapty colunns which i f present will screw up the sebsequent MLR analysis. The second part i s to per fori the MULTIPLE LINEAR REGRESSION (MLR) by using the DATA REGRESSION function provided by the new release of Lotus 1-2-3 Version2.  DELETE  The eapty columns are deleted by the WORKSHEET DELETE COLUMN function. The @SUM s t a t i s t i c function i s used to deteraine i f the coluans contain zero entries. It i s very iaportant to delete these empty columns (eapty coluan refers to the coluan with a l l the entries = 0 under a particular operation) because a deleted f i l e will aean a snail aatrix to be solved and also the algoritha of the present MLR function will not work i f eapty coluans are present.  MLR  Multiple linear regression w i l l be performed on the data f i l e with the following RANGES: X-RANGE = The operation range Y-RAN6E = The hour range OUTPUT-RANGE = The result of the MLR analysis and will be located at c e l l A1000  OPENING MENU ~ can be accessed froa Lotus spreadsheet by holding "ALT' key while pressing *M* OPERATIONS  4.12  There are a max. of 16 operation coluans  The H e l p Messages of  the  Analyzing  Routine  57  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23  BA \0  BB BC /xih"ENU41*  \H  /xit1ENU40*  HENU40  Convert Sort Analyze Plot Lotusl23 Help Convert lasSort piece Analyze coipPlot residuExit to LotHelp lenu /MASTER* /frSDRT* /frANALYZE* /xiHENU41* /xq* {gotoJCAl* /x•HENU40 • Data f i l e ? Hit return to continue {hoie)/fcce{?P data f i l e to be plotted {gotolAtOOO* go to MLR output range {goto>C1008* go to STD. ERR of Coef. /c..{end}{right}* copy this row of data (downPCdown} into the row below i t +<up}/{up}{upm00 I ERROR = ERROR/VALUE /c\.{end}{rightP {goto>A1009* put a f i e l d naie for I Std. Error of Coef.* this I ERROR row /x«HENU42*  BE  BF  B6  ,  HENU41  J  s  MENU42  2 4  25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51  BD  Relative R.Absolute R.Henu Lotusl23 Help Plot rel at i Plot absoluBack to openExit to LotHelp aenu /xgREL* /xgABS* /xir1ENU40* /xq* {goto}CAl*/xiMENU42*  REL  /cE2*DESCRIl* copy the description to x-range {goto)C1009*/grgtb graph reset graph type bar a..{endHright>* A-range olaX STD. ERROR* A-legend tfBAR CHART SHOWING Z STD. ERROR* t i t l e f i r s t tx DESCRI1 t i t l e X-axis = description *tyPERCENTAGE* t i t l e y-axis qxFl*x..{endKright}{left}*v quit; x-range = operations; view ncBAR*q* naae create /xiHENU42* back to HENU42  ABS  /cE2*DE5CRI2* copy the description to x-range {goto)C1007*/grgts graph reset graph type stack a..{endHright)* A-range b(down) B-range ..{endHright}* olaLABOUR HOUR* A-legend IbABS. STD. ERROR* B-legend tfSTACK BAR SHQUIN8 ABS. STD. ERROR* tx title first DESCRI2 t i t l e X-axis = description *tyH0URS* t i t l e y-axis qxFl*x..{endKright){left)*v quit; x-range = operations; view ncSTACK*q* naie create /xiHENU42* back to HENU42 Figure  4.13  The  Menus and  M a c r o s of  the  Plotting  Routine  58  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19  Figure  CA HELP 40: PLOT  There are two ways to see the distributions of the results. The I graph shows how the STAN0AR0 ERRORS of coefficients (i.e. LABOUR HOUR VARIABLES) vary i n t e n s of percentage alone. The STACK graph shows the ABS. STD. ERRORS on top of the COEFFICIENTS ( i . e . LABOUR HOUR VARIABLES). These graphs should help the aanageaent to pin-point places where attention i s neede.  GRAPHS  The graphs are naaed as BAR and STACK respectively. Since these graphs are not saved e x p l i c i t l y , the user aust use GRAPH NANE USE SAVE to save thea peraanently  HARDCOPY  a hardcopy of the observed graphs can be obtained by using the Lotus Printgraph on graph f i l e s  0PENIN6 NENU -- Can be accessed froa the Lotus spreadsheet by holding 'ALT" key while pressing "H"  4.14  The H e l p M e s s a g e s of  the  Plotting  Routine  A B I CONTR-0W6 HAN 2 5445-2 9 3 5445-2 21 4 543G-513 9 5 5436-513' 29 6 5442-37 21 7 5442-33 23  1000 1001 1002 1003 1004 1005 1006 1007 1008 1009  gure  C D E PIEC QTY DESCRIPTION 1 4 C200X15 1 4 C200X15 1 7 C200X15 1 6 C200X15 1 4 C20OX15 1 8 C200X15  F 6 H I 1BUR 1DRI IF IT HOURS 0 8 0 1 6 16 0 3 6 16 16 4.25 £ 16 32 6 4 4 0 3 8 52 0 6  BAR CHART SHOWING % STD. ERROR  B C D Regression Output: Constant Std Err of Y Est R Squared No. of Observations Degrees of Freedoi X Coefficient(s) Std Err of Coef. I Std. Error of Coef.  4.15  0  1.136750 0.795632 6 3  C2COX13 (771  0.230052 0.083943 0.100996 0.120980 0.030084 0.036905 52.58817 35.83920 36.54103  The R e s u l t s of the Component F i l e  K STD. ERROR  STACK BAR SHOWING A B S . STD. E R R O R  C200X15 cn ABS. STD. ERROR  A S C O £ 1 C0NTR-DU6 HAN PIEC QTY DESCRIPTION 2 5432-58 21 1 4 PL8 14 1 3 5432-58 4 PL8 4 5432-17 4 PL8 29 1 4 PL8 5 5432-17 29 1 4 PL8 6 5432-51 29 1 4 PL8 7 5432-51 29 1 8 5432-56 22 1 56 PL8 4 PL8 9 5432-31 29 1 29 1 4 PL8 10 5432-31  1000 1001 1002 1003 1004 1005 1006 1007 1008 1009  F 6 H I J 1LAY 1PSH 1PUN 1FIT HOURS 32 0 24 0 2.25 0 0 0 0 0.5 0 0 24 0 1.25 0 0 0 16 1.75 0 0 12 0 1.5 0 0 0 16 1.75 0 112 0 0 5 0 24 0 0 1.5 0 0 0 16 1.5  BAR CHART SHOWING % STD. ERROR 100  B C D Regression Output: Constant Std Err of Y Est R Squared No. of Observations Degrees of Freedoi X Coefficient(s) Std Err of Coef. I Std. Error of Coef.  Figure  4.16  1PSH 1771  0.464709 0.914567 9 5  1PUN  X STCTERHGR  STACK BAR SHOWING ABS. STD. ERROR 0.13 T  —  0.020312 0.045426 0.066666 0.104166 0.019483 0.004057 0.017318 0.016768 95.91903 8.931051 25.97807 16.09802  The R e s u l t s of the Component F i l e  PL8  E3  LABOUR HOLM  "¥^3  ASS. SIO. EJWOR  1FII  1 2 3 4 5 6 7  1000 1001 1002 1003 1004 1005 1006 1007 1008 1009  gure  A B C D C0NTR-DU6 HAN PIEC QTY 5432-50 5432-46 5432-9 5432-40 5432-36 5432-5  I J 6 H 1BUR ISAM 1DRI 1F1T HOURS 0 0 32 1.5 0 8 44 8 12 12 68 24 0 12 212 72 0 6 8 0 0 8 0 92  BAR CHART SHOWING % STD. ERROR  F  DESCRIPTION y150X24 W150X24 W150X24 M150X24 y150X24 H150X24  4fiO  B C D Regression Output: Constant Std Err of Y Est R Squared No. of Observations Degrees of freedoi X Coefficient(s) Std Err of Coef. 7. Std. Error of Coef  4.17  0 3.571181 0.654731 6 2 0.811904 0.216078 0.019865 0.102941 0.276692 0.908033 0.020706 0.035419 34.07945 420.2331 104.2375 34.40709  1BUR  1SAW  1DRI  IFTT  W160X24 1771  * STD. ERROR  STACK BAR SHOWING ABS. STD. ERROR 1.2  The R e s u l t s of t h e W150X24 Component F i l e iB U R  i s«w  I  M  W160K24 [771  LABOUR HOUR  fV\l  AOS. STD ERROR  inr  1 2 3 4 5 6  A B C 0 E C0NTR-DW6 NAN PIEC QTY DESCRIPTION 5432-60 14 PL8PL10 22 2 5432-9 92 PL8PL10 22 2 5436-601 20 PL8PL10 2 2 5 PL8PL10 5442-6 21 2 5442-63 3 PL8PL10 25 2  F 6 H I J K L tPSH 1PUN 1CLI 2PSH 2PUN 2CLI 20 0 0 0 8 0 56 0 56 56 0 64 24 0 0 0 40 32 6 8 0 4 4 0 3 3 0 1 0 0  HOURS 3.5 7 3.5 2.75 1  Error lessage froa Lotus: Too feu observations for nuiber of variables  Figure  4.18  The R e s u l t s of  the  PL8PL10 Component  File  1 2 3 4  A B C D E C0NTR-DW6 HAN PIEC QTY DESCRIPTION 5442-23 12 2 16 PL10H610X113 5442-4 14 2 3 PL10W610X113 5442-34 14 2 6 PL10H610X113  F 8 H I 1PSH 1PUN 2BUR HOURS 0 14 2 12 2 0 1 3 0 8 I 7.25  Error lessage froe Lotus: Too feu observation for nuiber of variables  Figure  4.19  The R e s u l t s  of  the  PL10W610X113 Component  File  Chapter 5 THEORY OF MLR  5.1  SYSTEM OF EQUATIONS In  system  order of  matrix  evaluate  A  represents piece  vector  of  the  x represents  operations  system  of  the  the  solution number  there  will  number o f for  by  the  has  of  of  exist  the  the  5.1.  equations  the  done  i n the  perfectly  b probably  64  will  is  have an  a  system  there the  not  a  equals case, large  f a b r i c a t i n g shop  system  fits  on  shop  for a r e a l  When a  means t h a t  are  An example of  fabricating  the  a  spent  t h a n unknowns b e c a u s e  columns of A .  on  independent e q u a t i o n s  t h a n unknowns,  The  observations.  The s y s t e m Ax = b w i l l  operations.  vector  A.  from t h e  fewer  x that  solved.  worker has  But i n g e n e r a l ,  Inconsistency  variables, a  hour v a r i a b l e s t h a t the  be o b s e r v e d  a vector  be  different  will  equations  words,  in  matrix  number o f  unknowns.  hour  operations  time  in  be more e q u a t i o n s  inconsistent.  other  if  relatively  more  not  figure  of  labour  the  algebraic equations  illustrated exact  set  contained  labour  Ax = b must  a component  b e i n g d e t e r m i n e d , and b i s the  the  algebraic equations  particular The  to  is  Ax = b  s a i d to  probably data b,  be  does or,  in  be a c o m b i n a t i o n  65 5.2  REGRESSION ANALYSIS Inconsistency  shop d a t a a s particular the  the  is  t i m e of  piece  will  same  job.  fluctuate  solution  x.  Rather  represents  the  best  s h o u l d be u s e d . give  this  it  absolute  objective  of  2 to  an  3 minutes  estimate  of  Linear  distance  that which  fit  minimizing  x.  the  is  closest  geometry  of  to  figure  space  can  be  perpendicular  b  in  5.2, the  represented to  the the  finding column  exact  x,  which  used  the  A  [11] the  which The  number  has  the  shown  p r o j e c t i o n Ax  space.  From  vector  Since  vector  to  actual  b and A x .  residual error  any  an  E = ||Ax-b||  column s p a c e . by  the  m i n i m i z a t i o n and  the  (Ax-b)  t h e column  A y , Ay must  ( A x - b ) or  property A Normal  between  Strang  to  do  (MLR) i s  vectors  this  For  hour v a r i a b l e s ,  to minimize E = | | A x - b | | .  equivalent  must be p e r p e n d i c u l a r t o  is  for  on  expect  labour  a  a hole  to  solution  The e r r o r  between  drill  to  Regression  has been c h o s e n .  E is  to  workers.  minutes  approximate time  t h e MLR i s  squares  5  the  methods c a n be used t o a c h i e v e  least  different  4 to  global  from day t o day  approximate  solution  o b s e r v e d t i m e and t h e the  between  the  same o p e r a t i o n on  does n o t make s e n s e  Multiple  average  the  slightly  worker B may t a k e Thus,  phenomenon f o r  performing  w o r k e r A may t a k e  C200X21 w h i l e  of  common  same w o r k e r and g r e a t l y  example,  is  a  equation  T  of  orthogonality  = transpose  of A  be  66  Hence,  the  approximate rp  normal  equation  minimum. sense  that  vector will  5.3  Note  _  solution  A Ax-A b = 0 that  x is  the  the  Ax c l o s e s t  be p r e s e n t  Hence,  it  is  must  satisfy  the  rp  A Ax = A b  E  to  be  x and x a r e d i f f e r e n t  in  the  approximate  b.  unless  or  vectors  best to  x  rp _  rp  of  x  which  residual  a perfect  for  error  gives E =  the  ||Ax-b||  fit.  ALGORITHM OF MLR The a l g o r i t h m of  equation  first  MLR Macro p r o g r a m s e t s  and t h e n  Normal e q u a t i o n :  solves  the  equation  up for  the  normal  x.  T T A Ax - A b = 0 T T A Ax = A b [A]  In  the  computer,  T  [A]  i  =[A]  we c a n s t o r e  T  b  A and b t o g e t h e r  with  their  T products data  after  storage,  p r e - m u l t l p l i c a t i o n by A  reduce  the  efficiency  of  i.e. [A]  Once  for  the LHS  T  [A | b ] = [ A A | A b ]  normal matrix  c o r r e s p o n d i n g RHS w i l l  T  equation to give  the the  T  has  been  identity average  computed, matrix  solution  x.  we can  and  the  67  T  —  T  [A A] x = A b  [I]  |  row  i  = x  operation  T The n e c e s s a r y be  reducible  non-zero can  be  pivots  testing non-zero  to  the  along  found a l o n g  no u n i q u e  has  and s u f f i c i e n t  identity the  pivot  along  been c o m p u t e d ,  approximate  Hence,  existence  solution  matrix  diagonal.  a column,  solution.  the  requirement  the  part  a solution  each  column.  x  of is  is no  matrix  of  the  If  for matrix  is  that  singular  if  c o r r e s p o n d i n g RHS v e c t o r ( f i g u r e 5.3).  will  are  and has  there  identity  to  pivot  algorithm  find  Once t h e  there  non-zero  the to  A A  for is  a  matrix be  the  OBSERVATIONS OF A TYPICAL FROM A F A B R I C A T I O N SHOP:  2 4 1 3 6  SAW SAW SAW SAW SAW  + + +  8 DRILL 2 DRILL 2 DRILL  +  8 DRILL  4 1 3 6  10 2 12 20  HOURS = 8 = 10 = 5 = 9 = 21  FIT FIT FIT FIT  0 10 2 12 20  8 2 2 0 8  '2  + + + +  COMPONENT  8 10 5 9 21  \  where  Figure  A =  5.1  2 4 1 3 6  8 0 2 10 2 2 0 12 8 20  A System o f  /8  / SAW \ X  =  DRILL  y  F I T j  b  10 5 9 21  Algebraic Equations  69  a s p a c e d e f i n e d by the c o l u m n s of A  Figure  5.2  A G r a p h i c a l I l l u s t r a t i o n Showing the M i n i m i z a t i o n of the R e s i d u a l E r r o r  70  INPUT: Global  data e.g.  from component  file  F o r L75X75X6, t h e r e a r e a number of g l o b a l shop d a t a f o r the f o l l o w i n g operations: -Layout - A n g l e Shear -Punch  T r a n s f o r m g l o b a l shop t d a t a t o m a t r i x form AA  ML  V SOLUTION:  No u n i q u e solution  L a b o u r Hour V a r i a b l e s e . g . Time f o r -Layout - A n g l e Shear -Punch  V END OF PROGRAM  Figure  5.3  A Flow C h a r t  for  the  MLR A a n a l y s i s  Chapter  6  CONCLUSIONS A computer  p r o g r a m on c o s t  accurately  performs  statistics program  r e q u i r e d has  BASIC  Fabricators reduced  A procedure  total  has  used  a new  s e r v e as  a powerful  labour  operations.  estimating This  but  Lotus  superior  to  by  Coast  the Steel  program  has  labour  hour  the  the  plant,  this  studies  continuous  hour  and  30%.  In  time  of a  obtain  procedure w i l l  that  brought  steel  by Y . C .  case  of  the  procedure  will  of  the  various  operation provide  variables  on  of  of  a  feedback  used  a different  in  earlier  real  the  in  the  research  was  two  is  application  This  and  used  Leung.  cost  1-2-3  in  Leung [2]  by Y . C .  the  structures.  approach  Regression  The  concluded  continuation  proposed  for  For  labour  of  operational  Forde  tool  the  and  estimation  U.B.C.  fabrication  this  project  a  such  time  sortings  program.  successful  is  that  and  utilizing  The management  found  plant,  update  developed  estimating.  of  and  been  been d e v e l o p e d in  which q u i c k l y  calculations,  estimating  start-up  fabrication  the  language.  L t d . has  their  variables  all  estimation  Bruce  was  a l g o r i t h m was  analyzing  programming the  71  Forde  of  the  project  ones done  a modification  estimation  macro  research  previous  The i d e a  in  at  [1].  The  of  one  Multiple  the  Linear  i n t r o d u c e d by B r u c e used. is  proven  to  l a b o u r hour v a r i a b l e s .  be The  72 work on l a b o u r hour v a r i a b l e s and  its  macro  capability.  computer  environment.  program  was  environment  the  the  Lotus  1-2-3  are  availability,  earlier under  later  the  use,  123  itself  is  fairly  flexibility  macro p r o g r a m w i l l  the the  most  time  future  should first  be  the  best  cost  that  fit  the  input  The  research  on  direct  to  the  A new method  process.  graphics.  Results  from  this  fabricators  of  case  of  that  their  of  and  use  and  computers.  allow  input  users  to  needs. process,  drawings  of  is  advised drawings  drawing  data  are  s h e e t s and then  typed  into  s h o u l d be d e v e l o p e d  The new a p p r o a c h s h o u l d  technology  to  management  Currently,  take-off  easy  estimation  part.  computer  steel  variety  consuming  investigated.  computer.  the  current  concluded  transferred  up t h i s of  of  management  that  the  study  program to  for  compatibility  The  From the  estimating  The main r e a s o n s  of  the  the  utilized  run on a  or a d j u s t  of  l a b o u r hour a n a l y s i s  macro p r o g r a m w i l l  modify  1-2-3  language  ease  the  a change  cost  the  of  Lotus  Basic  environment.  Lotus  using  T h e r e was  The  developed  but  flexibility.  was done  computer  study  o r even o t h e r  aided  take  to  advantage  drafting  can be h e l p f u l manufacturers.  speed  to  and  other  Chapter 7 FUTURE RESEARCH  7.1  COMPUTER  COST  ESTIMATION  PROGRAM  ON  LOTUS  1-2-3  SPREADSHEET A  flow  research.  chart  and an example  F i g u r e 7.1  shows a f l o w  cost  estimating  p r o g r a m s h o u l d be  1-2-3  spreadsheet  environment.  developed  should  subsequent  take-off,  menus. perform  the  menu t o  give  various  menus  together  estimating of  the  files  seen with  the  computer the  program the  users  c o m p u t a t i o n and f i n a l further  together  figure user  program, should give  lead  the  be  for  the  summary users  example  7.2.  These  manual of  a HELP of  flow  the  the chart  computer  enough g u i d e l i n e s  p r o g r a m on a s p r e a d s h e e t  to  should  and i n c l u d e  An  Lotus to  macro p r o g r a m  assistance. in  in  future  for  a  environment.  INPUT FROM DRAWINGS A fast  drawings is  can be  lead  for  how t h e  macro  The f u t u r e  users o n - l i n e  example,  rewrite  7.2  individual  of  implemented  The  menu w i l l  r e q u i r e d work.  the  cost  material,  guidance  chart  menu-driven to  Each subsequent  connect  and  be  give  now t h e  process.  and  into  the  most  computer  time  Currently,  from d r a w i n g s computer.  efficient  onto  Both  method  information  steps are  entering  s h o u l d be d e v e l o p e d  consuming p a r t  take-off  of  of is  the  because  from this  whole  estimating  manually  transferred  s h e e t s and t h e n slow and c o n t a i n  73  data  typed the  into  the  possibility  74 of  unnecessary  method  of  instead  of  The  the  transferred  in  in  the  computer  a r e drawn already  new  such  [13]  which  suitable  information  in  investigate a  by  exists  indicates  the  can  image  trace  a  single  step  should  also  to  the  computer  inputting computer  this  the  should method.  and  form of  method.  There  reader  diagrams  h a r w a r e and s o f t w a r e ,  drawing  in  graphics  investigation.  shows a scheme of  aided the  i n computer  as  from d r a w i n g s  F i g u r e 7.3  developing  7.4  technology  devices  drum-scanner  way.  this  should  two. latest  hardware  Future research  inputting  be c o n s i d e r e d  Given  errors.  this also  are  [12] on  and  much  method. be  Nowadays,  information  on  a data base  [14]  the  drawings.  i n f o r m a t i o n can in a  new  be  better  Technology  considered  in  many  drawings  the  drawings  [15].  Figure  75  new f i l e  Take-off E n t r y Menu  old  ^  edit  print  file  ^> a d d / c h a n g e Material Section Menu  remove ^  print  page by page  Computation Menu  OPENING MENU  9> l a b o u r  grouping  ^> m a t e r i a l material  Final Menu  ^  summary  grouping summary  Summary  H e l p Menu  Figure  7.1  A Flow C h a r t f o r Computer C o s t E s t i m a t i n g Program i n L o t u s 123  76  TAKE-OFF Take-off New  Menu  file  Old  file  Print  MATERIAL Material  Menu  Add/Change  ESTIMATING F I L E  COMPUTATION  Opening  Computation  Menu  Take-off Material Compute F i n a l summary Help  Remove  Print  Menu  Page/Page Labour g r o u p i n g M a t e r i a l grouping M a t e r i a l summary  FINAL SUMMARY  HELP H e l p Menu *^  •••••  •  Figure  7.2  An Example  Showing the  ••••  ••••  • • • •  S p r e a d s h e e t s and Menu  77  Drawings s c a n n i n g  Figure  7.3  A Scheme of  Utilizing  hardware  Computer  Graphics  DRAFTING  Figure  7.4  OFFICE  A Scheme U t i l i z i n g  C.A.D.  Technology  REFERENCES 1.  Forde,  B.,  "A  Evaluation Linear of 2.  3.  4.  Utilizing  Regression  British  Leung,  Y.C.,  Evalution  of  University  of  Lane,  6.  "A  Berry,  December  the  Sinha,  Hennessey, First  Que C o r p o r a t i o n ,  Posner,  J . , Hill,  LeBlond,  G.T.  Corporation, 10.  Hodges,  K.,  of  Civil  Thesis,  Plant  Small  Steel  Engineering,  Vol.  and  Reproductive  "1-2-3:  IEEE,  Tips,  on  1985.  Tricks,  and  1984.  S.E.,  Gettheil,  User's  E.  Manual",  and Lotus  1983.  Cobb,  Indiana,  and  Conference  Proceedings,  1-2-3  Design  (1984).  "High L e v e l  Indiana,  "Lotus  System:  Phoenix  J . , Miller,  J . L . , Krech,  Empirical  Design  Controlling  Digest  and C o b b , D . F . ,  Development C o r p o r a t i o n , 9.  of  and C o m m u n i c a t i o n s ,  M.L.,  Aided  1984.  Cost  Annual  Traps",  Davis,  University  M.A.Sc.  Information  Engineering  D.  Multiple  1979.  Implementation",  Andersen,  Design  1974.  G . L . , "Shop F l o o r  and  Computer  and  Canadian J o u r n a l  G.  to  B r i t i s h Columbia,  "Estimating  2,  and  Thesis,  Structures",  Nixon,  Computers  8.  Contribution  Prentice-Hall,  No.  Aided  Estimation  M.A.Sc.  R.E.,"Administering  D.,  Computer  1984.  Steel  Programming",  7.  Analysis",  Operations",  1,  to  Flexible  Columbia,  Buildings",  5.  Contribution  D.F.,  "Using  1-2-3",  Que  1983. D.,  Introduction  to  Crutchfield, Statistics", 79  R.S.,  " S t a t L a b : An  McGraw-Hill,  New  80 York, 11.  Strang, Ed.,  12.  1975. G.,  Academic  Malda,  Press,  New Y o r k ,  A . and S h i b a y a m a ,  Drawing 1985  " L i n e a r A l g e b r a and I t s  Reader  Computer  for  the  J . ,  13.  Harada,  H.,  Itoh,  Freehand Drawings 1985  Computer  Y.  Teicholz,  15.  Knox,  Implementation",  "CAD/CAM M.  Dekker,  Automatic  System",  IEEE  Analysis  and  1985. Ishii,  for  M . , "Recognition Engineering",  Pattern  Analysis  of IEEE and  1985.  E . , "CAD/CAM Handbook", C.S.,  Pattern  in Chemical Plant  Architecture  of  Management  for  and  Image D a t a b a s e Management, 14.  "Application  Architecture  2nd  1980.  Utility  Image D a t a b a s e Management,  Applications",  McGraw-Hill,  Systems 1983.  1985.  Planning  and  APPENDIX A  computer  diskette  is  -appended  p r o g r a m s on l a b o u r hour v a r i a b l e s . the  worksheet  diskette copy  of  of  copy.  the  "MASTER" opening  by  To e x i t  "Lotus  Release  2.  diskette  into  the  pressing from the  the  user  The u s e r  program.  make  the  the macro  load  can  user  has  the the  also  be  left  the  s h o u l d use  either  There i s  a HELP  "ESC" k e y . various  on  the  to  a  backup  Once l o a d e d ,  macro p r o g r a m , one  the  system  the  To s t a r t  a p p e a r and i t  " A L T " "M" a f t e r  for  the  should  RETRIEVE  macro  contains  s h o u l d be p l a c e d  Lotus spreadsheet.  123" menu i t e m o r t h e  assist  diskette  be u s e d w i t h  /FILE  automatically  show the  and works w i t h  tape  i n r u n n i n g the s h o u l d use  This  and must  no p r o t e c t i v e  menu w i l l  menu t o the  1-2-3  user  file  accessed menu.  that  the  files  appended  diskette  program,  the  Lotus  Note  backup  data  to  functions  offered  by  macro p r o g r a m . The  user  manual and t h e  p r o g r a m c a n be o b t a i n e d Dr.S.F.  listings  t h r o u g h the  Stiemer.  81  of  the  author  cost  estimating  or h i s  supervisor  

Cite

Citation Scheme:

        

Citations by CSL (citeproc-js)

Usage Statistics

Share

Embed

Customize your widget with the following options, then copy and paste the code below into the HTML of your page to embed this item in your website.
                        
                            <div id="ubcOpenCollectionsWidgetDisplay">
                            <script id="ubcOpenCollectionsWidget"
                            src="{[{embed.src}]}"
                            data-item="{[{embed.item}]}"
                            data-collection="{[{embed.collection}]}"
                            data-metadata="{[{embed.showMetadata}]}"
                            data-width="{[{embed.width}]}"
                            async >
                            </script>
                            </div>
                        
                    
IIIF logo Our image viewer uses the IIIF 2.0 standard. To load this item in other compatible viewers, use this url:
http://iiif.library.ubc.ca/presentation/dsp.831.1-0062932/manifest

Comment

Related Items