Day 11: Excel Chapter 8-10

1. Day 11:Excel Chapter 8-10 Rahul KaviRahul.Kavi@mail.wvu.edu September 24, 2013

2. Last class • Nested Functions • Logical Functions • Lookup/Reference Functions • Filtering Functions • One Variable What-If Analysis

3. Two variable What-IF • Similar to one variable • One variable goes in the first column, the other in the top row • Can only calculate results for one formula (In one variable analysis you can do any number of formulas. We did individual payments and cumulative payments.)

4. Two variable what-if example • Setup your variables in the first column and top row • Add a formula in the empty cell in the variable row and column • Use the What-If Data Table tool, specifying both the row and column input cells • Data->Data Tools->What-If Analysis->Data Table

5. Goal seek • Goal seek allows you to alter one variable to obtain the desired result of a single equation • Data->Data Tools->What-If Analysis->Goal Seek • Example: • You want to buy a car for $15,000. You are able to obtain a 5 year loan at 6% APR. You would like your monthly payment to be $200. How large of a down payment must you make?

6. Scenario Manager • Scenario Manager is a what-if analysis tool that allows you to define up to 32 scenarios to compare their effects on calculated results • Data->Data Tools->What-If Analysis->Scenario Manager • Example: Profit calculations for a business based on units sold, production cost per unit, shipping cost per unit

7. Adding scenarios • Data->Data Tools->What-If Analysis->Scenario Manager • Add… • Scenario name • Changing cells • Set values for each cell • Example: • Best Case: 75,000 units, $38/unit production cost, $0.30/unit shipping cost • Likely Case: 50,000, $42.95, $0.5 • Worst Case: 25,000, $48, $0.75

8. Working with scenarios • Delete • Edit • Show • Summary • Regular summary • PivotTable summary

9. Solver • Solver is similar to goal seek, but it allows multiple input values to change • Solver is an Add-In, so it must be loaded before you can use it • File->Options->Add-Ins->Manage: Excel Add-Ins-> Go… • Check “Solver Add-in”-> OK

10. Solver Example • Maximize profits for cabinet door manufacture by selecting how many of each door type to produce • Changing Variables (green) • Objective (blue) • Constraints (red)

11. Example continued • Data->Analysis->Solver • Specify Objective • Max, min, or specific value • Specify cells to vary • Specify constraints • Single cells or ranges • Include non-negativity constraints

12. Solving • Chose a solving method: • GRG Nonlinear: guaranteed local optimal • Simplex LP: guaranteed global optimal, must be linear • Evolutionary: good solutions for non smooth functions • Solve

13. hyperlinks • Insert->Links->Hyperlink • Inserts a link to a: • File/Document • Web site • Email address

14. 3D formulas • 3D formulas are formulas that use references from other sheets • ‘Worksheet name’!Range

15. Templates • File->New-> • Recent Templates • Sample Templates • My Templates • Office.com Templates

16. Cell Styles • Home->Styles->Cell Styles • Existing Styles • New Styles

17. Questions • This is the last lecture on Excel. If you have any questions you want answered before the exam, ask now.

18. Next Class • Introductory Database concepts