Transcript No Slide Title

Chapter 1- Introduction to Management Science
•
•
•
•
•
The Management Science Approach to Problem Solving
Model Construction: Production
Management Science Modeling Techniques
Breakeven Analysis and Excel Model Example
Indifference Point Analysis
1 - Chap 01
Applications of Management Science in Business:
Opening a Fairwood Fast Food Restaurant
Business questions:
1. Is it worth doing? (Break-even analysis,
Forecasting)
2. What seating capacity should we build?
(Decision analysis)
3. How much food material to prepare?
(Forecasting)
4. How to schedule the staff to ensure a certain
customer service level?
(Linear programming, Waiting line management)
2 - Chap 01
The Management Science Approach
• Management science uses an objective (logical, scientific and
mathematical) approach to solve management problems.
• It is used in a variety of organizations to solve many different
types of problems.
– Investment, resource allocation, production mix, marketing,
multi-period scheduling etc.
3 - Chap 01
The Management Science Process
The Management Science Process
4 - Chap 01
Steps in the Management Science Process
• Observation - Identification of a problem that exists in a
system or an organization.
• Definition of the Problem - Problem must be clearly and
consistently defined showing its boundaries and interaction
with the objectives of the organization.
• Model Construction - Development of the functional
relationships that describe the decision variables, objective
function and constraints of the problem.
• Model Solution - Model solved using management science
techniques.
• Model Implementation - Actual use of the model or its
solution.
5 - Chap 01
Example of Model Construction
Problem Definition
Information and Data:
- A bakery makes and sells birthday cakes (Cake A and Cake B)
- Cake A costs $100 to produce, Cake B costs $120
- Cake A sells for $200 while Cake B sells for $250
- Cakes A and B require 0.5 and 0.8 pounds of double cream to
make, respectively
- The bakery has 20 pounds of double cream for each day
Business problem: Assuming all cakes produced can be sold out,
determine the numbers of different cakes to produce to make the
most profit given the limited amount of double cream available.
6 - Chap 01
Example of Model Construction
Mathematical Model
Decision Variable:
x = number of Cake A to produce
y = number of Cake B to produce
Z = total profit
Model:
Z = $200x +$250y- $100x - $120y (objective function)
0.5x +0.8y <= 20 lb of double cream (resource constraint)
Parameters: $200, $100, 0.5 lb, 20 lbs (known values)
Formal specification of model:
maximize Z = $200x + $250y - $100x - $120y
subject to 0.5x +0.8y <= 20
x >= 0, y>= 0
7 - Chap 01
Management Science Modeling Techniques
8 - Chap 01
Characteristics of Modeling Techniques
• Linear mathematical programming: clear objective;
restrictions on resources and requirements; parameters
known with certainty.
• Probabilistic techniques: results contain uncertainty.
• Network techniques: model often formulated as diagram;
deterministic or probabilistic.
• Forecasting and inventory analysis techniques: probabilistic
and deterministic methods in demand forecasting and
inventory control.
• Other techniques: variety of deterministic and probabilistic
methods for specific types of problems.
9 - Chap 01
Break-Even Analysis
• Break-even analysis is an important type of cost-volume
analysis, which focuses on relationships between cost,
revenue, and volume of output.
• One purpose of break-even analysis is to estimate the income
of an organization under different operating conditions. It is a
key component of most business plans and is especially
important for starting-up companies seeking financing or
investors.
• Performing a break-even analysis is a simple way to determine
price levels and to estimate whether an expansion or cost
saving project makes good business sense.
10 - Chap 01
Break-Even Analysis
• The goal of a break-even analysis is to determine when sales
revenue equals total expenses; in simple terms, when a
business or operation "breaks even." The real value lies in
helping you determine the relationships between revenue,
fixed costs, and variable costs. Changing one variable changes
the results and allows you to model a variety of potential
scenarios and make better business decisions.
• You can use a break-even analysis to:
Make pricing decisions
Determine the feasibility of selling new products
Evaluate a project
11 - Chap 01
Break-Even Analysis
• Break-even analysis is used to determine the break-even point:
the number of units of a product to sell or produce (i.e. volume)
that will equate total revenue with total cost.
• The formula is simple:
Total Revenue = Fixed Costs + Total Variable Costs
12 - Chap 01
Break-Even Analysis
Model Components
Total cost (TC) - fixed cost plus total variable cost
cf + Qcv
Fixed cost (cf) - cost that remains constant regardless of number
of units produced. For example, rent and salaries are fixed costs;
a company will pay rent and salaries even if the company does
not produce any product.
Unit Variable cost (cv) - unit cost of product. For example, material
cost, shipment cost and sales commission.
Total variable cost (Qcv) - Cost that changes based on activity.
Increase or decrease when the company produces more or less
products. It is a function of production volume (Q) and unit
variable cost.
13 - Chap 01
Break-Even Analysis
Model Components
Total revenue (TR) - selling price per unit x sales volume
pQ where p is the selling price per unit
Profit(Z) - difference between total revenue Qp (p=price) and total
cost:
Z = pQ – (cf + Qcv)
14 - Chap 01
Break-Even Analysis
Computing the Break-Even Point
The break-even point is the volume at which total revenue
equals total cost and profit is zero:
Qb/e = cf/(p-cv)
15 - Chap 01
Example - Special Products Company
The Special Products Company produces expensive and unusual gifts
to be sold in stores that cater to affluent customers who already have
everything. The latest new-product proposal to management from the
company’s research department is a limited edition grandfather clock.
Management needs to decide whether to introduce this new product
and, if so, how many of these grandfather clocks to produce. Before
making this decision, a sales forecast will be obtained to estimate how
many clocks can be sold. Management wishes to make the decision that
will maximize the company’s profit. If the company goes ahead with this
product, a fixed cost of $50,000 would be incurred for setting up the
production facilities to produce this product. (Note that this cost would
not be incurred if management decided not to introduce the product
since the setup then would not be done.) In addition to this fixed cost,
there is a production cost that varies with the number of clocks
produced. The unit variable (marginal) cost is $400 per clock produced.
Each clock sold would generate a revenue of $900 for the company.
16 - Chap 01
Special Products Company
Q = Number of grandfather clocks to produce (Decision Variable)
cf = $50000 if Q > 0
(cf = 0 if Q = 0)
cv = $400 per unit
p = $900 per unit
Total variable cost = $400Q
Total cost = $50000 + $400Q
Total revenue = $900Q
Profit = Total revenue – Total cost
= $900Q – ($50000 + $400Q)
Qb/e = 100 units, break-even point
17 - Chap 01
Special Products Company
To cover fixed and variable costs and break even the company
will need to sell 100 clocks.
Sell more clocks and as long as the fixed cost doesn't increase,
each additional sale will generate an incremental gross profit of
$500.
On the other hand if the company sells less than 100 clocks the
company will not cover its fixed cost and will operate at a loss.
18 - Chap 01
$
Analysis of the Problem
$200,000
$160,000
Revenue = $ 900 x
Profit
$120,000
Cost = $50,000 + $ 400 x
Fixed cost
$80,000
Loss
$40,000
0
40
80
120
Break-even point = 100 units
160
200
x
19 - Chap 01
Special Products Company Spreadsheet
Total Revenue
Total Fixed Cost
Total Variable Cost
Profit (Loss)
Range Name
Cell
BreakEvenPoint
F9
FixedCost
C5
MarginalCost
C6
ProductionQuantity
C9
Profit
F7
SalesForecast
C7
TotalFixedCost
F5
TotalRevenue
F4
TotalVariableCost
F6
UnitRevenue
C4
Results
=UnitRevenue*MIN(SalesForecast,ProductionQuantity)
=IF(ProductionQuantity>0,FixedCost,0)
=MarginalCost*ProductionQuantity
=TotalRevenue-(TotalFixedCost+TotalVariableCost)
Break-Even Point =FixedCost/(UnitRevenue-MarginalCost)
20 - Chap 01
Sensitivity Analysis
A management science study usually devotes considerable time
to investigating what happens to the recommendations of the
model if any of the estimates turn out to considerably miss their
targets.
21 - Chap 01
Here is the impact on Qb/e of changing the fixed cost to $75,000.
22 - Chap 01
Here is the impact on Qb/e of changing the unit variable cost to
$300.
23 - Chap 01
Here is the impact on Qb/e of changing the price to $1,500.
24 - Chap 01
Example - Great Threads Company
Building an Excel Model
to Find a Break-even Point
25 - Chap 01
Background Information
• The Great Threads Company sells hand-knit sweaters. Great
Threads is planning to print a brochure of its products and
undertake a direct mail campaign.
• The cost of printing the brochure is $20,000 plus $0.10 a
catalog. The cost of mailing each catalog is $0.15. In addition,
the company will include direct reply envelopes in it’s
mailings. It incurs $0.20 in extra cost for each direct mail
envelope that is used by a respondent.
26 - Chap 01
Background Information
• The sales revenue of a customer order is $40, and the
company’s variable cost per order averages around 80% of the
order’s value.
• The company plans to mail 100,000 catalogs. It wants to
develop a spreadsheet model to answer the following
questions:
27 - Chap 01
Background Information
1. How does a change in the response rate affect profit?
2. For what response rate does a company break even?
3. If the company estimates a response rate of 3%, should it
proceed with the mailing?
4. How does the presence of uncertainty affect the usefulness of
the model?
28 - Chap 01
Parameters
Mailing:
• Fixed cost of printing = $20,000
• Variable costs:
Printing = $0.1
Mailing, buying names = $0.15
• Number of brochures mailed = 100,000
Order
• Unit revenue = $40
• Variable cost (% of order) = 80%
• Variable cost of envelopes = $0.2
29 - Chap 01
Excel Model of GREATTHREADS
30 - Chap 01
Excel Model of GREATTHREADS
• Note the clear layout of the model
• The input cells are outlined and shaded and separated from the
outputs.
• There are boldfaced headings, several headings are indented.
• Numbers are formatted appropriately.
• Text boxes to the right spell out all the range names used.
31 - Chap 01
Creating the Excel Model
To create this model, proceed through the following steps.
– Enter heading and range names
• Obviously we have a lot of cells, more than you might
want to enter, but you will see their value when we start
entering formulas.
– Enter input values
• The values in the shaded cells are all given in the
statement of the problem. Enter these values and format
them appropriately.
32 - Chap 01
Creating the Excel Model
– Model the responses
• We have not specified the response rate of the mailing,
so enter any reasonable values such as 8% in the
ResponseRate cell – we will perform sensitivity on this
value later on – and enter the formula
=NumMailed*ResponseRate in the NumResponse cell.
33 - Chap 01
Creating the Excel Model
– Model the total revenue, costs and profit.
• Enter the formula=NumResponses*AvgOrder in the Total
Revenue cell.
• Enter the formula=FCostPrinting,
=SUM(VCostMailing)*NumMailed and
=NumResponses*(AvgOrder*VCostOrderPct+
VcostEnvelopes) in the Cost cells (E10, E11, E12).
• Enter the formula=SUM(Costs) in the TotalCost cell,and enter
the formula=Total Revenue-TotalCost in the profit cell.
34 - Chap 01
Answering the Questions
• Now that a basic model has been created, we can answer the
questions posed by the company.
• For question 1, we form a data table to show how profit varies
with the response rate.
35 - Chap 01
Creating a Data Table
• First, enter a sequence of trial values of the response rate in
column A, and
– enter a “link” to Total Revenue in cell B20 with the formula
=E8
– enter a “link” to Total cost in cell C20 with the formula =E13
– enter a “link” to Profit in cell D20 with the formula =E14
• Finally, highlight the entire table range, A20:D30, and select the
Data/What-If Analysis Table menu item to bring up the dialog
box shown here.
Enter E4
36 - Chap 01
Creating a Data Table
• It should be filled in as shown to indicate that the only input
ResponseRate, is listed along a column.
• When you click OK, Excel substitutes each response rate value
in column A into the ResponseRate cell, recalculates the total
revenue, total cost and profit, and reports them in the data
table.
37 - Chap 01
Scatter Plot
• For a final touch, we have created a scatterplot (or in Excel’s
terminology X-Y chart) of the values in the data table.
$450,000.00
TR
$400,000.00
TC
$350,000.00
$300,000.00
$250,000.00
$200,000.00
$150,000.00
$100,000.00
Profit
$50,000.00
$0.00
-$50,000.00
0%
2%
4%
6%
8%
10%
12%
-$100,000.00
Response Rate
38 - Chap 01
Answering the Questions
• Clearly, profit increases in a linear manner as response rate
varies. More specifically, a 1% increase in the response rate
always increases profit by $7800.
• Here is the reasoning. Each 1% in response rate results in
100,000*0.01=1000 more orders. Each order yields an average
revenue of $40 but incurs a variable cost of $40*80%=$32 and a
$0.20 envelope cost. The net gain is $7.80 per order.
39 - Chap 01
Answering the Questions
• From the data table, we see that profit goes from negative to
positive when the response rate is somewhere between 5%
and 6%.
40 - Chap 01
Answering the Questions
• Question 2 asks for the exact breakeven point. This could be
found with trial and error but is easy with Excel’s Goal Seek
tool. Goal Seek is useful for solving a single equation in a
single unknown.
• Here the equation is Profit=0, and the single unknown is the
response rate.
41 - Chap 01
Answering the Questions
• In Excel terminology, the unknown is called the changing cell
because we are allowed to change it to make the equation true.
• To implement Goal Seek, select Data/What-If Analysis/Goal
Seek menu item and fill in the resulting dialog box as shown
below.
42 - Chap 01
Answering the Questions
• After clicking on OK, the ResponseRate and Profit cells have
values 5.77% and $0. In words, if the response rate is 5.77%
Great Threads breaks even. If the response rate is greater than
5.77%, the company makes money; otherwise, it loses money.
43 - Chap 01
Answering the Questions
• Question 3 asks if the company should proceed with the
mailing if the response rate is only 3%. From the data table, the
apparent answer is “no” because profit is negative, a loss.
However, like many business companies, we are taking the
short term view with this reasoning.
44 - Chap 01
Answering the Questions
• We should realize that many customers who respond to direct
mail will reorder in the future. The company makes $7.80 per
order. If each of the respondents ordered two or more times,
say, the company would earn 3000*$7.80*2=$46,800 more than
appears in the model, and profit would then be positive.
45 - Chap 01
Answering the Questions
• The moral is that we must look at long-term impact of our
decisions. However, if we want to incorporate the long term
explicitly into the model, we must build a more complex model.
46 - Chap 01
Answering the Questions
• Finally, question 4 asks about the impact of uncertainty in the
model. We would be kidding ourselves to think that all model
inputs are known with certainty.
• For example, the size of an order is not always $40 – it might
be, say, from $10 to $100. When there is a high degree of
uncertainty about model inputs, it makes little sense to talk
about the profit level or the breakeven response rate.
47 - Chap 01
Answering the Questions
• It makes more sense to talk about the probability that profit will
have a certain value or the probability that the company will
break even.
48 - Chap 01
Indifference Point Analysis
The indifference point analysis determines the point at which
there is no difference in profit (cost) between two alternative
methods. That is, at a particular point, the decision maker has no
preference for one option over another, they are equally
preferred.
Example: Site Selection
A firm will set-up a production line of a new product in Hong Kong
or in Shenzhen.
If the production line is set up in Hong Kong, the annual fixed
cost and unit variable cost would be $10,000,000 and $300
respectively.
If the production line is set up in Shenzhen, the annual fixed cost
and unit variable cost would be $8,000,000 and $400 respectively.
Determine the indifference annual demand volume (Q) of the
product for which both alternatives are equally good.
49 - Chap 01
Indifference Point Analysis
TC(HK) = $10,000,000 + $300 *Q
TC(SZ) = $8,000,000 + $400 * Q
At the indifference annual demand quantity,
TC(HK) = TC(SZ)
Implies
$10,000,000 + $300 *Q = $8,000,000 + $400 * Q
Solving the equation,
Q = 20,000
At this point, both options will give a total cost of $16,000,000
50 - Chap 01
Site
Fixed cost
Marginal cost
HK
Q
0
2500
5000
7500
10000
12500
15000
17500
20000
22500
25000
27500
30000
32500
35000
37500
40000
42500
45000
47500
50000
SZ
$10,000,000
$300
$8,000,000
$400
TC(HK)
$10,000,000
$10,750,000
$11,500,000
$12,250,000
$13,000,000
$13,750,000
$14,500,000
$15,250,000
$16,000,000
$16,750,000
$17,500,000
$18,250,000
$19,000,000
$19,750,000
$20,500,000
$21,250,000
$22,000,000
$22,750,000
$23,500,000
$24,250,000
$25,000,000
TC(SZ)
$8,000,000
$9,000,000
$10,000,000
$11,000,000
$12,000,000
$13,000,000
$14,000,000
$15,000,000
$16,000,000
$17,000,000
$18,000,000
$19,000,000
$20,000,000
$21,000,000
$22,000,000
$23,000,000
$24,000,000
$25,000,000
$26,000,000
$27,000,000
$28,000,000
51 - Chap 01
General Conclusion
• If Q < 20000, set up the production plant in Shenzhen.
• If Q = 20000, it is indifferent to set up the production plant in
either site.
• If Q > 20000, set up the production plant in Hong Kong
52 - Chap 01
53 - Chap 01
Please
login to the English Window and
download the file Breakeven_student.xls
from Moodle.
54 - Chap 01