Transcript Chapter 2 Describing Data: Graphs and Tables

Spreadsheet Models - DSS
Basic Profit Models
What-if, Sensitivity Analysis
Lecture Objectives
You should be able to :
1. Analyze a business situation and draw an
influence diagram.
2. Build basic profitability models on a
spreadsheet.
3. Perform what-if, sensitivity analyses.
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.
Influence Diagram
Profit
Revenue
Price/Room
Number of
Rooms Rented
Cost
Variable Cost
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?
Fixed Cost
VC/Room
Breakeven Analysis
Price
FC
50
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
Breakeven Point
Breakeven Analysis
Dollars
3000
2500
Revenue
2000
Tot Cost
1500
1000
500
0
0
5
10
15
20
25
30
35
Number of Rooms Rented
40
45
50
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?
Crossover Analysis –
Point of Indifference
Case 1
Price
FC
VC/unit
Case 2
50
70
1000
1800
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
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
Case 2
Rooms
Crossover Analysis
Crossover Analysis
5000
4000
$ Profit
3000
2000
Profit 1
1000
Profit 2
0
-1000 0
10
20
30
40
50
60
-2000
-3000
# Rooms
70
80
90 100
Pricing Analysis –
Demand Function
If the demand for rooms depends on the price as follows:
Quantity Demanded = 200 - 3*price,
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.
1. Determine the Goal.
2. How would Sally get such a demand equation for her
business?
3. Determine the best price to help her reach her goal.
Price and Profit
What is the best price?
Pricing Strategy Example
Max Rooms
FC
VC/unit
Price
100
1000
Demand = 200 - 3*p
10
Intercept
Slope
200
3
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
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
-2000
-1000
0
1000
1400
1000
0
-1000
-1000
Rev
profit
Profit Vs. Price
Motel Pricing Analysis
2000
Profit/Day ($)
1500
1000
500
0
-500 0
10
20
30
40
50
-1000
-1500
-2000
-2500
Price/Room ($)
60
70
80
Sensitivity Analysis
Pricing Strategy Example
Price
FC
If the estimate of Variable
Costs ($10 per room per
day) is inaccurate, how does
it affect the solution?
50
1000
VC/unit
10
Rooms
50
Rev
2500
FC
1000
VC
500
The table below shows profits at different prices and variable costs
Tot Cost
1500
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
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
VC
Prices per room
Sensitivity to Variable Costs
Sensitivity Analysis
2000
1500
VC = 7
Profit $
VC = 8
1000
VC = 9
VC = 10
500
VC = 11
VC = 12
VC = 13
0
20
24
28
32
36
40
-500
Price/Room
44
48
52
Extend the Analysis
How would this entire analysis change if you
were analyzing a larger hotel like the Marriott
instead of a motel?