1 / 31

Excel Review

Excel Review. Weekend Executive MBA April 2003. Agenda. Part 1 Working smarter Part 2 What Dr. Kornish expects Part 3 Model building advice from the experts Part 4 Modeling exercises. Agenda – more detail. Part 1

rogeliom
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 Weekend Executive MBA April 2003

  2. Agenda • Part 1 • Working smarter • Part 2 • What Dr. Kornish expects • Part 3 • Model building advice from the experts • Part 4 • Modeling exercises

  3. Agenda – more detail • Part 1 Working smarter: A demo of a number of Excel techniques that will make you a more efficient user. • Part 2 What Dr. Kornish expects you to know: Critical model-building skills. Demonstrations and hands-on practice. • Part 3 Advice from Fuqua’s Decision Science faculty: Fundamentals of how to approach Excel model-building. • Part 4 Modeling exercises: Skills application.

  4. Agenda – more on Part 2: What Dr. Kornish Expects You to Know • Critical “going in” knowledge • Bad & good formula-writing technique • About formulas • Key logical functions (IF, AND, OR, NOT, MIN, MAX) • Charting Basics • Printing • Online Help • Excel tools she’ll introduce in class • In the context of your work • Data Tables • Solver • Etc. Pencil & paper (or PC) exercises Solver

  5. My goals for this session • Decision Models Course Preparation • View selected techniques to make your work with Excel easier and more efficient. • Review and practice formula-writing basics and logical functions. • A Modeling Approach • Consider a straightforward framework for approaching Excel modeling. • Continuing • Suggest materials, resources, and suggestions for continued learning about Excel.

  6. Handouts & Materials • On Paper • WEMBA Excel Review • Supplementary Excel materials • Diskette • Demo and practice files • WEMBA Excel Review Webpage • More files

  7. Before getting started • 1. Check a box • Complete the Excel self-assessment sheet • 2. Resources for Learning More about Excel • See “Suggested Excel Resources” in the blue handout, page 1. • Text Recommendations • For tutorial work • For quick reference • For comprehensive reference • Video/CD-ROM/Web Recommendations

  8. Part 1: Working Smarter Part 1: Working Smarter Part 2: What Dr. Kornish Expects Part 3: Model-Building Advice From the Experts Part 4: Modeling

  9. Part 1: Working smarter • Topics from the handout Efficiency Techniques in Excel • Selected features on • Workbook organization • Data entry & edit • Arranging & viewing the worksheet

  10. Part 1: Working SmarterWorkbook Organization • Toolbars • Quick access, docking, customizing. • Worksheet tabs • Add, rename, delete, re-order, group. • Copying and/or moving worksheets • The best method.

  11. Part 1: Working SmarterData Entry & Edit • The formula bar • More than just formula display. • Formats • Number, alignment, fonts, duplication, etc. • Fill & extend • Fast repetition or extension. • Naming cells & ranges • How & why.

  12. Part 1: Working SmarterArranging & Viewing the Worksheet • One- and two-way splits • Never again lose sight of key headers. • Viewing multiple windows • In the same workbook or across workbooks. • Moving & selecting • Some useful power techniques.

  13. Part 2: What Dr. Kornish Expects You to Know Part 1: Working Smarter Part 2: What Dr. Kornish Expects You to Know Part 3: Model-Building Advice From the Experts Part 4: Modeling

  14. Writing formulasFormula review FormulaReview.xls BadAndGoodFormulaTechnique.xls

  15. Logic Practice MoreLogicPractice.xls LogicPractice.xls

  16. Charting Charting Basics.xls

  17. Part 3: Model-Building Advice Part 1: Working Smarter Part 2: What Dr. Kornish Expects Part 3: Model-Building Advice From the Experts Part 4: Modeling

  18. On Model-Building: Expert advice • Handout • 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.

  19. 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.

  20. Checklist, continued • 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.

  21. Part 4: Modeling Part 1: Working Smarter Part 2: What Dr. Kornish Expects Part 3: Model-Building Advice From the Experts Part 4: Modeling

  22. 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. • Traditionally, Oak Products has made 40 of each model chair in August.

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

  24. 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.

  25. 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

  26. Build the model See the Oak Products handout. 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.

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

  28. 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

  29. Oak Products model summary • As you work • use the 5-item model-building checklist • Put down what you know, then • get more data, if needed • rearrange the layout, if needed • express data relationships using formulas • Then use the model • change the decision variable values • perhaps use Solver to find the best solution

  30. End

More Related