100%(2)2 out of 2 people found this document helpful
This preview shows page 15 - 18 out of 69 pages.
COMPUTING RETIREMENT CONTRIBUTION WITH VLOOKUP FUNCTIONVLOOKUP(F5,$E$25:$F$27,2,TRUE)*F5COMPUTING FEDERAL TAX WITH IF FUNCTIONIF(H5*52>$C$25,H5*$C$26,H5*$C$27)
10/3/201716Spreadsheets can be used as Decision Support System (DSS)On thing DSSdoes is support the ability to perform Sensitivity AnalysisSensitivity Analysis is seeing how changes in one or more input variables impact the value of one or more outcome variablesExcel performs Sensitivity Analysis in these ways,1.What-If analysisa)Scenario Managerb)Data Table2.Goal-Seek analysis3.SolverDECISION SUPPORT IN SPREADSHEETSTwo Types of What-If Analysisare:1.Scenario Manager takes different input values as a group, set by one who has experience to make estimates, often the groups are best, typical and worse, and Scenario Manager then finds the output value from the spreadsheet model for each input value group2.Data Table takes a range of inputs of different values, where the values change in steady increments, and finds the resulting output values for each input. (We will come back to this in a later lecture.)Note, that only one input value can vary with Data Table, while a group of input values can vary with Scenario Manager.The point of the What-If Analysis is to see the impact input changes have on outcomesDECISION SUPPORT IN SPREADSHEETSSCENARIO MANAGERScenario Manager in Excel allows you to create predefined scenarioswhere each scenario may differ in the values of some inputsYou can generate outputs corresponding to each predefined scenarioIn the example below we see three scenarios for a building expansion to a business, where the size and costs increase Goal Seek Goal Seek works the opposite direction of What-IfIt takes a target output value, and finds the needed single input valueto reach that target output valueSolverSolver also works the opposite direction of What-IfFor a target optimized output[i.e. min, max, value], it finds the set of input valuesthat also satisfy some constraints(We will come back to this in a later lecture.)DECISION SUPPORT IN SPREADSHEETS
10/3/201717Collyn Bear, one of your employees, asked the following question:“I would like to receive net pay of $500 per week. How many hours would I need to work to get that net pay?”Answering this question requires you to think backwards starting from a target/goal output value and then finding an input value that will lead to the target output valueYou could open the weekly payroll worksheet and try answering the question with trial and error, but this is inefficient GOAL SEEK EXAMPLEExcel provides a goal seek functionality where you can provide the desired output value for a celland also the input cell that may be varied to get the desired value. Excel then searches for an input value that will provide the desired output value.