MIS 446 Business Analysis and Modeling

1
MIS 446 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 Modeling and Decision Support

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 (7 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 this class, intro MIS courses and
  healthcare operations mgt (EMBA-HCM)
• http//www.sba.oakland.edu/faculty/isken/

4
Healthcare Operations Analysis

• Internal business analysis / decision support
  consultant
• Simulation modeling
• Critical care tower
• Ptube systems
• Staffing and scheduling
• Database and DSS development using Access, Excel
  and other tools
• Various statistical and operations analysis
  studies

5
Class Then Syllabus

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

http//www.sba.oakland.edu/faculty/isken/MIS446/

• 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...

6
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
7
Primary Learning Objectives

• Quantitative spreadsheet based modeling of
  business problems
• Advanced Excel skills (we're talking 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

8
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

9
Class Structure

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

10
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.

11
About the Data
A patient type code
Location ID
When patient entered and exited the location.
Unique Patient Arrival ID
12
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?

13
Decision Support SystemsThe Classic Structure
Internal and External data
User Interface
Data Management
Model Bases Management
Knowledge Bases Management
14
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

15
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

16
Convergence of Data and Models for Decision
Support

• Data is retrospective, models offer possibility
  of prediction
• Data is simply not enough for solving many
  difficult business problems
• A routing example
• A material handling example
• Business Intelligence the latest buzzword
• BI is one of the fastest growing areas of IT/MIS
• Difficult to outsource
• Supporting managerial decision making, not just
  automating business processes
• Combine business savvy, analytical thinking, IT
  wizardry

17
The Crux of the Class

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

18
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

19
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
20
The End ProductCh 27 in VBA
21
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

22
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

23
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

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

26
Spreadsheet Simulation ExampleBookstore
Inventory Problem

• Bookstore needs to set order quantity for
  calendar
• Unit cost and selling price known
• Unsold calendars can be returned for partial
  refund (amount known)
• Demand unknown - probability distribution
• Decision Variable - order quantity
• Whats the best order quantity?

27
Background Information

• In August, Walton Bookstore must decide how many
  of next years nature calendars to order.
• Each calendar costs the bookstore 7.50 and is
  sold for 10.
• After February 1 all unsold calendars are
  returned to the publisher for a refund of 2.50
  per calendar.

28
Background Information -- continued
Where did we get these?

• Walton wants to maximize the expected profit from
  calendar sales.
• Hmm, whats the expected demand?

29
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

30
A Few OR/MS 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

31
The OR/MS Toolbox

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

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

32
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

33
Finding out more

• INFORMS the premier professional society
• the portal for OR/MS
• http//www.informs.org/Resources/Job_Information/
• 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
• This class
• geek heaven

34
The Proposition

• 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 .
• IT without analysis and modeling falls short of
  impacting business as it could
• Many IT people dont know enough about processes
  and modeling.

35
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
36
Worktime

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