Computation Section
 - Capital Budgeting

Our example is a project to design and install an assembly line. The details of the project will become apparent as we discuss the use of the add-in.

To create a capital budgeting worksheet select the Add Estimate command from the menu. The dialog defining the project appears below. The name specified in the first field is important because it is used to name the worksheet on which the data is placed. The name cannot contain spaces or punctuation. Once created, the worksheet name cannot be changed. Otherwise some features of the add-in will not work.

For our first example we use only two levels of detail with 11 items. The items are identified with activities for the project. The buttons indicate that we are constructing a capital budgeting worksheet with fixed, or point, estimates of the activity costs. The checkboxes near the bottom control features of the worksheet. Click the Freeze Window button to freeze a portion of the worksheet containing row and column headings. Then the data portion of the worksheet may be moved without losing sight of the row and column headings. Click the Hide Columns button to hide worksheet columns holding intermediate calculated results so as not to distract from the final results. Both options may be reversed using standard Excel worksheet commands. The Random Data option is useful for the student interested in how the add-ins works, but not interested in entering data.



The worksheet for the first example is shown below after the data has been entered. The project is described by the Work Breakdown Structure or WBS. The principle characteristic of the WBS is that it includes all activities that must be performed to complete the project and that the activities not overlap in function. There are many uses for the WBS including assigning of management responsibilities and the time scheduling of activities as described in our Project Management add-in. Here we use it for estimating the costs associated with the project. This is called the Capital Budget.

Activities are described by integer indices. Level 1 is for the project and is assigned the number 1. This number is the same for all activities on the worksheet, so we do not provide a column for this index. Column C shows the index for the first level of detail. It is labeled L2 because it is the second index for the activities. Activities are uniquely identified by the indices. For example the Capacity Planning activity is written as "1.2 Capacity Planning". The program makes no attempt to sort the activities by number or assure that activities have unique numbers. This is the responsibility of the user.

For our example we use 1.0 for the project and use the last activity for the completion, or Finish, event. The Change button at the top of the form is used to add or delete rows from the structure. Rows cannot be added before the first row or after the last one. The first and last rows cannot be deleted.



The data consists of the level indices, the description of the activities and the cost estimates. These estimates are called top-down estimates since the activities have not been expressed in enough detail to identify cost components.


More Levels of Detail


For a complex project, activities at level 2 can divided into simpler ones for management, cost estimating and scheduling purposes. Below we see the example problem with an additional level of detail. The work packages are now at level 3.

  A further level of detail shows the basic cost components of labor, materials and overhead. Only part of the example is shown. It has 135 activities. A complex project might have thousands of activities. The number of activities allowed by the add-in is limited only by the number of rows on the Excel worksheet.




Clicking the Summarize button at the top of the page constructs the summary results shown below. The summary beginning at column I lists all the activities at the first level below the project, level 2. Column K sums all the cost entries associated with each of these activities. The cost of the entire project in in column M. This is level 1. The program does not summarize levels below the two shown.




Return to Top

tree roots

Operations Management / Industrial Engineering
by Paul A. Jensen
Copyright 2004 - All rights reserved