Transcript 12_6
Example 12.6
A Financial Planning Model
Background Information
General Ford (GF) Auto Corporation is trying to
determine what type of compact car to develop.
Each model is assumed to generate sales for 10
years.
GF has gathered information about the following
quantities through focus groups with the marketing
and engineering departments.
12.1 | 12.2 | 12.3 | 12.4 | 12.5 | 12.7 |12.8 | 12.9 | 12.10 |
12.11 | 12.12 | 12.13 | 12.14 | 12.15 | 12.16 | 12.17
Background Information -continued
– Fixed cost of developing car. This cost is assumed to be
normally distributed with a $2.3 billion mean and a standard
deviation of $0.5 billion.
– Variable production cost. This cost, which includes all
variable production costs required to build a single car, is
normally distributed for each model during year 1 with a
mean and standard deviation of $7800 and $600. Each year
after year 1 the variable production cost is the previous
year’s multiplied by an inflation factor. Each year this
inflation facto is assumed to be normally distributed with
mean 1.05 and standard deviation 0.015. All production
costs are assumed to occur at the ends of the respective
years.
12.1 | 12.2 | 12.3 | 12.4 | 12.5 | 12.7 |12.8 | 12.9 | 12.10 |
12.11 | 12.12 | 12.13 | 12.14 | 12.15 | 12.16 | 12.17
Background Information -continued
– Selling price. The price in year 1 is already set at $11,800.
After year 1 the price will increase by the same inflation
factor that drives production costs. Like production costs,
revenues from sales are assumed to occur at the ends of the
respective years.
– Demand. The demand for cars in year 1 is assumed to be
normally distributed with a mean of 100,000. The standard
deviation is 10,000. After year 1 the demand in the given
year is assumed to be normally distributed with mean equal
to the actual demand in the previous year and standard
deviation 10,000. An implication of this assumption is that
demands in successive years are not probabilistically
independent.
12.1 | 12.2 | 12.3 | 12.4 | 12.5 | 12.7 |12.8 | 12.9 | 12.10 |
12.11 | 12.12 | 12.13 | 12.14 | 12.15 | 12.16 | 12.17
Background Information -continued
– Production. In any particular year GF plans to base its production
policy on the probability distribution of demand for that year - before
the actual demand for that year is observed. If demand in any given
year is greater than production, then the excess demand is lost. If
production in any year is greater than demand, GF will sell the
excess cars at an end-of-year discount of 30%.
– Interest rate. GF plans to use a 10% interest rate to discount future
cash flows.
Given these assumptions, GF wants to develop a simulation
model that will evaluate its NPV (net present value) for this new
car over the 10-year time horizon.
12.1 | 12.2 | 12.3 | 12.4 | 12.5 | 12.7 |12.8 | 12.9 | 12.10 |
12.11 | 12.12 | 12.13 | 12.14 | 12.15 | 12.16 | 12.17
GFAUTO.XLS
The simulation model can be found in this file and
appears on the next slide.
12.1 | 12.2 | 12.3 | 12.4 | 12.5 | 12.7 |12.8 | 12.9 | 12.10 |
12.11 | 12.12 | 12.13 | 12.14 | 12.15 | 12.16 | 12.17
12.1 | 12.2 | 12.3 | 12.4 | 12.5 | 12.7 |12.8 | 12.9 | 12.10 |
12.11 | 12.12 | 12.13 | 12.14 | 12.15 | 12.16 | 12.17
Developing the Spreadsheet
Model
The model can be formed as follows.
– Inputs. Enter the various inputs in the shaded cell.
– Production multiplier. The only real decision GF has to
make is the multiplier k for its production level. To
experiment with several values of this multiplier, enter the
formula =RISKSIMTABLE({0.8, 1, 1.2}) in cell E20. Other (or
more) values could be tried here.
– Variable cost inflation factors. Rows 26-42 contain a
single 10-year simulation. The approach is to enter
appropriate formulas in column B and C for years 1 and 2,
then copy the year 2 formulas to the columns for the other
years, and finally calculate the values in rows 36, 39, 40 and
42. Begin by entering the variable production cost inflation
factor relating year 2 to year 1 in cell C27 with the formula
=RISKNORMAL(InflMean,InflStdev) and copying this to
the rest of row 27.
12.1 | 12.2 | 12.3 | 12.4 | 12.5 | 12.7 |12.8 | 12.9 | 12.10 |
12.11 | 12.12 | 12.13 | 12.14 | 12.15 | 12.16 | 12.17
Developing the Spreadsheet
Model -- continued
– Production quantities. The production quantity in year 1 is
based on the expected demand and the standard deviation
of demand in year 1, so we enter the formula
=Dem1Mean+ProdFactor*Dem1StDev in cell B28. For
other years, the expected demand is the previous year’s
actual demand, and this is used to calculate the production
quantity. Therefore for year 2, enter the formula
=B29+ProdFactor*Dem1StDev in cell B28 and copy it
across to the rest of the row 28.
– Demands. Generate a demand in year 1 in cell B29 with the
formula =RISKNORMAL(Dem1Mean,Dem1Stdev). As in
the previous step the expected demand for year 2 is the
actual demand for year 1. So generate demand for year 2 in
cell C29 with the formula
=RISKNORMAL(29,DemStdev), and then copy it to the rest
of row 29 to generate demands for the other years.
12.1 | 12.2 | 12.3 | 12.4 | 12.5 | 12.7 |12.8 | 12.9 | 12.10 |
12.11 | 12.12 | 12.13 | 12.14 | 12.15 | 12.16 | 12.17
Developing the Spreadsheet
Model -- continued
– Variable production costs. Generate the variable
production cost for year 1 in cell B30 with the formula
=RISKNORMAL(VC1Mean,VC1Stdev). Then use the
inflation factor in row 27 to generate the variable production
cost for year 2 in cell C30 with the formula =B30*C27 and
copy this across to the rest of row 30.
– Selling prices. Enter the (nonrandom) selling price for year
1 in cell B31 with the formula =Price1. Then generate the
price for year 2 in cell C31 with the formula =B31*C27 and
copy this across to the rest of row 31.
12.1 | 12.2 | 12.3 | 12.4 | 12.5 | 12.7 |12.8 | 12.9 | 12.10 |
12.11 | 12.12 | 12.13 | 12.14 | 12.15 | 12.16 | 12.17
Developing the Spreadsheet
Model -- continued
– Production costs. The production cost for any year is the
production quantity multiplied by the variable production
cost, so enter the formula =B28*B30 in cell B33 and copy it
to the rest of row 33.
– Revenues. The revenues in any year are calculated in one
of two possible ways. If demand is greater than production
quantity, then revenue is the sales price multiplied by the
production quantity. If demand is less than the production
quantity, then revenue is the sales price multiplied by the
demand, plus the discounted sales price multiplied by the
number of cars left over. Therefore, calculate the revenue for
year 1 in cell B34 with the formula
=IF(B28<B29,B31*B28,B31*(B29+(1-Discount)*(B28B29)))
and copy it to the rest of row 34.
12.1 | 12.2 | 12.3 | 12.4 | 12.5 | 12.7 |12.8 | 12.9 | 12.10 |
12.11 | 12.12 | 12.13 | 12.14 | 12.15 | 12.16 | 12.17
Developing the Spreadsheet
Model -- continued
– Fixed cost. Generate fixed cost of developing the car in cell
B36 with the formula
=RISKNORMAL(FCMean,FCStdev)*1000.
– NPVs. Calculate the NPV of all production costs (in millions
of dollars) in cell B39 with the formula
=NPV(IntRate,Costs)
Similarly, enter the formula =NPV(IntRate,Revenues) in cell
B40 for revenues.
– Total NPV. Finally calculate the total NPV in cell B42 with
the formula =RISKOUTPUT( )+B40-B36-B39
12.1 | 12.2 | 12.3 | 12.4 | 12.5 | 12.7 |12.8 | 12.9 | 12.10 |
12.11 | 12.12 | 12.13 | 12.14 | 12.15 | 12.16 | 12.17
Using @Risk
Now that the spreadsheet is setup we can use the
@Risk toolbar to run the simulation.
We set the number of iterations to 1000 and the
number of simulations to 3.
After running @Risk, we obtain the summary
measures for the total NPV shown on the next slide.
We see that the multiplier k definitely makes a
difference.
12.1 | 12.2 | 12.3 | 12.4 | 12.5 | 12.7 |12.8 | 12.9 | 12.10 |
12.11 | 12.12 | 12.13 | 12.14 | 12.15 | 12.16 | 12.17
@Risk Results
Here is the summary results and simulations
statistics.
12.1 | 12.2 | 12.3 | 12.4 | 12.5 | 12.7 |12.8 | 12.9 | 12.10 |
12.11 | 12.12 | 12.13 | 12.14 | 12.15 | 12.16 | 12.17
@Risk Results -- continued
Based on these results, GF might want to experiment
with even larger values of k.
Higher values of k mean larger production quantities.
This will result in more end-of-year discounted sales,
but it is evidently better than lost sales from
insufficient supply.
The corresponding histogram for k = 1.2 appears on
the next slide. It’s wide spread indicates the large
amount of uncertainty about the 10-year NPV for this
car.
12.1 | 12.2 | 12.3 | 12.4 | 12.5 | 12.7 |12.8 | 12.9 | 12.10 |
12.11 | 12.12 | 12.13 | 12.14 | 12.15 | 12.16 | 12.17
@Risk Results -- continued
12.1 | 12.2 | 12.3 | 12.4 | 12.5 | 12.7 |12.8 | 12.9 | 12.10 |
12.11 | 12.12 | 12.13 | 12.14 | 12.15 | 12.16 | 12.17
@Risk Results -- continued
GF could make a lot of money, or it could lose a lot.
We entered two representative values in the Left X
and the Right X boxes.
They show that the probability of a negative NPV is
slightly greater than 0.22 and the probability of NPV
being less than $10 million is 0.65.
We certainly would not discourage the company from
proceeding with this car, because there is a lot of
potential for profit, but it should also be aware of the
potential for loss.
12.1 | 12.2 | 12.3 | 12.4 | 12.5 | 12.7 |12.8 | 12.9 | 12.10 |
12.11 | 12.12 | 12.13 | 12.14 | 12.15 | 12.16 | 12.17