excel review global executive mba april 2004. session goals preparation for the coming term...
Post on 22-Dec-2015
219 Views
Preview:
TRANSCRIPT
Session goals
Preparation for the coming term− Review and practice essential Excel techniques.
A model building approach− Consider recommendations for approaching Excel
modeling.
ContinuingMaterials, resources, and suggestions for continued review
and/or learning about Excel.
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 OwnPractice exercises. Choose the materials that cover the areas
in which you most need practice.
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)
Part 1: Critical Excel elements
Part 1: Critical Excel elementsPart 2: Model building advice from the expertsPart 3: Exercises
Critical Excel Elements
Excel Basics Topics− Organization− Formatting− Editing− Formulas & logical functions− Forecasting− The Data Table− Charting− Online Help
File: BasicsPractice.xls
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.xlssee the worksheet named
Naming Ranges.
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.xlssee the worksheet named
Formatting.
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.xlssee the worksheet named
Basic Editing.
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.xlssee the worksheet named
Formulas.
First: The Excel IF Statement
The syntax
=IF(Condition-to-Test, Value-If-True, Value-If-False)
The IF function has three parameters:1. Condition-to-Test2. Value-If-True3. Value-If-False
A key logical Excel function
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
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)
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
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
Paper-based Excel logic exercisesto work on by yourself for practice
Answers to all the exercises are at the
end of the document.
Practice: Data Tables
Exercise 21: The One-Input Data Table Exercise 22: The Two-Input Data Table
In the file BasicsPractice.xlssee the worksheet named
Data Tables.
Practice: Charting
Exercise 23: Generate a Quick Chart Exercise 24: The Chart Wizard Exercise 25: A Scatter Plot (XY Chart)
In the file BasicsPractice.xlssee the worksheet named
Charting.
Part 2: Model building advice from the experts
Part 1: Critical Excel elementsPart 2: Model building advice from the expertsPart 3: Exercises
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.
Five item modeling checklist
1. Identify Known Values− The givens; can’t be modified.− (Do you need more information?)
2. Identify Decision VariablesThe quantities you control.You’ll manipulate these items to find an optimal model
solution.
3. Determine the OutputsWhat you want to solve, show, find, maximize or
minimize.
4. Be aware of any ConstraintsLimits to inputs or outputs. Tradeoffs.
5. Build Relationships into the Model− Relationships between known values and variables,
expressed in formulas.
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.
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
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.
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
Part 3: Exercises
Part 1: Critical Excel elementsPart 2: Model building advice from the expertsPart 3: Exercises
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
top related