Basic Profit Models

1
Basic Profit Models
Chapter 3 Part 1 Influence Diagram
2
In building spreadsheets for deterministic
models, we will look at

• ways to translate the black box representation
  into a spreadsheet model.

• recommendations for good spreadsheet model
  design and layout

• suggestions for documenting your models

• useful features of Excel for modeling and
  analysis

3
Example 1 Simon Pie
The Pies are then processed and sold to local
grocery stores in order to generate a profit.
Follow the three steps of model building.
4
profit
The next step is to develop the relationships
inside
the black box. A good way to approach this is to
create an Influence Diagram.
5
To create an Influence Diagram
6
performance measure variable
Profit
Start here
7
Profit
Now, further decompose each of these intermediate
variables into more related intermediate
variables ...
8
Profit
Revenue
9
Based on the previous Influence Diagram, create
the equations relating the variables to be
specified in the spreadsheet.
10
Profit
Total Cost
Revenue
Profit Revenue Total Cost
11
Profit
Revenue
Revenue Pie Price Pies Demanded
Pies Demanded
Pie Price
12
Profit
Total Cost
Processing Cost
Ingredient Cost
Total Cost Processing Cost Ingredients Cost
Fixed Cost
Fixed Cost
13
Profit
Total Cost
Processing Cost
Processing Cost Pies Demanded Unit Pie
Processing Cost
Pies Demanded
Unit Pie Processing Cost
14
Profit
Total Cost
Ingredients Cost Qty Filling Unit Cost
Filling Qty Dough Unit Cost Dough
Ingredient Cost
Required Ingredient Quantities
Unit Cost Filling
Unit Cost Dough
15
Simons Initial Model Input Values
16
Chapter 3Part 2Break-Even and Cross-Over
Analysis

• MGS 3100

17
Background

• The Generalized Profit Model
• A decision-maker will break-even when profit is
  zero.
• Set the generalized profit model equal to zero,
  and then solve for the quantity (Q).
• For simplicity, assume that the quantity produced
  is equal to the quantity sold. This assumption
  will be relaxed in the module on decision
  analysis.

18
Basic Relationships

• Profit (p) Revenue (R) - Cost (C)
• Revenue (R) Selling price (SP) x Quantity (Q)
• Cost (C) Variable cost (VC) x Quantity (Q)
  Fixed Cost (FC)
• Remember quantity produced quantity sold

19
Basic Relationships cont

• By substitution
• p (SP x Q) ((VC x Q) FC)
• p SPQ - VCQ FC
• p (SP-VC)Q - FC

Notice sign reversal when parentheses are removed!
Just a bit of algebraic reorganization
20
Contribution Margin

• If Contribution Margin (CM) SP-VC, then by
  substitution
• p CMQ FC
• In case you want to figure the quantity at
  break-even, you just need to rearrange

21
Break-Even Quantity

• p CMQ FC
• p FC CMQ
• (p FC)/CM (CMQ)/CM
• (p FC)/CM Q
• Q (p FC)/CM
• In the case of break-even, where p 0, the
  formula boils down to
• Q FC/CM

22
Quantity and Profit Example

• Again, Q (FC p)/CM
• If fixed cost is 150,000 per year, selling price
  per unit (SP) is 400, and variable cost per unit
  (VC) is 250, what quantity (Q) will produce a
  profit of 300,000?
• Q (150,000300,000)/(400-250)
• Q 450,000/150
• Q 3000

23
Cross-Over Point

• The cross-over point (or indifference point) is
  found when we are indifferent between two plans.
  
• In other words, the quantity when profit is the
  same for each of two plans.

24
Cross-Over Point, cont

• To find the cross-over point for Plan A and B,
  set the profit formulas for each plan equal to
  each other
• pplanA pplanB, so
• (CMQ FC) planA (CMQ FC)planB
• QAtoB (FCA - FCB)/(CMA CMB)

25
Cross-Over Point, cont

• So all you need are the fixed costs and
  contribution margins (selling price and variable
  cost) to solve.
• For example, here are three plans

26
Cross-Over Point, cont
What is the profit at each of these points?
Cross-Over Points A to B B to
C QCO (150,000-450,000)/(150-250) (450,000-2,850,
000)/(250-300) 3000 units 48,000 units
27
Calculating Profit at the Cross-Over

• After calculating cross-over, we have a quantity
  that can be plugged back into the formula to find
  profit at the cross-over point

pB CMBQ - FCB 250(48,000) - 450,000
11,550,000, or pC 300(48,000) -
2,850,000 11,550,000
pA CMAQ FCA 150(3000) - 150,000
300,000, or pB 250(3000) - 450,000
300,000