Ordering NCAA TShirts

1
Example 2.1

• Ordering NCAA T-Shirts

2
Background Information

• 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 is thinking about ordering 1400 t-shirts, but
  he wants to build a spreadsheet model that will
  let him experiment with the uncertain demand and
  his order quantity.

3
Building a Model

• The logic behind the model is simple. An Excel IF
  function will be used.

• In this base model the profit is calculated with
  the formula-750-6B4IF(B3gtB4,10B4,10B34(B4-
  B3))
• This model is entirely correct, but it is not
  very readable or flexible.

4
Building a Model -- continued

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

5
Building a Model -- continued

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

6
Building a Model -- continued

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