BUSINESS APPRAISAL FOR PRODUCT LIFECYCLE MANAGEMENT IMPLEMENTED INDUSTRIES
67
1.2 REDUCTION IN PRODUCT DEVELOPMENT COSTS
1.2.1 Formula for Table 4.18
Cell Cell Formula Equation Copied to
D4 =B4*C4/100 (4.17) D5:D14
1.2.2 Formula for Table 4.19
Cell Cell Formula Equation Copied to
C6 =IF(C4=0,0,C4+C5-1) D6:I6
C7 =IF((C6 > (365*3)),"Phase exceeds 3 years","") D7:I7
C8 =ROUND(C4/30,0) D8:I8
C9 =ROUND(C6/30,0) D9:I9
C10 =C9-C8 D10:I10
C18 =IF(SUM(C12:C14)=C5,"ok","error") D18:I18
1.2.3 Formula for Table 4.20
Cell Cell Formula Equation Copied to
B16 =B15*'Table 4.19'!C5 Total persons in that C16:H16
phase*phase duration
1.2.4 Formula for Table 4.21
Cell Cell Formula Equation Copied
to
B3 =('Table 4.20'!$B$4*'Table 4.19'!$C$12+'Table (4.18) Other
4.20'!$C$4*'Table 4.19'!$D$12+'Table cells
4.20'!$D$4*'Table 4.19'!$E$12+'Table
4.20'!$E$4*'Table 4.19'!$F$12+'Table
4.20'!$F$4*'Table 4.19'!$G$12+'Table
4.20'!$G$4*'Table 4.19'!$H$12+'Table
4.20'!$H$4*'Table 4.19'!$I$12+'Table
4.20'!$I$4*'Table 4.19'!$J$12+'Table 4.20'!$J$4*'Table
4.19'!$K$12+'Table 4.20'!$K$4*'Table
4.19'!$L$12)/365
1.2.5 Formula for Table 4.22
Cell Cell Formula Equation Copied to
C3 ='Table 4.21'!$B$3*(1-'Table 4.18'!D4/100) (4.19) Other cells
68
1.2.6 Formula for Table 4.23
Cell Cell Formula Equation Copied to
C3 =('Table 4.21'!B3-'Table 4.22'!C3)*'Table 4.18'!E4 (4.20) Other cells
1.3 REDUCTION IN TIME-TO-MARKET
1.3.1 Formula for Table 4.24
Cell Cell Formula Equation Copied to
D7 =D5*D6 Delay caused by changes = No. of changes in E7:J7
phase * Avg. delay due to one change
D8 =D4-D7 Phase workload without any changes = phase E8:J8
workload with changes - delay caused by changes
1.3.2 Formula for Table 4.25
Cell Cell Formula Equation Copied to
C10 =C9+'Table 4.24'!D8 Phase duration with PLMs = Delay D10:I10
caused by changes with PLMs + Phase
workload without any changes
C11 ='Table 4.20’!B16 D11:I11
C12 =C11-C10 Time saving = Phase duration planned – D12:I12
Phase duration with PLMs
1.3.3 Formula for Table 4.26
Cell Cell Formula Equation Copied to
C7 =I2*(C6/I3) (4.21) D7,E7
1.4 TOTAL COST SAVINGS
1.4.1 Formula for Table 4.29
Cell
F3
G3
H3
Cell Formula
='Table 4.28'!F7*'Table 4.28’!F3
='Table 4.28'!F7*'Table 4.28'!G3+'Table 4.28'!G7*'Table 4.28’!F3
='Table 4.28'!F7*'Table 4.28'!H3+'Table 4.28'!G7*'Table 4.28'!G3+'Table
4.28'!H7*'Table 4.28'!F3
69
1.5 PLMs ACQUISITION AND OPERATION COSTS
1.5.1 Formula for Table 4.32
Cell Cell Formula Equation Copied to
C16 =C10*‘Table 4.31’!H3 + C15 (4.22) D16,E16
C18 =C17*‘Table 4.31’!H8 (4.23) D18,E18
C25 =C21*‘Table 4.31’!H9 + C24 (4.24) D25,E25
C26 =C21* ‘Table 4.31’!H9
1.6 RETURN ON INVESTMENT
1.6.1 Formula for Table 4.34
Cell Cell formula Equation
C15 =ROUND(B15/(1 + F10),2)
C16 =ROUND(B16/(1+F10)^2,2)
C17 =ROUND(B17/(1+F10)^3,2)
F20 =(B14) + NPV(F10,B15:B17) (4.26)
F22 =IRR(B14:B17,0.1) (4.27)
F24 =F20/36
70
APPENDIX 2
TABLES AND FIGURES: EPD PROJECTS
1.1 IMPACT OF PLMs ON TOTAL QUALITY COSTS
Table 1.1 Appraisal costs before and after PLMs
71
Figure 1.1 Trend of appraisal costs before PLMs
Table 1.2 Appraisal cost forecasts using linear regression
Table 1.3 Appraisal costs with and without PLMs
72
Table 1.4 Labour cost details
Table 1.5 Time spent for searching data before and after PLMs
Table 1.6 Prevention costs per year
73
Figure 1.2 Trend of prevention costs before PLMs
Table 1.7 Prevention cost forecasts
Table 1.8 Prevention costs with and without PLMs
74
Table 1.9 Internal failure costs before and after PLMs
Figure 1.3 Trend of total failure costs before PLMs
75
Table 1.10 Failure cost forecasts
Table 1.11 Failure costs with and without PLMs
Table 1.12 Quality reporting costs before and after PLMs
76
Figure 1.4 Trend of quality reporting costs before PLMs
Table 1.13 Quality reporting cost forecasts
Table 1.14 Quality reporting costs with and without PLMs