Breakeven Analysis at Great Threads

1
Example 2.3

• Breakeven Analysis at Great Threads

2
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 its mailings. It
  incurs 0.20 in extra cost for each direct mail
  envelope that is used by a respondent.

3
Background Information -- continued

• The average size of a customer order is 40, and
  the companys variable cost per order averages
  around 80 of the orders value.
• The company plans to mail 100,000 catalogs. It
  wants to develop a spreadsheet model to answer
  the following questions
• How does a change in the response rate affect
  profit?
• For what response rate does a company break even?
• If the company estimates a response rate of 3,
  should it proceed with the mailing?
• How does the presence of uncertainty affect the
  usefulness of the model?

4
BreakevenAnalysis.xls

• This file contains the completed model.
• The key variables appear below.

5
BreakevenAnalysis.xls -- continued

• 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.

6
Developing the Model

• To create this model, proceed through the
  following steps.
• 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.
• Values of input variables and the decision
  variable. The values in the shaded cells are all
  given in the statement of the problem. Enter
  these values and format them appropriately.

7
Developing the Model -- continued

• Model the responses. Enter any reasonable values
  such as 8 in the Response_rate cell you will
  perform sensitivity on this value later on and
  enter the formula Number_mailedResponse_rate in
  cell E5.
• Model the revenues, costs and profits. Enter the
  formula Number_of_responsesAverage_order in
  cell E8.Enter the formula Fixed_cost_printing,
  Variable_cost_of_printing_mailingNumber_mailed
  and Number_of_responsesVariable_cost_per_order
  in the Cost cells (E9, E10, E11).Enter the
  formula SUM(E9E11) in the cell E12, and enter
  the formula Total_revenue-Total_cost in cell E13.

8
Developing the Model -- continued

• 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. The table
  is shown here.

9
Developing the Model -- continued

• First, enter a sequence of trial values of the
  response rate in column A, and enter a link to
  profit in cell B17 with the formula Profit.
• Finally, highlight the entire table range,
  A17B27, and select the Data/Table menu item to
  bring up the dialog box shown here.

10
Developing the Model -- continued

• It should be filled in as shown to indicate that
  the only input Response_rate, is listed along a
  column.
• When you click OK, Excel substitutes each
  response rate value in column A in to the
  Response_rate cell,, recalculates the profit, and
  reports it in the data table.
• For a final touch, we have created a scatterplot
  (or in Excels terminology X-Y chart) of the
  values in the data table.

11
Developing the Model -- continued

• Clearly, profit increases in a linear manner as
  response rate varies. More specifically, a 1
  increase in the response rate always increased
  profit by 7800.
• Here is the reasoning. Each 1 in response rate
  results in 100,000 x 0.011000 more orders. Each
  order yields an average revenue of 40 but incurs
  a variable cost of 40 x 80 32 and a 0.20
  envelope cost. The net gain is 7.80 per order,
  or 7800 for 1000 orders.

12
Using Goal Seek

• From the data table, we see that profit goes from
  negative to positive when the response rate is
  somewhere between 5 and 6.
• Question 2 asks for the exact breakeven point.
  This could be found with trial and error but is
  easy with Excels Goal Seek tool. Goal Seek is
  useful for solving a single equation in a single
  unknown.
• Here the equation is Profit0, and the single
  unknown is the response rate.

13
Using Goal Seek -- continued

• 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 Tools/Goal Seek
  menu item and fill in the resulting dialog box as
  shown below.

14
Using Goal Seek -- continued

• After clicking on OK, the Response_rate 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 then
  5.77, the company makes money otherwise, it
  loses money.
• 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 U.S. companies, we are taking the short term
  view with this reasoning.

15
Answering the Questions --continued

• 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 x 7.80 X 2 46,800
  more than appears in the model, and profit would
  then be positive.
• 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.

16
Using Goal Seek -- continued

• 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.
• 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.