Chapter 2 Introduction to Spreadsheet Modeling Introduction Introduction

Chapter 2 Introduction to Spreadsheet Modeling

Introduction • Introduction to Excel modeling – illustrates some interesting and relatively simple models. • Modeling process – less well known, but particularly helpful, Excel tools that are available

Spreadsheet Modeling • Elements of a spreadsheet/mathematical models – Inputs • Known, estimated, uncertain – Decision variables • Values must be determined using the model – Outputs • Values of interest • Computed from the inputs and the decision variables.

Spreadsheet Modeling • Using the model – Optimization – Sensitivity analysis – Prepare reports

Best practices • Readability of the model – Clear and logical layout – Separation of different parts of a model – Clear headings for all inputs, decision variables, and outputs – Range names – Formatting features – bold, italics, coloring, etc. – Cell comments – Text boxes for assumptions and explanations

Best practices • Modelling • • The formulas and logic must be correct Breakdown computations to small chunks and build Model must be flexible for modification Plan ahead, revise your plan as needed

Six models Example NCAA T-Shirts Bookshelves Break even analysis Excel file Concepts 01 -TShirt Sales-Shell. xlsx Basic modeling concepts Creating names for cells Excel IF function 02 -Bookshelf Costs Absolute cell references Shell. xlsx Creating basic charts 03 -Breakeven Analysis- Cell comments Shell. xlsx One-way Data Table Goal seek Formula Auditing

Six models Example Ordering with quantity discounts Golf club demand NPV at Acron Excel file Concepts 04 -Quantity Discounts IF, MAX, MIN functions -Shell. xlsx VLOOKUP function Two-way Data Table SUMPRODUCT function Adding cell comments and text boxes 05 -Golf Club Demand- Scatter plot trend lines Shell. xlsx INDEX function MATCH function Absolute reference in conditional formatting 06 -Calculating NPV function Shell. xlsx Data Table Conditional formatting

Summary of Management Science terms Term Explanation Page Model inputs The numeric values that are given in any problem statement 22 Decision variables The variables a decision maker has control over to obtain better solutions 22 Model outputs The numeric values that result from combinations of inputs and decision variables through the use of logical formulas 22 Net present value (NPV) The current worth of a stream of cash flows that occur in the future 55 Discount rate Interest rate used for discounting future cash flows to get the net present value 55

Summary of Key Excel Terms Term IF function Explanation Excel Useful for =IF(condition, result. If. True, implementing logic result. If. False) Relative, Useful for copying A 1 (relative), $A 1 or A$1 (mixed), absolute cell formulas; absolute row $A$1 (absolute); press F 4 to cycle addresses or column stays fixed, through possibilities relative row or column "moves" Range names Useful for making Type name in Name box, or use formulas more Create from Selection shortcut meaningful (Ctrl+Shift+F 3) Pasting range Provides a list of all Use Paste List from Use in Formula names range names in the dropdown list (F 3) current workbook Cell comments Useful for documenting Right-click on cell, select Insert contents of the cell Comment menu item Page 24 29 33 33 34

Summary of Key Excel Terms Term Explanation One-way data Shows how one or table more outputs vary as a single input varies Goal Seek Solves one equation in one unknown Formula Useful for checking Auditing which cells are related toolbar to other cells through formulas fx button Useful for getting help on Excel functions VLOOKUP Useful for finding a function particular value based on a comparison Two-way data Shows how a single table output varies as two inputs vary Excel Use Data Table from What-If Analysis dropdown list Page 36 Use Goal Seek from What-If Analysis dropdown list Use Formula Auditing buttons on Formulas ribbon 37 On Formula Bar 42 =VLOOKUP(value. To. Compare, lookup. Table, column. To. Return) 42 Use Data Table from What-If Analysis dropdown list 43 38

Summary of Key Excel Terms Term SUMPRODUCT function Trendline tool Conditional formatting Splitting screen Explanation Excel Calculates the sum of =SUMPRODUCT(rang products of values in e 1, range 2) two (or more) similarsized ranges Superimposes the best- With chart selected, fitting line or curve of a right-click on any particular type on a point and select Add scatter chart or time Trendline series graph Formats cells depending Use Conditional on whether specified Formatting on Home conditions hold ribbon Useful for separating Use screen splitters the screen horizontally at top and right of and/or vertically scrollbars Page 44 47 51 57

Summary of Key Excel Terms Term Explanation Excel Efficient selection Useful for selecting a While pressing the large rectangular range Shift key, click on upper-left and bottom-right cells of range Efficient copying Shortcut for copying a Select the range, formula to a range enter the formula, and press Ctrl+Enter NPV function Calculates NPV of a =NPV(discount. Rate, c stream of cash flows at ash. Flows) the ends of consecutive years, starting in year 1 Page 58 58 58