Excel Review

Weekend Executive MBAApril 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 Working smarter: A demo of a number of Excel techniques

that will make you a more efficient user.

Part 2What Dr. Kornish expects you to know: Critical model-

building skills. Demonstrations and hands-on practice.

Part 3Advice from Fuqua’s Decision Science faculty:

Fundamentals of how to approach Excel model-building.

Part 4Modeling exercises: Skills application.

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

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.

Handouts & Materials

On Paper− WEMBA Excel Review− Supplementary Excel

materials

Diskette− Demo and practice files

WEMBA Excel Review Webpage− More files

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

Part 1: Working Smarter

Part 1: Working SmarterPart 2: What Dr. Kornish ExpectsPart 3: Model-Building Advice From the ExpertsPart 4: Modeling

Part 1: Working smarter

Topics from the handoutEfficiency Techniques in Excel

Selected features on− Workbook organization− Data entry & edit− Arranging & viewing the worksheet

Part 1: Working Smarter

Workbook Organization

Toolbars− Quick access, docking, customizing.

Worksheet tabs− Add, rename, delete, re-order, group.

Copying and/or moving worksheets− The best method.

Part 1: Working Smarter Data 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.

Part 1: Working Smarter Arranging & 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.

Part 2: What Dr. Kornish Expects You to Know

Part 1: Working SmarterPart 2: What Dr. Kornish Expects You to KnowPart 3: Model-Building Advice From the ExpertsPart 4: Modeling

Writing formulasFormula review

BadAndGoodFormulaTechnique.xls

FormulaReview.xls

Logic Practice

LogicPractice.xls

MoreLogicPractice.xls

Charting

Charting Basics.xls

Part 3: Model-Building Advice

Part 1: Working SmarterPart 2: What Dr. Kornish ExpectsPart 3: Model-Building Advice From the ExpertsPart 4: Modeling

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.

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.

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.

Part 4: Modeling

Part 1: Working SmarterPart 2: What Dr. Kornish ExpectsPart 3: Model-Building Advice From the ExpertsPart 4: Modeling

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.

Question

Might a different product mix be more profitable?

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.

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

Build the modelSee 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.

Using the model to maximize total profit

40 53 40 4075 5 28 37

…with guesswork …with Solver

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

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

End