(Last updated/edited on
Nov 08 1998 at 02:08:48 PM
(to TREE_98.XLS description and sheets)
(Cost Modeling Slides - PDF format)
(How To Do Data Table Analyses with Excel & 123 - large
The COSTSKEL.XLS worksheet has been setup to simplify
the development of a workable cost model that can be combined with the decision
analysis tools that are the core analytical element of the course.
As you are aware, there are several possible ways to model the costs of manufacturing/producing
a product, ranging from basic accounting data through to sophisticated technical
process and economic modeling. This tool is set up to provide a framework for four
possible avenuces to cost estimation. One of these four should be sufficiently flexible
to meet your modeling needs.
Although each of the four approaches are distinct, they have common elements. In
particular, each modeling approach assumes that you can state:
There are also common elements for the outputs for compatible modeling approaches.
Approaches 1, 2, and 4 compute the per piece variable costs, the per piece capital
costs, and the per piece total costs. Approach 3, as you will see, cannot supply
such a breakdown. However, the only reason for needing this breakdown is if you
wish to have the TREE_98 model compute only the net present value of the cash flows
(requiring you to subtract the capital costs yourself - extra work for you!). Otherwise,
using the total piece cost will automatically include the annualized/amortized capital
costs into the cash flow (it depends on your assumptions about capital).
The scale of the effort - production capacity. This is a measure of the total
number of units that can actually be produced each year. Almost all production facilities
are limited in the total number that can be produced, usually due to constraints
on the capital equipment. In general, this factor is used to determine the total
capital cost of production each year - a cost which must be recovered.
The actual annual production - the number of units produced. Although it
is possible to devise a plant of a known capacity, you are unable to predict the
demand for your product with 100% accuracy. Thus, it is possible that you may have
overbuilt, in which case you will have less parts to distribute your annual capital
costs over; alternatively, demand may be greater than capacity, which you cannot
Modeling Approach 1
Approach 1 is closest to the modeling approach known as technical cost modeling,
in that it attempts to break down the factors of cost into smaller elements that
can be readily parametrized. The first two inputs are the common elements described
above, the size of the plant and the actual number of parts produced in a particular
Variable costs of production are broken into two parts; the portion of cost that
derives from the materials used in production (say, the metal required to cast an
engine block) and the other variablee costs per part (for example, the energy required
to melt and process the material and any labor costs that would otherwise not be
bourne if the part were not manufactured).
Capital costs are estimated through a combination of the next set of factors. This
model essentially scales production by assuming that a machine of only one size
can be used to make these parts, and, with increases in production capacity, more
machines must be purchased. "Dedicated machines" can only be used on a
single production line, while reusable equipment can be used for all machines. The
user must first supply the costs of these machines, and then must supply a set of
values describing the rate at which parts are produced by these machine. The rate
of production, in conjuntion with the total amount of productive time available,
defines the total amount of equipment that must be purchased in order to meet the
targeted plant capacity (and thus, defines the total capital cost of the facility.)
When amortized, this capital cost can be combined with the variable costs to yield
an estimated production cost, shown below (as Piece Cost).
Note that this approach to cost estimation also allows the user to develop other
descriptive statistics about the process, which may be used to validate the results
of the cost estimation.
Modeling Approach 2
Approach 1 assumes that capital costs increase linearly with capacity, essentially
by adding manufacturing lines. However, this linear assumption is not always appropriate,
in that it may instead be possible to buy a bigger machine, rather than many smaller
ones. Approach 2 allows you to model capital costs this way by assuming that the
cost of equipment varies log-linearly with production capacity (mirroring, for example,
the classical chemical engineering cost estimation approach of scaling costs according
to capacity raised to the 0.6 power). This approach also groups all variable costs
into a single term. Otherwise, the cost calculations are essentially the same.
Modeling Approach 3
Modeling Approach 3 assumes that all the user knows is the final cost of part production
for a variety of plant sizes, and for a variety of production rates. In effect,
the user simply fills out a table of known values (derived, for example, from a
large set of accounting data), which can be referred to via spreadsheet table lookup
functions. In effect, this approach requires the user to develop piece cost estimates
by hand, which can then be employed for similar circumstances. All that is requires
is simply filling out the table correctly.
Modeling Approach 4
Approach 4 uses a balance sheet metaphor to structure the inputs to the cost model.
The user can specify up to four 'levels of effort' - essentially the scale of production.
There are three kinds of cost factors that can be specified for each of these production
These three tables are combined to calculate a piece cost, once the actual production
rate is specified. Although sample line items are given, space is also set aside
for the user to define items that are specific to the process being modeled (e.g,
VC3, VC4, AC3, etc.).
costs per piece produced;
recurring annual costs; and
one time capital costs.
The investment costs are amortized annually, using the built-in spreadsheet functions
for calculating loan payments.
The user can then specify processing and accounting assumptions in the tables which
follow the input data tables for the various cost elements. The capital amortization
assumptions are defined, and the capacity and annual production parameters can be
specified. It is important to note that the model will choose the cost column whose
capacity is greater than or equal to the value specified in cell R40 - in other
words, this model approach assumes that the technology of production can only be
provided at the sizes specified in the four input columns; it does not attempt to
interpolate between the columns.
Following the output information is a sample 2-way data table, computing the unit
costs of production for various combinations of plant capacity and annual production.
Note that the costs are undefined (#NAME?) when production exceeds capacity. Note
also that the columns headed by 150000 and 200000 are exactly the same. As was mentioned
above, even though the table specifies a production capacity of 150000, there is
no data for a plant of that size, so the cost model uses the next largest feasible
plant size for which data is available - 200000.
Frank R. Field, III (last updated/edited on
Nov 08 1998 at 02:08:48 PM
This page has been built
with HomePage Publisher.