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).
$10 per day Variable cost per room is
(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 Cost Price/Room Number of Rooms Rented 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 VC/unit Rooms Rev FC VC Tot Cost Profit 50 1000 10 5
250 10 500 1000 1000 50 1050
-800
100 1100
-600 15
750 1000 150 1150
-400
20 1000 1000 200 1200
-200 25
1250 1000 250 1250
0
30 1500 1000 300 1300
200 35
1750 1000 350 1350
400
40 2000 1000 400 1400
600 45
2250 1000 450 1450
800
50 2500 1000 500 1500
1000
Breakeven Point
Breakeven Analysis
3000 2500 2000 1500 1000 500 0 0 Revenue Tot Cost 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 1 Rooms Rev FC VC Tot Cost Profit Case 2 Rooms Rev FC VC Tot Cost Profit 50 1000 10 20
1000 1000 200 1200
-200 20
1400 1800 200 2000
-600 40
2000 1000 400 1400
600 40
2800 1800 400 2200
600 Case 2 70 1800 10 60
3000 1000 600 1600
1400 60
4200 1800 600 2400
1800 80
4000 1000 800 1800
2200 80
5600 1800 800 2600
3000 100
5000 1000 1000 2000
3000 100
7000 1800 1000 2800
4200
Crossover Analysis
Crossover Analysis
5000 4000 3000 2000 1000 0 -1000 0 -2000 -3000 10 20 30 40 50 60 70 80 90 100
# Rooms
Profit 1 Profit 2
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
Pricing Strategy Example
Max Rooms FC VC/unit 100 1000 10
What is the best price?
Demand = 200 - 3*p Intercept 200 Slope 3 Price
0 10 20
Rooms Demanded Rooms Rented Rev FC VC Tot Cost profit
200 100 170 100 140 100 0 1000 1000 2000
-2000
1000 1000 1000 2000
-1000
2000 1000 1000 2000
0
30 40 50 60 70 80 110 100 80 80 50 50 20 20 -10 0 -40 0 3000 1000 1000 2000
1000
3200 1000 800 1800
1400
2500 1000 500 1500
1000
1200 1000 200 1200
0
0 1000 0 1000
-1000
0 1000 0 1000
-1000
Profit Vs. Price
Motel Pricing Analysis
2000 1500 1000 500 0 -500
0
-1000 -1500 -2000 -2500
10 20 30 40 50 Price/Room ($) 60 70 80
Sensitivity Analysis
Pricing Strategy Example
Price FC VC/unit
Rooms
Rev FC VC Tot Cost
50 1000 10
50
2500 1000 500 1500
profit 1000 7 30
1300
VC 8 9 10 11 12 13
1200 1100
1000
900 800 700
35 1660 1565
1470
1375
1280 1185 1090
If the estimate of Variable Costs ($10 per room per day) is inaccurate, how does it affect the solution?
The table below shows profits at different prices and variable costs 40
1640 1560
1480 1400 1320 1240 1160 45
1470
Prices per room 50
1150
55
680 1405 1340
1275
1210 1145 1080 1100 1050
1000
950 900 850 645 610
575
540 505 470
60
60 40 20
0
-20 -40 -60
Sensitivity to Variable Costs
Sensitivity Analysis
2000 1500 1000 500 0 20 24 28 32 36 40 44 48 52 -500
Price/Room
VC = 7 VC = 8 VC = 9 VC = 10 VC = 11 VC = 12 VC = 13
Extend the Analysis
How would this entire analysis change if you were analyzing a larger hotel like the Marriott instead of a motel?