1 / 19

Mastering Excel: Advanced Functions & What-If Analysis Techniques

In this class, we explored advanced Excel functions including nested, logical, and lookup/reference functions, alongside filtering techniques. Key concepts such as one-variable and two-variable What-If analysis were explained. We demonstrated how to use the Goal Seek feature to alter variables for desired outcomes and introduced Scenario Manager for analyzing multiple scenarios. Additionally, we covered the Solver add-in for optimizing decisions with constraints and various solving methods. Resources for templates and styles were also discussed in preparation for future topics.

ishi
Télécharger la présentation

Mastering Excel: Advanced Functions & What-If Analysis Techniques

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related