Decisions Involving the Time Value of Money

1
Example 2.5

• Decisions Involving the Time Value of Money

2
Background Information

• Acron is a large drug company. One of its new
  drugs, Niagara, is coming to market and Acron
  needs to determine how much annual production
  capacity to build for this drug.
• Government regulations make it difficult to add
  capacity at a later date, so Acron must determine
  a capacity recommendation before the drug comes
  to market.
• The drug will be sold for 20 years before it
  comes off patent. After the 20 years, the rights
  to produce the drug are virtually worthless.

3
Background Information -- continued

• Acron has made the following assumptions
• Year 1 demand will be 10,000 units
• During years 2-6, annual growth of demand will be
  15.
• During years 7-20, annual growth of demand will
  be 5.
• It costs 6, payable at the end of year 1, to
  build each unit of annual production capacity.
  The cost of the building capacity is deprecisted
  on a straight-line 5-year basis.
• During year 1, Niagara will sell for 8 per unit
  and will incur a variable cost 5 to produce.

4
Background Information -- continued

• The cost of maintaining a unit capacity during
  year 1 is 1.
• The sales price, unit variable cost, and unit
  capacity maintenance cost will increase by 5 per
  year.
• Profits are taxed at 40.
• All cash flows are assumed to occur at the end of
  each year, and the corporate discount rate is 10

5
Background Information -- continued

• Acron wants to develop a spreadsheet model of its
  20-year cash flows. Then it wants to answer the
  following questions.
• What capacity level should be chosen?
• How does a change in the discount rate affect the
  optimal capacity level?
• How realistic is the model?

6
The Model

• The model of Acrons cash flows appears on the
  next slide.
• As with many financial spreadsheet models that
  extend over a multi-year period, this model is
  not as bad as it looks.
• Usually, we enter typical formulas in the first
  year or two and then copy this logic across to
  all years.

7
(No Transcript)
8
The Model -- continued

• To create the model, enter the given data in the
  input section, enter any trial value for the
  capacity decision in the Capacity cell, name
  ranges, and complete the following steps.
• Building cost and depreciation. Enter the toal
  building cost in the BuildingCost cell with the
  formula CapacityUnitCapCost then enter the
  depreciation over the first 5 years by entering
  the formula BuildingCostDeprecRate in cell B24
  and copying it across to cell F24.

9
The Model -- continued

• Demand and units sold. The demand is governed by
  the percentage rate increases assumed by Acron.
  However, the number of units that can be sold is
  limited to building capacity. Therefore we enter
  the formula Demand1 in cell B26 and the formula
  B26(1DemGrowth2_6) in cell C26 and copy it
  across to cell G26, enter the formula
  G26(1DemGrowth7_20) in cell H26 and copy it
  across to U26. Then enter the formula
  MIN(B26,Capacity) in cell B27 and copy it across
  to cell U27.
• Unit prices and costs. The unit selling price and
  unit costs all grow by the same inflation factor.
  To calculate them for year 1, enter the formulas
  UnitPirice1, UnitVCost1, and UnitMaintCost1 in
  cells B29, B30 and B31. Then for all other years,
  enter the formula B29(1InflRate) in cell C29
  and copy it to the range C29U31.

10
The Model -- continued

• Revenues and costs. The revenues and variable
  costs depend on the number of units sold, so
  enter the formula B27B29 in cell B33 and copy
  it to the range B33U34. Then to calculate the
  maintenance cost, enter the formula CapacityB31
  in cell B35 and copy it across to cell U35.
• Pretax profits, after tax profits, and free cash
  flows. This part is a bit tricky, especially if
  you are not an accountant. For tax purposes,
  depreciation is deducted from the difference
  between revenue and (nonbuilding) costs.
  Therefore, to obtain pretax profit, the amount on
  which taxes are based, enter the formula
  B33-B34-B35-B24 in cell B37 and copy it across.

11
The Model -- continued

• Next, subtract taxes to obtain after-tax profits,
  but keep in mind that there is no tax if there is
  a loss. This implies the formula
  IF(B37lt0,B37,B37(1-TaxRate)) in cell B38, which
  can be copied across. Finally the free cash flow,
  the real profit after taxes, is found by adding
  back the depreciation but subtracting the
  building cost in year 1. To obtain this enter the
  formula B38B24-B23 in cell B39 and copy it
  across.
• Net present value. The NPV is based on the
  sequence of cash flows in row 39. From our
  general discussion of NPV, the value in cell B39
  should be multiplied by 1/(1r)1, the value in
  cell C39 should be multiplied by 1/(1r)2, and so
  on, and these quantities should be summed to
  obtain the NPV.

12
The Model -- continued

• Fortunately Excel has a built in NPV function to
  accomplish this calcualtion. To use it enter the
  formula NPV(DiscRate,FreeCashFlow) in the NPV
  cell This NPV function takes two arguments the
  discount rate and a range of cash flows.

13
Answering the Questions

• We now turn to Acrons first question How much
  capacity should it build?
• As usual, it is useful to create the data table
  and corresponding chart shown on the next slide.
• These show how NPV varies for different levels of
  capacity. More specifically, it indicates that
  Acron can maximize its NPV by using a capacity
  level of 21,000 units.

14
(No Transcript)
15
Answering the Questions -- continued

• Question 2 asks about the effect of the discount
  rate on optimal capacity.
• This is an important question for two reasons.
• First, it is often difficult for a company to
  determine the appropriate discount rate.
• Second, the NPV is typically quite sensitive to
  the discount rate.
• To answer the questions, we build the two-way
  data table and corresponding chart. The table and
  chart follow on the next slides

16
(No Transcript)
17
Optimal Capacity Level Versus Discount Rate
18
Answering the Questions -- continued

• For each discount rate, we locate the maximum NPV
  and corresponding capacity level and record them
  in rows 84 and 85.
• The chart is based on the values in row 85.
• As we see, larger discount rates typically result
  in lower NPVs because future cash flows are
  discounted more heavily.
• Beyond this, the chart shows how the optimal
  decreases as the discount rate increases.

19
Answering the Questions -- continued

• The reasoning is basically that bad things,
  especially building costs, tend to occur early,
  whereas good things tend to occur later on.
• A higher discount rate magnifies the bad things
  relative to the good things, so it induces the
  company to build less capacity.
• Finally, we discuss the realism of our model.

20
Answering the Questions -- continued

• Probably the major flaw is that we have ignored
  uncertainty. It is clear demand, future prices
  and future costs are highly uncertain.
• Of course, there are almost always ways to make
  any model more realistic at the cost of
  increased complexity.
• For example, we could model the impact of
  competition on Niagaras profitability. We could
  also realize that Acrons pricing policy is not
  set in stone and the price it charges will
  influence the likelihood that competition will
  enter the market.

21
Answering the Questions -- continued

• Finally, Acron could probably add capacity in the
  future if it is experiencing larger than expected
  demand.
• However, it is important to realize that future
  flexibility in decision making has an impact on
  the correct decision for today.