Transcript 2_5

Example 2.5
Decisions Involving the Time
Value of Money
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.
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.
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%
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?
The Model

The model of Acron’s 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.
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
=Capacity*UnitCapCost then enter the depreciation over
the first 5 years by entering the formula
=BuildingCost*DepredRate in cell B24 and copying it
across to cell F24.
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*(1+DemGrowth2_6) in cell C26
and copy it across to cell G26, enter the formula
=G26*(1+DemGrowth7_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*(1+InflRate) in cell C29
and copy it to the range C29:U31.
The Model -- continued
– Revenues and costs. The revenues and variable costs
depend on the number of units sold, so enter the formula
=B$27*B29 in cell B33 and copy it to the range B33:U34.
Then to calculate the maintenance cost, enter the formula
=Capacity*B31 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.
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(B37<0,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 =B38+B24-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/(1+r)1, the
value in cell C39 should be multiplied by 1/(1+r)2, and so on,
and these quantities should be summed to obtain the NPV.
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.
Answering the Questions

We now turn to Acron’s 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.
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
Optimal Capacity Level Versus
Discount Rate
Answering the Questions -continued

For each discount rate, we locate the maximum NPV
and corresponding capacity level and record them in
rows 85 and 86.

The chart is based on the values in row 86.

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.
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.
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 Niagara’s profitability. We could also
realize that Acron’s pricing policy is not set in stone
and the price it charges will influence the likelihood
that competition will enter the market.
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.