Highline Class, BI 348
Basic Business Analytics usingExcel,Chapter 07What-If Spreadsheet Models
Spreadsheet ModelsGood Spreadsheet Model BuildingData Tables & GoalSeekVLOOKUP functionLOOKUP FunctionSUMPRODUCT functionIFand COUNTIFS functionsMATCHfunctionINDEX functionFormulaAuditing
Mathematical and logic-based models
Builtwith formula inputs,formulas, functionsand other ExcelfeaturesReferred to as what-ifmodelsThe beauty of such models as that they instantaneous recalculate when formula inputs changeFeatures such asData Tables, Goal Seek and Solver can be used to find solutions
Data Models (chapter 2 and later)
Source Raw Data comes from:An Excel sheetorIs imported into Excel from an external sourceData is cleaned andstored as a proper data set using the Excel Table feature or the Data ModelPower Query is an efficient feature to cleandataExcel Table feature is used so that source data is dynamicPivotTables or formulas are used to create a report
Models can be a combination of both
Good Spreadsheet ModelBuilding
General PrinciplesExcel’sGolden Rule: If formula input (parameter, assumption, variable) can change put it in a celland referto it in the formula with a cell reference.2) Label all formula inputs.3) Separate the formula input area from the model area.4) Model is where you create your formulas and where you can put your decision variable.5) Label all elements in the model area.6) Use appropriate Number & Stylistic Formatting to make the spreadsheet easy to understand. Remember: Number Formatting is a Facade.7) Keep default alignments to visually portray the data type (Numbers toright, Text toleft)8) Name all sheets and files smartly.9) Sometimes it is helpful to create math formulas or a influence diagram to describe your model.
Two schools of thought about stylistic:MinimalDefault Greylines.Boldfor Field Names and importantitems.More than minimalFormat field names and importanttext.Formatcells with formulas with lightgreen.RawData has no fillcolor.Someborders and other formattingokay.Chose one and be consistent.
Good Spreadsheet Model Design
Influence diagram: Visual representation that shows which entities influence others in a model.Parts of the model are represented by circular or oval symbols callednodes, and arrows connecting the nodes show influence.Building a Mathematical ModelDefine notation for every node in the influence diagram.Define formulas.Variables managers have control over = Decision VariablesVariables that can change, butmanagersDON’T havecontrol over =ParametersDon’t confuse with “parameter” term we used inBusn210 Statistics Class, which meant a calculation for a population.
Data Table: Excel tool which quantifies the impact of changing the value of a specificFormula Inputon anFormula.One-way datatableTwo-waydatatable
1 Variable DataTableStepsfrom sheet“1 V DataTable”:
1) You must have a formula that uses formula inputs. We have that in cell B30. It is the Total Profit = TP(q) =TR(q) - TC(q) formula.2) The B30 formula is pointing to two previous formulas (cells B28 and B29) that use the formula input from cell B25 (our decision variable).3) In cell C34, create a formula that points to the Total Profit = TP(q) =TR(q) - TC(q) formula.4) Because we want to see how the "Total Profit" formula will change when we change the Decision Variable, "Quantity", we create a column of the new quantity inputs for quantity in the range B35:B47.5) We highlight the range B34:C47, with the range C35:C47 containing empty cells.6) On the Data Ribbon, we go to the Data Tools group, then click on the What-If Analysis dropdown arrow, then click Data Table.7) Keyboard for Data Table: Alt, A, W, T or Alt, D, T8) Because out New Inputs for "Quantity" are in a column, we click in the "Column input cell" textbox and then click on the cell that contains the Decision Variable, "Quantity", cell B25.9) When you click OK, the Array Function TABLE is automatically entered as an Array Formula (notice the curly brackets in the formula bar)10) This TABLE function automatically takes the formula in cell B30 (which is using the two previous formulas in cells B28 and B29) and substitutes the New Inputs for Quantities and displays the results in the range C35:C47.**Note: the use of "Column input cell" may seem backwards if you are used to using PivotTables where we have "Row area" Criteria and "Column area" criteria. This is an inconsistency on Microsoft's part.
2Variable Data Table Steps from sheet“2V Data Table”:
1) You must have a formula that uses formula inputs. We have that in cell B30. It is the Total Profit = TP(q) =TR(q) - TC(q) formula.2) The B30 formula is pointing to two previous formulas (cells B28 and B29) that use the formula inputs from cell B25 (our decision variable) and from cell B9 (Defect Rate).3) In cell C33, create a formula that points to the "Total Revenue" formula. This must be in the upper left corner!!!!4) Because we want to see how the "Total Revenue" formula will change when we change the Decision Variable, "Quantity" and "Defect Rate", we create a column of the new quantity inputs for quantity in the range C34:C46 and a row of new costs in the range D33:H33.5) We highlight the range C33:H46, with the range D34:H46 containing empty cells.6) On the Data Ribbon, we go to the Data Tools group, then click on the What-If Analysis dropdown arrow, then click Data Table.7) Keyboard for Data Table: Alt, A, W, T or Alt, D, T8) Because our New Inputs for "Quantity" are in a column, we click in the "Column input cell" textbox and then click on the cell that contains the Decision Variable, "Quantity", cell B25.9) Because our New Inputs for "Defect Rate" are in a row, we click in the "Row input cell" textbox and then click on the cell that contains the Variable, "Defect Rate", cell B9.10) When you click OK, the Array Function TABLE is automatically entered as an Array Formula (notice the curly brackets in the formula bar)11) This TABLE function automatically takes the formula in cell B30 (which is using the two previous formulas in cells B25 and B9) and substitutes the New Inputs for Quantities and Defect Rate into the TABLE function formula and displays the results in the range D34:H46.**Note: the use of "Column input cell" and "Row input cell" may seem backwards if you are used to using PivotTables where we have "Row area" Criteria and "Column area" criteria. This is an inconsistency on Microsoft's part.
Whatdoes Goal Seekdo:Getsformula to evaluate to desired result, by automatically changing one formula input.It is the reverse of what we normally do when we change a formula input. We are saying: "Hey formula, I want you to b "x", so can you please tell me what the formula input should be?"2) Goal Seek Keyboard: Alt T, G or Alt A, W, G3) Set Cell textbox: Contains a formula with a formula input that you want to change4) To value textbox: Result you want the formula to be. Must be typed into text box.5) By changing cell textbox: Must be cell reference for formula input
VLOOKUP Function:VLOOKUP Delivers a value to a cellTell VLOOKUP what value it should look up = 1st argumentTell VLOOKUP where the table is = 2nd argumentTell VLOOKUP what column holds the value you want to return to the cell = 3rd argumentTell VLOOKUP whether you are doing exact ( 0 ) or approximate (leave argument blank) match = 4thargument
VLOOKUP function Exact Match:
Exact Match: means VLOOKUP starts at first item in first column and looks through column at each item until it finds an exact match.If there are duplicates, it only finds the first one.If it can't find a match, it returns an #N?A error.1st column is where VLOOKUP "looks" to figure out what row in the table has the value it wants
VLOOKUP function Approximate Match:
leave 4th argument blank (default behavior)First column must be sorted ascending (biggest to smallest).Metaphor for understanding how it works:It starts at the first item in first column, and looks at each one and when it bumps into first bigger value, it jumps back one row.Binary search is how it really works.Binary Search and is faster than Linear Search (Exact Match)Binary Search reduces search time because it repeatedly divides the table in half and checks the one in the middle to help reducecalctime.It doesn't have to check each one.If lookup_value is less than 1st value in table LOOKUP returns #N/A
Type of lookup:ApproximateMatch onlyIf you sort your column you can trick it into doing Exact MatchIf you use “lookup_value” and “array” arguments:Does Vertical or Horizontal lookup:Tabletaller or equal to width, does vertical lookupTable wider than tall, does horizontal lookupExactly same rows and columns,does vertical lookupAlwaystakes last value from lastcolumnIf you use “lookup_value” and“lookup_vector” and “result_vector arguments:LOOKUP will find the position of the “lookup_value” in the “lookup_vector” to find the relative position, and that retrieve an item from the “result_vector” in that relative position.LOOKUPcan handlearray calculationsand will not require Ctrl + Shift + Enter
Multiplies arrays (ranges or arrays of values) of the same dimension, and then adds.The array argument in the SUMPRODUCT can handle array operations without an special keystroke.When you want to add the result of an array operation (operation that results in many answers), you can use SUMPRODUCT rather then the SUM function.
IF and COUNTIFS functions
IF Functions:IF function puts one of two things into a cell. IF functions can also be "nested" to deliver more than 2 things.The "things" can be numbers, text, or formulas, functions.Give it a logical test that evaluates to TRUE or FALSE, and then tell it what to put in the cell ififit evaluates to TRUE and what to put in the cell if it evaluates to FALSE.COUNTIFS Function:Counts with one or more conditions or criteria
MATCH function is a lookup function that returns the relative position of an item in a listlookup_value is the value you tell the match function to lookuplookup_arrayis the list that you look an item up in[match_type] tells the MATCH what sort of lookup to do:1 or empty =approximatematch; table sorted ascending; first bigger value bumped into then jump back one position, if value is smaller than first item returns #N/A, if bigger than last it returns last value2 =extractmatch, if duplicates, it finds first one only, can't find ititshows #N/A-1 =approximatematch; table sorted descending; first smaller value bumped into then jump back one position, if value is bigger than first item returns #N/A, if smaller than last it returns last value
INDEX is a lookup function that can do a two-waylookup or one-way lookup.array argumentcan be:Atwodimensionaltable (both a row and acolumn).orAone dimensionaltable(row or column).row_numargument tells indexfrom whichrow toretrieve the item.column_numargument tells index from whichcolumn toretrieve theitem.The intersection of the row and column is the value that is returned to the cell orformula.
Formula Ribbon Tab, Formula Auditing Group:Trace PrecedentsTrace DependentsRemove ArrowsShow Formulas: Ctrl + ~Error Check: Not reliableEvaluate Formula: watch how Excel calculates a formula step by step . Keyboard: Alt, M, VWatch Window: Shows Formula in Window that allows you so see how a particular formula changes when you are anywhere in the workbook.