BA2000 Spreadsheet Models in Management Science

Planned as an experimental course in Fall, 2005 to be team taught by RJ Banis and  RM Nauss
most of the project ideas I've listed here are already put together complete with instructional videos. However, between now and next Fall, the plan will be revised to take more advantage of topics covered in the Winston text.

Textbook:
Wayne Winston (2004)Microsoft EXCEL Data Analysis and Business Modeling, MicrosoftPress, ISBN 0-7356-1901-8 list $39.99
Search Inside the Book
 

Videos and other course materials on course CDs and DVD-- This is different from the CD's that came with the book.

Suggested supplementary texts, not required.  No need to buy these, but they are good books for reference in this area and will be on reserve in the Library:

• John Walkenbach (2003) EXCEL 2003 Bible, Wiley, ISBN 0-7645-3967-1 list $39.99 Search Inside the Book
• Robert J. Banis (2005) Youth Risk Behavior Survey with Student Guide for Statistical Analysis in EXCEL, Heuristic Books, ISBN 1-888725-24-9 list $28.95   Handouts and videotutorial CD ROMs will be distributed as part of the course materials.
• Winston & Albright (2001) Practical Management Science, Duxbury, ISBN 0-534-42435-X list $119.95 Search Inside the Book

Purpose/goals:

 Crossdiscipline With an emphasis on Management Science Models applied to various dimensions of Business.:

LOM, marketing, Accounting, Finance, Economics, org behavior

 promote more quantitative approaches in business courses

 Interest students in quantitative aspects early

 target broad appeal-- we should try to make this a popular course with several sections, general enough to attract some students from outside business as well.

 feed into rather than preempt material covered in e.g. statistics, forecasting, computer models in accounting, computer models in finance, other more detailed area-specific offerings.

Concepts/principles:

•  Early in the Curriculum
•  Not too difficult--across the top survey
•  EXCEL techniques not depending on expensive proprietary addins
•  Entertaining and interesting
•  USEFUL
•  Highlight OR/MS
•  Preview--"Other courses that allow you to do more with this."

Prerequisites::

Preliminary COURSE OUTLINE-first half:

• Navigation in Excel--data capacity and features
•  customize EXCEL
•  passwords and protection
•  audit toolbar
•  Goal seek

• Formula Design-- relative and absolute addressing
 example: factor rating and group decisions
 Max EMV for objective decisions
 Sensitivity analysis,
•  conditional formatting, scenarios and tables
 Martha Stewart Z-score Table (Table function)
 Z scores and IQ's
•  Overview of statistics functions and data analysis tools.

• Importing, cleaning and managing data
 Forms for efficient data entry
 filter and extract selected records
 (several examples: mailing list management demonstrates parsing,
  sorting, concatenation. also shows WORD merge)
 exchanging data with other applications

• Charting:
 Histograms (not usually taught in computer courses!)
Charting-- Stock indexes, unemployment and Market analysis-- Beta's
 Preview multiple regression & time series (CAPM model and estimating
  Beta's-- e.g., p369-374 in Winston & Albright, 2001)
• Workbooks:
 Managing 3D sheets:
  fixed and variable cost budgets for numerous cost and revenue centers

• Solver-- Comparative advantage and assigning jobs
( Mathematics of The Dilbert Principle)
Preview: Solver production schedules and product mix decisions

• Pivottables & Pivotcharts--e.g --This is a large video file-don't try to view it unless you have a fast connection--   survey crosstabulations
 
 Broad appeal: risky behavior of highschool (or college) students.
 Navigating in large datasets (14000 rows, 100 columns)
 Preview: Statistics will allow analysis (though we won't do it here)
 alternatively, the flexible Chi-Square model is a good example for
  formula design, but may be a little deeper into stats than we want to
  go in this course)

• Intro to Macros

(a little VBA? perhaps a simulation model with macros)
• Random extinction of family names  http://www.umsl.edu/~rbanis/252/docs/namsim2.xls

• Slot machine configuration simulation. What does "96% payout" mean? slotsimulation.xls
• A slot machine game configuration is described by wizardofodds.com
• Suppose payoffs are proportional to odds: is it better to play a high jackpot low frequency game, or a medium jackpot higher frequency game? use this model to explore the question. changing  odds by adding symbols to reels causes recalculation of payoffs.

Part II