Spreadsheet Based Decision Support

1
Spreadsheet Based Decision Support

• Power Excel for Business Analysts

2
Revised High Level Structure
Data Warehousing (Access)
Online Analytical Processing (OLAP) (Excel)
Session 1
Data Cleaning and Transformation (Excel)
Session 2
Models and Modeling (Excel)
Session 3
3
Models and Modeling

• Beyond Data

4
Models

• Simplified representation or abstraction of
  reality.
• Capture essence of system without unnecessary
  details
• Models tailored for specific types of problems
• Models help us understand the world
• Prediction (What if?)
• Optimization (Whats best?)

5
Types of Models

• Physical or scale model
• crash test dummy
• architectural model
• Computer simulation model
• flight trainer
• discrete event model (e.g. SimCity)
• Mathematical model
• Yb0b1x1b2x2
• FMA
• Optimization model

6
Descriptive vs. Prescriptive Models

• Descriptive Model
• Describes a system in terms of parameters and
  variables
• If we change some input parameter, what will
  happen to our output performance measure?

• Prescriptive Model
• Suggests good or optimal solutions
• Also made up of parameters and variables
• Searches over many possible solutions to find
  best solution (in some sense)

7
Queueing Models

• Many manufacturing, service, computer, and
  communication systems have queueing related
  subsystems
• Queueing models explore relationship between
  arrival processes, service processes, queueing
  disciplines and waiting times, queue lengths,
  server utilization
• Large number of different queueing models
• Some models simple, others very complex

8
Multiple Servers in Parallel
Customers in Queue
Servers
9
Call Center StaffingUsing a Descriptive Queueing
Model
(1) Inputs
Given these
Predict these
(2) Queueing Model(s)
Mathematical equations
(3) Outputs
10
Ironic email from HFHSReceived Monday afternoon
How are you??? Life here at HFHS is OK. We have
had a few changes within the department and a few
leadership changes in the System recently...
  BLAH BLAH Personal update BLAH BLAH   I
actually do have a work related question for you
(hopefully you don't mind!!)...I assume you
recall the phone model you completed for us...is
it possible to use that same model (either as is
or with a few tweaks) to look at CSR staffing at
the front desks? A co-worker and I are looking
for a way to get decent numbers for both the
phone rooms and the front desk personnel and
believe this kind of a model (we don't know if
this exact one would work) may give us our best
answer. What do you think??
11
Call Center What if Examples

• Given a40 calls/hr, b15 mins/call and c12
  customer service representatives (CSR), what is
  the expected time customers will spend on hold
  (EWq) ?
• If a increases to 45 calls/hr, how will EWq
  change?
• If a increases to 45 calls/hr but we can decrease
  b to 12 mins/call, how does EWq change?
• For a45 and b12, how much can we reduce c ( of
  staff) down to before EWq exceeds 5 minutes?

12
Spreadsheet Based Queueing Model Template
13
Call Center Staff SchedulingAn Optimization
Model Based DSS

• Consider simplified problem
• Assume mean call arrival rate and call length
  have been forecasted from historical data
• Assume weve used a queueing model to find number
  of staff needed for one shift for each day of the
  week to meet some speed to answer target
• Now, lets look at the scheduling problem we face

14
The Scheduling Problem

• Staff works 5 consecutive days
• Can start any day of the week
• Ex T, W, Th, F, Sa

• Objective
• Minimize total amount of staff needed
• By Finding
• Number of employees starting their 5-day
  workstretch each day of the week
• Subject to constraints
• Daily staffing requirements are met

15
Solving Optimization Problems with the Excel
Solver

• Solver is Excel add-in (Frontline Systems, Inc.)
• Pretty sophisticated optimization program
• Min or Max some cell
• By manipulating values in decision variable cells
• Subject to constraints (on the decision
  variables) in other cells
• Linear, integer, and non-linear problems

16
The Solver Interface
Our model of the scheduling problem
Solver
17
About Optimization Problems

• Can be very, very, very, very hard to solve
  optimally
• Very good solutions to very difficult problems
  are found routinely using computerized
• Mathematical models
• Intelligent heuristics
• Many, many, many business applications
• Staff scheduling (airlines, hospitals, call
  centers, etc.)
• Routing (Fed Ex, supply chain)
• Production scheduling
• Financial porfolio planning
• Computer/telecommunications network planning

18
Learning More