Sensitivity Analysis and Budget Fundamentals

Introduction to Sensitivity Analysis and Budgeting

• The session revolves around a how-to guide on performing sensitivity analysis using Excel for business budgeting.
• Emphasis on understanding critical thinking and examining the data provided to derive solutions.

Classic Limo Case Study

Background

• Classic Limo provides limousine services primarily to the Bryce Airport.
• Services have fixed pricing per trip, with stable costs per trip.

Economic Factors Affecting Budgeting

• Owner Mark Pence budgets income based on two fluctuating economic factors:
  • Fuel cost per trip.
  • Number of customers using the service.

Important Distinction:

• Although Mark acknowledges that fuel costs and customer counts can affect each other, he treats them as independent variables for analysis.

Contribution Margin Scenarios

• Contribution Margin (CM) is defined as the difference between sales revenue and variable costs.
• Given three scenarios for analysis:
  1. Excellent Scenario:
  • Contribution Margin = $50;
  • Customers = 10,500.
  1. Fair Economy Scenario:
  • Contribution Margin = $25;
  • Customers = 6,000.
  1. Poor Economy Scenario:
  • Contribution Margin = $15;
  • Customers = 4,500.

Costs Associated with Service

• Fixed Other Service Costs: $50,000 plus $5 for each customer ride exceeding 6,000.
• Annual Administrative and Marketing Costs: $25,000 plus 10% of the total contribution margin.

Calculating Operating Profit

• Use the formula for operating profit that accounts for contribution margin, contribution from customers, and all costs involved.
• Steps to arrive at operating profits from each scenario.
• Example calculation:
  • For poor economy scenario with 4,500 customers:
  • CM = $15
  • Calculate operating profit using: $ext{Operating Profit} = ext{(CM imes Customers)} - ext{(Other Service Costs + Admin Costs)}$
• Discuss the operational impact of each scenario on profitability and marketing costs.

Glacier Creamery Case Study

Background

• Glacier Creamery specializes in making and selling ice cream.

Sensitivity Analysis Parameters

• Focus is placed on two key variables:
  • Quantity of ice cream sold (affected by average summer temperatures).
  • Variable costs of production (dependent on the cost of raw materials).

Sales Dynamics

• Expected sales volume of ice cream is 400,000 gallons, with the sensitivity of +/- 2,000 gallons related to temperature variations.
• Example Calculation:
  • Given a temperature of 74°F, demand would decrease to:
    $400,000 - (76 - 74) imes 2,000 = 398,000 ext{ gallons}$

Temperature and Cost Scenarios

• Possible temperature scenarios for the analysis:
  • 75°F, 77°F, and 80°F with corresponding sales impacts.
• Variable costs are given as:
  • $3.00/gallon, $3.50/gallon, and $4.00/gallon

Contribution Margin and Costs

• Selling price maintained is $6/gallon, leading to contribution margins calculated as:
  $ext{Contribution Margin} = ext{Selling Price} - ext{Variable Cost}$
• Total Contribution Margin includes calculations for each temperature scenario based on quantity sold.
• Total Expected Fixed Operating Cost is:
  $400,000 + ext{(Sales Revenue imes 10 ext{ extit{ ext{ ext{percent}}}})}$

Working with Excel

• Importance of setting proper headers and utilizing Excel for calculations demonstrated during class.
• Students are encouraged to calculate contribution margins and other costs using Excel formulas for accuracy.

Project Requirements

• Each student will form groups for the budgeting project, ensuring collaboration and discussion for better understanding.
• The project focuses on applying sensitivity analysis and operational cost understanding in budget preparation.

Conclusion

-Students will continue working on the project in the upcoming class, with the objective of applying learned concepts in real-world scenarios.

• Feedback and guidance will be provided in subsequent sessions to ensure comprehension and accuracy.