73 220 Lecture02
73 220 Lecture02
73 220 Lecture02
73220
Lecture 02
Agenda
● Review of last class
– Course outline, introduction to QDM
● Break-even analysis
– Graphical, algebraic, and Excel
● Next class
Example: Ponderosa Development
Corp.
● Question:
What is the monthly profit if 12 houses
per
month are built and sold?
● Answer:
1000
115,000x
800
600
Total Cost =
400
40,000 +
200 105,000x
Break-Even Point = 4 Houses
0
0 1 2 3 4 5 6 7 8 9 10
Number of Houses Sold (x)
1
Using Excel for Breakeven Analysis
1
Example: Ponderosa Development
Corp.
● Formula Spreadsheet
A B
1 PROBLEM DATA
2 Fixed Cost $40,000
3 Variable Cost Per Unit $105,000
4 Selling Price Per Unit $115,000
5 MODEL
6 Sales Volume
7 Total Revenue =B4*B6
8 Total Cost =B2+B3*B6
9 Total Profit (Loss) =B7-B8
1
Example: Ponderosa Development
Corp.
● Question
What is the monthly profit if 12 houses
per month are built and sold?
1
Example: Ponderosa
Development Corp.
● Spreadsheet Solution
A B
1 PROBLEM DATA
2 Fixed Cost $40,000
3 Variable Cost Per Unit $105,000
4 Selling Price Per Unit $115,000
5 MODEL
6 Sales Volume 12
7 Total Revenue $1,380,000
8 Total Cost $1,300,000
9 Total Profit (Loss) $80,000
1
Example: Ponderosa Development
Corp.
● Question:
What is the breakeven point for monthly sales of the
houses?
● Spreadsheet Solution:
– One way to determine the break-even point using a
spreadsheet is to use the Goal Seek tool.
– Microsoft Excel’s Goal Seek tool allows the user to
determine the value for an input cell that will cause the
output cell to equal some specified value.
– In our case, the goal is to set Total Profit to zero (i.e.,
breakeven point) by seeking an appropriate value for
Sales Volume.
1
Example: Ponderosa Development
Corp.
● Spreadsheet Solution: Goal Seek Approach
1
Example: Ponderosa Development
Corp.
● Spreadsheet Solution: Goal Seek Approach
Completed Goal Seek Dialog Box
1
Example: Ponderosa Development
Corp.
● Spreadsheet Solution: Goal Seek Approach
A B
1 PROBLEM DATA
2 Fixed Cost $40,000
3 Variable Cost Per Unit $105,000
4 Selling Price Per Unit $115,000
5 MODEL
6 Sales Volume 4
7 Total Revenue $460,000
8 Total Cost $460,000
9 Total Profit (Loss) $0
1
Range Name for Spreadsheet Model
1
Special Products Break-Even
Analysis
● The Special Products Company produces expensive and
unusual gifts.
● The latest new-product proposal is a limited edition
grandfather clock.
● Data:
– If they go ahead with this product, a fixed cost of $50,000 is
incurred.
– The variable cost is $400 per clock produced.
– Each clock sold would generate $900 in revenue.
– A sales forecast will be obtained.
2
Mathematical Model of the Problem
● Decision variable:
– Q = Number of grandfather clocks to produce
● Costs:
– Fixed Cost = $50,000 (if Q > 0)
– Variable Cost = $400 Q
– Total Cost =
» 0, if Q = 0
» $50,000 + $400 Q, if Q > 0
● Profit:
– Profit = Total revenue – Total cost
» Profit = 0, if Q = 0
» Profit = $900Q – ($50,000 + $400Q) = –$50,000 + $500Q, if Q > 0
2
Graphical Analysis
$
$200,000
$160,000
Revenue = $900 x
Profit
$120,000
Fixed cost Cost = $50,000 + $400 x
$80,000
Loss
$40,000
Breakeven point = 100 units
2
Spreadsheet Model
Data Results
Unit Revenue $900 Total Revenue $270,000 Range Name Cell
Fixed Cost $50,000 Total Fixed Cost $50,000
FixedCost C5
Marginal Cost $400 Total Variable Cost $120,000
Sales Forecast 300 Profit (Loss) $100,000 MarginalCost C6
ProductionQuantity
C9
Production Quantity 300 Profit F7
SalesForecast C7
Results TotalFixedCost F5
Total Revenue =UnitRevenue*MIN(SalesForecast,ProductionQuantity) TotalRevenue F4
Total Fixed Cost =IF(ProductionQuantity>0,FixedCost,0)
Total Variable Cost =MarginalCost*ProductionQuantity TotalVariableCostF6
Profit (Loss) =TotalRevenue-(TotalFixedCost+TotalVariableCost) UnitRevenue C4
2
Useful Excel Functions
● MIN (range or numerical values) gives the
minimum of several numerical values.
● MAX (range or numerical values) gives the
maximum of several numerical values.
● IF(a, b, c): a is a logical statement. If a is true, it
returns a value of b; otherwise, it returns a value
of c.
● Use Tools Goal Seek to find break-even point.
Note: Be cautious to analyze the solution to make
sure it makes sense. You may try your initial
production quantity of 300 to see what happens.
2
Next Class
● Do some break-even analysis problems in
Chapter 1.
● Read Chapter 2 – Linear Programming
● Bring to class
– Notes for next class