1 / 38

Excel Review

Excel Review. Global Executive MBA April 2004. Session goals. Preparation for the coming term Review and practice essential Excel techniques. A model building approach Consider recommendations for approaching Excel modeling. Continuing

tibor
Download Presentation

Excel Review

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. Excel Review Global Executive MBA April 2004

  2. Session goals • Preparation for the coming term • Review and practice essential Excel techniques. • A model building approach • Consider recommendations for approaching Excel modeling. • Continuing Materials, resources, and suggestions for continued review and/or learning about Excel.

  3. Agenda • Part 1 Critical Excel elements: Follow along on your PC as we review a set of essential Excel techniques. • Part 2 Model-building advice from the experts: Fuqua’s Decision Science faculty on the fundamentals of Excel model-building. Apply the approach to model a problem (Oak Products) and use Solver. • Part 3 – On Your Own Practice exercises. Choose the materials that cover the areas in which you most need practice.

  4. Handouts & Materials • On the web • Practice files • Documentation • Links to other sources • On Paper • Slides from this presentation • Basics Review (a description of basic Excel techniques that accompanies the BasicsReview.xls file) • Logic Exercises (on paper)

  5. Part 1: Critical Excel elements • Part 1: Critical Excel elements • Part 2: Model building advice from the expertsPart 3: Exercises

  6. Critical Excel Elements • Excel Basics Topics • Organization • Formatting • Editing • Formulas & logical functions • Forecasting • The Data Table • Charting • Online Help File: BasicsPractice.xls

  7. Practice: Naming Ranges • Ways to name a range • Exercise 1: Insert, Name, Create • Exercise 2: Insert, Name, Define • Exercise 3: Use the “Name Box” on the formula bar • Exercise 4: Display named ranges • Exercise 5: Add a comment to a cell In the file BasicsPractice.xls see the worksheet named Naming Ranges.

  8. Practice: Formatting • Exercise 6: Merge and center • Exercise 7: Apply formatting • Exercise 8: Apply borders • Exercise 9: Create a text box • Exercise 10: Use the format painter In the file BasicsPractice.xls see the worksheet named Formatting.

  9. Practice: Basic Editing • Exercise 11: Edit Fill • Exercise 12: Excel Custom Lists • Exercise 13: Copy & Paste a Formula • Exercise 14: Convert Formulas to Values • Exercise 15: Transpose Data In the file BasicsPractice.xls see the worksheet named Basic Editing.

  10. Practice: Formulas • Exercise 16: Copy a Formula Using a Relative Reference • Exercise 17: Copy a Formula Using an Absolute Reference • Exercise 18: Use Built-in Functions • Exercise 19: Excel Logical Functions ...and practice on your own • Exercise 20: Formula Auditing Tools In the file BasicsPractice.xls see the worksheet named Formulas.

  11. First: The Excel IF Statement A key logical Excel function • The syntax =IF(Condition-to-Test, Value-If-True, Value-If-False) • The IF function has three parameters: • Condition-to-Test • Value-If-True • Value-If-False

  12. Additional logical functions that may be embedded as IF function parameters. The Excel IF Statement • =IF(Condition-to-Test, Value-If-True, Value-If-False) This is a logical statement of some kind that returns either TRUE or FALSE. Examples: A1>B5 (C22/C25)<=A1 AND(5<10, 10<20) OR(5<10, 10>20) MAX(C77:C80)>600

  13. The Excel IF Statement • =IF(Condition-to-Test, Value-If-True, Value-If-False) The value the cell holding the IF statement takes on if the Condition-to-Test evaluates to TRUE. Examples: 2500 “Bill over due” A15*B15

  14. The Excel IF Statement • =IF(Condition-to-Test, Value-If-True, Value-If-False) The value the cell holding the IF statement takes on if the Condition-to-Test evaluates to FALSE. Examples: 2500 “Bill over due” A15*B15

  15. Paper-based Excel logic exercisesto work on by yourself for practice Answers to all the exercises are at the end of the document.

  16. FALSE TRUE FALSE

  17. An IF statement can contain 7 levels of nesting.

  18. A nested IF statement

  19. See the LogicPractice.xls file. And – Also see the MoreLogicPractice.xls file.

  20. Practice: Data Tables • Exercise 21: The One-Input Data Table • Exercise 22: The Two-Input Data Table In the file BasicsPractice.xls see the worksheet named Data Tables.

  21. Practice: Charting • Exercise 23: Generate a Quick Chart • Exercise 24: The Chart Wizard • Exercise 25: A Scatter Plot (XY Chart) In the file BasicsPractice.xls see the worksheet named Charting.

  22. Part 2: Model building advice from the experts • Part 1: Critical Excel elements • Part 2: Model building advice from the expertsPart 3: Exercises

  23. On Model-Building: Expert advice • Handout • Excel Design & Audit Tips • Model components • Understand the components present in most spreadsheet models. • Checklist • Make those components part of a checklist. • Use the checklist items to: • Organize new models. • Improve existing models.

  24. Five item modeling checklist 1. Identify Known Values • The givens; can’t be modified. • (Do you need more information?) 2. Identify Decision Variables The quantities you control. You’ll manipulate these items to find an optimal model solution. 3. Determine the Outputs What you want to solve, show, find, maximize or minimize. 4. Be aware of any Constraints Limits to inputs or outputs. Tradeoffs. 5. Build Relationships into the Model • Relationships between known values and variables, expressed in formulas.

  25. A review of model building & Solver File: OakProductsStart.xls Solver

  26. Oak Products: Overview • Oak Products • A small company that manufactures chairs. • The company has six chair models. • Each chair model • Requires a particular mix of components. • August is a slow month • Because August is traditionally a vacation month, only the parts already on hand in the factory can be used for August production. • Oak Products makes 40 of each model chair in August.

  27. Question • Might a different product mix be more profitable?

  28. To find out... • Data we need • the components each model requires • how many of each component are on hand • how much profit each model generates • Then • Analyze the data to determine the most profitable product mix, accounting for constraints.

  29. Model Checklist • Objective • maximize August profit • Known Values • profit per chair, parts-on-hand, parts required • Constraints • limited parts on hand, parts requirements • Decision variables • how many of each model to make

  30. To build the model Three stages: Part 1: Initial arranging of the data. Add any known values. Part 2: Build formulas to express data relationships. Part 3: Use the model to find the best answer.

  31. 40 53 40 40 75 5 28 37 …with Solver …with guesswork Using the model to maximize total profit

  32. Identify for Solver • Target cell • Total Profit • Changing cells (or decision variables) • Quantity of each chair to produce • Constraints • No “negative production” • Use only inventory on hand

  33. Part 3: Exercises • Part 1: Critical Excel elements • Part 2: Model building advice from the expertsPart 3: Exercises

  34. Exercises on Your Own Use the materials on the GEMBA Excel Review site to practice Excel techniques with which you need practice. http://faculty.fuqua.duke.edu/~pecklund/GEMBA/index.htm

  35. End

More Related