BA2000 Spreadsheet Models in Management Science

Mode: Recitation/Seminar/Discussion   Ref#41466  Section 001  3.0 Credits
TR     12:30 pm - 01:45 pm     Room 134 SOCIAL SCIENCE BUSINESS (a computer lab with 30 workstations)
   Topic: Spreadsheet Models In Management Science

An experimental course in Fall, 2005 team taught by RJ Banis and  RM Nauss

Tentative Grading:

No Exams. The grade will depend on exercises, some of which we will do together in class, and a project to be presented to the rest of the group towards the end of the course.

The YRBSS Project and a proposal for the final project are due Thursday, November 17.

Tentative description of Final Project:

Projects will be presented on Dec 1, 6, and 8. 
Projects may be either team or individual. If a Team project, responsibility for separate sections or aspects of the project should be identified clearly, as two people may not get the same grade on a joint project. A project should consist of application of tools studied in this course to some real-life case related to work, volunteer activity, or selected observed situation in everyday life.
A project should involve quantitative analysis and work proportional to the number of participants. Maximum of 2 students on a project. 25 minute presentation each person with roles of each clearly defined.
Students not presenting on a given day will produce "suggestions on the presentation/project" assignment that contributes to the grade to asssure everybody gets a hearing.
Comments must be strictly positive, and will go to the presenters anonymously at the end of the course.

tools they should use, e.g.:
PivotTables or Solver, graphs of some kind, some interactivity features such as forms, conditional formatting.
Other tools they might use: lookup tables,  Macros, random number generators or other stochastic simulation.

 Participants' sections of each report will receive grades based on criteria such as novelty, clarity, correct application,
significance/utility of the result. Reports will not be returned, so please keep a copy for yourself, and specify if any of the information is to be treated as confidential.


 BA1800 Introduction to Computers and Data Processing, or equivalent.
 MA 1105, Introduction to Statistics and Probability.


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

You may need access to a computer where you can install the software from the book to do the exercises. You should also have a copy of Microsoft Office on your computer so you can do the EXCEL assignments at home. Try the bookstore for the Academic version Office Pro at a very reasonable price.

There will be Videos and other course materials on CD-- This is different from the CD that comes with the book.

Suggested supplementary texts, not required.  No need to buy these, but they are good books for reference in this area:

Preliminary COURSE OUTLINE-first half:

The specifics will change quite a bit, as we will make good use of the textbook. We will also adjust the material according to background and interests of the participants.The examples shown here are from related courses.

Week one:
    Overview and EXCEL Basics
    Participant Survey

Example: A practical thinking problem, "How to take exams" part 1--see mygateway course documents.
                --given points and times for questions on an exam in which there is not time to do it all, what sequence should you use to maximize total score?
                    initially assume partial credit proportional to time spent for the last question that would be only partially completed.
                    How would the sequence change if there were no partial credit?

 Example: factor rating and group decisions==  Jobrate sheet without formulas Example: Max EMV & EVPI for objective decisions, value of information and sensitivity analysis, Example: Net Present Value Functions NPV Example: The Gambler's Ruin Example: Martha Stewart Z-score Table (Two-way Table function) Example: George Washington's Word Processor stuck on CapsLock

Examples: Some online data:
                    Missouri Sex Offender Database (MSHP, requires Internet Explorer)
Unfortunately, since the start of the course, Missouri has made it more difficult to access these data. You can't easily download the the whole dataset.  You  can, however, search it a small piece at a time online if you have the patience,

Example: Internal Rate of Return Example Simulation:
Document  Baseball simulation due 1230 on Tuesday 10/18
Assignment_10_04__baseball_.xls ( 51712 Bytes )

Example: GoalSeek

Document  Ch 16 Lemonade Goal Seek
Ch_16_lemonadegstemp.xls ( 15360 Bytes )
Document  Ch 16 Mortgage Goal Seek
Ch_16_Mortgage_example_and__4.xls ( 15360 Bytes )
Example: Optimizing with EXCEL SOLVER
Files from the book available on mygateway:

Document  Solver intro 10-11-2005
solverintro10_11_05.xls ( 29184 Bytes )
Document  Ch 25 Production Mix Example
Ch_25_production_mix_10_11_05.xls ( 33792 Bytes )
Document  Ch 26 transportation problem 10-13-2005
Ch_26_transport_Oct_13_2005.xls ( 32768 Bytes )
Document  Ch 26 example: Transportation extension
Chapter_26_extension.xls ( 20480 Bytes )
Document  Ch 26 #3 HLOOKUP function
Ch26_num_3_Hlookup_function.xls ( 20480 Bytes )
Document  Ch 27 example workforce scheduling
Ch_27_employee_scheduling.xls ( 19968 Bytes )
Document  Ch27 cost and overtime
cH27__2_COSTS_AND_OT.xls ( 18432 Bytes )
Document  Ch27 scheduling TV ads
ch27__4_scheduling_TV_ads.xls ( 20480 Bytes )
Document  Ch 28 capital budgeting
Ch_28_text_capbudget.xls ( 36352 Bytes )

Document  Assignment Due Nov 1
ch26num4.xls ( 18432 Bytes )

Example: Youth Risk Behavior Surveillance Survey data, Pivottables & Pivotcharts
  • -Navigating in large datasets (15,000 rows, 100 columns)
  • Importing and parsing fixed length datafiles we will work with the 2003 data so you get experience with importing
  • Preview of BA3300 statistics: Statistics will allow significance analysis (though we won't do it here)
  • Workbooks:
  • Charting:
  • Intro to Macros
  • ****************

    Final Exam

    Scheduled Thursday 15 december, 2005. Since there are no exams, this is the last date for submission of a formal copy of the course final project.
    However, presentation of the project will be during the last few weeks of classes.

    Return to Bud's Page