Teaching Operations Courses Using Spreadsheets: Business Education Excellence - PowerPoint PPT Presentation

1 / 28
About This Presentation
Title:

Teaching Operations Courses Using Spreadsheets: Business Education Excellence

Description:

Teaching Operations Courses Using Spreadsheets: Business Education ' ... in different phases of completion (through your course web) ... Gap between UB and LB ... – PowerPoint PPT presentation

Number of Views:59
Avg rating:3.0/5.0
Slides: 29
Provided by: erhan1
Category:

less

Transcript and Presenter's Notes

Title: Teaching Operations Courses Using Spreadsheets: Business Education Excellence


1
Teaching Operations Courses Using Spreadsheets
Business Education Excellence
  • HEC
  • 31 January 2000
  • Erhan Erkut
  • Faculty of Business
  • University of Alberta

2
Remember that
  • Typical Business Student
  • Weak algebra/calculus background
  • Difficulty with abstraction
  • Little/no training in modeling
  • Hungry for computer skills
  • Will live in spreadsheets

3
How to lose a Business student
4
Whats with the xij thingies?
5
Aha!
6
That makes sense..
7
As you will recall
  • Spreadsheets, because...
  • Demystify modeling
  • Lift the algebraic curtain
  • Powerful what-if
  • Can analyze, optimize, simulate, build DSS
  • Visualization/animation
  • 40 Million users
  • One in every office
  • Empower students

8
Lets not get carried away...
  • Documentability
  • What is a formula, what is data
  • What feeds into what
  • Scaleability
  • i1, ., n
  • Dimensionitis
  • try adding a third (or fourth) index
  • Not quite the answer to all of our problems.

9
Nevertheless..
  • They work really well in teaching quant. material
    to Business students.
  • Students can follow
  • and much more importantly, they can DO IT!
  • Many more students are turned on.
  • P(applyspreadsheet) gtgt P(applyalgebra)
  • Many will become really good at it.
  • And now some tips...

10
Tips for Teaching with Excel
  • Students should be able to see the sheet.
  • Students must be able to make sense of the sheet
    (color, names, layout, comments).
  • Using a spreadsheet in class does not mean
    showing students completed spreadsheets it means
    building spreadsheets from scratch, with the
    students.
  • Give students sheets in different phases of
    completion (through your course web).
  • Humans make mistakesdo not be afraid.

11
Tips for Teaching with Excel
  • Spreadsheets are what-if tools. Exploit them.
  • Spreadsheet modeling paradigm is different from
    algebraic modeling paradigm do not force your
    favorite layout on a spreadsheet model.
  • Use cell/range names when dealing with solver.
  • Demonstrate abuses.
  • Use animation.

12
Spreadsheet solver
  • Modeling language
  • ... with graphical user interface

13
Excel solver
  • Birth date 1991 (Excel 3.0)
  • Producer Frontline Systems
  • Current Population 40M
  • Optimizers
  • Linear simplex w. bounds
  • Nonlinear GRG
  • Integer BB

14
How large a problem?
  • Standard 200 variables
  • Premium 800
  • Platform 16,000

15
Speed?
  • Excel 3.0
  • 5x
  • Excel 5.0
  • 20x
  • Excel 97
  • 25x
  • Premium

16
Bugs?
  • Bugs in earlier versions
  • Frontline offers 500 for a new bug discovery
  • Two of our students received the prize...

17
No competition for CPLEX,...
  • but real-world problems are within reach.

18
Frontline says
  • 7M/year savings via a 200-variable application
    (Fortune 50 company)
  • No. of applications with lt 200 variables
    (5-10) x (No. of larger applications)
  • OR/MS Pros. are involved in lt 1 of all solver
    applications in use

19
Recent features
  • Auto scaling applies to LP
  • Can set convergence tolerance for IP
  • Improved linearity tests
  • Direct input of binary variables (bin)
  • Much faster

20
Premium advertising
  • Altius, fortius, celsius
  • Linearity and feasibility reports
  • Standard IP breadth-first search
  • Premium IP
  • depth-first followed by breadth-first
  • sophisticated selection rules
  • dual simplex

21
The solver is..
  • not designed for the OR Pros,
  • but created with the spreadsheet user in mind.
  • wider use
  • - wider abuse

22
Some of the abuses
  • Using IF instead of 0/1 variables
  • Not reading the dialogue boxes
  • Not scaling the problem
  • Using a high tolerance for IP
  • Not keeping everything on one sheet
  • Finding local optimum for NLP
  • f(x)/g(x) lt c
  • Assume Linear Model (Y/N)
  • 2 tons

23
A few tips/reminders
  • Precision (? )
  • a b means... b ? gt a gt b ?,
  • Tolerance Gap between UB and LB in IP
  • NLP multiple runs with different starting points
    (if OF not convex)
  • Dual variables are formatted the same way as
    variable and constraint values.

24
A few curiosity items
  • Set target cell equal to ??
  • Leftover from Goal Seek

25
A few curiosity items
  • min (2SUMPRODUCT)
  • min (SUMPRODUCT2)
  • 2(min SUMPRODUCT)
  • Whats the difference?

26
A few curiosity items
  • Limits report
  • (What is the max/min each variable can take?)
  • Why have one?
  • cause Lotus 1-2-3 had one...

27
Examples
  • Product mix (Apples)
  • Build model together and solve
  • Solver Table
  • Macro
  • Aggregate Planning (MacPherson macro)
  • Facility location
  • 1-median
  • p-median macro
  • Minimal cost network flow (Red Dog)
  • Shortest Path (macro)

28
Examples
  • Maximal Covering
  • Build model and solve
  • Solver Table
  • Vehicle Routing
  • Farthest insertion macro
  • Clarke-Wright macro
  • Fisher-Jaikumar (Lagrangian Relaxation)
  • Queueing
  • Workload smoothing
Write a Comment
User Comments (0)
About PowerShow.com