Chapter 15: "What if" Thinking Helps: Ad d S d h t f Pl iAdvancedSpreadsheetsfor PlanningFluency with Information TechnologyThird EditionbyLawrence SnyderLawrence SnyderCopyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-WesleyDesigning a Spreadsheet•When a spreadsheet is used repeatedly itWhen a spreadsheet is used repeatedly,itbecomes a tool of planning, analysis, and decision-makingdecisionmaking• Must be well designed and flexible to be ff tieffective1-2Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-2The Trip•Driving trip to the Arctic Circle fromDriving trip to the Arctic Circle fromChicago•Make a spreadsheet to figure out how long•Make a spreadsheet to figure out how longit will take and how much it will cost1-3Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-31-4Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-4Design Guidelines•Focus on FormFocus on Form– Make the form logical, the layout clean, and the entries clear and understandable• Descriptive info should be on the top and left• Summary info should be on the bottom and rightUse different fonts colors separate sheet for each table–Use different fonts,colors,separate sheet for each table• Explain EverythingIt should be possible to know immediately–It should be possible to know immediatelywhat every cell means1-5Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-5Initial Spreadsheet: Applying the Rules• Focus on Form:–title, authors, completion date, column headings, color, clean font, data aligned• Explain Everything:– comments added to cells (Insert > Comment)1-6Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-6Conditional Formatting•Cell Value IsSpecificationsCell Value IsSpecifications– Format > Conditional Formatting…•Change formatting in response to certain conditions•Change formatting in response to certain conditions– Can specify one or more conditionsCan use formulas in the comparison–Can use formulas in the comparison• Example: bold data greater than average of range1-7Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-71-8Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-8Conditional Formatting (cont)•Formula IsSpecificationsFormula IsSpecifications– Allows comparison with cells other than the one being formattedone being formattedA2 cell: =IF(B2>AVERAGE(B$2:B$7), TRUE, FALSE)1-9Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-9Conditional Formatting (cont)•Distinguish Between the U S and CanadaDistinguish Between the U.S.and Canada– Italicize fuel cost if fuel price estimate is in Canadian dollarsCanadian dollarsE2 cell: =IF(Left(D2,2)="CA", TRUE, FALSE)1-10Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-10Conditional Formulas•Computing with conditional factorsComputing with conditional factors• Figuring the Amount PaidMil ll (22) il lit (5 8)–Miles per gallon(22) vs. miles per liter(5.8)=IF(LEFT(D2,2)="US", E2*C2/22, E2*C2/5.8)1-11Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-11Conditional Formulas (cont)•Cost in One CurrencyCost in One Currency– Canadian to US currency exchange rate (0.93)=IF(Left(D2 2)="CA"F2*0 93 F2)=IF(Left(D2,2)= CA,F2*0.93,F2)1-12Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-12Naming: Symbolic Reference• Defining NamesRf t llbli llbth–Referto cells symbolicallyby name ratherthan literally by explicit column/row position–Anameis a word or phrase assigned to a cell–Anameis a word or phrase assigned to a cellor range of cells (no spaces)• Name used wherever cell references are used• Minimizes errors when columns/rows added later• In Excel 2003Insert > Name > Define–Insert > Name > Define– Insert > Name > Apply• In Excel 2007FltbDfiN1-13Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-13–Formulastab > DefineName1-14Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-14Naming: Symbolic Reference (cont)•Applying NamesApplying Names=IF(LEFT(D2,2)="US", fuelPrice*distance/22, fuelPrice*distance/5.8)– Safer, easier to read and understand• Make Assumptions Explicit– Assign instance-specific quantities that may change to cells, give them names, and use these names in the formulas•mpg mpl exchange rate # of travelers•mpg,mpl,exchange rate,# of travelers=IF(LEFT(priceSrc,2)="US", fuelPrice*distance/mpg,fuelPrice*distance/mpl)1-15Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-15"What If" Analysis•Spreadsheets recalculate everythingSpreadsheets recalculate everythingwhenever a number is changed•Ideal for speculating on the consequences•Ideal for speculating on the consequencesof changeMk h d ti hth t–Make a change and notice whathappenstothe "bottom line"1-16Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-16"What If" Analysis (cont)•Direct ExperimentationDirect Experimentation– Potential risk of making permanent changes to data and formulasdata and formulas• ScenariosA named alternative to a spreadsheet based–A named alternative to a spreadsheet basedon different inputsAid to understanding changes in plans like–Aid to understanding changes in plans,likechanges in gas mileage1-17Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-17Scenarios• Tune-up Scenario–Excel 2003: Select mpg cellTools > Scenarios–Excel 2003: Select mpg cell,Tools > Scenarios…– Excel 2007: Data tab > Data Tools > What-If Analysis1-18Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-181-19Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-191-20Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-20Scenarios (cont)•Traveling Companions ScenarioTraveling Companions Scenario– Adding buddies in the car will drop the mpg but also the cost per personbut also the cost per person1-21Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-211-22Copyright © 2008 Pearson Education, Inc. Publishing as Pearson Addison-Wesley15-22Analyzing a Model•Formulating a