Lecture 2: Decision Making with Spreadsheets

Agenda

• Relationship among cost, revenue, and profit
• Break-even analysis using Excel
• Guidelines for making good spreadsheet models

Importance of Cost, Revenue, and Profit

• Understanding the relationship between profit and cost is crucial for:
  • Maximizing profit or minimizing cost in linear programming models.
  • Projecting costs, revenue, and profits based on production volumes.

Key Concepts

Definitions

• Profit: Profit = Revenue - Cost
• Fixed Cost: Cost that remains constant regardless of production volume (e.g., rent, payroll).
• Variable Cost: Cost that varies with production volume (e.g., shipping charges, sales commissions).

Cost Structure

• Total Cost Model:
  • Example: C(x) = Fixed Cost + Variable Cost
    • Fixed Cost = $3000
    • Variable Cost = $2 per unit produced
    • Total Cost for producing x units: C(x) = 3000 + 2x
• Marginal Cost: The cost increase associated with a one-unit increase in production volume. In this case, Marginal Cost = $2.

Revenue Model

• Total Revenue:
  • Example: R(x) = Selling Price × Volume = $5x
• Marginal Revenue: The increase in total revenue resulting from a one-unit increase in sales volume, here Marginal Revenue = $5.

Profit Function

• Profit Function:
  • Profit = Revenue - Cost
  • Example: P(x) = R(x) - C(x) = 5x - (3000 + 2x) = -3000 + 3x
• Break-even Analysis: Finding the production volume (x) where profit = 0.
  • Setting the profit function to 0:
    • 0 = -3000 + 3x
    • x = 1000 units (Break-even point)

Break-even Analysis with Excel

• Use the Goal Seek function in Excel for break-even analysis:
  • Set cell: Profit cell (set to 0)
  • Changing cell: Production volume cell
  • Result: Find the break-even quantity (1000 units).

Modeling with Spreadsheets (4 Stages)

1. Plan: Visualize the end goal and sketch the spreadsheet layout.
2. Build: Create a small-scale model.
3. Test: Try different solutions and check logic.
4. Analyze: Evaluate solutions using optimization tools, return to planning if needed.

Guidelines for Building Good Spreadsheet Models

• Enter and clearly identify all data before formulating.
• Separate data from formulas; refer to data cells in formulas.
• Avoid hardcoded numbers in formulas.
• Keep the spreadsheet simple and easy to interpret.
• Use range names for clarity in formulas.
• Use relative and absolute references effectively to simplify formula copying.
• Use Excel's fill command for efficient data entry.
• Distinguish cell types with borders, shading, and colors.

Conclusion

• This lecture covered the relationship among cost, revenue, and profit, break-even analysis, and guidelines for building effective spreadsheet models.
• Next lecture will focus on formulating a linear programming (LP) problem.