COMPUTING RETIREMENT CONTRIBUTION WITH VLOOKUP FUNCTION

Computing retirement contribution with vlookup

This preview shows page 15 - 18 out of 69 pages.

COMPUTING RETIREMENT CONTRIBUTION WITH VLOOKUP FUNCTION VLOOKUP(F5,$E$25:$F$27,2,TRUE)*F5 COMPUTING FEDERAL TAX WITH IF FUNCTION IF(H5*52>$C$25,H5*$C$26,H5*$C$27)
Image of page 15
10/3/2017 16 Spreadsheets can be used as Decision Support System (DSS) On thing DSS does is support the ability to perform Sensitivity Analysis Sensitivity Analysis is seeing how changes in one or more input variables impact the value of one or more outcome variables Excel performs Sensitivity Analysis in these ways, 1. What-If analysis a) Scenario Manager b) Data Table 2. Goal-Seek analysis 3. Solver DECISION SUPPORT IN SPREADSHEETS Two Types of What-If Analysis are: 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 group 2. 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 outcomes DECISION SUPPORT IN SPREADSHEETS SCENARIO MANAGER Scenario Manager in Excel allows you to create predefined scenarios where each scenario may differ in the values of some inputs You can generate outputs corresponding to each predefined scenario In 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-If It takes a target output value , and finds the needed single input value to reach that target output value Solver Solver also works the opposite direction of What-If For a target optimized output [i.e. min, max, value], it finds the set of input values that also satisfy some constraints (We will come back to this in a later lecture.) DECISION SUPPORT IN SPREADSHEETS
Image of page 16
10/3/2017 17 Collyn 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 value You could open the weekly payroll worksheet and try answering the question with trial and error, but this is inefficient GOAL SEEK EXAMPLE Excel provides a goal seek functionality where you can provide the desired output value for a cell and 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.
Image of page 17
Image of page 18

You've reached the end of your free preview.

Want to read all 69 pages?

  • Spring '13
  • Unknown

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture