MIS 646 Business Analysis and Modeling

1
MIS 646 Business Analysis and Modeling

• A Spreadsheet Modeling Based Business Analysis
  Studio

2
First Class Overview

• Introductions
• MIS 446 A Business Analysis Studio
• Syllabus review
• Schedule of topics
• Lets get started
• A data analysis challenge
• A simple scheduling challenge
• A simulation example
• Web query example
• Excel tutorial
• A Brief Overview of Business Analytics

3
Prof. Mark Isken

• BSE, MSE, Ph.D. in Industrial and Operations
  Engineering from University of Michigan
• Operations analyst for William Beaumont Hospital
  and Henry Ford Health System and some small
  consulting companies (10 years)
• Joined OU Fall 1999 as full-time faculty member
  of Dept. of Decision and Information Sciences
• Im a techie love working with computers and
  mathematical models to help solve business
  problems
• Teach MIS 446/646 Business Analysis Modeling,
  intro MIS courses and healthcare operations mgt
• http//www.sba.oakland.edu/faculty/isken/

4
Healthcare Operations Analysis

• Internal business analysis / decision support
  consultant
• Simulation modeling
• Critical care tower, emergency departments
• Pneumatic tube systems, outpatient clinics
• Staffing and scheduling
• people, cases, tests, etc.
• Database and DSS development using Access, Excel
  and other tools
• Various statistical and operations analysis
  studies
• An interesting recent job posting...

5
Information Technology Can Support/Enable/Automat
e Both
?

• Decision oriented business processes
• Planning
• Analyzing, exploring
• Logistics
• Scheduling
• Bidding
• Investing

• Transaction oriented business processes
• Order processing
• Billing
• Paying
• Standard reporting
• Shipping
• Tracking

6
A Challenge

• IT without analysis and modeling falls short of
  impacting business as it could
• Many IT people dont know enough about processes
  and modeling.
• Analysis and modeling without Information
  Technology (IT) falls short of impacting business
  as it could
• Many business process analysis/modeling people
  dont know enough about IT .

7
The Crux of the Class

• Data is good.
• Data is often not enough, need models too.
• ModelsDataVBA Decision support system

8
Why Spreadsheets?

• Spreadsheets are the de facto standard platform
  for modeling and analysis in business today
• The language of business
• Growing interest in spreadsheet engineering
• Errors
• Sarbanes-Oxley
• Spreadsheets play key role in business
  intelligence portfolio of companies
• Excel has rich set of modeling and analysis tools
• Many sophisticated add-ins available
• Spreadsheet based modeling wave in many top
  business schools (Indiana U., Ivey, Dartmouth,
  Michigan, etc.)
• End user DSS development via VBA
• A wide open opportunity for stardom
• Can tie with other products such as DBMS

9
Class Then Syllabus

• Program and/or Major
• Work experience
• What brought you here?

http//www.sba.oakland.edu/faculty/isken/MIS646/
(public) Moodle for private portion of course
website

• Course Web site will be the place to go for
  course information and materials.
• Get used to checking it frequently
• Announcements
• Homeworks
• Downloads
• Tons of Resources...

10
Spreadsheet Based Decision Support
Management science modeling
Application Development
Data analysis
Simulation
User Forms
Database
Optimization
Statistics
Automation
Stochastic modeling
OLAP
Environment customization, error prevention
handling
Analysis Tools
Decision Analysis
Basic foundation
Modeling
Spreadsheet modeling
VBA
11
Primary Learning Objectives

• Quantitative spreadsheet based modeling of
  business problems
• Advanced Excel skills (we're talking super power
  user)
• Excel VBA
• End-user DSS development
• Models data IS/IT Decision support systems
  (DSS)
• Presenting technical analyses
• Role of modeling and DSS in business today
• An area of big opportunity for MIS folks

12
Themes

• Business problem driven
• Hands on, learn by doing
• Getting data from various sources (e.g. ERP
  systems, data warehouses, other IS) and adding
  "smarts" through modeling and analysis is a much
  needed and valuable skill
• Data is not enough
• Management science IT business value
• Excel is good, ExcelVBA is even better
• Ask lots of questions, think, work hard, try
  things, have fun
• Cultivate technical business analysts or
  consultants

13
Class Structure

• Workshop style
• Mixture of
• Mini-lecture
• In-class problem solving
• Work time
• TAKE ADVANTAGE OF CLASS TIME

14
OK, Lets Start with a little data problem

• Download PatientLocationLog.txt from the
  Downloads section (right click Save Target As)
• Somehow, get it into Excel so you can analyze it
• This is a table of entries of patients to various
  locations in a clinic. Note that each patient may
  visit multiple locations.

15
About the Data
A patient type code
Location ID
When patient entered and exited the location.
Unique Patient Arrival ID
16
Your Challenge

• How many times did a patient with PatientType2
  enter Location 38?
• If you get that, how might you quickly find how
  many patients of each type entered each location?
• What date, time and day of the week did the very
  first patient arrive to location 31?
• How much total time (in minutes) did the patient
  with ArrivalID16 spend in the clinic?

17
Why are decision problems hard?

• Uncertainty in key problem inputs and in the
  future
• EXAMPLE Demand for open heart surgery next year
• Complex relationship between variables
• the physics of business processes and services
• EXAMPLE Decrease in network delay due to
  additional server
• Massive number of alternatives
• schedules, plans, routes, scenarios
• EXAMPLE Scheduling airline crews
• Multiple, often conflicting objectives
• EXAMPLE maximize product variety and minimize
  production cost
• Difficulty quantifying outcomes and making
  tradeoffs
• EXAMPLE what is the value of your customers
  wait time for service?
• Obtaining and using data
• get good at info capture/manipulation/queryinq/ana
  lyzing/reporting
• EXAMPLE antiquated IS with no simple way to
  obtain basic business data
• Organization and political constraints and
  pressures - reality
• EXAMPLE your great solution ignored due to
  powerful opponent

18
Decision Support SystemsThe Classic Structure
Internal and External data
User Interface
Data Management
Model Bases Management
Knowledge Bases Management
19
DSS in the 90sA data-centric view

• ERP systems consolidate data
• E-commerce generates tons of data
• Data warehousing/OLAP/data mining/knowledge
  management Business Intelligence
• Reaffirmation that datavaluable corp. resource

20
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?)
• Two modeling examples
• TSP
• Ptube

21
The 00s Business Analytics Convergence of Data
and Models

• Data is retrospective models offer possibility
  of prediction
• Who will default on their loan?
• How busy will the hospital be tomorrow?
• Data is great but may simply not enough for
  solving many difficult business problems
• What If? A simulation example from healthcare
• Whats Best? A routing example
• Data warehousing, online analytical processing
  and data mining are of great industry interest
• DSSResources.com, www.intelligententerprise.com
  and dmreview.com

22
Competing on Analytics

• Netflix Cinematch (Netflix Prize), shipping
  logistics and prioritization, pricing
• Amazon - recommendation
• Google web analytics, ad pricing
• Harrahs data warehousing, closed loop
  marketing
• Marriott optimal room pricing, promos
• Boston Red Sox, NE Patriots, SA Spurs

23
Spreadsheet DSS Example - Portfolio Optimization

• Given a set of investments, how do we find the
  portfolio that has the lowest risk and yields an
  acceptable expected return?
• The single period mean-variance Markowitz model
  (1991 Nobel Prize)
• You saw this in your Intro Finance course

Stocks
Bonds
Gold/silver
x1
x2
x3
x4
x5
x6
24
The End ProductCh 31 in VBA
25
Value of Putting it all Together

• Portfolio optimization example underscores value
  of combining
• Domain knowledge (e.g. finance)
• Mathematical modeling skills (e.g. probability,
  statistics, optimization)
• Information systems skills (e.g. VBA, application
  development)
• Financial Engineering at graduate programs
  throughout the country

26
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

27
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

28
The Solver Interface
Our model of the scheduling problem
Solver
29
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

30
Spreadsheet Simulation Example
31
What is Operations Research Management Science?

• Application of mathematical modeling, data and
  information technology for informed decision
  making.
• Build models to help understand complex systems
  comprised of people, technology and processes.
• Related to applied mathematics, information
  systems, computer science, economics, industrial
  engineering, systems engineering
• Applied broadly in many industries
• Business analytics

32
Business Week Cover Story Recognizes Business
Analytics

• Business Week, in the cover story of its January
  23rd, 2006 issue, recognizes what INFORMS and
  operations researchers have been saying for years
  - that O.R. and math will rock your world. Read
  the article online at www.businessweek.com and
  http//businessweek.com/magazine/content/06_04/b39
  68001.htm.

33
A Few Business Analytics Applications

• Vehicle routing
• Supply chain management
• Revenue management (airlines and hotels)
• Scheduling
• People
• Machines
• Capacity planning
• How many, how big, etc.
• Decision analysis
• Medical
• Investment
• Marketing

• Financial modeling
• Design and analysis of information and
  telecommunications systems
• Customer service systems
• Waits and delays
• Military tactics/strategy
• Healthcare policy
• Organ transplant
• Smallpox vaccination
• Sports
• Player selection
• Strategy
• Metrics

34
The OR/MS Toolbox

• Statistics
• Computer simulation
• Queuing models
• Forecasting
• Decision analysis
• Optimization

• Computer programming
• Spreadsheets
• Databases
• IT
• Business knowledge

35
The Stars are Aligning

• PC's are cheap and extremely powerful
• Huge interest and investment in ERP and data
  warehousing in business as people realize value
  of integration and of data
• E-commerce making even more data electronically
  available
• E-commerce exposing businesses to their customers
  in ways never envisioned
• The evolution of products like MS Excel and MS
  Access into very capable platforms for end-user
  decision support activities
• Many top business schools have created
  spreadsheet based modeling courses
• The field of operations research/management
  science is popping up in general business
  publications and information systems publications
  as its value is becoming more widely recognized

36
Finding out more

• MIS 446/646 Business Analysis and ModelingMIS
  636 Decision Support SystemsMIS 4??/6??
  Simulation modeling in Business
• geek heaven
• INFORMS the premier professional society
• the portal for operations research / management
  science
• The Science of Better
• INFORMS Student Union
• http//www.informs.org/Resources/Job_Information/
• Interfaces
• OR/MS Today very readable monthly magazine
• applications Math Model Takes Aim at
  Bioterrorism
• software reviews Spreadsheet Add-Ins for OR/MS
• about being a professional wizard Irritating
  Einsteins Pose Special Challenges for Managers
• www.e-Optimization.com
• Teradata Student Network (Ill be emailing you
  login info)

37
The Business Analyst
Business analyst is one of the least well-defined
IT titles. Most observers agree that it can mean
a technical person with some business expertise,
or a business person with some technical skills.
"The business analyst is a squishy job title,
but it's important, and those people are hard to
come by," says Tony Graffeo, divisional vice
president for global information services and
head of the internal IT department at Computer
Associates International, in Islandia, N.Y.
"There are technical people who know bits and
bytes but can't speak in end-user terms, and
business people who lack enough technical
expertise to make things happen in IT."
Source 1999 InfoWorld Compensation Survey
38
Worktime

• Survey
• Excel tutorial
• Prep problems for next time
• Homework 1
• Explore Excel out in the World from course website