A Financial Planning Model

1
Example 12.6

• A Financial Planning Model

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

3
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 years 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.

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

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

6
GFAUTO.XLS

• The simulation model can be found in this file
  and appears on the next slide.

7
(No Transcript)
8
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.

9
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 Dem1MeanProdFactorDem1StDev
  in cell B28. For other years, the expected demand
  is the previous years actual demand, and this is
  used to calculate the production quantity.
  Therefore for year 2, enter the formula
  B29ProdFactorDem1StDev 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
  formulaRISKNORMAL(29,DemStdev), and then copy
  it to the rest of row 29 to generate demands for
  the other years.

10
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 B30C27 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 B31C27 and copy this
  across to the rest of row 31.

11
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
  B28B30 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 formulaIF(B28ltB29,B31B28,B
  31(B29(1-Discount)(B28-B29)))and copy it to
  the rest of row 34.

12
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

13
Using _at_Risk

• Now that the spreadsheet is setup we can use the
  _at_Risk toolbar to run the simulation.
• We set the number of iterations to 1000 and the
  number of simulations to 3.
• After running _at_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.

14
_at_Risk Results

• Here is the summary results and simulations
  statistics.

15
_at_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. Its wide spread indicates the
  large amount of uncertainty about the 10-year NPV
  for this car.

16
_at_Risk Results -- continued
17
_at_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.