Projecting the Costs of Bookshelves at Woodworks

1
Example 2.2

• Projecting the Costs of Bookshelves at Woodworks

2
Background Information

• The Woodworks Company knows that wood prices and
  labor costs are likely to increase in the future,
  and it would like to project its costs of
  manufacturing the bookshelves into the future.
• Build a spreadsheet model that allows the company
  to experiment with the growth rates in wood and
  labor costs so that a manager can see, both
  numerically and graphically, how the costs of the
  bookshelves will vary in the next few years.

3
Planning the Model

• The reasoning behind the model is
  straightforward.
• First project the unit costs for wood and labor
  into the future. Then for any year, multiply the
  unit costs by the required numbers of board-feet
  and labor hours per bookshelf.
• Finally, add the wood ad labor costs to obtain
  the total cost of a bookshelf.

4
BookshelfCost.xls

• The completed spreadsheet model is shown.

5
Developing the Model

• Develop the model with the following steps.
• Inputs Enter the inputs into the upper left
  corner of a worksheet. These can be referred to
  later with Excel formulas.
• Design output table Think ahead of time how you
  want to structure your outputs. The important
  point is that you should have some logical design
  in mind before diving in.
• Projected unit costs of wood It is important to
  have a strategy in mind before you enter the
  formulas. You should design your spreadsheet so
  that you can enter a single formula and then copy
  it whenever possible.

6
Developing the Model -- continued

• For example enter the formula B9 in cell B19
  and copy it to cell C19. Then enter the general
  formula B19(1B10) in cell B20 and copy it to
  the range B20C25.
• Projected unit labor costs To calculate
  projected hourly labor costs, enter the formula
  B13 in cell D19. Then enter the formula
  D19(1B14) in cell D20 and copy it down to
  column D.
• Projected bookshelf costs With careful use of
  absolute and relative addresses, enter a single
  formula for these costs for all years and for
  both types of wood. To do this, enter the formula
  B5B19B6D19 in cell E19 and copy it to the
  range E19F25.

7
Developing the Model -- continued

• Chart Highlight the range E19F25 and click on
  Excels Chart Wizard button. This leads you
  through a sequence of steps. You should
  experiment with the possibilities.
• The model can be used to answer any what-if
  questions Woodworks might want to ask.
• It has been built in such a way that a manager
  can enter any desired values in the input cells,
  and all of the outputs, including the chart, will
  update automatically.
• Burying input numbers inside Excel formulas is
  bad practice.