Chapter 2 Describing Data: Graphs and Tables

Download Report

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?