Module3-Slides

Spreadsheet Modeling Overview

Accompanying Heizer and Render Operations Management textbooks.

The Importance of a Spreadsheet in Operations Management

  • When Excel Runs Your Life: Spreadsheet modeling tools help in decision-making by analyzing data.

    • Combines elements of calculations (Algebra, Calculus, Trigonometry).

  • Basic Operations Examples:

    • Sales and Expenses data:

      • Sales: 1,914, 2,531

      • Expense: 900, 700

      • Balances: 524, 1,831

    • Different stages of life utilizing spreadsheet skills.

Defining a Model

  • What is a Model?

    • Representation of key factors and relationships of a system.

    • Types of Models:

      • Mental Models: Conceptualizations of understood systems.

      • Visual Models: Graphical representations to support a concept.

      • Physical Models: Tangible representations of systems.

      • Mathematical Models: Use mathematical formulations to represent scenarios.

      • Spreadsheet Models: Utilize spreadsheets to create interactive and dynamic models.

Key Elements of a Mathematical Model

  • Parameters: Uncontrollable factors imposed by the system.

  • Decision Variables: Controllable factors decided by the modeler.

  • Objective Function: A measure that is either minimized or maximized (e.g., profit).

  • Constraints: Limitations imposed on the model, indicating resource availability.

    • Influence Chart: Not shown in charts, but vital to understanding relationships.

The Spreadsheet Modeling Process

  1. Preliminary Steps:

    • Turn off the computer and draw an influence chart outlining key elements and their interrelationships.

    • Sketch an overall plan identifying where inputs, decision variables, intermediate calculations, and objective functions will be placed in the model.

  2. Development Steps:

    • Develop the base case model in the spreadsheet.

    • Test the model using trial values to ensure functionality before analyzing it further.

    • Document the model for clarity and understanding for others.

Example 1 - SportsFeet Manufacturing

  • Scenario: Analyze production quantities for a new shoe line.

  • Cost Analysis:

    • Variable cost: $9 per shoes.

    • Fixed costs: $52,000.

    • Selling price: $25 per shoe.

    • Resource limitation: Maximum of 5000 pairs due to raw materials.

Key Elements in SportsFeet Model

  • Objective Function: Optimize profit.

  • Decision Variables: Quantity of shoes produced and sold.

  • Parameters: Selling price, fixed costs, variable costs.

  • Constraints: Raw material limits impacting production.

Financial Relationships for SportsFeet

  • Profit Calculation: Profit = Revenue - Total Cost.

  • Revenue Equation: Revenue = (Quantity Made & Sold) × Selling Price.

  • Total Cost: Total cost = Fixed Cost + Variable Cost (Variable Cost = [Quantity Made & Sold] × Unit Cost).

Mathematical Formulation for SportsFeet

  • Decision Variable: Q (Number of shoes produced & sold).

  • Objective Function: Maximize Profit = 25Q - (9Q + 52000).

  • Constraints: 25Q - 9Q + 52000 ≤ 5000 (Production resource limits).

Influence Chart for SportsFeet Manufacturing

  • Breakdown of how Profit, Total Cost, Revenue, and other factors are related to decisions made about production quantity.

Example 2: Sidneyville Desk Manufacturing

  • Scenario: Two types of desks produced using three types of woods, each with specified constraints.

  • Objective Function: Profit maximization based on desk types produced.

  • Decision Variables: Quantity of each type of desk (Rolltop and Regular).

  • Parameters and Constraints: Unit profit per desk type and wood available.

Key Relationships in Sidneyville Model

  • Profit Calculation: Profit = Rolltop Profit + Regular Profit based on units produced.

  • Mathematical Formulation: Maximize Total Profit under certain wood availability constraints.

Natural Progression and Interaction of Examples

  • Each example builds upon the understanding of how to structure mathematical models, identifying key components, and formulating decisions based on given data.

  • Influences noted for each modeling scenario keep the focus on practical applications and real-world scenarios in operations management.