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.Sc, The University 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. of C i v i l Engineering) We accept th i s thesis as conforming to the required standard THE UNIVERSITY OF BRITISH COLUMBIA July 1986 © W.Y. ALBERT SHING, 1986 In presenting this thesis in p a r t i a l fulfilment of the requirements for an advanced degree at the University of B r i t i s h Columbia, I agree that the Library s h a l l make i t freely available for reference and study. I further agree that permission for extensive copying of t h i s thesis for scholarly purposes may be granted by the Head of the C i v i l Engineering Department or by his or her representatives. It i s understood that copying or publication of this thesis for f i n a n c i a l gain s h a l l not be allowed without my written permission. The University of B r i t i s h Columbia 2075 Wesbrook Place Vancouver, Canada V6T 1W5 Date: July 1986 ABSTRACT An arrangement has been set up between the U n i v e r s i t y of B r i t i s h Columbia and an i n d u s t r i a l f i r m ,Coas t S t e e l F a b r i c a t o r s , to i n v e s t i g a t e the t o p i c of computer c o s t e s t i m a t i o n of s t e e l s t r u c t u r e s . The o b j e c t i v e of t h i s r e s e a r c h i s to computer ize the c u r r e n t cos t e s t i m a t i n g proces s of Coast S t e e l F a b r i c a t o r s and i n v e s t i g a t e a method of g e n e r a t i n g the labour hour v a r i a b l e s used by the e s t i m a t i n g program. T h i s r e s e a r c h has made the f o l l o w i n g c o n t r i b u t i o n s : A computer e s t i m a t i n g program was deve loped u s i n g the o p e r a t i o n a l approach and a method was i n v e s t i g a t e d to generate the l abour hour v a r i a b l e s r e q u i r e d by the computer e s t i m a t i n g program. The unique arrangement between t h i s s tudy and the i n d u s t r i a l f i r m has proven to be s u c c e s s f u l . Coast S t e e l F a b r i c a t o r s updrated t h e i r e s t i m a t i n g p r o c e s s and a c h i e v e d a h i g h e r e f f i c i e n c y . The author was g iven hands-on e x p e r i e n c e and f r e e access of the data f i l e s throughout the s t u d y . The computer c o s t e s t i m a t i n g program i s w r i t t e n in B a s i c and runs under the MAI-Four computer system. The program i s menu-dr iven to a l l o w the u s e r s t o : 1 . s t o r e l abour hour v a r i a b l e s ; 2. en ter i n p u t s from t a k e - o f f s ; 3. compute the l a b o u r hours and m a t e r i a l we ight s ; and 4. c a l c u l a t e the l abour c o s t , m a t e r i a l cos t and t o t a l c o s t . Coast S t e e l F a b r i c a t o r s r e p o r t e d that the computer c o s t e s t i m a t i n g program has reduced the o v e r a l l e s t i m a t i n g time i i b y 3 0 % . T h e l a b o u r h o u r v a r i a b l e s a r e d e f i n e d a s t h e t i m e f o r e a c h o p e r a t i o n . A s t h e s e v a r i a b l e s a r e v i t a l t o t h e c o m p u t e r e s t i m a t i n g p r o g r a m , a m e t h o d h a s b e e n d e v e l o p e d t o o b t a i n t h e s e v a r i a b l e s b y c o l l e c t i n g s h o p d a t a a t a g l o b a l l e v e l t h r o u g h a n i n f o r m a t i o n s y s t e m . T h e g l o b a l s h o p d a t a w i l l t h e n b e a n a l y z e d b y t h e t e c h n i q u e o f M u l t i p l e L i n e a r R e g r e s s i o n ( M L R ) u s i n g a s p r e a d s h e e t p r o g r a m ( L o t u s 1 - 2 - 3 ) . A n o p t i m u n s i z e o f 200 o b s e r v a t i o n s was r e c o m m e n d e d t o g i v e a m o n t h l y a v e r a g e . T h e p r o g r a m i s a l s o m e n u - d r i v e n t o a l l o w t h e u s e r s t o : 1. c o n v e r t g l o b a l s h o p d a t a i n t o p i e c e f i l e s ; 2. s o r t t h e p i e c e f i l e i n t o c o m p o n e n t f i l e s ; 3. p e r f o r m MLR a n a l y s i s o n t h e c o m p o n e n t f i l e t o o b t a i n t h e l a b o u r h o u r v a r i a b l e s ; a n d 4. p l o t t h e s t a n d a r d e r r o r o f e a c h l a b o u r h o u r v a r i a b l e . T h e l a b o u r h o u r v a r i a b l e s w i l l s e r v e a s a p o w e r f u l t o o l f o r t i m e s t u d i e s i n 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 p l a n t a n d w i l l p r o v i d e f e e d b a c k t o t h e c o s t e s t i m a t i o n p r o g r a m o f a n o n g o i n g f a b r i c a t o r . S i n c e t h e a p p l i c a t i o n s a r e w r i t t e n i n t h e B a s i c a n d s p r e a d s h e e t e n v i r o n m e n t s , p r o g r a m s c a n b e e a s i l y c u s t o m i z e d f o r u s e b y a n u m b e r o f s t e e l f a b r i c a t o r s . TABLE OF CONTENTS ABSTRACT i i LIST OF FIGURES vi ACKNOWLEDGEMENTS v i i i 1 . INTRODUCTION 1 2. COMPUTER ESTIMATING PROGRAM 3 3. LABOUR HOUR VARIABLES 12 3.1 Introduction 12 3.2 Current Shop Practice and Global Shop Data 12 3.3 C o l l e c t i n g Global Shop Data, Approach #1 14 3.4 C o l l e c t i n g Global Shop Data, Approach #2 16 3.5 C o l l e c t i n g Global Shop Data, Approach #3 17 3.6 C o l l e c t i n g Global Shop Data, Approach #4 18 4. 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 Sorting Routine 38 4.3.3 MLR Routine 41 4.3.4 Plotting Routine 42 5. THEORY OF MLR 64 5.1 System of Equations 64 5.2 Regression Analysis 65 5.3 Algorithm of MLR 66 6. CONCLUSIONS 71 7. FUTURE RESEARCH 73 7.1 Computer Cost Estimation Program on Lotus 1-2-3 Spreadsheet 73 7.2 Input From Drawings 73 iv REFERENCES APPENDIX LIST OF FIGURES FIGURE PAGE 2.1 The Page by Page Summary 9 2.2 The Formula and Single Unit Methods of Retrieving Labour Hour Variables 10 2.3 The Relationship Between This Research and the Industrial Firm 11 3.1 Four Approaches of Co l l e c t i n g Shop Data 21 3.2 Material B i l l and Route Card 22 3.3 Global and Local Shop Data 23 3.4 An Example of Approach #1 24 3.5 An Example of Approach #2 25 3.6 A Comparison of Approach #1 and #2 26 3.7 An Example of Approach #3 27 3.8 A Scheme of Approach #4 28 4.1 The Opening Menu 45 4.2 The Global Data F i l e 46 4.3 The Function of the Conversion Routine 47 4.4 An Example of the Single 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 ..50 4.7 The Help Messages of the Conversion Routine 51 4.8 The Function of the Sorting Routine 52 4.9 The Menus and Macros of the Sorting Routine 53 4.10 The Help Messages of the Sorting Routine 54 4.11 The Menus and Macros of the Analyzing Routine ...55 4.12 The Help Messages of the Analyzing Routine 56 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 Pl o t t i n g Routine 58 4.15 The Results of the C200X15 Component F i l e 59 v i 4.16 The Resu l t s of the PL8 Component F i l e 60 4.17 The R e s u l t s of the W150X24 Component F i l e 61 4.18 The R e s u l t s of the PL8PL10 Component F i l e 62 4.19 The Re s u l t s of the PL10W610X113 Component F i l e ..63 5.1 A System of A l g e b r a i c Equations 68 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 69 5.3 A Flow Chart f o r the MLR A n a l y s i s 70 7.1 A Flow Chart f o r Computer Cost E s t i m a t i n g Program i n Lotus 123 75 7.2 An Example Showing the Spreadsheets and Menus ...76 7.3 A Scheme U t i l i z i n g Computer Graphics 77 7.4 A Scheme U t i l i z i n g C.A.D. Technology 78 v i i ACKNOWLEDGEMENTS The investigations and research were made possible by a Natural Science and Engineering Research Council of Canada (NSERC) Postgraduate scholarship and through a G.R.E.A.T.-Award for the author by the B.C. Science Council and a f i n a n c i a l contribution by Coast Steel Fabricators Ltd., Port Coquitlam. The author is grateful for this support. Mike Liska and Victor Dick, assistant and chief estimator, and Herb Hornstein, head manager of Coast Steel Fabricators Ltd. are due sincere thanks for their guidance and cooperation. The author wishes to express his sincere thanks to Professor S.F. Stiemer, his supervisor, for the supervision and advice. I would l i k e to thank my wife, Fern, for her patience throughout my research. Trademarks: IBM i s a trademark of International Business Machines Corporat ion Lotus 1-2-3 i s a trademark of Lotus Development Corporation MAI-Four i s a trademark of MAI Canada Ltd. v i i i Chapter 1 INTRODUCTION New general strategies have recently been investigated at U.B.C. and the p o s s i b i l i t y of developing a computerized estimation program for steel structures exists [1] [2]. Early results from these research programs were promising and invited an implementation of these findings into the workplace. A lo c a l steel fabricator, Coast Steel Fabricators Ltd.,was trying to upgrade their estimating process to take advantage of the new strategies and computer technologies. This research included a case study of computer aided estimation using an operational approach. The study was composed of two parts. In the f i r s t part, a computer estimating program was developed on a mini-computer in Basic. This program has been completed and was put to use as a dai l y operation tool in the estimating department. The computer estimating program has reduced the t o t a l estimating time by 30%. The second part of the research project was concerned with the generation of the labour hour variables used in the estimating program at the fabri c a t i n g shop. These variables are needed to calculate the labour hours and thus the labour cost. They are important because they r e f l e c t the performance of the fabricating shop. Several approaches of c o l l e c t i n g global shop data have been investigated for dif f e r e n t levels of shop management. The global shop data were reduced by multiple linear regression u t i l i z i n g a 1 2 program w r i t t e n i n Lotus 123 macro language. The g l o b a l shop data served as feedback f o r the e s t i m a t i n g program as w e l l as a database f o r e f f i c i e n c y , coverage and l e a r n i n g curve s t u d i e s [ 3 ] . F i n a l l y , t h i s p r o j e c t showed that f u t u r e r e s e a r c h should be d i r e c t e d towards the development of a mechanism which s i m p l i f i e s the process of data r e t r i e v a l from shop drawings. Chapter 2 COMPUTER ESTIMATING PROGRAM A computer estimating program has been written in Basic language for the "MAI-Four" computer system. Recently, a u t i l i t y program was added to handle f i l e storage and deletion. The estimating program is menu-driven allowing the access of four major sub-programs. Their functions are: 1 . storage of the labour hour variables; 2. input of take-off sheets; 3. computation of the labour hours and material weights; and 4. ca l c u l a t i o n of the labour costs, material costs and to t a l costs. To run the computer estimating program, d e t a i l s of the drawings must be f i r s t recorded on the take-off sheets and then entered into the computer. A hardcopy of the entries, known as the take-off summary, w i l l be issued by the estimating program. The take-off summary i s similar to the page by page summary shown in figure 4.1 except that the "WEIGHT", "HOURS", and the bottom part are missing. Note that each l i n e on the take-off summary w i l l represent a str u c t u r a l member and the operations associated with i t . The take-off summary i s checked and then used as the input data f i l e for the computer estimating program which calculates the material weights and the labour hours of the required operations. The results are summarized by the page by page summary. The values under the "WEIGHT" and "HOURS" 3 4 headings in figure 2 .1 are the calculated weights and the labour hours. The bottom part gives the t o t a l number of operations and the corresponding time of performing them. The results of the page by page summary w i l l then be sorted in terms of a c t i v i t y , description, and shape to give the labour grouping, material grouping and material summary. F i n a l l y , the unit material costs, wages, overhead and p r o f i t are entered to give the t o t a l cost. Because of the agreement with Coast Steel Fabricators, further d e t a i l s of the computer estimating program cannot be discussed to protect their trade secrets. The approach used in the computer estimating program is known as the operational approach. Costs of steel structures were previously calculated by estimating the weight and multiplying i t by a cost factor [ 4 ] . This was accurate when labour costs were r e l a t i v e l y low. However, with the increasing importance of labour costs on t o t a l costs and the a v a i l a b i l i t y of new technologies for handling the massive data associated with labour costs, an advanced approach can be employed for steel structure estimation. In general, the t o t a l cost is composed of the material, labour, equipment and overhead [ 1 ] . The material cost can be found by summing up the products of the individual material weights and the corresponding material costs. The costs for equipment and overhead are fixed costs and can be taken as a percentage of the t o t a l of the variable costs of material and labour. Labour costs are the most d i f f i c u l t 5 items to e s t i m a t e . D i f f e r e n t o p e r a t i o n s r e q u i r e d i f f e r e n t amounts of labour t i m e . However, the b a s i c o p e r a t i o n s such as s h e a r i n g , p u n c h i n g , d r i l l i n g , e t c . are r e l a t i v e l y f i x e d . The key to e s t i m a t i n g l abour hours l i e s in the i d e n t i f i c a t i o n of these common o p e r a t i o n s for each s t r u c t u r a l component and the proper a p p l i c a t i o n of known labour hour v a r i a b l e s . T h i s approach i s known as the o p e r a t i o n a l a p p r o a c h . On the o ther hand, Leung [2] has sugges ted g e n e r a l formulas to cover a l l o p e r a t i o n s for a l l components, for example, a g e n e r a l formula for d r i l l i n g i s n DR = n{T + I (T ). + t *N, + t *N. }/N s ^_ 1 a i 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 = time for o p e r a t i o n ( i . e . d r i l l i n g ) T g = se tup time T g = a l ignment time t^ = d r i l l i n g t ime per h o l e N, = number of h o l e s h t = t ime to c e n t e r one punch h o l e N = number of i d e n t i c a l members N g = maximum number of p l a t e s i n a s t a c k . The advantage of the formula method i s that o n l y a s m a l l set of l a b o u r hour v a r i a b l e s are needed to cover the o p e r a t i o n s of a l l components. The d i s a d v a n t a g e i s tha t s i n c e the formula does not cover a l l the p a r a m e t e r s , t h i s method may 6 not g i v e good r e s u l t s f or some components. However, i f each o p e r a t i o n on a g i v e n component i s t r e a t e d as a s i n g l e independent u n i t , the l abour c o s t s can be d e r i v e d u s i n g these u n i t s . The advantage of t h i s s i n g l e u n i t method i s that each u n i t w i l l best r e f l e c t the o p e r a t i o n on an i n d i v i d u a l component and hence g i v e s a more a c c u r a t e labour hour e s t i m a t e . A f u l l set of a l l l abour hour v a r i a b l e s must be o b t a i n e d in order to per form the e s t i m a t i o n . In the r e p o r t e d p r o j e c t , both methods are used to complement each o t h e r . The s i n g l e u n i t method w i l l be used as the b a s i c mechanism of the computer e s t i m a t i n g program. However, i f an i n d i v i d u a l l a b o u r hour v a r i a b l e i s not a v a i l a b l e , the formula method i s used to o b t a i n i t and then s t o r e i t i n the a p p r o p r i a t e data f i l e . A f u l l set of l a b o u r formulas have been c o m p i l e d by the e s t i m a t i n g department through t h e i r s t u d i e s i n the f a b r i c a t i n g shop. F i g u r e 2.2 shows how the two methods are used t o g e t h e r . In g e n e r a l , the formula method i s used f o r the s t a r t - u p f a b r i c a t i o n p e r i o d when there are v i r t u a l l y no p r e v i o u s r e c o r d s of l abour hour v a r i a b l e s . I t can a l s o be used in a d es ign o f f i c e for cos t o p t i m i z a t i o n . The s i n g l e u n i t method i s more o f t e n used when a manufac turer a l r e a d y possesses a l a r g e data base of the l abour hour v a r i a b l e s . Methods of r e t r i e v i n g these l abour hour v a r i a b l e s w i l l be d i s c u s s e d l a t e r . The computer e s t i m a t i n g program has been t e s t e d s u b s t a n t i a l l y and was put i n t o use f o r over a y e a r . The 7 management of Coast S t e e l r e p o r t e d tha t i t has reduced t h e i r t o t a l e s t i m a t i o n time by 30%. With h i g h e r e f f i c i e n c y , the e s t i m a t i n g department was observed to be a b l e to m a i n t a i n t h e i r s e r v i c e wi th a s m a l l e r s t a f f . The computer e s t i m a t i n g program has become an i n d i s p e n s i b l e t o o l and was r e a d i l y accepted by a l l the e s t i m a t o r s . The computer e s t i m a t i n g program not on ly r e l i e v e d t h e i r workload but a l s o a l l o w e d a d d i t i o n a l a n a l y s i s which was not performed b e f o r e . For example, when an e s t i m a t o r i d e n t i f i e d an area where s a v i n g c o u l d be made, he suggested a s t r u c t u r a l e q u i v a l e n t to i t . Because of the c a l c u l a t i o n s i n v o l v e d , o n l y one s t r u c t u r a l e q u i v a l e n t c o u l d be c o n s i d e r e d . However, w i th the computer e s t i m a t i n g program, a v a r i e t y of v e r s i o n s can be c o n s i d e r e d to determine which produces the g r e a t e s t s a v i n g . S ince a l l of the e s t i m a t o r s are graduates of U n i v e r s i t i e s or t e c h n i c a l i n s t i t u t e s , they are more w i l l i n g to accept changes . They welcome h i - t e c h and t h i n k the use of a computer w i l l enchance t h e i r work. Hence, the f i r s t p a r t of t h i s r e s e a r c h , the cos t e s t i m a t i n g program, was completed on s c h e d u l e . C o n t i n u a l feedback and a d v i c e was g i v e n by the e s t i m a t o r s which c o n t r i b u t e d to the success of the e s t i m a t i n g program. On the o ther hand, management from the shop seem to have a lower academic background and are more r e l u c t a n t to accept changes . They t h i n k the c u r r e n t system i s adequate and d i s l i k e the computer . Because of the l a c k of i n t e r e s t shown by the shop management, the second p a r t of t h i s r e s e a r c h was behind s c h e d u l e . Input from the 8 shop management was minimal and most of the r e s e a r c h was done through o b s e r v a t i o n s by the author in the shop. Thus the f i n d i n g s of the l abour hour v a r i a b l e s cannot be t e s t e d and v e r i f i e d in the shop. The arrangement between t h i s study and the i n d u s t r i a l f i r m can be summarized i n f i g u r e 2 . 3 . The author p r o v i d e s t e c h n i c a l support to Coast S t e e l in c o m p u t e r i z i n g t h e i r e s t i m a t i n g proces s and in r e s e a r c h i n g o ther e s t i m a t i n g i s s u e s . In r e t u r n , Coast S t e e l a l l o w s access to t h e i r data f i l e and c o n t r i b u t e s some f i n a n c i a l and f a c i l i t y s u p p o r t . The key to such an arrangement l i e s in a common i n t e r e s t . S i n c e both p a r t i e s are i n t e r e s t e d in the t o p i c of cos t e s t i m a t i n g of s t e e l s t r u c t u r e s , e f f o r t s can be c o - o r d i n a t e d and r e s u l t s w i l l be s h a r e d . However, s i n c e the i n d u s t r i a l f i r m i s o n l y i n t e r e s t e d in the work they are d o i n g , they may ignore o ther s t u d i e s . The s tudent must c o l l e c t as much r e s e a r c h data as p o s s i b l e whi l e he i s s t i l l working on areas the f i r m i s i n t e r e s t e d i n . Care sh ou ld a l s o be taken as to what m a t e r i a l must be d i s c l o s e d or w i t h h e l d to p r o t e c t the t r a d e s e c r e t s of the i n d u s t r i a l f i r m . f FILE *AME = SAMPLE DIVISION : 0 * MORMAL * COAST STEEL FA3*l£ATQ* PAGE 8V PASE SJtlArttf O A I E J 08/15/84 P A S E t 1 PAGE O T Y DESCRIPTION IN-LINE OPErfAl13*3 SH H/LB LENGTH AEIGHI CODE A B C 0 El F G CUT BEV P*E COP P.M ORl SIO CJT CJT HJL HOL 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 M O J H 3 1/1 1 1/2 r 1/S 1 1/4 o 16 21 18 i) 1/D 1/S 1/ / irs™ 1/9 1/10 fa<i/8 J.5X1/2 18X1/2 6X1/2 3X3X1/4 H X 4 X 1 / 4 _g 1UX1 U 0 a ~ H — w o -pr PL PL -pxr L L 40 iO 1201) 2 "55 25 I3T5- Z 55 21.ii 1183 2 0 0 0 0 u 0 0 ~tr 4.9 6.6 11.5 0 0 -vs-— 0 0 -o ~TB— 1. S 2.5 .5 lb -TT5-1.3 0 -.-5'--0 0 " T T 28 >7 - 2 ? ~ 4 99 -$TT-43 0 -12--0 0 - o — 2A 2A -2A~ 2A 2A 0 0 2 21 0 3 - o 2 — o — n r — o r — 2 0 0 0 1 1.5 0 0 U 0 0 0 —0~ 0 0 —o~ 0 0 ~0 0 o -IT — 0 0 ~u— u u "D— 0 0 - « — T B — 3 r 0 0 0 4 0 1 -j o — 1 -a a l 0 0 0 0 0 0 0 0 "2 q 0-o o a 0 0 0 o o — 0 0 0 0 0 0 0 -o a o-o o o - o — 0 " — 0 0 0 0 0 0 0 -o o~ 0 0 0 1 -9--—f/-0 0 o — o 0 0 0 0 0 ...... 0 0 - o — 0 0 0 0 0 2.2 I. o I .6 0 ~t— 2 1.1 I -.0 1.5 1 T7TT r~ 1/12 1 1/13 0 iv n—ZT 1/15 0 l/lf> 0 T7T7 3— -c— P L 0 SP~ 0 ' 0 -2TT-2A 0 -2A-0 0 U— —tr o 0 0 o — o -0 0 0 0 v—-tr -o o-o o o o - T ) — o -o o o o -TJ 0-1 0 1.3 o o o o — i 1 o o o o o o —o o a I DIAL 1E13H1: 4102 TOTAL H'JJHSs IB O P S 2 3 2 2 2 34 4 0 0 0 0 0 3 0 0 0 0 H O J H S 2 1 . 7 .& .3 .3 1.9 .5 0 0 0 0 0 0 0 0 0 5.4 O P S 2A 15 0 0 2 38 4 3 4 1 1 2 1 2 1 1 0 H3JRS 2A 1.9 0 0 .4 .4 1.5 I.B .9 .8 .6 1.2 1 . 7 1 12.6 C O D E : PC3 = TOTAL HOOKS: IB F i g u r e 2.1 T h e P a g e b y P a g e S u m m a r y 10 INSIDE COMPUTER ESTIMATING PROGRAM ask for labour hour variable ^ return the required labour hour variable DATA BASE containing a l l the labour hour variables available (Single Unit Method) 1\ missing labour hour variable, prompt user for input input the labour variable in the computer f USER look up the general formula and calculate the labour hour variable by feeding in the various parameters by hand (Formula Method) Figure 2.2 The Formula and Single Unit Methods of Retrieving Labour Hour Variables U.B.C. INDUSTRIAL FIRM: THIS RESEARCH provides technical supports, research and development COAST STEEL FABRICATORS access of their data f i l e , finance and f a c i l i t i e s Figure 2.3 The Relationship Between This Research and the Industrial Firm Chapter 3 LABOUR HOUR VARIABLES 3. 1 INTRODUCTION Labour hour v a r i a b l e s are used in the o p e r a t i o n a l a p p r o a c h . The o b j e c t i v e i s to f i n d a method to o b t a i n these v a r i a b l e s . T h i s w i l l be d i s c u s s e d in the f o l l o w i n g c h a p t e r s . M u l t i p l e l i n e a r r e g r e s s i o n i s used to a n a l y z e the g l o b a l shop data to g i v e the l abour hour v a r i a b l e s . The a n a l y s i s i s done on a micro-computer spreadsheet (Lotus 1-2-3) u s i n g macro programming t e c h n i q u e . The s tandard e r r o r of each labour hour v a r i a b l e w i l l be p l o t t e d to show the p r e c i s i o n of the the a n a l y s i s . In t h i s case s t u d y , four approaches of c o l l e c t i n g shop data have been examined ( f i g u r e 3 . 1 ) . Only the f i r s t three approaches are i n v e s t i g a t e d in d e t a i l . The l a s t approach which i n v o l v e s a fundamental change of the f a b r i c a t i n g shop management w i l l on ly be d i s c u s s e d as a t h e o r e t i c a l o p t i o n . 3.2 CURRENT SHOP PRACTICE AND GLOBAL SHOP DATA C u r r e n t l y , in the case of Coast S t e e l F a b r i c a t o r s , m a t e r i a l s are handled by m a t e r i a l b i l l s and the job sequences are s chedu led by the route c a r d s ( f i g u r e 3 . 2 ) . The l a b o u r hours are r e c o r d e d in the time c a r d s i n such a manner tha t no l abour hour v a r i a b l e can be o b t a i n e d d i r e c t l y wi thout some s o r t i n g . In g e n e r a l , the workers r e c o r d t h e i r t ime on a b a s i s of c l a s s i f i c a t i o n which s e p a r a t e s a l l 12 1 3 components i n t o beams, columns, frames, e t c . i n s t e a d of component b a s i s such as W150X27, L75X75X6, e t c . For example, a worker r e c o r d s 2 hours under the c l a s s i f i c a t i o n of "beam" toge ther w i t h the drawing numbers i n s t e a d of b r e a k i n g the 2 hours i n t o i n d i v i d u a l o p e r a t i o n s on d i f f e r e n t components such as 1 hour on sawing W150X27 and 1 hour on punching 20 h o l e s on P L 8 . The c u r r e n t system of shop m o n i t o r i n g i s adequate as long as the f a b r i c a t o r i s i n t e r e s t e d in the t o t a l l abour hours and i n d i v i d u a l l abour hours under each c l a s s i f i c a t i o n . However, these shop r e c o r d s are not the g l o b a l shop data r e q u i r e d for the d e t e r m i n a t i o n of the l a b o u r hour v a r i a b l e s . F o r m e r l y , time s t u d i e s had to be used to determine the l abour hour v a r i a b l e s . In f a c t the c u r r e n t l a b o u r hour v a r i a b l e s used in the computer e s t i m a t i n g program were found by time s t u d i e s . However, i t seems to be more e f f i c i e n t to c o l l e c t the g l o b a l shop data and a n a l y z e the data to o b t a i n the performance of the i n d i v i d u a l worker and h i s p r o d u c t i o n . The g l o b a l shop data are j u s t combinat ions of l o c a l shop d a t a . The l o c a l or p a r t i c u l a r shop data are the labour hour v a r i a b l e s that are to be found. The g l o b a l shop data are the sum or o b s e r v a t i o n of s e v e r a l l o c a l d a t a , for example, the t o t a l t ime for s h e a r i n g and punching of a 10 mm p l a t e i s " g l o b a l shop da ta" whereas the i n d i v i d u a l t ime to shear and time to punch are the " l o c a l shop d a t a " , i . e . the l abour hour v a r i a b l e s . M u l t i p l e l i n e a r r e g r e s s i o n i s then used to c a l c u l a t e the l o c a l shop d a t a , i . e . l abour hour 1 4 v a r i a b l e s from the g l o b a l shop data ( f i g u r e 3 . 3 ) . These l abour hour v a r i a b l e s can be used to update the computer e s t i m a t i n g program. If both the e s t i m a t i n g department and the shop management have adopted the same i n f o r m a t i o n system for the l abour o p e r a t i o n s , these v a l u e s can be used d i r e c t l y in the computer e s t i m a t i n g program. U n f o r t u n a t e l y , the shop management has c l a s s i f i e d a l l the a d d i t i o n a l o p e r a t i o n s shown in f i g u r e 2.1 in terms of weld and f i t . However, o ther i n - l i n e o p e r a t i o n s are used by both depar tments . Thus , most of the labour hour v a r i a b l e s are c o m p a t i b l e w h i l e a s m a l l p o r t i o n of them w i l l r e q u i r e e x p e r i e n c e to i n t e r p o l a t e . Because of the t ime c o n s t r a i n t of t h i s r e s e a r c h , a l i n k a g e program has not yet been d e v e l o p e d . 3.3 COLLECTING GLOBAL SHOP DATA, APPROACH #1 "Raw" shop data must be c a r e f u l l y s t u d i e d and s o r t e d i n t o g l o b a l shop d a t a . S t u d i e s and s o r t i n g are r e q u i r e d because i n the c u r r e n t job s e t - u p the workers do not r e c o r d enough i n f o r m a t i o n on the time c a r d . For i n s t a n c e , the workers never r e c o r d what type of o p e r a t i o n they have done on the time c a r d . Moreover , they sometimes even f o r g e t to put down the drawing numbers. Hence, a d e t a i l e d study of the p e r i o d of o p e r a t i o n and sequence of job flow must be made in order to a s s i g n g l o b a l shop data for c e r t a i n o p e r a t i o n s ( f i g u r e 3 . 4 ) . Assume tha t the g l o b a l shop data on c o n t r a c t 5441 and drawings 11, 14, 17 are to be found . From the t ime c a r d s of 1 5 f i g u r e 3 .4 , worker number 9 and 12 have spent 0.75 hour and 1 hour at 7 a .m. r e s p e c t i v e l y . Worker number 25 who i s the l a s t one working on these drawings has spent 4.0 hours a t 1 p .m. However, what they a c t u a l l y d i d i s not known s i n c e they do not put down the k i n d of o p e r a t i o n they have p e r f o r m e d . To see what k i n d of o p e r a t i o n s are r e q u i r e d in these drawings , the route c a r d sh ou ld be checked . The route c a r d has i n d i c a t e d that saw, d r i l l and f i t on W150X24 p l u s angle shear and punch on L75X75X10 shou ld be p e r f o r m e d . From the route c a r d and the time c a r d s , one can conc lude tha t a set of o p e r a t i o n s have been performed and a set of t imes are observed in p e r f o r m i n g the o p e r a t i o n s . However, the r e l a t i o n s h i p between these two i n d i v i d u a l s e t s of o p e r a t i o n s and t imes i s not c l e a r . A l s o the number of the o p e r a t i o n s i s not known y e t . To f i n d i t , the a c t u a l drawings s h o u l d be r e f e r e n c e d to g i v e the exact number of each o p e r a t i o n , for example, there are 3 angle shears and 6 punches on L75X75X10. The job sequence i n d i c a t e s that both the saw and ang le shear must be performed f i r s t and i n d e p e n d e n t l y , f o l l o w e d by the d r i l l and f i n a l l y f i t . T h i s sequence of job flow i s u s u a l l y i n d i c a t e d by the arrangement of the o p e r a t i o n columns on the route c a r d . From e x p e r i e n c e , ang le shear and punch are performed toge ther and d r i l l and f i t are handled toge ther by the f i t t e r . The g e n e r a l p r a c t i c e in the shop d i c t a t e s tha t c e r t a i n workers w i l l u s u a l l y per form the same s o r t of o p e r a t i o n a l l the t i m e . Worker number 9, 12 and 25 are known as the shear 16 man, saw man and f i t t e r r e s p e c t i v e l y . Hence, one can deduce the g l o b a l shop data from the raw shop da ta through a d e t a i l e d study and s o r t i n g . These g l o b a l shop data are summarized by the l a s t box in f i g u r e 3 . 4 . In g e n e r a l , t h i s approach i s p o o r l y s u i t e d to o b t a i n g l o b a l shop data as i t i n v o l v e s e x t e n s i v e f a b r i c a t i o n knowledge, e f f o r t and p a t i e n c e . A l s o , s i n c e e x p e r i e n c e p l a y s a major r o l e in t h i s a p p r o a c h , i t w i l l be d i f f i c u l t to implement i n t o a computer program. However, the advantage of t h i s approach i s tha t r e l a t i v e l y fewer raw shop da ta are needed to deduce the g l o b a l shop data which are a l s o e a s i e r to a n a l y z e . For i n s t a n c e , one can c o r r e c t l y c o n c l u d e that the l a b o u r hour v a r i a b l e f o r sawing W150X24 i s 1/3 h o u r . 3.4 COLLECTING GLOBAL SHOP DATA, APPROACH #2 The second approach a l s o uses the raw shop data w i t h a s imple s o r t i n g r o u t i n e . A l l hours r e f e r r i n g to the same drawings on the time c a r d s w i l l be added to g i v e the i n d i v i d u a l t o t a l hours on the same d r a w i n g s . F i g u r e 3.5 shows t h a t the i n d i v i d u a l hours on each time c a r d are not p a r t i c u l a r i m p o r t a n t . Rather the sum of these hours for the same drawings i s needed to be a s s i g n e d to a number of o p e r a t i o n s . No d e d u c t i o n , l o g i c or e x p e r i e n c e i s r e q u i r e d to f i n d the g l o b a l shop d a t a . The magnitude of the o p e r a t i o n s on drawings 11, 14 and 17 can be o b t a i n e d by r e f e r e n c i n g the i n d i v i d u a l d r a w i n g s . The o p e r a t i o n s are then summed up to g i v e the l e f t hand s i d e of the e q u a t i o n 17 shown in f i g u r e 3 . 5 . The t o t a l hours c o r r e s p o n d i n g to each drawing w i l l be the r i g h t hand s i d e of the e q u a t i o n . T h i s e q u a t i o n r e p r e s e n t s the g l o b a l shop d a t a . The r e s u l t s between the f i r s t and second approaches are summarized in f i g u r e 3 . 6 . One can e a s i l y see tha t the g l o b a l shop data generated by the second approach i s f a i r l y c o m p l i c a t e d and more raw shop data are needed. N e v e r t h e l e s s , more raw shop data can be e a s i l y o b t a i n e d wi th the use of o l d e r r e c o r d s . The c o m p l i c a t e d e q u a t i o n s of t h i s approach do not r epresen t a major o b s t a c l e because they are r e a d i l y s o l v e d by the computer . T h u s , i f a f a b r i c a t o r does not want to change h i s m o n i t o r i n g system, the second approach i s recommended because the procedure can be implemented i n t o a computer program to produce the l abour hour v a r i a b l e s . 3.5 COLLECTING GLOBAL SHOP DATA, APPROACH #3 A t h i r d p o s s i b i l i t y r e q u i r e s a s l i g h t d e v i a t i o n from the c u r r e n t shop p r a c t i c e but r e s u l t s in a much b e t t e r i n f o r m a t i o n system for c o l l e c t i n g g l o b a l shop d a t a . O p e r a t i o n s on the route c a r d s s h o u l d be q u a n t i f i e d . The r o u t e c a r d a lone w i l l g i v e the RHS of the g l o b a l e q u a t i o n wi thout r e f e r e n c i n g the d r a w i n g s . The workers must c i r c l e the o p e r a t i o n s on the route c a r d and r e p o r t the time they spent on the o p e r a t i o n . Now the hours i n s i d e the c i r c l e w i l l g i v e the LHS of the g l o b a l e q u a t i o n . T h e r e f o r e , g l o b a l shop data w i l l appear on the route c a r d s and w i l l then be 18 e n t e r e d i n t o the computer for the a n a l y t i c a l a n a l y s i s . F i g u r e 3 . 7 shows an example of t h i s a p p r o a c h . S i n c e the d a i l y hours of the i n d i v i d u a l worker can be summed from these route c a r d s , the time c a r d s are no longer needed. So b a s i c a l l y , the workers themselves are a c t u a l l y spending l e s s time on work r e p o r t s than be fore (Approach #1 and #2). There may be an i n c r e a s e d work l o a d on the management as the o p e r a t i o n s must be q u a n t i f i e d on the route c a r d s . The e n t r y of route c a r d i n f o r m a t i o n w i l l r e p l a c e t h a t of the time c a r d . Hence, the e x t r a e f f o r t i s on ly m i n i m a l . As the raw shop data are formula ted i n an a p p r o p r i a t e way, a l l of them w i l l become the g l o b a l shop d a t a . L i t t l e s o r t i n g i s r e q u i r e d to compi l e these g l o b a l shop data for MLR a n a l y s i s . Moreover , these g l o b a l shop data can a l s o be used for a c c o u n t i n g , f o r e c a s t i n g , c o n t r o l l i n g and s c h e d u l i n g p u r p o s e s . 3 . 6 COLLECTING GLOBAL SHOP DATA, APPROACH #4 The f o u r t h approach i s an i d e a l approach of c o l l e c t i n g g l o b a l shop d a t a . Forde [ l ] has proposed c o n t r o l s t a t i o n s at c r i t i c a l shop l o c a t i o n s such that the time at which a job passes through these s t a t i o n s can be r e c o r d e d . The d u r a t i o n between two subsequent work s t a t i o n s w i l l r e p r e s e n t the time taken for a c e r t a i n o p e r a t i o n , i . e . the RHS of the g l o b a l e q u a t i o n . With the job sequence be ing known, t h i s d u r a t i o n of t ime can be a s s i g n e d to that o p e r a t i o n r e p r e s e n t i n g the LHS of the g l o b a l e q u a t i o n . Hence, a g l o b a l e q u a t i o n w i l l 19 be set up and w i l l r e p r e s e n t g l o b a l shop d a t a . The mechanism behind the f o u r t h approach i s very s i m i l a r to that of the t h i r d a p p r o a c h . The d i f f e r e n c e between them i s that the f o u r t h approach i s f u l l y automat ic wh i l e the t h i r d approach depends on human i n p u t . Berry [5] suggests that pre -encoded media such as b a r - c o d e s and magnet ic s t r i p d e v i c e s be used to c o l l e c t the shop d a t a . The f o u r t h approach u t i l i z e s s ens ing d e v i c e s to r e g i s t e r the flow and time of a job in the shop. A scheme of the f o u r t h approach i s shown in f i g u r e 3 . 8 . Not on ly w i l l the d a i l y workload of both workers and management be reduced but a l s o unnecessary human e r r o r d u r i n g data e n t r y w i l l be e l i m i n a t e d . The i n f o r m a t i o n from the drawings i s s t i l l t a k e n - o f f by the shop management and en tered i n t o the computer . The computer r e c o r d s a l l t h i s i n f o r m a t i o n and i s sue the most o p t i m a l route c a r d s . The computer s e l e c t s the o p t i m a l m a t e r i a l to be used and i n s t r u c t s the worker to put an i n d i c a t o r on i t . Once the m a t e r i a l i s brought i n t o the shop, an e n t r y time i s r e c o r d e d by a s ens ing d e v i c e such as the bar code d e t e c t o r l o c a t e d at the e n t r a n c e . Some o p e r a t i o n s are performed to t h i s m a t e r i a l , and when f i n i s h e d the m a t e r i a l w i l l be passed on to o ther workers f o r the next o p e r a t i o n . When the m a t e r i a l i s passed from one l o c a t i o n to a n o t h e r , the worker number t o g e t h e r wi th the m a t e r i a l i n f o r m a t i o n are r e g i s t e r e d and the t ime of o c c u r r e n c e i s aga in r e c o r d e d . The sequence i s repeated u n t i l the f i n i s h e d component l e a v e s the shop and the e x i t t ime i s r e c o r d e d . 20 Once the system i s set up , the workers w i l l soon be unaware of the f a c t that t h e i r performance i s c o n t i n u o u s l y m o n i t o r e d . S i n c e l i t t l e human input i s i n v o l v e d , e r r o r s or m i s i n f o r m a t i o n w i l l be r e d u c e d . However, t h i s i d e a l approach may i n v o l v e changes in the c u r r e n t shop p r a c t i c e and the i n s t a l l a t i o n of the s e n s i n g hardware . A case study of t h i s approach i s proposed f o r the f u t u r e . 21 APPROACH: #1 DETAIL STUDY AND SORTING INVOLVING DEDUCTION, LOGIC AND EXPERIENCE RAW SHOP DATA #2 RAW SIMPLE SHOP DATA SORTING GLOBAL SHOP DATA #3 MODIFIED manual input > SHOP DATA #4 IDEAL automat ic input 1 SHOP DATA F i g u r e 3 . 1 Four Approaches of C o l l e c t i n g Shop Data E T C ETC E T C ANGLE SHEAR PUNCH MATERIAL BILL O e s c r . Oty. Cut/M W150X27 1 12 C200X2 1 1 3 L75X75X6 1 5 ROUTE CARD C o n t r a c t : Drawings: 0 D e s c r . L 1 W150X27 1 C200X21 1 L75X75X6 OPERATIONS .sh Punch Saw X F i t X X X F i g u r e 3.2 M a t e r i a l B i l l and Route C a r d to to GLOBAL SHOP DATA MULTIPLE LINEAR REGRESSION (MLR) LABOUR HOUR VARIABLE (LOCAL SHOP DATA) / Figure 3 . 3 Global and Local Shop Data ro CA) 24 TIME CARDS: Worker No.: 9 Date: July 8,86 Time i n : 7:01 Code Contr/Dwg. beam 5441/11,14 Time 17 .75 Worker No.: 12 Date: July 8,86 Time i n : 7:00 Code Contr/Dwg. beam 5441/11,14, Time 17 1 Worker No.: 25 Date: July 8,86 Time i n : 7:03 Code Contr/Dwg. Time frame 41/115,160 2 beam 5441/11,14,17 4 Contract: 5441 Drawing: 11,14,17 Q Descr. Code L.sh Punch Saw D r i l l 3 W150X24 beam X X 3 L75X75X10 .. X X DRAWINGS: OPERATIONS Wl50X24:SAW DRILL FIT L75X75X10:L.SH FIT TIME(HRS) GLOBAL SHOP DATA: SEQUENCE OF JOB i W150X24: 3 SAW 9 DRILL 12 FIT L75X75X10 :3 L.SH 6 PUNCH f(OPERATIONS)=TIME 3(W150X24 SAW)=1 3(L75X75X10 L.SH)+ 6(W150X24 PUNCH)=.75 9(W150X24 DRILL)+ 12(W150X24 FIT)=4 V .75 1 4 LOGIC AND EXPERIENCE Figure 3.4 An Example of Approach #1 25 TIME CARDS: Worker N o . : 9 Date: J u l y 8,86 Time i n : 7:01 Code Contr /Dwg. beam 5441/11,14 Time 17 .75 Worker N o . : 12 Date : J u l y 8,86 Time i n : 7:00 Code Contr /Dwg. beam 5441/11,14 Time 17 1 Worker N o . : 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 DRAWINGS: Wl50X24:SAW DRILL FIT L75X75X10:L.SH FIT V Wl50X24:3 SAW 9 DRILL 12 FIT L75X75X10:3 L . S H 6 PUNCH GLOBAL SHOP DATA: ADD 3(W150X24 SAW)+9(W150X24 DRILL)+12(W150X24 FIT)+ 3CL75X75X10 L.SH)+6(L75X75X10 PNCH) =5.75 F i g u r e 3.5 An Example of Approach #2 26 APPROACH #1 3(W150X24 SAW)=1 MLR=>W150X24 SAW=l/3 3(L75X75X10 L .SH)+ 6(L75X75X10 PUNCH)=.75 REQUIRES ONE EXTRA EQUATION TO SOLVE 9(W150X24 D R I L D + REQUIRES ONE EXTRA 12(W150X24 FIT)=4 EQUATION TO SOLVE RAW SHOP DATA APPROACH #2 3(W150X24 SAW)+ 9(W150X24 DRILL)+ 12(W150X24 FIT)+ 3(L75X75X10 L .SH)+ 6(L75X75X10 PUNCH)=5.75 REQUIRES FOUR H-tEXTRA EQUATIONS TO SOLVE F i g u r e 3.6 A Comparison of Approach #1 and #2 27 ROUTE CARD: CONTRACT: 5441 DRAWING: 11,14,17 3(W150X24 SAW)=1 3CL75X75X10 L.SH)+6(L75X75X10 PUNCH)=.75 9(W150X24 DRILL)+12(W150X24 FIT)=4 Note: 9 / .75 => Worker n o . / H o u r s spent F i g u r e 3.7 An Example of Approach #3 F i g u r e 3.8 A Scheme of Approach #4 Chapter 4 SORTING AND MULTIPLE LINEAR REGRESSION ON LOTUS 123 4.1 INTRODUCTION The p r e v i o u s l y d e s c r i b e d computer cos t e s t i m a t i n g program in chapter two was w r i t t e n in B a s i c language . T h i s l i m i t e d the a v a i l a b i l i t y , ease of use , c o m p a t i b i l i t y and f l e x i b i l i t y of the program. In order to improve on these p o i n t s , an a n a l y t i c a l program has been deve loped u s i n g the c u r r e n t most popu lar h i g h l e v e l programming language , Lo tus 1-2-3 and i t s macro f e a t u r e [ 6 ] . T h i s macro program c o n s i s t s of the c o n v e r s i o n , s o r t i n g , a n a l y z i n g and p l o t t i n g r o u t i n e s . The c o n v e r s i o n r o u t i n e c o n v e r t s g l o b a l shop data i n t o f i l e s c o n s i s t i n g of the same number of component p i e c e s up to a maximum of two p i e c e s . The s o r t i n g r o u t i n e s o r t s the g l o b a l shop data i n t o f i l e s c o n t a i n i n g the same component. Subsequent ly the g l o b a l shop data of the same component are a n a l y z e d and reduced to l o c a l shop data i . e . l a b o u r hour v a r i a b l e s through M u l t i p l e L i n e a r R e g r e s s i o n (MLR). F i n a l l y the l abour hour v a r i a b l e s are a n a l y z e d and t h e i r s t a n d a r d e r r o r s are graphed by the p l o t t i n g r o u t i n e . The graph w i l l i n d i c a t e to the users the a c c u r a c y of the l a b o u r hour v a r i a b l e s d e r i v e d . The macro program i s menu-dr iven and resembles the Lotus 1-2-3 menu. The opening menu w i l l s t a r t a u t o m a t i c a l l y but can a l s o be acces sed from the r e g u l a r Lot us 1-2-3 s p r e a d s h e e t . 29 30 4.2 LOTUS 1-2-3 ENVIRONMENT The reasons for s w i t c h i n g the programming environment from B a s i c to Lotus 1-2-3 macro language are a v a i l a b i l i t y , ease of use , c o m p a t i b i l i t y and f l e x i b i l i t y . "Lotus 1-2-3" i s c u r r e n t l y the most p o p u l a r bus ines s sof tware package on the market . The macro program deve loped i s composed of s h o r t program r o u t i n e s which can be e a s i l y c o p i e d and assembled f o r o ther p u r p o s e s . S ince each macro program r o u t i n e i s independent , the user can j u s t implement the ones which f i t s h i s needs. The c o m p a t i b i l i t y of c e r t a i n sof tware on d i f f e r e n t computer hardware i s always a p r o b l e m . Even the most p o p u l a r B a s i c language has c o m p a t i b i l i t y problem on d i f f e r e n t computer hardwares . For i n s t a n c e , some adjustments must be made to adapt the c o s t e s t i m a t i n g program to the IBM p e r s o n a l computer as p r e v i o u s l y d e s c r i b e d sof tware i s des igned for the M a i - F o u r B a s i c system. Moreover , programs w r i t t e n in IBM P C ' s B a s i c language may not run even on other IBM c o m p a t i b l e computers . Lo tus 1-2-3 i s a more common environment than B a s i c . Almost a l l b u s i n e s s micro -computers are a b l e to run Lotus 1-2-3. The macro program w i l l r e s i d e i n s i d e L o t u s 1-2-3, and the c o m p a t i b i l i t y of the macro program i s d e f i n i t e l y not a p r o b l e m . Thus more users can take advantage of the r e s u l t s of t h i s r e s e a r c h p r o j e c t . The f l e x i b i l i t y a s s o c i a t e d wi th macro programming w i l l a l l o w u s e r s to customize the macro program to best f i t t h e i r 31 needs . The e f f e c t of each command w i l l be d i s p l a c e d on the s creen such that the user can always fo l l ow and debug the macro programs. Input and output are taken c a r e of by Lotus 1-2-3. The users can expand and improve the e x i s t i n g program and can c r e a t e a c c o u n t i n g , f o r e c a s t i n g and s c h e d u l i n g program r o u t i n e s which share the same g l o b a l shop d a t a . 4.3 LOTUS 1-2-3 MACRO PROGRAM The macro program i s d e s i g n e d for Lotus 1-2-3 r e l e a s e 2 and runs on the IBM PC w i t h 512 K memory and DOS 2 . 1 . The program deve loped i s composed of four main p a r t s ; namely the c o n v e r s i o n , s o r t i n g , a n a l y z i n g and p l o t t i n g r o u t i n e s . Each r o u t i n e i s a worksheet i t s e l f which c o n t a i n s i t s own menus and macro commands. These worksheets are i n t e r - c o n n e c t e d by the opening menu as shown in f i g u r e 4.1 [ 7 ] . There are two ways to s e l e c t an item from the menu. The f i r s t way i s to p o i n t to a menu item by moving the arrow key on the n u m e r i c a l pad to h i g h l i g h t the i tem and then p r e s s i n g the " r e t u r n " key. The second way i s to type the f i r s t l e t t e r of the menu i tem [ 8 ] . The program i s menu-driven such that the user can per form a l l the o p e r a t i o n s j u s t by p o i n t i n g or t y p i n g the menu i t ems , for example, when the "p lo t" item i s s e l e c t e d , the p l o t worksheet w i l l be a u t o m a t i c a l l y r e t r i e v e d and the subsequent menu w i l l prompt the u s e r s f o r next s e l e c t i o n . The i n t e r - c o n n e c t i o n i s o b t a i n e d by u s i n g the a u t o - e x e c u t e macro "\0" which w i l l be a u t o m a t i c a l l y invoked 32 once the worksheet i s l o a d e d . Hence, the macro program w i l l appear as a complete program eventhough i t i s composed of four worksheet s . On the o ther hand, the macro program can be used as an advanced f e a t u r e to the r e g u l a r Lotus menus and f u n c t i o n s . S ince the opening menu can a l s o be accessed from the spreadsheet by h o l d i n g the "ALT" key whi l e p r e s s i n g the "M" key, the user i s f ree to use the f e a t u r e of the macro program even when he i s working on something o ther than the MLR a n a l y s i s . Moreover , the user can combine o ther menus wi th the opening menu to i n c r e a s e p r o d u c t i v i t y . Worksheet de s ign and data format are very important to the macro program. A good worksheet d e s i g n enhances the r e a d a b i l i t y and o r g a n i z a t i o n of the program. With Lotus 1-2-3 r e l e a s e 2, there i s more freedom in d e s i g n i n g the worksheet wi thout r e g a r d to space because on ly the i n d i v i d u a l loaded c e l l s are saved in random access memory. In the macro program r o u t i n e s , the input data always s t a r t s at the upper l e f t hand s i d e of the worksheet i . e . the home p o s i t i o n and the output data are p l a c e d e i t h e r at c e l l A1000 or c e l l A2000. The menus and macro commands are programmed at c e l l BA1 and the h e l p messages are w r i t t e n at c e l l CA1. S i n c e the format of the input data f i l e w i l l a f f e c t the w o r k a b i l i t y of the program, the data s h o u l d be examined more c l o s e l y . In f i g u r e 4 . 2 , the g l o b a l shop data f i l e shown i s a t y p i c a l input da ta f i l e . Note tha t the g l o b a l shop data are a r b i t r a r i l y d e r i v e d and do not neces sary 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 i m i t e d . These 33 data a r e used on ly to i l l u s t r a t e the f u c t i o n s and procedures of the macro program. The f i e l d names of the g l o b a l shop data a r e d e s c r i b e d as f o l l o w s . Column A r e f e r s to the c o n t r a c t number and the drawing number. Column B r e f e r s to the i n d i v i d u a l worker . Column C i n d i c a t e s the number of d i f f e r e n t p i e c e s used to c r e a t e one s t r u c t u r a l member. T h i s number i s e i t h e r one or two s i n c e the member p i e c e i s t e m p o r a r i l y r e s t r i c t e d to two at most. There are two reasons for t h i s r e s t r i c t i o n . F i r s t l y , L o t u s 1-2-3 w i l l on ly a l l o w f i l e names to be e i g h t c h a r a c t e r s l o n g . Thus i t w i l l be very d i f f i c u l t to keep t r a c k of the d i f f e r e n t component f i l e s of more than two members. For i n s t a n c e , the PL10PL16W200X22 and PL 1OPL16W610X113 w i l l have the same f i l e name eventhough the t h i r d p i e c e i s d i f f e r e n t . A method to c o r r e c t t h i s l i m i t a t i o n i s to d e v e l o p a system of n u m e r i c a l names to represen t a l l the combinat ion of d i f f e r e n t members, for example, number 12345678 w i l l r epresen t PL 1OPL16W200X22 and number 23456789 w i l l r e p r e s e n t PL 1OPL16W61 OX 113. S ince there are 100,000,000 numbers a v a i l a b l e , a f u l l range of d i f f e r e n t members can be r e p r e s e n t e d . Next , Lotus 1-2-3 w i l l on ly a l l o w t h i r t y - t w o f i e l d s in the /DATA QUERY f u n c t i o n . An a d d i t i o n of the t h i r d member w i l l i n c r e a s e the t o t a l number of f i e l d s above the t h i r t y - t w o l i m i t as there are t h i r t e e n o p e r a t i o n s for each member. A way to overcome t h i s l i m i t a t i o n i s not to use the /DATA QUERY f u n c t i o n but use the /DATA SORT f u n c t i o n i n s t e a d . However, such a sw i t ch w i l l d r a s t i c a l l y r e t a r d the power and speed of the 34 c o n v e r s i o n r o u t i n e because i t takes s e v e r a l SORT f u n c t i o n s to emulate one QUERY f u n c t i o n . Such a t r a d e - o f f i s not j u s t i f i e d at present because L o t u s 1-2-3 may w e l l remove such a l i m i t a t i o n in the f u t u r e . Column D means the t o t a l number of members used , e . g . i f there are 2 PL8 and 3 W150X24, PIECE=2 and QTY=2+3=5. Column E means the d e s c r i p t i o n of the member e . g . P L 8 . In case of a double p i e c e such as PL8 and PL10, the d e s c r i p t i o n w i l l be PL8PL10. No space i s a l lowed between the two names PL8 and PL10 because t h i s d e s c r i p t i o n w i l l be used l a t e r as a worksheet f i l e name and Lotus w i l l not permi t any space in a f i l e name. A l s o , s i n c e L o t u s w i l l on ly take the f i r s t e i g h t c h a r a c t e r s as a f i l e name, a t t e n t i o n must be p a i d to ensure each d e s c r i p t i o n has the f i r s t e i g h t c h a r a c t e r s d i s t i n c t . For example, PL8W610X113 and PL8W610X217 w i l l have the same f i l e name as they both have PL8W610X in t h e i r f i r s t e i g h t c h a r a c t e r s . The user can change the l a t e r d e s c r i p t i o n as P8W610X217 or PL8W610217 to d i s t i n i s h i t from PL8W610X113. Columns F to AE are the o p e r a t i o n s performed f tor the member(s) . The f i r s t t h i r t e e n o p e r a t i o n s r e f e r to the f i r s t member in case of a double p i e c e and these o p e r a t i o n s w i l l be the o n l y o p e r a t i o n s for a s i n g l e p i e c e . S i m i l a r l y , the next t h i r t e e n o p e r a t i o n s w i l l r e f e r to the second member of the double p i e c e . The t h i r t e e n o p e r a t i o n s are the t e m p l a t e , l a y o u t , a n g l e s h e a r , p l a t e s h e a r , punch, b u r n , saw, d r i l l , l i n e punch , bend, f i t , weld and c l i p r e s p e c t i v e l y . F i n a l l y , column AF i s the t o t a l hours spent on a l l those o p e r a t i o n s 35 t o g e t h e r . The p o s i t i o n of the f i e l d s are important but t h e i r s i z e w i l l not a f f e c t the w o r k a b i l i t y of the macro program. In o ther words, the user can change the s i z e of a l l the columns but shou ld not change t h e i r o r d e r . To copy the macro program, the user shou ld run the Lotus 1-2-3 r e l e a s e 2 system d i s k and s t a r t t y p i n g the menus ,macro commands and h e l p messages on the a p p r o p r i a t e l o c a t i o n s as i n d i c a t e d i n the i n d i v i d u a l r o u t i n e s . For the n o v i c e user of L o t u s 1-2-3, any book on the market such as the Lotus 1-2-3 manual [8] and the U s i n g L o t u s 1-2-3 [9] shou l d g i v e enough guidance to the u s e r . The menus and macro commands are l o c a t e d at c e l l BA1 and the h e l p messages at c e l l CA1 . Note that an apos trophe must be typed be fore the the s l a s h , b a c k s l a s h , formula and n u m e r i c a l l a b e l s . Once these menus and macros are c o p i e d , the user shou ld move the c u r s o r to c e l l BA1. The column BA c o n t a i n s the range names of the menus and macros and must be a c t i v a t e d by u s i n g /RANGE NAME LABEL RIGHT [ 7 ] . The user w i l l then be prompted for the range and he can move the p o i n t e r to cover a l l the range names on column BA. These menus and macros can be t e s t e d by p r e s s i n g "ALT" "M" to see i f the range names are a c t i v a t e d . S ince the macro program has assumed the four i n d i v i d u a l worksheets to be MASTER, SORT, ANALYZE and PLOT. The c o r r e s p o n d i n g menus and macros must be saved under these four f i l e names, i . e . the c o n v e r t i n g , s o r t i n g , a n a l y z i n g and p l o t t i n g r o u t i n e s shou ld be saved under the f i l e names of m a s t e r , s o r t , a n a l y z e and p l o t r e s p e c t i v e l y . The user 36 s h o u l d a l s o copy the g l o b a l data f i l e to t e s t the macro program. 4 .3 .1 CONVERSION ROUTINE The c o n v e r s i o n r o u t i n e which can be acces sed through the opening menu w i l l c o n v e r t the master g l o b a l data f i l e i n t o p i e c e f i l e s ( f i g u r e 4 . 3 ) . The maximum number of d i f f e r e n t components of each r e c o r d i s t e m p o r a r i l y l i m i t e d to two. To s t a r t the c o n v e r s i o n r o u t i n e , the user should l o a d the "MASTER" worksheet i n t o Lotus spreadsheet by the / F I L E RETRIEVE f u n c t i o n . Remember that the c o n v e r s i o n r o u t i n e i s saved as the MASTER f i l e as d e s c r i b e d in the p r e v i o u s s e c t i o n . Once the f i l e i s r e t r i e v e d , the opening menu w i l l a u t o m a t i c a l l y appear and prompt the user for a menu i t e m . When the c o n v e r s i o n item i s chosen , the next menu w i l l ask the user to name an input data f i l e such as the g l o b a l data f i l e to be c o n v e r t e d . Then the next menu w i l l prompt the user for the c h o i c e of output f i l e s . E i t h e r a s i n g l e or double f i l e i s a l l o w e d . If the s i n g l e item i s s e l e c t e d , the macro program w i l l c o n v e r t the s i n g l e p i e c e r e c o r d s of the input data f i l e i n t o an output f i l e c a l l e d "SINGLE". L i k e w i s e , the "DOUBLE" output f i l e w i l l be c o n v e r t e d by grouping the double p i e c e r e c o r d s i n the input data f i l e . F i g u r e s 4.4 and 4.5 show the r e s u l t s of the c o n v e r s i o n r o u t i n e on the g l o b a l shop data f i l e . The c o n v e r s i o n r o u t i n e uses the /DATA QUERY f u n c t i o n to m a n i p u l a t e the d a t a . The input data are assumed to be at 37 the upper l e f t c o r n e r of the s p r e a d s h e e t . The output of the s i n g l e p i e c e r e c o r d s w i l l be p l a c e d at c e l l A1000 w h i l e those of the double p i e c e r e c o r d s w i l l be put at c e l l A2000. In f i g u r e 4 . 6 , the c o n v e r s i o n r o u t i n e s t a r t s w i t h an a u t o - e x e c u t e macro at c e l l BA1. Note that the range name for i t i s "\0" which i s "backs lash z e r o " . The a u t o - e x e c u t e macro w i l l be executed once the "MASTER" worksheet i s loaded and invokes the menu MENUO which i s l o c a t e d at c e l l BA5. C e l l BA3 shows another macro " \ M " which can be acces sed by t y p i n g "ALT" " M " . T h i s macro w i l l a g a i n invoke the menu MENUO. There are s i x menu items i n MENUO. They are the c o n v e r t , s o r t , a n a l y z e , p l o t , Lotus 123 and h e l p . The row at BB6 w i l l f u r t h e r d e s c r i b e the f u n c t i o n s of the menu items as shown in f i g u r e 4 . 1 . The next row i . e . BB7 to BG7 i s the commands be ing executed when the item i s chosen . I f the conver t i tem i s s e l e c t e d , the command /xmMENU1~ w i l l invoke the next menu MENU1. S i m i l a r l y , when the s o r t , a n a l y z e or p l o t i s c h o s e n , the i n d i v i d u a l worksheet w i l l be r e t r i e v e d by the / f r command. The Lotus 123 i tem w i l l e x i t the macro program to the r e g u l a r s p r e a d s h e e t . F i n a l l y , the h e l p i tem w i l l move the screen to c e l l CA1 to g ive user more i n f o r m a t i o n about the program as shown in f i g u r e 4 . 7 . Note that the messages shou l d be typed as long l a b e l s occupy ing the column CA o n l y . MENU1 i s another menu which w i l l ask the user to input a da ta f i l e such as the g l o b a l shop data f i l e . The command / f c c e { ? } ~ w i l l combine the input data f i l e at the "home" p o s i t i o n of the worksheet and the next 38 command /xmCONVERT~ w i l l invoke the next menu CONVERT l o c a t e d below. There are three items in the CONVERT menu. The c e l l s below the s i n g l e and double items are "Convert to s i n g l e p i e c e data f i l e " and "Convert to double p i e c e data f i l e " r e s p e c t i v e l y . The commands /xgSINGLE~ and /xgDOUBLE~ w i l l invoke the macros SINGLE l o c a t e d at c e l l BA20 and DOUBLE l o c a t e d at c e l l BA33 r e s p e c t i v e l y . The macro SINGLE w i l l set the ouput f i e l d s at c e l l A1000 by c o p y i n g the input data f i e l d s . Next , the c r i t e r i o n range i s set at c e l l s AT1 and AT2 . The c r i t e r i o n f i e l d i s PIECE and the c r i t e r i o n i s one. Only the columns A to S are used as the input range because the s i n g l e p i e c e r e c o r d s are s h o r t e r than the double p i e c e r e c o r d s . When a l l the i n p u t , output and c r i t e r i o n ranges are s e t , the data w i l l be e x t r a c t e d . F i n a l l y , the l a s t f i e l d of the output w i l l be r e p l a c e d by the "HOURS" and the output f i l e i s saved under the name of "SINGLE". The macro w i l l r e t u r n the opening menu when the commands are f u l l y e x e c u t e d . S i m i l a r l y , the macro DOUBLE w i l l c o n v e r t the input data f i l e i n t o double p i e c e data f i l e in the same f a s h i o n . Note tha t the column BB c o n t a i n s the macro commands w h i l e the column BE ho lds the comments of those commands. 4 .3 .2 SORTING ROUTINE The s o r t i n g r o u t i n e s o r t s the p i e c e f i l e i n t o f i l e s c o n t a i n i n g components of the same d e s c r i p t i o n . For i n s t a n c e , the s o r t i n g program w i l l s o r t the s i n g l e p i e c e 39 f i l e i n t o f i l e s c o n t a i n i n g one component on ly such as PL8, W150X24, e t c as shown in f i g u r e 4 . 8 . I f there are more than one component such as the double p i e c e f i l e s , the d e s c r i p t i o n of these data f i l e s w i l l be a c o m b i n a t i o n of the components w i t h the component names in a scend ing o r d e r , e . g . PL8W150X24 as a d e s c r i p t i o n . The purpose of the c o n v e r s i o n and s o r t i n g r o u t i n e s i s to reduce the master da ta f i l e i n t o s m a l l e r f i l e s tha t c o n t a i n the same unknowns. I t i s very i n e f f i c i e n t to s o l v e 1000 e q u a t i o n s wi th 1000 unknowns i f the same problem can be c o n v e r t e d and s o r t e d i n t o 100 se t s of 10 e q u a t i o n s wi th 10 unknowns. I t i s e a s i e r to s o l v e a 10X10 m a t r i x and the r e s u l t i s more a c c u r a t e . S i n c e there i s no r e l a t i o n s h i p between any two i n d i v i d u a l s i n g l e p i e c e members, say PL8 and W150X24, the e q u a t i o n s are independent . In o ther words, the system of equat ions can be d e c o u p l e d . Note that double p i e c e data are t r e a t e d as i n d i v i d u a l s t r u c t u r a l members and hence are a l s o independent . For example, PL8W150X24 by i t s e l f s h o u l d g ive the l a b o u r hour v a r i a b l e s for both PL8 and W150X24 i f enough da ta are c o l l e c t e d . I t seems t h a t work has been repeated i n d e t e r m i n i n g the l abour hour v a r i a b l e s . The reason f o r tha t i s sometimes a p a r t i c u l a r member, such as a s m a l l a n g l e , i s a t t a c h e d to o ther members to form a double p i e c e member. The labour hour v a r i a b l e s for t h i s member can o n l y be de termined through the double p i e c e d a t a . Meanwhi le , f o r some o ther members, such as the l a r g e c h a n n e l s and I - s h a p e s , the s i n g l e p i e c e data w i l l be 40 e a s i e r to o b t a i n . A l o g i c a l way to c o n v e r t the master data f i l e i s to group the data a c c o r d i n g to the number of d i f f e r e n t members i t c o n t a i n e d . The c o n v e r t e d data f i l e s w i l l then be f u r t h e r s o r t e d by the d e s c r i p t i o n s of the members. Hence, there are e v e n t u a l l y many s m a l l data f i l e s which c o n t a i n on ly a few unknowns. Such a reduced data f i l e w i l l become the input f i l e s for MLR a n a l y s i s . F i g u r e 4.9 shows a l i s t i n g of the s o r t i n g r o u t i n e . A c l o s e look at t h i s l i s t i n g i n d i c a t e s that there are many s i m i l a r i t i e s between the c o n v e r s i o n and s o r t i n g r o u t i n e s . However, the h e l p messages are q u i t e d i f f e r e n t as seen in f i g u r e 4 .10 . The s o r t i n g macro l o c a t e d at c e l l BA15 a l s o use /DATA QUERY f u n c t i o n for data m a n i p u l a t i o n , but a few t r i c k s have been added . F i r s t , the output f i l e s are no longer l i m i t e d to s i n g l e and double o n l y and the d e s c r i p t i o n of the member(s) must be used to name the output f i l e s . T h i s has been a c h i e v e d by c o p y i n g the d e s c r i p t i o n at c e l l E2 to the range name DESCRIP at BA28. Next , the program must keep t r a c k of the d e s c r i p t i o n s used . S ince data are u s a l l y mixed in the input f i l e , the e f f i c i e n c y of the s o r t i n g r o u t i n e w i l l be g r e a t l y reduced i f the used r e c o r d s are s o r t e d over and over a g a i n . Such a redundancy i s avo ided by d e l e t i n g the the used r e c o r d s as shown in c e l l BB30 i . e . the /dqddq~ command. F i n a l l y , the program w i l l check i f a l l the r e c o r d s have been used by the command /xi(C2=0)~/xmMENU20~ at c e l l BB15. 41 4.3.3 MLR ROUTINE With each component f i l e , one can perform the MLR a n a l y s i s to o b t a i n the l abour hour v a r i a b l e s . The theory and a l g o r i t h m of the MLR a n a l y s i s w i l l be d i s c u s s e d in the next c h a p t e r . F i g u r e s 4.11 and 4.12 show the l i s t i n g and the h e l p messages of the MLR r o u t i n e . Remember that t h i s r o u t i n e shou ld be saved under the f i l e name "ANALYZE" as r e q u i r e d by the macro program. The MLR r o u t i n e can be d i v i d e d i n t o two p a r t s . The f i r s t p a r t which s t a r t s from c e l l BB15 to BB40 i s concerned wi th the d e l e t i o n of the empty " o p e r a t i o n " co lumns. The f i r s t t h i r t y e n t r i e s of each o p e r a t i o n w i l l be summed. If the sum i s z e r o , the column i s s a i d to be empty and i s d e l e t e d . Note that the d e l e t i o n i s done from the l a s t o p e r a t i o n i . e . column AE to the f i r s t one i . e . column F because the o ther way round w i l l r e s u l t in some empty columns not b e i n g summed and hence not be ing d e l e t e d . The second p a r t of the MLR r o u t i n e i s of course concerned w i t h the r e g r e s s i o n i t s e l f . The r e g r e s s i o n i s done by u s i n g the /DATA REGRESSION f u n c t i o n . S i n c e t h i s f u n c t i o n o n l y a l l o w s s i x t e e n independent v a r i a b l e s , the maximum o p e r a t i o n columns must be l e s s than or equa l to s i x t e e n a f t e r the empty columns are d e l e t e d . T h i s requirement w i l l u s u a l l y be f u l f i l l e d as each member w i l l have a o n l y few o p e r a t i o n s . No member w i l l have a l l t h i r t e e n o p e r a t i o n s because some o p e r a t i o n s are i n c o n f l i c t ; for example, a member can e i t h e r be punched or d r i l l e d but not b o t h . In c e l l BB45, the i n t e r c e p t i s f o r c e d to be zero 42 by the command / i z . The i n t e r c e p t or the s e t - u p time as in the case of t h i s study i s assumed to be zero because the program would l i k e to d i s t r i b u t e the s e t - u p time i n t o the l abour hour v a r i a b l e s r a t h e r than hav ing an e x p l i c i t s e t - u p t i m e . The reason i s that the e x p l i c i t s e t - u p time w i l l be t r u e for c e r t a i n combinat ions of o p e r a t i o n s say saw, d r i l l and f i t , but w i l l not be a p p l i c a b l e for o ther combinat ions of o p e r a t i o n s such as saw, d r i l l and weld . Such a problem can be a v o i d e d i f the s e t - u p time i s d i s t r i b u t e d i n t o the l abour hour v a r i a b l e s . 4 .3 .4 PLOTTING ROUTINE The p l o t t i n g r o u t i n e c a l c u l a t e s the r e l a t i v e s t a n d a r d e r r o r for each labour hour v a r i a b l e in terms of p e r c e n t a g e . The r o u t i n e w i l l then d i s p l a y the r e l a t i v e s t a n d a r d e r r o r s in a bar c h a r t and the a b s o l u t e s t a n d a r d e r r o r on top of the l a b o u r hour v a r i a b l e s in a s t a c k e d bar c h a r t . These graphs shou ld h e l p management to p i n - p o i n t p l a c e s where a t t e n t i o n i s needed. F i g u r e s 4.13 and 4.14 show the l i s t i n g and h e l p messages of the p l o t t i n g r o u t i n e . The commands at c e l l s BB12 to BB20 w i l l c a l c u l a t e the r e l a t i v e s t a n d a r d e r r o r by d i v i d i n g the a b s o l u t e s t a n d a r d e r r o r by the c o e f f i c i e n t i . e . the l a b o u r hour v a r i a b l e . The macro REL at c e l l BA26 w i l l p l o t a bar c h a r t showing the r e l a t i v e s t a n d a r d e r r o r as shown i n f i g u r e s 4.15 to 4 .17 . The s tandard e r r o r i n d i c a t e s the degree of p r e c i s i o n in q u a n t i t a t i v e terms [ 1 0 ] . S i m i l a r l y , the macro ABS at c e l l BA38 w i l l p l o t a s t a c k e d 43 bar c h a r t showing the a b s o l u t e s tandard e r r o r s on top of the labour hour v a r i a b l e s . F i g u r e s 4.15 to 4.17 are the r e s u l t s of the p l o t t i n g r o u t i n e on the component f i l e s C200X15, PL8 and W150X24. The upper r i g h t hand p a n e l shows the component f i l e data a f t e r the empty columns are d e l e t e d . The p a n e l r i g h t below i t g i v e s the r e s u l t s of the MLR a n a l y s i s . The i n d i v i d u a l items of the MLR output are d e s c r i b e d as f o l l o w s . The "Constant" term at c e l l A1001 r e f e r s to the s e t - u p time as mentioned b e f o r e . The next i tem below i t r e f e r s to the s t a n d a r d e r r o r of the dependent v a r i a b l e i . e . the t o t a l h o u r s . The "R squared" term at c e l l A1003 r e f e r s to the c o e f f i c i e n t of d e t e r m i n a t i o n . The "No. of O b s e r v a t i o n s " term means the the number of r e c o r d s be ing used . In g e n e r a l , the l a r g e r the number of o b s e r v a t i o n s , the b e t t e r the r e s u l t . S i n c e i t i s not p r a c t i c a l to have an i n f i n i t e number of o b s e r v a t i o n s , a monthly average shou ld be adequate . Assuming ten r e c o r d s per day and twenty days a month, 200 o b s e r v a t i o n s shou ld g ive meaningfu l l abour hour v a r i a b l e s . The next i tem r e f e r s to the degree of freedom of the system of e q u a t i o n s . The X c o e f f i c i e n t s at c e l l A1007 are the l abour hour v a r i a b l e s which are the o b j e c t i v e of the macro program. T h e i r s t a n d a r d e r r o r s are l i s t e d r i g h t below them. The u n i t s of the s t a n d a r d e r r o r and the l abour hour v a r i a b l e s are h o u r s . S i n c e the labour hour v a r i a b l e s are of d i f f e r e n t magni tudes , t h e i r s t a n d a r d e r r o r s are q u i t e d i f f i c u l t to compare. To overcome t h i s , the r e l a t i v e 44 s t a n d a r d e r r o r s are computed in terms of percentage a lone to g ive a b e t t e r p i c t u r e of the p r e c i s i o n of the v a r i a b l e s . The bar and s tacked bar c h a r t s of f i g u r e s 4.15 to 4.17 shou l d the h e l p management to e v a l u a t e shop per formance . F i g u r e 4.18 and 4.19 g i v e the r e s u l t s of the component f i l e s PL8PL10 and PL 10W61 OX 113. S ince there are l e s s o b s e r v a t i o n s than unknowns, no s o l u t i o n can be found . Note that the number of o b s e r v a t i o n s must be g r e a t e r than the number of unknowns in order to have a s o l u t i o n . A l : Convert S o r t Ana lyze P l o t Lotus123 H e l p Convert master g l o b a l data f i l e i n t o p i e c e f i l e s A B C D E F G A1 : Convert Sort p i e c e A B l S o r t | Ana lyze P l o t Lotus123 H e l p data f i l e i n t o component data f i l e s C D E F G A1 : Convert Analyze A A n a l y ze S o r t user s p e c i f i e d B C D P l o t Lotus123 H e l p component da ta f i l e E F G A1 : Convert S o r t Ana lyze P l o t the r e s i d u a l e r r o r A B C D P l o t Lotus123 H e l p E A1: Convert E x i t to A S o r t Lo tus B Ana lyze 123 C D P l o t L o t u s l 2 3 H e l p A1: Convert S o r t Ana lyze P l o t L o t u s l 2 3 H e l p H e l p menu A B C D E F G F i g u r e 4.1 The Opening Menu A B C D E F G H I J K L H N O P Q R S T 1 CQNTR-DU6 HAN PIEC QTY DESCRIPTION ITEN ILAY 1LSH IPSH 1PUN 1BUR ISAM 1DRI 1LIN 1BEN 1FIT 1HEL ICLI 2TEN 2LAY 2 5432-50 7 1 2 11150X24 0 0 0 0 0 0 4 32 0 0 0 0 0 1.5 3 5432-58 21 1 4 PL8 0 32 0 0 24 0 0 0 0 0 0 0 0 2.25 4 5432-58 14 | 4 PL8 0 0 0 0 0 0 0 0 0 0 0 0 0 0.5 5 5432-46 7 I 4 H150X24 0 0 0 0 0 0 0 8 0 0 44 0 0 8 6 5432-17 29 1 4 PL8 0 0 0 0 24 0 0 0 0 0 0 0 0 1.25 7 5432-17 29 1 4 PL8 0 0 0 0 0 0 0 0 0 0 16 0 0 1.75 8 5445-2 9 I 4 C200I15 0 0 0 0 0 8 0 0 0 0 0 0 0 1 9 5445-2 21 1 4 C200X15 0 0 0 0 0 6 0 16 0 0 0 0 0 3 10 5432-9 7 1 6 U150I24 0 0 0 0 0 12 0 68 0 0 24 0 0 12 11 5432-40 14 1 7 U150X24 0 0 0 0 0 0 0 212 0 0 72 0 0 12 12 5432-36 29 1 3 W1S0X24 0 0 0 0 0 6 0 0 0 0 0 0 0 8 13 5432-60 22 14 PL8PL10 0 0 0 20 0 0 0 0 0 0 0 0 0 0 0 14 5432-5 7 1 5 W150K24 0 0 0 0 0 0 0 0 0 0 92 0 0 8 15 5432-51 29 1 4 PL8 0 0 0 0 12 0 0 0 0 0 0 0 0 1.5 16 5442-23 12 2 16 PL10U610XU3 0 0 0 0 14 0 0 0 0 0 0 0 0 0 0 17 5442-4 14 2 3 PL10U610H13 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 18 5432-9 22 2 92 PL8PL10 0 0 0 56 0 0 0 0 0 0 0 0 56 0 0 19 5436-601 2 2 20 PL8PL10 0 0 0 24 0 0 0 0 0 0 0 0 0 0 0 20 5442-34 14 2 6 PL10U610I113 0 0 0 0 B 0 0 0 0 0 0 0 0 0 0 21 5436-513 9 1 7 C200X15 0 0 0 0 0 6 0 16 0 0 16 0 0 4.25 22 5432-51 29 1 4 PL8 0 0 0 0 0 0 0 0 0 0 16 0 0 1.75 23 5432-56 22 1 56 PL8 0 0 0 112 0 0 0 0 0 0 0 0 0 5 24 5442-6 21 5 PL8PL10 0 0 0 6 B 0 0 0 0 0 0 0 0 0 0 25 5436-513' 29 1 6 C200X15 0 0 0 0 0 6 0 16 0 0 32 0 0 6 26 5432-31 29 1 4 PL8 0 0 0 24 0 0 0 0 0 0 0 0 0 1.5 27 5432-31 29 1 4 PL8 0 0 0 0 0 0 0 0 0 0 16 0 0 1.5 28 5442-37 21 1 4 C200X15 0 0 0 0 0 4 0 4 0 0 0 0 0 3 29 5442-33 23 1 8 C200.15 0 0 0 0 0 8 0 52 0 0 0 0 0 6 30 5442-63 25 2 3 PL8PL10 0 0 0 3 3 0 0 0 0 0 0 0 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 8 0 0 0 0 0 0 0 0 0 3.5 0 0 0 2 0 0 0 0 0 0 0 12 0 0 0 1 0 0 0 0 0 0 0 3 0 56 0 0 0 0 0 0 0 0 64 7 0 40 32 0 0 0 0 0 0 0 0 3.5 0 0 0 1 0 0 0 0 0 0 0 7.25 0 4 4 0 0 0 0 0 0 0 0 2.75 0 t 0 0 0 0 0 0 0 0 0 1 F i g u r e 4.2 The G l o b a l Data F i l e 47 CONVERSION PROGRAM O p e r a t i o n s : Cont/Dwg 1st set 2nd set • • / • • • • / • • mm/ mm • m / mm • m / mm • • / • • m m / m m MASTER GLOBAL DATA F I L E O p e r a t i o n s : Cont/Dwg 1st set • • • • • • * • • • / • • • • » • • • * • SINGLE PIECE DATA F I L E O p e r a t i o n s : Cont/Dwg 1st set 2nd set • • • / mm • ••/•• • ••••••• • • • • • • • • •>•••••• DOUBLE PIECE DATA F I L E F i g u r e 4.3 The F u n c t i o n of the C o n v e r s i o n Rout ine A B C D £ F S H I J K L M N O P Q R S I CONTR-DHG HAN PII X QTY DESCRIPTION 1TEH 1LAY 1LSH 1PSH 1PUN 1BUR ISAM 1DRI 1LIN 1BEN lFIT 1UEL 1CLI HOURS 2 5432-50 7 2 U150I24 0 0 0 0 0 0 4 32 0 0 0 0 0 1.5 3 5432-58 21 1 4 PLB 0 32 0 0 24 0 0 0 0 0 0 0 0 2.25 4 5432-58 14 4 PLB 0 0 0 0 0 0 0 0 0 0 0 0 0 0.5 5 5432-46 7 1 4 H150I24 0 0 0 0 0 0 0 B 0 0 44 0 0 B 1.25 6 5432-17 29 4 PL8 0 0 0 0 24 0 0 0 0 0 0 0 0 7 5432-17 29 1 4 PLB 0 0 0 0 0 0 0 0 0 0 16 0 0 1.75 8 5445-2 9 4 C200I15 0 0 0 0 0 8 0 0 0 0 0 0 0 1 9 5445-2 21 1 4 C200J15 0 0 0 0 0 6 0 16 0 0 0 0 0 3 10 5432-9 7 6 U150X24 0 0 0 0 0 12 0 68 0 0 24 0 0 12 11 5432-40 14 1 7 U1S0X24 0 0 0 0 0 0 0 212 0 0 72 0 0 12 12 5432-36 29 3 U1S0X24 0 0 0 0 0 6 0 0 0 0 0 0 0 8 13 5432-5 7 1 5 H150X24 0 0 0 0 0 0 0 0 0 0 92 0 0 8 1.5 14 5432-51 29 1 4 PL8 0 0 0 0 12 0 0 0 0 0 0 0 0 15 5436-513 9 I 7 C200J15 0 0 0 0 0 6 0 16 0 0 16 0 0 4.25 16 5432-51 29 4 PLB 0 0 0 0 0 0 0 0 0 0 16 0 0 1.75 17 5432-56 22 1 56 PLB 0 0 0 112 0 0 0 0 0 0 0 0 0 5 18 5436-513' 29 6 C200115 0 0 0 0 0 6 0 16 0 0 32 0 0 6 19 5432-31 29 1 4 PLB 0 0 0 24 0 0 0 0 0 0 0 0 0 1.5 20 5432-31 29 4 PLB 0 0 0 0 0 0 0 0 0 0 16 0 0 1.5 21 5442-37 21 1 4 C200I15 0 0 0 0 0 4 0 4 0 0 0 0 0 3 22 5442-33 23 8 C200I15 0 0 0 0 0 8 0 52 0 0 0 0 0 6 F i g u r e 4.4 An Example of the S i n g l e P i e c e Data F i l e A B C D E 1 CONTR-DUG MAN PIEC BTY DESCRIPTION F G H I J K L M N O P Q R ITEM 1LAY 1LSH 1PSH IPUN (BUR ISAM 1DRI 1LIN 1BEN 1FIT 1HEI ICLI 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 2 5432-60 22 2 14 PL8PL10 0 0 0 20 0 0 0 0 0 0 0 0 0 0 0 0 8 0 0 0 0 0 0 0 0 0 3.5 3 5442-23 12 2 16 PL10W610X113 0 0 0 0 14 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0 0 0 0 12 4 5442-4 14 2 3 PL 101(6101113 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 3 5 5432-9 22 2 92 PL8PL10 0 0 0 56 0 0 0 0 0 0 0 0 56 0 0 0 56 0 0 0 0 0 0 0 0 64 7 6 5436-601 2 2 20 PL8PL10 0 0 0 24 0 0 0 0 0 0 0 0 0 0 0 0 40 32 0 0 0 0 0 0 0 0 3.5 7 5442-34 14 2 6 PL10H610X113 0 0 0 0 8 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 7.25 8 5442-6 21 2 5 PL8PL10 0 0 0 6 8 0 0 0 0 0 0 0 0 0 0 0 4 4 0 0 0 0 0 0 0 0 2.75 9 5442-63 25 2 3 PL8PL10 0 0 0 3 3 0 0 0 0 0 0 0 0 0 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 derived and do not necessary r e f l e c t the shop performance of Coast Steel Fabricators Ltd. Figure 4 . 5 An Example of the Double Piece Data F i l e 50 BA BB BC BO BE BF BG 1 \0 /xsHENUO* 2 3 \M /xiHENUO* 4 5 MENUO Convert Sort Analyze Plot Lotus123 Help 6 Convert aasSort piece Analyze coiPlot residuExit to LotHelp ienu 7 /xsMENUl* /frSORT* /frANALYZE*/frSORT* /xq CgotoJCAP 8 /xiHENUO* 9 NENU1 Data f i l e ? 10 Hit return to continue 11 (hone} 12 /fcce<?>* 13 /XBCONVERT* 14 15 CONVERT Single Double Help 16 Convert to Convert to Help ienu 17 /xgSINGLE* /xgOOUBLE* {goto)CA34* 18 /xiCONVERT* 19 20 SINGLE /cAl..Sl*A1000* . set: output f i e l d at A1000 21 /cCl..Cl*ATl* c r i t e r i a range at ATI..AT2 22 {goto)AT2*l* copy 1 to AT2 23 {goto}Sl*{endXdown}* anchor at the lower right corner 24 /dqri..{ho«e)* data querry: input range 25 cATl..AT2* c r i t e r i a range 26 oA1000..S1900*eq output range 27 {goto)S1000* go to 2TENP 28 HOURS" replace 2TEMP with HOURS 29 /fxvSlNGLE* f i l e extract: SINGLE 30 A1000..Sl900*r(esc} extract range 31 /xiHENUO* back to HENUO* 32 33 DOUBLE /cAl..AF1*A2000* set: output f i e l d at A2000 34 /cCl..C1*AT1* c r i t e r i a range at ATI..AT2 35 {goto>AT2%2* copy 2 to AT2 36 /dqrilhoieKendKdown}.. data querry: input range 37 {hoieXendJCright}* 38 cATl..AT2* c r i t e r i a range 39 oA2000..AT2900*eq output range 40 /fxvDOUBLE* f i l e extract: DOUBLE 41 A2000..AT2900*r(esc} extract range 42 /xaHENUO* back to MENUO* F i g u r e 4.6 T h e M e n u s a n d M a c r o s o f t h e C o n v e r s i o n R o u t i n e 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 CA HELP 0 : OPENING HENU - Can be accessed froa Lotus spreadsheet by holding "ALT" key while pressing "H" EXIT HENU TO START -By choosing "Lotus 123" option or h i t t i n g "ESC" 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 I SINGLE I PATH: HASTER 1 I I DOUBLE SORT SORT ANALYZE ANALYZE PLOT PLOT F i g u r e 4.7 The H e l p Messages of the C o n v e r s i o n R o u t i n e 52 SORTING PROGRAM O p e r a t i o n s : 1st se t Cont/Dwg Temp L a y . . . F i t Hrs • • • / • • • • • • / • • • ••• ••• ••• • • • / ••• ••• • • • • • SINGLE PIECE DATA F I L E ( a l l o p e r a t i o n s are l i s t e d ) O p e r a t i o n s : 1st set Cont/Dwg Lay L . S h . . D r i l l Hrs • • • / ••• ••• • • • / ••• ••• L75X75X6 (on ly r e q u i r e d o p e r a t i o n s are l i s t e d ) O p e r a t i o n s : 1st set Cont/Dwg Temp L a y . . P u n c h Hrs • • • ••• • • • PL8 (on ly r e q u i r e d o p e r a t i o n s a r e l i s t e d ) —7* O p e r a t i o n s : 1st set Cont/Dwg Saw D r i l l . . F i t Hrs • » • • • • • » • W150X24 (on ly r e q u i r e d o p e r a t i o n s are l i s t e d ) F i g u r e 4.8 The F u n c t i o n of the S o r t i n g Rout ine 53 8A BB BC BD BE BF B6 I \0 /xiHENim* 2 3 \H /xiHENU2Q* 4 5 MENU20 Convert Sort Analyze Plot Lotusl23 Help 6 Convert lasSort piece Analyze coiPlot residuExit to LotHelp ienu 7 /frHASTER* /xiHENU2P /frANALYZE""/frPLOT* /xq {goto)CAP 8 /XBMENU20" 9 HENU21 Data f i l e ? 10 Hit return to continue II {hotel 12 /f c c e i ? ) * 13 /xgSORr 14 15 SORT / x i ^ O r / x i r O W O " i f C2 = 0, end loop and MENU20 16 {H0HE}/cAl..AFPAl000* copy output fields at A1000 17 {60T05E1* go to description 18 /cfENDHDOHNPATP copy description to colmn E 19 (goto)E2* go to description 20 ^"OESCRIP* copy description into DESCRIP 21 /dqiCHOKE} data querry: input range 22 ..{ENDKR16HTKENDKDQWNP 23 CAT1..AT2* c r i t e r i a range 24 OA1000..AF2000* output range 25 eq extract; quit 26 {GOTO}A1000* go to output range 27 /fxv f i l e extract by DESCRIPtion 28 DESCRIP 29 *A1000..AF2000V{esc} extract range 30 /dqddq* delete the extracted records 31 /xgSQRT* start the loop again F i g u r e 4.9 The Menus and Macros of the S o r t i n g Routine 54 CA 1 HELP 20: 2 3 SORT Sort the SIN6LE or DOUBLE data f i l e into component 4 f i l e s containing the sane type of aeaber(s) only 5 6 PURPOSE The purpose of the CONVERT and SORT routines i s to 7 reduce the global shop data into saaller and simpler 8 f i l e s containing the sane type of lei b e r s . 9 It is nore effi c i e n t to solve a 100 sets of 10x10 t a t r i x 10 than to solve a 1000 xlOOO uatrix. 11 12 METHOD The DATA QUERRY function i s used to sort the piece f i l e . 13 The f i r s t description of the data records ( i . e . entry E2) 14 i s used as the CRITERIA to EXTRACT the records out. 15 These records will becoae a data f i l e theiselves by the 16 FILE XTRACT function. The extracted records w i l l be deleted 17 by the DATA QUERRY DELETE function. Thus the next c r i t e r i a 18 w i l l be replaced by a new one and the process repeats 19 again u n t i l l a l l the records are extracted. 20 The QTY (i.e. entry C2) i s used to deteraine the end 21 of the loop. 22 23 ERROR One of the possible errors i s the component f i l e 24 lay have already existed f r o i previous run. 25 Hence, the user should renaie existed component f i l e s 26 with a different naies. 27 28 OPENING MENU - can be accessed froa Lotus spreadsheet by holding "ALT' 29 key while pressing "N" 30 31 FIELDS There are a HI. of 32 f i e l d s in DATA QUERRY F i g u r e 4.10 The H e l p Messages of the S o r t i n g Rout ine 55 BA BB BC BD BE BF B6 I \0 /xiHENU3P 2 3 \H /x»r1EMU30* 4 5 HENU30 Convert Sort Analyze Plot Lotusl23 Help 6 Convert aasSort piece Analyze coaPlot residuExit to LotHelp ienu 7 /frHASTER* /frSORT* /xaHENU3P /frPLOT* /xq {gotoJCAP 8 /xaHENU30* 9 HENU31 Data f i l e ? 10 Hit return to continue II (hote) 12 /fcce{?P 13 /xgDELETE* 14 15 DELETE /xilSU«(AEl..AE30)<=0%/«dcAEr delete eapty coluans: 16 /xifJSUH(ADi..AD30X=0,7wdcADP 17 /xilSUN(ACl..AC30X=0 ,7ydcACP use 8SUH function to check 18 /xi§SUr1(ABl..AB30X=0*/wdcABP i f the colunn i s eapty 19 /xiiSUM(AAl..AA30K=0,7wdcAAP 20 /xi«SUH(Zl..Z30X=0VwdcZP i f yes, delete coluan 21 /xi§SUM(Yl..Y30X=0VwdcYP i f no, check the next one 22 /xi6SUH(Xl.J30X-0VwdcXP on the l e f t 23 /xi§SUH(Wl..W30X=0 ,7wdcWP 24 /xilSUH(Vl..V30X=0*/wdcVP note the deleting starts froa 25 /xi§SUH(Ul..U30X=0'7vdcUP the right end and proceed to 26 /xi§SUH(Tl..T30X=0*/wdcTP the l e f t 27 /xiiSUH(Sl..S30X=0 , ,/wdcSP 28 /xi«SUH(Rl..R30X=0"7vdcRP 29 /xi§SUH(Ql..Q30X=0VwdcQP 30 /xi@SuH(Pl..P30X=0VvdcPP 31 /xiiSUH(Q1..030X=0Vwdc0P 32 /xi§SuH(Nl..N30X=0*/vdcNP 33 /xi§SUH(Hl..M30X=0'7vdcHP 34 /xi6SU(1(Ll..L30X=0VwdcLP 35 /xilSUH(Kl..K30X=0VwdcKP 36 /xi«SUH(Jl..J30X=:0VwdcJP 37 /xilSUH(Il..I30X=0 %/wdcIP 38 /xi8SUH(Hl..H30X=0VwdcHP 39 /xi§SUH(G1..630X=0Vwdc6P 40 /xi8SUH(Fl..F30X=0VwdcFP 41 {goto}F2,> 42 /drrx..{endXright) go to the f i r s t operation 43 UeftKendXdownP DATA REGRESSION RESET X-range 44 yCendXrightL-IendlCdown}'* Y-range 45 oAlOOO'Mzg Output;go 46 {gotolAlOOO* go to OUTPUT range 47 /cE2*DESCRIP* copy description into DESCRIP 48 /fXV FILE EXTRACT VALUE 49 DESCRIP description as f i l e naae 50 *Al..AT1500*r{esc} extract RANGE 51 /xaMENOr back to NENU30 Figure 4.11 The Menus and Macros of the Analyzing Routine 56 CA 1 HELP 30: 2 3 ANALYZE The ANALYZE routine i s composed of two parts. 4 The f i r s t part i s to delete the eapty colunns which i f 5 present w i l l screw up the sebsequent MLR analysis. 6 The second part i s to per f o r i the MULTIPLE LINEAR 7 REGRESSION (MLR) by using the DATA REGRESSION function 8 provided by the new release of Lotus 1-2-3 Version2. 9 10 DELETE The eapty columns are deleted by the WORKSHEET DELETE 11 COLUMN function. The @SUM s t a t i s t i c function i s used to 12 deteraine i f the coluans contain zero entries. It i s 13 very iaportant to delete these empty columns (eapty 14 coluan refers to the coluan with a l l the entries = 0 15 under a particular operation) because a deleted f i l e 16 w i l l aean a snail aatrix to be solved and also the 17 algoritha of the present MLR function w i l l not work 18 i f eapty coluans are present. 19 20 MLR Multiple linear regression w i l l be performed on 21 the data f i l e with the following RANGES: 22 X-RANGE = The operation range 23 Y-RAN6E = The hour range 24 OUTPUT-RANGE = The result of the MLR analysis and 25 will be located at c e l l A1000 26 27 OPENING MENU ~ can be accessed froa Lotus spreadsheet by holding "ALT' 28 key while pressing *M* 29 30 OPERATIONS There are a max. of 16 operation coluans F i g u r e 4.12 The Help Messages of the A n a l y z i n g Rout ine 57 BA BB BC BD BE BF B6 1 \0 /xih"ENU41* 2 3 \H /xit1ENU40* 4 5 HENU40 Convert Sort Analyze Plot Lotusl23 Help 6 Convert lasSort piece Analyze coipPlot residuExit to LotHelp lenu 7 /MASTER* /frSDRT* /frANALYZE* /xiHENU41* /xq* {gotoJCAl* 8 /x•HENU40,• 9 HENU41 Data f i l e ? 10 Hit return to continue 11 {hoie)/fcce{?P data f i l e to be plotted 12 {gotolAtOOO* go to MLR output range 13 {goto>C1008* go to STD. ERR of Coef. 14 /c..{end}{right}*J copy this row of data 15 (downPCdown} into the row below i t 16 +<up}/{up}{upm00s I ERROR = ERROR/VALUE 17 /c\.{end}{rightP 18 {goto>A1009* put a f i e l d naie for 19 I Std. Error of Coef.* this I ERROR row 20 /x«HENU42* 21 22 MENU42 Relative R.Absolute R.Henu Lotusl23 Help 23 Plot rel at i Plot absoluBack to openExit to LotHelp aenu 2 4 /xgREL* /xgABS* /xir1ENU40* /xq* {goto}CAl*/xiMENU42* 25 26 REL /cE2*DESCRIl* copy the description to x-range 27 {goto)C1009*/grgtb graph reset graph type bar 28 a..{endHright>* A-range 29 olaX STD. ERROR* A-legend 30 tfBAR CHART SHOWING Z STD. ERROR* t i t l e f i r s t 31 tx 32 DESCRI1 t i t l e X-axis = description 33 *tyPERCENTAGE* t i t l e y-axis 34 qxFl*x..{endKright}{left}*v quit; x-range = operations; view 35 ncBAR*q* naae create 36 /xiHENU42* back to HENU42 37 38 ABS /cE2*DE5CRI2* copy the description to x-range 39 {goto)C1007*/grgts graph reset graph type stack 40 a..{endHright)* A-range 41 b(down) B-range 42 ..{endHright}* 43 olaLABOUR HOUR* A-legend 44 IbABS. STD. ERROR* B-legend 45 tfSTACK BAR SHQUIN8 ABS. STD. ERROR* 46 tx t i t l e f i r s t 47 DESCRI2 t i t l e X-axis = description 48 *tyH0URS* t i t l e y-axis 49 qxFl*x..{endKright){left)*v quit; x-range = operations; view 50 ncSTACK*q* naie create 51 /xiHENU42* back to HENU42 F i g u r e 4.13 The Menus and Macros of the P l o t t i n g Routine 58 CA 1 HELP 40: 2 3 PLOT There are two ways to see the distributions of the 4 results. The I graph shows how the STAN0AR0 ERRORS of 5 coefficients ( i . e . LABOUR HOUR VARIABLES) vary in t e n s 6 of percentage alone. The STACK graph shows the ABS. 7 STD. ERRORS on top of the COEFFICIENTS (i.e. LABOUR 8 HOUR VARIABLES). These graphs should help the 9 aanageaent to pin-point places where attention i s neede. 10 11 GRAPHS The graphs are naaed as BAR and STACK respectively. 12 Since these graphs are not saved e x p l i c i t l y , the user 13 aust use GRAPH NANE USE SAVE to save thea peraanently 14 15 HARDCOPY a hardcopy of the observed graphs can be obtained 16 by using the Lotus Printgraph on graph f i l e s 17 18 0PENIN6 NENU -- Can be accessed froa the Lotus spreadsheet by holding 'ALT" 19 key while pressing "H" F i g u r e 4.14 The H e l p Messages of the P l o t t i n g Rout ine A B C D E F 6 H I I CONTR-0W6 HAN PIEC QTY DESCRIPTION 1BUR 1DRI IF IT HOURS 2 5445-2 9 1 4 C200X15 8 0 0 1 3 5445-2 21 1 4 C200X15 6 16 0 3 4 543G-513 9 1 7 C200X15 6 16 16 4.25 5 5436-513' 29 1 6 C200X15 £ 16 32 6 6 5442-37 21 1 4 C20OX15 4 4 0 3 7 5442-33 23 1 8 C200X15 8 52 0 6 1000 1001 Constant 1002 Std Err of Y Est 1003 R Squared 1004 No. of Observations 1005 Degrees of Freedoi 1006 1007 X Coefficient(s) 1008 Std Err of Coef. 1009 I Std. Error of Coef. B C D Regression Output: 0 1.136750 0.795632 6 3 0.230052 0.083943 0.100996 0.120980 0.030084 0.036905 52.58817 35.83920 36.54103 gure 4.15 The R e s u l t s of the C200X15 Component F i l e BAR CHART SHOWING % STD. ERROR C2COX13 (771 K STD. ERROR STACK BAR SHOWING ABS. STD. ERROR cn ABS. STD. ERROR A S C O £ F 6 H I J 1 C0NTR-DU6 HAN PIEC QTY DESCRIPTION 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 2 5432-58 21 1 4 PL8 3 5432-58 14 1 4 PL8 4 5432-17 29 1 4 PL8 5 5432-17 29 1 4 PL8 6 5432-51 29 1 4 PL8 7 5432-51 29 1 4 PL8 8 5432-56 22 1 56 PL8 9 5432-31 29 1 4 PL8 10 5432-31 29 1 4 PL8 1000 1001 Constant 1002 Std Err of Y Est 1003 R Squared 1004 No. of Observations 1005 Degrees of Freedoi 1006 1007 X Coefficient(s) 1008 Std Err of Coef. 1009 I Std. Error of Coef. B C D Regression Output: 0.464709 0.914567 9 5 0.020312 0.045426 0.066666 0.104166 0.019483 0.004057 0.017318 0.016768 95.91903 8.931051 25.97807 16.09802 F i g u r e 4.16 The R e s u l t s of the PL8 Component F i l e BAR CHART SHOWING % STD. ERROR 100 1PSH 1PUN 1FII 1771 X S T C T E R H G R STACK BAR SHOWING ABS. STD. ERROR 0.13 T — E3 LABOUR HOLM "¥^ 3 ASS. SIO. EJWOR A B C D 1 C0NTR-DU6 HAN PIEC QTY 2 5432-50 3 5432-46 4 5432-9 5 5432-40 6 5432-36 7 5432-5 DESCRIPTION y150X24 W150X24 W150X24 M150X24 y150X24 H150X24 F 6 H 1BUR ISAM 1DRI 0 0 12 0 6 0 I J 1F1T HOURS 32 8 68 212 0 0 0 44 24 72 0 92 1.5 8 12 12 8 8 1000 1001 Constant 1002 Std Err of Y Est 1003 R Squared 1004 No. of Observations 1005 Degrees of freedoi 1006 1007 X Coefficient(s) 1008 Std Err of Coef. 1009 7. Std. Error of Coef B C D Regression Output: 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 gure 4.17 The R e s u l t s of the W150X24 Component F i l e BAR CHART SHOWING % STD. ERROR 4fiO 1BUR 1SAW 1DRI IFTT W160X24 1771 * STD. ERROR STACK BAR SHOWING ABS. STD. ERROR 1.2 i B U R i s«w I M inr W160K24 [771 LABOUR HOUR f V \ l AOS. STD ERROR A B C 0 E F 6 H I J K L 1 C0NTR-DW6 NAN PIEC QTY DESCRIPTION tPSH 1PUN 1CLI 2PSH 2PUN 2CLI HOURS 2 5432-60 22 2 14 PL8PL10 20 0 0 8 0 0 3.5 3 5432-9 22 2 92 PL8PL10 56 0 56 56 0 64 7 4 5436-601 2 2 20 PL8PL10 24 0 0 40 32 0 3.5 5 5442-6 21 2 5 PL8PL10 6 8 0 4 4 0 2.75 6 5442-63 25 2 3 PL8PL10 3 3 0 1 0 0 1 Error lessage froa Lotus: Too feu observations for nuiber of variables F i g u r e 4.18 The R e s u l t s of the PL8PL10 Component F i l e A B C D E F 8 H I 1 C0NTR-DW6 HAN PIEC QTY DESCRIPTION 1PSH 1PUN 2BUR HOURS 2 5442-23 12 2 16 PL10H610X113 0 14 2 12 3 5442-4 14 2 3 PL10W610X113 2 0 1 3 4 5442-34 14 2 6 PL10H610X113 0 8 I 7.25 Error lessage froe Lotus: Too feu observation for nuiber of variables F i g u r e 4.19 The R e s u l t s of the PL10W610X113 Component F i l e Chapter 5 THEORY OF MLR 5.1 SYSTEM OF EQUATIONS In order to e v a l u a t e the labour hour v a r i a b l e s , a system of a l g e b r a i c e q u a t i o n s Ax = b must be s o l v e d . The m a t r i x A r e p r e s e n t s the set of o p e r a t i o n s done on a p a r t i c u l a r p i e c e of a component i n d i f f e r e n t o b s e r v a t i o n s . The v e c t o r x r e p r e s e n t s the l abour hour v a r i a b l e s that are be ing d e t e r m i n e d , and b i s the time the worker has spent on the o p e r a t i o n s c o n t a i n e d in matr ix A . An example of a system of a l g e b r a i c e q u a t i o n s from the f a b r i c a t i n g shop i s i l l u s t r a t e d by f i g u r e 5 . 1 . The system Ax = b w i l l have an exact s o l u t i o n i f the number of independent e q u a t i o n s equa l s the number of unknowns. But i n g e n e r a l , f or a r e a l c a s e , there w i l l be more e q u a t i o n s than unknowns because a l a r g e number of e q u a t i o n s w i l l be observed in the f a b r i c a t i n g shop for the r e l a t i v e l y fewer o p e r a t i o n s . When a system Ax = b has more e q u a t i o n s than unknowns, the system i s s a i d to be i n c o n s i s t e n t . I n c o n s i s t e n c y means that there p r o b a b l y does not e x i s t a v e c t o r x that p e r f e c t l y f i t s the data b, o r , i n o ther words, the v e c t o r b p r o b a b l y w i l l not be a combinat ion of the columns of A . 64 65 5.2 REGRESSION ANALYSIS I n c o n s i s t e n c y i s a common phenomenon for the g l o b a l shop data as the time of p e r f o r m i n g the same o p e r a t i o n on a p a r t i c u l a r p i e c e w i l l f l u c t u a t e s l i g h t l y from day to day for the same worker and g r e a t l y between d i f f e r e n t workers . For example, worker A may take 2 to 3 minutes to d r i l l a ho le on C200X21 w h i l e worker B may take 4 to 5 minutes to do the same j o b . Thus , i t does not make sense to expect an exact s o l u t i o n x. Rather an approximate s o l u t i o n x, which r e p r e s e n t s the best e s t i m a t e of the l abour hour v a r i a b l e s , should be u s e d . M u l t i p l e L i n e a r R e g r e s s i o n (MLR) i s used to g ive t h i s average s o l u t i o n x. The e r r o r between the a c t u a l observed t ime and the approximate time i s E = | | A x - b | | which i s the a b s o l u t e d i s t a n c e between the v e c t o r s b and Ax . The o b j e c t i v e of the MLR i s to min imize E = | | A x - b | | . A number of methods can be used to a c h i e v e t h i s m i n i m i z a t i o n and the l e a s t squares f i t has been c h o s e n . S trang [ 1 1 ] has shown that m i n i m i z i n g E i s e q u i v a l e n t to f i n d i n g the p r o j e c t i o n Ax which i s c l o s e s t to b in the column space . From the geometry of f i g u r e 5 .2 , the r e s i d u a l e r r o r v e c t o r (Ax-b) must be p e r p e n d i c u l a r to the column space . S i n c e the column space can be r e p r e s e n t e d by any v e c t o r A y , Ay must be p e r p e n d i c u l a r to (Ax-b) or p r o p e r t y of o r t h o g o n a l i t y T A = t r a n s p o s e of A Normal equat ion 66 Hence, the approximate s o l u t i o n x must s a t i s f y the rp _ rp rp _ rp normal e q u a t i o n A Ax-A b = 0 or A Ax = A b for E to be minimum. Note that the v e c t o r s x and x are d i f f e r e n t in the sense that x i s the best approximate of x which g i v e s the v e c t o r Ax c l o s e s t to b. Hence, r e s i d u a l e r r o r E = | | A x - b | | w i l l be present un le s s i t i s a p e r f e c t f i t . 5.3 ALGORITHM OF MLR The a l g o r i t h m of MLR Macro program se t s up the normal e q u a t i o n f i r s t and then s o l v e s the e q u a t i o n for x. T - T Normal e q u a t i o n : A Ax - A b = 0 T - T A Ax = A b [ A ] T [A] i =[A] T b In the computer , we can s t o r e A and b toge ther wi th t h e i r T p r o d u c t s a f t e r p r e - m u l t l p l i c a t i o n by A for e f f i c i e n c y of data s t o r a g e , i . e . [ A ] T [A | b] = [ A T A | A T b ] Once the normal e q u a t i o n has been computed, we can reduce the LHS matr ix to the i d e n t i t y m a t r i x and the c o r r e s p o n d i n g RHS w i l l g i v e the average s o l u t i o n x. 67 T — T [A A] x = A b | row o p e r a t i o n [I ] i = x T The neces sary and s u f f i c i e n t requirement for m a t r i x A A to be r e d u c i b l e to the i d e n t i t y matr ix i s t h a t there are n o n - z e r o p i v o t s a long the d i a g o n a l . I f no n o n - z e r o p i v o t can be found a l ong a co lumn, the matr ix i s s i n g u l a r and has no unique s o l u t i o n . Hence, p a r t of the a l g o r i t h m for t e s t i n g the e x i s t e n c e of a s o l u t i o n i s to f i n d i f there i s a n o n - z e r o p i v o t a long each co lumn. Once the i d e n t i t y matr ix has been computed, the c o r r e s p o n d i n g RHS v e c t o r w i l l be the approximate s o l u t i o n x ( f i g u r e 5.3). O B S E R V A T I O N S O F A T Y P I C A L C O M P O N E N T F R O M A F A B R I C A T I O N S H O P : HOURS 2 SAW + 8 DRILL = 8 4 SAW + 2 DRILL + 10 F I T = 10 1 SAW + 2 DRILL + 2 F I T = 5 3 SAW + 12 F I T = 9 6 SAW + 8 DRILL + 20 F I T = 21 ' 2 4 1 3 6 \ 8 2 2 0 8 0 10 2 12 20 8 10 5 9 21 where A = 2 8 0 4 2 10 1 2 2 3 0 12 6 8 20 / SAW \ X = D R I L L b y F I T j / 8 10 5 9 21 F i g u r e 5.1 A System of A l g e b r a i c Equat ions 69 a space d e f i n e d by the columns of A F i g u r e 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: G l o b a l data from component f i l e e . g . For L75X75X6, there are a number of g l o b a l shop data for the f o l l o w i n g o p e r a t i o n s : -Layout -Ang le Shear -Punch Transform g l o b a l shop t data to matr ix form A A ML V No unique s o l u t i o n SOLUTION: Labour Hour V a r i a b l e s e . g . Time f o r -Layout -Ang le Shear -Punch V END OF PROGRAM F i g u r e 5.3 A Flow Chart for the MLR A a n a l y s i s Chapter 6 CONCLUSIONS A computer program on c o s t e s t i m a t i o n which q u i c k l y and a c c u r a t e l y performs a l l the c a l c u l a t i o n s , s o r t i n g s and s t a t i s t i c s r e q u i r e d has been deve loped u t i l i z i n g the program BASIC language . The management of Coast S t e e l F a b r i c a t o r s L t d . has found that such a program has reduced t h e i r t o t a l e s t i m a t i n g time by 30%. A procedure has been deve loped to o b t a i n the labour hour v a r i a b l e s used in e s t i m a t i n g . In the case of the s t a r t - u p of a new f a b r i c a t i o n p l a n t , t h i s procedure w i l l serve as a powerfu l t o o l for t ime s t u d i e s of the v a r i o u s l abour o p e r a t i o n s . For c o n t i n u o u s o p e r a t i o n of a f a b r i c a t i o n p l a n t , t h i s procedure w i l l p r o v i d e feedback and update 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. T h i s p r o j e c t conc luded tha t the e a r l i e r r e s e a r c h was s u c c e s s f u l and brought in r e a l a p p l i c a t i o n in the e s t i m a t i o n of s t e e l s t r u c t u r e s . T h i s r e s e a r c h p r o j e c t i s a c o n t i n u a t i o n of the two p r e v i o u s ones done at U . B . C . by Y . C . Leung [2] and Bruce Forde [ 1 ] . The o p e r a t i o n a l approach used i s a m o d i f i c a t i o n of the one proposed by Y . C . Leung . The idea of M u l t i p l e L i n e a r R e g r e s s i o n on c o s t e s t i m a t i o n was i n t r o d u c e d by Bruce Forde but a d i f f e r e n t a l g o r i t h m was used . The L o t u s 1-2-3 macro programming i s proven to be s u p e r i o r i n a n a l y z i n g the l abour hour v a r i a b l e s . The 71 72 work on l abour hour v a r i a b l e s was done u s i n g Lotus 1-2-3 and i t s macro c a p a b i l i t y . There was a change of the computer env ironment . The e a r l i e r c o s t e s t i m a t i n g program was deve loped under the B a s i c language environment but the l a t e r l a b o u r hour a n a l y s i s u t i l i z e d the Lotus 1-2-3 env ironment . The main reasons for that are a v a i l a b i l i t y , ease of use , c o m p a t i b i l i t y and f l e x i b i l i t y . Lo t us 123 i t s e l f i s f a i r l y easy to use and the macro program w i l l run on a v a r i e t y of computers . The f l e x i b i l i t y of the macro program w i l l a l l o w users to modify or a d j u s t the program to best f i t t h e i r needs . From the study of the c u r r e n t cos t e s t i m a t i o n p r o c e s s , the management c o n c l u d e d that the input of drawings i s the most time consuming p a r t . The management a d v i s e d tha t f u t u r e r e s e a r c h on the d i r e c t input of drawings s ho u l d be i n v e s t i g a t e d . C u r r e n t l y , drawing data are f i r s t t r a n s f e r r e d to t a k e - o f f sheets and then typed i n t o the computer . A new method shou ld be deve loped to speed up t h i s p r o c e s s . The new approach shou ld take advantage of the t echno logy of computer a i d e d d r a f t i n g and computer g r a p h i c s . R e s u l t s from t h i s case study can be h e l p f u l to o ther s t e e l f a b r i c a t o r s or even other m a n u f a c t u r e r s . Chapter 7 FUTURE RESEARCH 7.1 COMPUTER COST ESTIMATION PROGRAM ON LOTUS 1-2-3  SPREADSHEET A flow c h a r t and an example g ive guidance f o r f u t u r e r e s e a r c h . F i g u r e 7.1 shows a flow c h a r t of how the computer c o s t e s t i m a t i n g program sh ou ld be implemented in the Lotus 1-2-3 spreadsheet env ironment . The macro program to be deve loped shou ld be menu-dr iven to l e a d the u s e r s for the subsequent t a k e - o f f , m a t e r i a l , computat ion and f i n a l summary menus. Each subsequent menu w i l l f u r t h e r l e a d the users to per form the r e q u i r e d work. The f u t u r e macro program sh ou ld connect the i n d i v i d u a l f i l e s toge ther and i n c l u d e a HELP menu to g i v e users o n - l i n e a s s i s t a n c e . An example of the v a r i o u s menus can be seen in f i g u r e 7 .2 . These flow c h a r t and example, toge ther wi th the user manual of the computer c o s t e s t i m a t i n g program, sh ou ld g ive enough g u i d e l i n e s for a r e w r i t e of the program on a spreadsheet env ironment . 7.2 INPUT FROM DRAWINGS A f a s t and e f f i c i e n t method of e n t e r i n g da ta from drawings i n t o the computer shou ld be deve loped because t h i s i s now the most t ime consuming p a r t of the whole e s t i m a t i n g p r o c e s s . C u r r e n t l y , i n f o r m a t i o n i s manual ly t r a n s f e r r e d from drawings onto t a k e - o f f sheets and then typed i n t o the computer . Both s teps are slow and c o n t a i n the p o s s i b i l i t y 73 74 of unnecessary e r r o r s . F u t u r e r e s e a r c h shou ld i n v e s t i g a t e a method of i n p u t t i n g t h i s i n f o r m a t i o n in a s i n g l e s t e p i n s t e a d of two. The l a t e s t t echno logy in computer g r a p h i c s shou ld a l s o be c o n s i d e r e d in the new i n v e s t i g a t i o n . There are new hardware d e v i c e s such as the image reader [12] and the drum-scanner [13] which can t r a c e diagrams on d r a w i n g s . Given the s u i t a b l e harware and so f tware , i n f o r m a t i o n can be t r a n s f e r r e d from drawings to the computer in a much b e t t e r way. F i g u r e 7.3 shows a scheme of t h i s method. Technology i n computer a i d e d drawing sh ou ld a l s o be c o n s i d e r e d in d e v e l o p i n g the i n p u t t i n g method. Nowadays, many drawings are drawn by computer and i n f o r m a t i o n on the drawings a l r e a d y e x i s t s in the form of a data base [14] [15 ] . F i g u r e 7.4 i n d i c a t e s t h i s method. 75 OPENING MENU T a k e - o f f E n t r y Menu M a t e r i a l S e c t i o n Menu Computation Menu F i n a l Summary Menu ^ Help Menu new f i l e f i l e old ^ e d i t p r i n t >^ add/change remove ^ p r i n t page by page summary 9> labour grouping >^ m a t e r i a l grouping m a t e r i a l summary F i g u r e 7.1 A Flow C h a r t for Computer Cost E s t i m a t i n g Program i n L o t u s 123 76 ESTIMATING F I L E Opening Menu T a k e - o f f M a t e r i a l Compute F i n a l summary H e l p TAKE-OFF T a k e - o f f Menu New f i l e Old f i l e P r i n t MATERIAL M a t e r i a l Menu Add/Change Remove P r i n t COMPUTATION Computation Menu Page/Page Labour grouping M a t e r i a l grouping M a t e r i a l summary FINAL SUMMARY HELP Help Menu *^ • • • • • • • • • • • • • • • • • • F i g u r e 7.2 An Example Showing the Spreadsheets and Menu 77 Drawings scanning hardware F i g u r e 7.3 A Scheme of U t i l i z i n g Computer G r a p h i c s DRAFTING OFFICE F i g u r e 7.4 A Scheme U t i l i z i n g C . A . D . Technology REFERENCES 1. F o r d e , B . , "A C o n t r i b u t i o n to Computer A i d e d Des ign E v a l u a t i o n U t i l i z i n g F l e x i b l e E s t i m a t i o n and M u l t i p l e L i n e a r R e g r e s s i o n A n a l y s i s " , M . A . S c . T h e s i s , U n i v e r s i t y of B r i t i s h C o l u m b i a , 1984. 2. Leung , Y . C . , "A C o n t r i b u t i o n to Computer A i d e d Des ign E v a l u t i o n of S t e e l S t r u c t u r e s " , M . A . S c . T h e s i s , U n i v e r s i t y of B r i t i s h C o l u m b i a , 1984. 3 . L a n e , R . E . , " A d m i n i s t e r i n g and C o n t r o l l i n g P l a n t O p e r a t i o n s " , P r e n t i c e - H a l l , 1979. 4. N i x o n , D . , " E s t i m a t i n g the Cost of Smal l S t e e l B u i l d i n g s " , Canadian J o u r n a l of C i v i l E n g i n e e r i n g , V o l . 1, No. 2, December 1974. 5. B e r r y , G . L . , "Shop F l o o r I n f o r m a t i o n System: Des ign and Implementat ion", E n g i n e e r i n g D i g e s t (1984) . 6. S i n h a , G . and Hennessey, K . , "High L e v e l R e p r o d u c t i v e Programming", F i r s t Annual Phoenix Conference on Computers and Communicat ions , P r o c e e d i n g s , I E E E , 1985. 7. A n d e r s e n , D. and Cobb, D . F . , "1-2-3: T i p s , T r i c k s , and T r a p s " , Que C o r p o r a t i o n , I n d i a n a , 1984. 8. Posner , J . , H i l l , J . , M i l l e r , S . E . , G e t t h e i l , E . and D a v i s , M . L . , "Lotus 1-2-3 U s e r ' s M a n u a l " , L o t u s Development C o r p o r a t i o n , 1983. 9. L e B l o n d , G . T . and Cobb, D . F . , "Using 1-2-3", Que C o r p o r a t i o n , I n d i a n a , 1983. 10. Hodges, J . L . , K r e c h , D . , C r u t c h f i e l d , R . S . , " S t a t L a b : An E m p i r i c a l I n t r o d u c t i o n to S t a t i s t i c s " , M c G r a w - H i l l , New 79 80 Y o r k , 1975. 11. S t r a n g , G . , " L i n e a r A l g e b r a and I t s A p p l i c a t i o n s " , 2nd E d . , Academic P r e s s , New Y o r k , 1980. 12. M a l d a , A . and Shibayama, J . , " A p p l i c a t i o n of Automat ic Drawing Reader for the U t i l i t y Management System", IEEE 1985 Computer A r c h i t e c t u r e for P a t t e r n A n a l y s i s and Image Database Management, 1985. 13. H a r a d a , H . , I t o h , Y . and I s h i i , M . , " R e c o g n i t i o n of Freehand Drawings in Chemica l P l a n t E n g i n e e r i n g " , IEEE 1985 Computer A r c h i t e c t u r e for P a t t e r n A n a l y s i s and Image Database Management, 1985. 14. T e i c h o l z , E . , "CAD/CAM Handbook", M c G r a w - H i l l , 1985. 15. Knox, C . S . , "CAD/CAM Systems P l a n n i n g and Implementat ion", M. Dekker , 1983. APPENDIX A computer d i s k e t t e i s -appended to show the macro programs on labour hour v a r i a b l e s . T h i s d i s k e t t e c o n t a i n s the worksheet data f i l e s and must be used w i t h the system d i s k e t t e of Lotus 1-2-3 Re lease 2. The user s h o u l d make a copy of the appended d i s k e t t e and works w i t h the backup c o p y . Note that no p r o t e c t i v e tape shou ld be p l a c e d on the backup d i s k e t t e in r u n n i n g the program. To s t a r t the macro program, the user shou l d use / F I L E RETRIEVE to l o a d the "MASTER" f i l e i n t o the L o t u s s p r e a d s h e e t . Once l o a d e d , the opening menu w i l l a u t o m a t i c a l l y appear and i t can a l s o be a c c e s s e d by p r e s s i n g "ALT" "M" a f t e r the user has l e f t the menu. To e x i t from the macro program, one s h o u l d use e i t h e r the "Lotus 123" menu i tem or the "ESC" key. There i s a HELP menu to a s s i s t the user f o r the v a r i o u s f u n c t i o n s o f f e r e d by the macro program. The user manual and the l i s t i n g s of the c o s t e s t i m a t i n g program can be o b t a i n e d through the author or h i s s u p e r v i s o r D r . S . F . S t i e m e r . 81 

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:
https://iiif.library.ubc.ca/presentation/dsp.831.1-0062932/manifest

Comment

Related Items