Spreadsheet Models - DSS

1
Spreadsheet Models - DSS

• Basic Profit Models
• What-if, Sensitivity Analysis

2
Lecture Objectives

• You should be able to
• Analyze a business situation and draw an
  influence diagram.
• Build basic profitability models on a
  spreadsheet.
• Perform what-if, sensitivity analyses.

3
Breakeven Analysis

• Consider a relatively simple situation
• Sally owns a motel with a hundred rooms. Fixed
  daily cost is 1000 (includes mortgage, staff
  salaries, maintenance). Variable cost per room is
  10 per day (includes extra utility cost, room
  cleanup, etc). At a fixed room price of 50 per
  day, what is the breakeven point?
• Draw an influence diagram leading up to your
  profit.
• Compute the breakeven point.

4
Influence Diagram
The boxes that cannot be split any further (for
this simple example) are the basic inputs for the
analysis. How is Number of Rooms Rented different
from the rest of the inputs? Are there any other
dependencies that are not shown above?
5
Breakeven Analysis
Price 50
FC 1000
VC/unit 10
Rooms 5 10 15 20 25 30 35 40 45 50
Rev 250 500 750 1000 1250 1500 1750 2000 2250 2500
FC 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000
VC 50 100 150 200 250 300 350 400 450 500
Tot Cost 1050 1100 1150 1200 1250 1300 1350 1400 1450 1500
Profit -800 -600 -400 -200 0 200 400 600 800 1000

6
Breakeven Point
7
Crossover Point
You have the option of subcontracting to improve
room quality and the surroundings, but that would
increase fixed costs to 1800, with no change to
variable costs. You will, however, be able to
charge 70 per room per day. At what point will
you be indifferent between your current mode of
operation and the new option?
8
Crossover Analysis Point of Indifference
Case 1 Case 2
Price 50 70
FC 1000 1800
VC/unit 10 10
Case 1
Rooms 20 40 60 80 100
Rev 1000 2000 3000 4000 5000
FC 1000 1000 1000 1000 1000
VC 200 400 600 800 1000
Tot Cost 1200 1400 1600 1800 2000
Profit -200 600 1400 2200 3000
Case 2
Rooms 20 40 60 80 100
Rev 1400 2800 4200 5600 7000
FC 1800 1800 1800 1800 1800
VC 200 400 600 800 1000
Tot Cost 2000 2200 2400 2600 2800
Profit -600 600 1800 3000 4200
9
Crossover Analysis
10
Pricing Analysis Demand Function

• If the demand for rooms depends on the price as
  follows
• Quantity Demanded 200 - 3price,
• what price should Sally charge for a room?
• Assume Fixed Cost is still 1000 per day and
  Variable cost is 10 per day per room.
• Determine the Goal.
• How would Sally get such a demand equation for
  her business?
• Determine the best price to help her reach her
  goal.

11
Price and Profit
What is the best price?
Pricing Strategy Example Pricing Strategy Example
Max Rooms 100
FC 1000 Demand 200 - 3p Demand 200 - 3p
VC/unit 10 Intercept Slope
200 3

Price 0 10 20 30 40 50 60 70 80

Rooms Demanded 200 170 140 110 80 50 20 -10 -40
Rooms Rented 100 100 100 100 80 50 20 0 0

Rev 0 1000 2000 3000 3200 2500 1200 0 0
FC 1000 1000 1000 1000 1000 1000 1000 1000 1000
VC 1000 1000 1000 1000 800 500 200 0 0
Tot Cost 2000 2000 2000 2000 1800 1500 1200 1000 1000
profit -2000 -1000 0 1000 1400 1000 0 -1000 -1000
12
Profit Vs. Price
13
Sensitivity Analysis
Pricing Strategy Example Pricing Strategy Example Pricing Strategy Example
Price 50
FC 1000
VC/unit 10
Rooms 50
Rev 2500
FC 1000 The table below shows profits at different prices and variable costs The table below shows profits at different prices and variable costs The table below shows profits at different prices and variable costs The table below shows profits at different prices and variable costs The table below shows profits at different prices and variable costs The table below shows profits at different prices and variable costs The table below shows profits at different prices and variable costs
VC 500
Tot Cost 1500 Prices per room Prices per room Prices per room Prices per room Prices per room Prices per room Prices per room Prices per room Prices per room
profit 1000 30 35 40 45 50 55 60
7 1300 1660 1640 1470 1150 680 60
8 1200 1565 1560 1405 1100 645 40
9 1100 1470 1480 1340 1050 610 20
VC 10 1000 1375 1400 1275 1000 575 0
11 900 1280 1320 1210 950 540 -20
12 800 1185 1240 1145 900 505 -40
13 700 1090 1160 1080 850 470 -60

• If the estimate of Variable Costs (10 per room
  per day) is inaccurate, how does it affect the
  solution?

14
Sensitivity to Variable Costs
15
Extend the Analysis

• How would this entire analysis change if you
  were analyzing a larger hotel like the Marriott
  instead of a motel?