Q)

--------

N O 9 B D G H L M 1 Chapter 06 Enterprise Analysis and Design Methodology 2 3 Q - 2: [20] (Multiple Linear Regression) 4 Suppose you work for a consultant company that implements electronic medical records for 5 doctor's offices. You have data on the requirements analysis duration for previous projects as 6 follows: 7 Patients No Doctors No Time (hrs) 8 1500 2. 28.0 2400 3 40.0 10 1250 1 20.5 11 3150 2 40.0 12 1850 3 35.0 13 1200 1 20.0 14 5700 5 80.0 15 4200 4 58.0 16 17 [12] a) Do a regression analysis to develop an equation that can be used to estimate the 18 requirements analysis duration for a project 19 [04] b) Verify your equation using cel's Regression Analysis. 20 [04] c) Estimate the requirements analysis duration for a project that has 21 3000 patients and 3 doctor(s). 22 D E F G . 1 J K K L M z N O P Q Q R S T U Given: Input x1 1500 2400 1250 3150 1850 1200 5700 4200 Input Output x2 Y 2 28.0 3 40.0 1 20.5 2 3 1 20.0 5 80.0 4 4 58.0 40.0 35.0 Then, Linear Regression model: and Fitted Linear Regression model: y = Bo + B1X1 + B2X2 + = Bo + BAX: + B2X2 Our Goal is to find the vector B where = (X'X)-1 X'y Hence, Patients Doctors 1500 2 28.0 A B 22 23 Solution: 24 Q - 2: 25 [12] [] a) 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 X = y = = 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 X': X Transpose 1 X'= 1500 2 2 Using Excel Matrix Multiplication: =MMULT(D54:K56,D44:F51) or Using only the SUMPRODUCT functior inefficient) 21 3000 X'X= 8 1500 1500 2250000 2 3000 X' X = 4 Then, again using Excel Matrix Multiplication: EMMULT(D54:K56,144:151) Then, Using only the SUMPRODUCT functio inefficient) 28 42000 X' y = X' y A A B C E F G . 1 J K L M N P Q Q R S T U V D 2 Using Excel Matrix Multiplication: =MMULTID54:K56,D44:F51) or Using only the SUMPRODUCT functiol (inefficient) X' X = X' X = 8 1500 1500 2250000 2 3000 2 3000 4 Then, again using Excel Matrix Multiplication: EMMULTID54:K56.144:151) Then, Using only the SUMPRODUCT functio inefficient) BBBBBmn&8 388m88mm X'y = 28 X'y= 42000 56 It is not possible to compute the inverse of X'X using the SUMPRODUCT function. You should use Excel MINVERSE function. After selecting the region for the inverse, type: =MINVERSE(range of X'X), then press Again using Excel, obtain the Matrix Inve =MINVERSE(D61F63) (X' X}"'- 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 (X' x) = B= (X'X)** X'y = 4.2904 0.0084 5.2045 Similarly, it is not possible to use the SUMPRODUCT function for the last step. So, again use Excel matrix multiplication. B = Therefore, the fitted regression model is: (X1,42) = Bo + B1X1 + B2X2 (x1,x2) = 4.29039 You can also use Data>Data Analysis>Regression in Excel to verify your results. + 0.00841 + 5.2045 (x1, x2) = 0.00000+ 0.00000 * Number of Patients +0.00000 * Number of Doctors [04] b) See Ch06-Q2b Sheet for Excel verification. [04] C) Estimation of the requirements analysis duration for a project that has 3000 patients and 3 doctor(s): [3000, 3) = hours