Intro to Spreadsheet Modeling

  • 7-weeks
  • 1.5 credits
  • Prerequisite: MBA Core

This course will introduce students to the art and science of developing quantitative models that can be used to make better business decisions. We will introduce students to optimization models (including the use of genetic algorithms) with the Premium Excel Solver, simulation models (with the EXCEL add-in @RISK), decision tree models (with the EXCEL add-in PRECISION TREE), and spreadsheet sensitivity analysis (with the SOLVER TABLE and TOP RANK EXCEL add-ins). A major emphasis of the course will be on sensitivity analysis because that will be critical for your internship or your first job.

What you will learn in this course:

  1. The Product Mix Problem:
    1. What product mix should Eli Lilly produce each month in the Clinton, Indiana Plant?
    2. How sensitive is the optimal product mix to changes in prices, machinery availability and other factors?
  2. Manpower Scheduling Problems:
    1. How should a maternity ward schedule their nurses to minimize cost and maximize employee satisfaction?
  3. Transportation Model:
    1. Where should GM produce cars to minimize the sum of shipping and production costs?
  4. Assignment Model:
    1. How should PWC assign consultants to jobs to maximize their productivity?
  5. Facility Location:
    1. Where should an Internet retailer locate their warehouses?
    2. Rating Sports Teams with the Excel Solver
  6. Sales Force Allocation:
    1. How can Eli Lilly or Pfizer determine an allocation of sales force effort to different products which maximizes corporate profitability?
  7. Introduction to Genetic Algorithms:
    1. How can GA's be used to solve for profit-maximizing prices and for cluster analysis?
  8. Analyzing the Introduction of a New Product:
    1. What factors are most critical to determining new product profitability?
    2. How does Eli Lilly determine the optimal capacity level for a new product?
    3. How can we accurately estimate new product profitability?
  9. Cash Budget Simulations:
    1. How can we estimate the profitability that a plant will go over budget during the next year?
  10. More complicated simulations:
    1. How can we make EXCEL simulate craps?
    2. How can we determine optimal machine maintenance decisions?
  11. To Test Market or Not?
    1. How can we use decision trees and PRECISION TREE to determine whether or not P & G should test market a new detergent?

Kelley School of Business

Faculty & Research