Using Spreadsheets in Management Science Models Spreadsheets have

Using Spreadsheets in Management Science Models • Spreadsheets have become a powerful tool in management science modeling. • Several reasons for the popularity of spreadsheets: – Data are submitted to the modeler in spreadsheets – Data can be analyzed easily using statistical and mathematical tools readily available in the spreadsheet. – Data and information can easily be displayed using graphical tools. A. Ruiz 2 -656 -97 Méthodes d ’aide à la décision en RO

Basic Excel functions and operators • Arithmetic Operations – Addition of cells A 1 and B 1: – Subtracting cell B 1 from A 1: – Multiplication of cell A 1 by B 1: – Division of cell A 1 by B 1: – Cell A 1 raised to the power in cell B 1: A. Ruiz 2 -656 -97 Méthodes d ’aide à la décision en RO = A 1 + B 1 = A 1 - B 1 = A 1 * B 1 = A 1 / B 1 = A 1^ B 1

Basic Excel functions and operators • Relative and absolute addresses – All row and column references are considered relative unless preceded by a “$” sign – When copied, ‘relative addresses’ change relative to the original cell position. Example: Cell E 5=A 1+B$3+$C 4+$D$6 Cell G 9 = C 5+D$3+$C 8+$D$6 A. Ruiz 2 -656 -97 Méthodes d ’aide à la décision en RO

Basic Excel functions and operators • The F 4 key – Pressing F 4 will automatically put a $ sign in highlighted portions of formulas. • Press the F 4 key once: The sign “$” appears in front of all rows and columns of the highlighted area of the formula. • Press the F 4 key twice: The “$” sign appears in front of only the row references of the highlighted area of the formula. • Press the F 4 key third time: The “$” sign appears in front of only the column references of the highlighted area of the formula. • Press the F 4 key forth time: All the “$” signs are eliminated. A. Ruiz 2 -656 -97 Méthodes d ’aide à la décision en RO

Basic Excel functions and operators • Arithmetic functions – Sum =SUM(A 1: A 3) • Returns the sum A 1+A 2+A 3 – Average =Average(A 1: A 3) • Returns the arithmetic average of cells A 1, A 2, A 3 – SUMPRODUCT =SUMPRODUCT(A 1: A 3, B 1: B 3) • Returns the sum of products A 1·B 1+A 2·B 2+A 3·B 3 – ABS =ABS(A 3) • Returns the absolute value of the entry in cell A 3. A. Ruiz 2 -656 -97 Méthodes d ’aide à la décision en RO

Basic Excel functions and operators • Arithmetic functions – continued – SQRT =SQRT(A 3) • Returns ÖA 3 – MAX =MAX(A 1: A 9) • Returns the Maximum of the entries in cells A 1 through A 9. – MIN =MIN(A 1: A 9) • Returns the Minimum of the entries in cells A 1 through A 9. A. Ruiz 2 -656 -97 Méthodes d ’aide à la décision en RO

Basic Excel functions and operators • Statistical functions – RAND() =RAND() • Generate a random number between 0 and 1 from a uniform distribution. – Probabilities and variable values under the normal distribution • NORMDIST =NORMDIST(25, 20, 3, TRUE) Returns P(X<25) when m = 20 and s = 3 • NORMSDIST =NORMSDIST(1. 78) Returns P(Z<1. 78) A. Ruiz NORMINV =NORMINV(. 55, 20, 3) Returns x 0, , such that P(X<x 0)=. 55 when m = 20 and s = 3 NORMSMINV =NORMSINV(. 55) Returns z 0, such that P(Z<z 0)=. 55 2 -656 -97 Méthodes d ’aide à la décision en RO

Basic Excel functions and operators • Statistical functions – Probabilities and variable values under the t- distribution • TDIST =TDIST(1. 5, 12, 1) Returns P(t>1. 5) when n=12 TINV =TINV(. 05, 15) Returns t 0, , such that P(t<-t 0)=. 025 and P(t>t 0)=. 025 when n=15. Note: =TDIST(1. 5, 12, 2) returns P(t<-1. 5) + P(t>1. 5) when n=12. A. Ruiz 2 -656 -97 Méthodes d ’aide à la décision en RO

Basic Excel functions and operators • Statistical functions – Other probability distributions – Poisson =POISSON(7, 5, TRUE) • Returns P(X<7) for Poisson with l = 5. Note: false returns the probability density P(X = 7) – EXPONDIST =EXPONDIST(40, 1/20, TRUE) • Returns P(X<40) for the exponential distribution with 1/m=20 Note: false returns the probability density f(40)=20 exp(-20(40)) A. Ruiz 2 -656 -97 Méthodes d ’aide à la décision en RO

Basic Excel functions and operators • Conditional functions: – IF =IF(A 4>4, B 1+B 2, B 1 – B 2) • Returns B 1+B 2 if A 4>4, and B 1 – B 2 if A 4£ 4. – SUMIF =SUMIF(F 1: F 12, “>60”, G 1: G 12) • Returns G 1+G 2+…+G 12 only if F 1+F 2+…+F 12>60 A. Ruiz 2 -656 -97 Méthodes d ’aide à la décision en RO

Basic Excel functions and operators – VLOOKUP =VLOOKUP(6. 6, A 1: E 6, 4) • If the values in column A of a given table [A 1: E 6] are sorted (in an ascending order), VLOOKUP finds the largest value in column A that is less than or equal to 6. 6, identifies the row it belongs to, and returns the value in the fourth column that correspond to this row. Note: If the values in column A are not sorted, =VLOOKUP(6. 6, A 1: E 6, 4, FALSE) finds the value 6. 6 in column A, identifies the row it belongs to, and returns the value in the fourth column that corresponds to this row. A. Ruiz 2 -656 -97 Méthodes d ’aide à la décision en RO

Basic Excel functions and operators • Statistical/Optimization – Data Analysis [Selected from the Tools menu]. Useful entries: • • A. Ruiz Descriptive Statistics Regression Exponential Smoothing Anova 2 -656 -97 Méthodes d ’aide à la décision en RO