Robert Zimmer

1
Lecture 2

• Robert Zimmer
• Room 6, 25 St James

2
This course is about building models and making
decisions

• It is about organising information
• It is about being able to ask what-if questions
• It is about applying powerful mathematical models
  (I might try to teach you some maths when you
  arent looking but that is incidental)

3
Example of a decisionshould I have another beer?

• Organising Information
• How much money I have
• How much money a beer costs
• How drunk am I?
• Do I have to drive?
• How fat am I?
• How much do I like the people in the pub?
• How much do I like the people at home?

4
Another Question

• What is the most money I am prepared to pay for
  this drink? That is at what price does the
  pleasure of the drink become less than its price?

5
Some more questions

• What is the geometric shape of all the points at
  which the pleasure of the beer exactly matches
  the pain of the payment?
• How will my pleasure, my weight, and my mental
  state compare if instead of a beer I have chips?
• or do my Java coursework?

6

• Spreadsheet modeling is the process of entering
  the inputs and decision variables into a
  spreadsheet and then relating them appropriately,
  by means of formulas, to obtain the outputs.
• Once a model is created there are several
  directions in which to proceed.
• Sensitivity analysis to see how one or more
  outputs change as selected inputs or decision
  variables change.
• Finding the value of a decision variable that
  maximizes or minimizes a particular output.
• Create graphs to show graphically how certain
  parameters of the model are related.

7

• Good spreadsheet modeling practices are
  essential.
• Spreadsheet models should be designed with
  readability in mind.
• Several features that improve readability
  include
• A clear logical layout to the overall model
• Separation of different parts of a model
• Clear headings for different sections of the
  model
• Liberal use of range names
• Liberal use of formatting features
• Liberal use of cell comments
• Liberal use of text boxes for assumptions, lists
  or explanations

8
Example 2.1 Building a Model

• Randy Kitchell is a NCAA t-shirt vendor. The
  fixed cost of any order is 750, the variable
  cost is 6 per shirt.
• Randys selling price is 10 per shirt, until a
  week after the tournament when it will drop to 4
  apiece. The expected demand at full price is 1500
  shirts.
• He wants to build a spreadsheet model that will
  let him experiment with the uncertain demand and
  his order quantity.

9

• In this model the profit is calculated with the
  formula
• Profit Revenue Cost
• and the Cost 750 6B4

10
Revenue

• Case 1
• Demand outstrips order (B3 gt B4)
• In that case everything gets sold for 10 dollars
• Revenue is then simply 10B4
• (since B4 is the number ordered)

11
Revenue

• Case 2You have ordered too many.
• That is order (B3) is less than peak demand
• Then you can only sell B3 at 10 dollars and the
  rest (B4-B3) at 4 dollars
• Revenue 10B34(B4-B3)

12
Revenue Formula

• Revenue
• IF(B3gtB4,10B4,10B34(B4-B3))

13
Profit Formula

• Profit
• IF(B3gtB4,10B4,10B34(B4-B3))
• (750 6 B4)

14
Adding Flexibility

• We add flexibility by allowing more things to vary

15
(No Transcript)
16
Ex. 2.1(contd) - Building a Model

• The formula can be rewritten to be more
  flexible.-B3-B4B9IF(B8gtB9,10B8B6(B9-B8))
• It can be made more readable by using range
  names. The formula would then read-Fixed_order_c
  ost-Variable_costOrder IF(Demand gt Order,
  Selling_priceOrder, 10DemandSalvage_value
  (Order-Demand)

17
Ex. 2.1(contd) - Building a Model

• We might like to have profit broken down into
  various costs and revenues, rather one single
  profit cell. The profit formula would be
  -(B12B13)(B15B16).
• Range names could be used for these intermediate
  output cells, but it is probably more work than
  it is worth.
• Labels and/or color coding can help a lot with
  readability.

18
Ex. 2.1(contd) - Building a Model

• Data tables could be used to see how sensitive
  profit is to the inputs, the demand, and the
  order quantity, and charts to show any numerical
  results graphically.

19
Example 2.2 Cost Projections

• The company knows that wood prices and labor
  costs are likely to increase in the future, and
  it would like to project its costs of
  manufacturing the bookshelves into the future.
• The data can be found in Table 2.1.
• Build a spreadsheet model that allows the company
  to experiment with the growth rates in wood and
  labor costs so that a manager can see, both
  numerically and graphically, how the costs of the
  bookshelves will vary in the next few years.

20
Ex. 2.2(contd) - Planning the Model

• The reasoning behind the model is
  straightforward.
• First project the unit costs for wood and labor
  into the future. Then for any year, multiply the
  unit costs by the required numbers of board-feet
  and labor hours per bookshelf.
• Finally, add the wood ad labor costs to obtain
  the total cost of a bookshelf.

21
Ex. 2.2(contd) The Model
22
Ex. 2.2(contd) Developing the Model

• Develop the model with the following steps.
• Inputs Enter the inputs into the upper left
  corner of a worksheet. These can be referred to
  later with Excel formulas.
• Design output table You need to think ahead of
  time how you want to structure your outputs. The
  important point is that you should have some
  logical design in mind before diving in.
• Projected unit costs of wood It is important to
  have a strategy in mind before you enter the
  formulas. You should design your spreadsheet so
  that you can enter a single formula and then copy
  it whenever possible.

23
Ex. 2.2(contd) Developing the Model

• For example enter the formula B9 in cell B19
  and copy it to cell C19. Then enter the general
  formula B19(1B10) in cell B20 and copy it to
  the range B20C25.
• Projected unit labor costs To calculate
  projected hourly labor costs, enter the formula
  B13 in cell D19. Then enter the formula
  D19(1B14) in cell D20 and copy it down to
  column D.
• Projected bookshelf costs With careful use of
  absolute and relative addresses, enter a single
  formula for these costs for all years and for
  both types of wood. To do this, enter the formula
  B5B19B6D19 in cell E19 and copy it to the
  range E19F25.

24
Developing the Model -- continued

• Chart Highlight the range E19F25 and click on
  Excels Chart Wizard button. This leads you
  through a sequence of steps. You should
  experiment with the possibilities.
• The model can be used to answer any what-if
  questions Woodworks might want to ask.
• The model has been built in such a way that a
  manager can enter any desired values in the input
  cells, and all of the outputs , including the
  chart, will update automatically.
• Burying input numbers inside Excel formulas is
  bad practice.

25
2.4 Breakeven Analysis

• Many business problems require us to find the
  appropriate level of some activity.
• This might be the level that maximizes profit, or
  it might be the level that allows a company to
  break even no profit, no loss.

26
Example 2.3 - Breakeven Analysis

• The Great Threads Company 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.
• The average size of a customer order is 40, and
  the companys variable cost per order averages
  around 80 of the orders value.

27
Ex. 2.3(contd) - Breakeven Analysis

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

28
Ex. 2.3(contd) - Planning the Model

• A single bottom line output variable, in this
  case profit, is of most concern.
• The logic for converting inputs and the decision
  variable into outputs is quite straightforward.
  Then it must be investigated how the response
  rate affects the profit with a sensitivity
  analysis.

29
Ex. 2.3(contd) - Developing the Model

• To create this model, proceed through the
  following steps.
• Heading and range names Be cautious not to go
  overboard with range names.
• Enter input values Some of the values have been
  combined in the statement of the problem. To
  document this process, enter comments in a few
  cells. Inserting comments in cells is a great way
  to document your spreadsheet models without
  making it too cluttered.
• Model the responses Enter any reasonable value,
  such as 8, in the Respone_rate cell.
  Number_mailedResponse_rate in cell E5

30
Ex. 2.3(contd) - Developing the Model

• Model the revenues, costs and profits
• Enter the formula Number_of_responsesAverage_ord
  er in the in cell E8.
• Enter the formula Fixed_cost_of_printing,
  Variable_cost_of_printing_mailingNumber_mailed
  and Number_of_responsesVariable_cost_per_order
  in cells E9, E10, and E11.
• Enter the formula SUM(E9E11) in the cell E12,
  and enter the formula Total_revenue-Total_cost
  in the cell E13.

31
Ex. 2.3(contd) - Data Table

• A a one-way data table is formed to show how
  profit varies with the response rate.
• Data tables are called what-if tables. They
  illustrate what happens to selected outputs if
  selected inputs change.
• From the data table it can be seen that profit
  changes from negative to positive when the
  response rate is somewhere between 5 and 6.
• This could be found by trial and error, but it is
  easier to find with Excels Goal Seek tool.

32
Ex. 2.3(contd) - Goal Seek

• Goal seek is useful for solving a single equation
  in a single unknown.
• The unknown is called the changing cell because
  it is allowed to be changed to make the equation
  true.
• Select the Tools/Goal Seek menu item and fill in
  the resulting dialog box.
• If the response rate is 5.77, Great Threads
  breaks even.

33
Ex. 2.3(contd) - Limitations of the Model

• Question 3 asks whether the company should
  proceed with the mailing if the response rate is
  only 3.
• The apparent answer is no because profit is
  negative. This reasoning is taking the short-term
  view.
• To consider the long term impact of our decisions
  the model must incorporate the long term
  explicitly into the model. To do this a more
  complex model must be built.

34
Ex. 2.3(contd) - Limitations of the Model

• Question 4 asks about the impact of uncertainty
  in the model.
• 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.

35
2.6 Decisions Involving the Time Value of Money

• Cash flows are received at different points in
  time, and a company must determine a course of
  action that maximizes the value of cash flows.
• The later a dollar is received, the less valuable
  the dollar is. This is useful in making
  decisions.
• 1.00 X 1/(1r) now 1.00 a year from now
• The value 1/(1r) in the above equation is called
  the discount factor.

36

• The quantity on the left is called the present
  value of 1.00 received a year from now.
• If money can be invested at annual rate r
  compounded each year, then 1 received t years
  from now has the same value as 1/(1r)t dollars
  received today that is, the 1 is discounted by
  the discount factor raised to the t power.
• By multiplying a cash flow received t years from
  now by 1/(1r)t (its present value), then the
  total value of all cash flows over all years is
  called the net present value (NPV) of our cash
  flows.

37

• The rate r (usually called the discount rate)
  used by major corporations generally comes from
  some version of the capital asset pricing model.
• The discount factor is 1 divided by 1 plus the
  discount rate.
• The NPV is the sum of all discounted cash flows.