Using COSTSKEL.XLS
(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 PDF file)

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.

Common Elements

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

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

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 capacities:
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.).


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.