|
|
|
|
|
| GUIDANCE |
|
Pricing Handbook 3. Automated Cost Models Table of Contents 3.2 Using the Appropriate Application 3.3 Planning the Model: Organization of Data and Exhibits 3.3.2 File Size Considerations 3.3.3 Determining the Necessary Level of Detail 3.5 Application Functions and Analytical Tools 3.7 Transforming Cost Models to Hard Copy
3. Automated Cost Models
This chapter addresses the terms, concepts, and issues involved in building and utilizing automated cost models. A computer has the capability to perform a series of computations significantly faster than a manual calculation process. The main reason for creating a cost model on a computer is to use the built-in functions of the computer to process data. Instead of the analyst adding, subtracting, multiplying, and dividing, the computer can do this in much less time and with much greater accuracy. Formulas within spreadsheets can be used to increase the speed and efficiency of data processed within a model. Cost models can function as an aid in creating and documenting a prenegotiation position and as on-line support during negotiations. A change can be made to any data in the model, and the total will be recalculated quickly. When preparing a negotiation position, a cost model should consist of spreadsheets for the basic items being negotiated (usually contract line item numbers (CLINs) or work breakdown structure (WBS) elements), summary sheets, and sheets containing backup data (other direct costs, rates etc.). Essentially, all of the elements that make up contract cost should be represented in the model. Cost models are organized in a hierarchy such that the backup data are used to calculate the basic items that are used to calculate summaries that show the total contract price. When completed, a cost model provides a comparison of proposed, evaluated, and negotiated contract cost at various levels of detail.
3.2 Using the Appropriate Application Using the most appropriate software application is key to building an automated cost model. Software applications are available in a variety of classifications (i.e., spreadsheets, databases etc.) and brands (e.g., Excel, Lotus 1-2-3, Quattro Pro). The appropriate classification and brand should be fairly evident. Spreadsheet applications are often used for creating automated cost models. Spreadsheets use formulas to generate scenarios and to perform complex calculations. They are well suited for storing, analyzing, and presenting dynamic information. Spreadsheets allow the flexibility that a cost model requires. Identifying which brand of spreadsheet application is best (Lotus 1-2-3, Quattro Pro, Excel) is a matter of preference and availability of software. Currently, the FAA uses Excel almost exclusively, but contractors often use other brands. In the past, compatibility was a major concern when switching between applications created by different manufacturers. With advances in technology, compatibility is less of an issue. Most software will open files and accept data created in other software applications. The only other issue concerning various spreadsheet applications is the use of three-dimensional workbooks (notebooks) versus one-dimensional worksheets (spreadsheets). A worksheet is the primary document used to store and manipulate data consisting of cells organized into rows and columns. A workbook is a collection of worksheets. Workbooks are recommended because they allow storage of multiple worksheets in one file and provide an easier method to segregate and control data.
3.3 Planning the Model: Organization of Data and Exhibits Before sitting down at the computer to build a cost model, there are a few key steps that will save a significant amount of time. Efficiency depends on organization. A few decisions regarding the organization and structure of a cost model should be made at the beginning of the modeling process. The following topics provide some ideas about issues that should be considered before creating the model. Before constructing a cost model, the analyst needs to understand what purpose the model will serve. The purpose will dictate the information included as output or results generated by the model. The construction of the model will vary depending on the purpose of the model. A model built for a source evaluation should be geared toward determining the most probable contract cost. A negotiation model needs to be more flexible and will probably contain more detail than one being used to determine the most probable cost only. A model to be used in negotiations should be geared toward tracking positions and making quick changes to the data. Some contracting officers (COs) simply use the contractor position and the FAA position in negotiations. Others may prefer to have more than one FAA position such that they have a range within which negotiated costs should fall (i.e., minimum and maximum positions). The CO may also have a preference for a specific data presentation. It may be helpful to hand-draw a diagram of the model and show it to the CO before sitting down at the computer. This will give the CO an opportunity to make comments. 
3.3.2 File Size Considerations Regarding file size, there are two areas of concern: floppy disk capacity and RAM. The file size may eliminate the ability to save the cost model to a floppy disk. If the file is to be downloaded to a floppy disk, the file size should not exceed the memory capacity of a disk (1.44M for 3.5 inch diskettes). Alternative methods of transferring the file The amount of RAM consumed by both the application and the cost model may affect the speed at which the data are calculated. Keep in mind that spreadsheet applications require a certain amount of RAM to operate. When the RAM is full, the computer slows down, making the analyst wait and increasing the risk of an "insufficient memory" error. As a result, the analyst needs to be aware of the file size. If the file size begins to get too large or if the computer seems to be extremely slow, the analyst should put segregable parts of the cost model into separate files. In addition, the following memory tips may help minimize the file size. 
3.3.3 Determining the Necessary Level of Detail 
Restructuring an existing model so that it includes more detail than originally intended can be extremely difficult. Sometimes changing the level of detail in an existing model requires more work than developing an entirely new model. To avoid this problem, it is best to develop the cost model based on the level of detail at which the contract will be negotiated. Since the COs usually decide the level at which negotiations will occur, their advice should be sought before designing the model. Typically negotiations occur at either the CLIN, SLIN (sub-contract line item), or WBS (work breakdown structure) level. Often the model will need to include at least one level of detail lower than the negotiation level. This lower level is used to calculate rates, hours, material dollars etc. which will be summed into the CLIN, SLIN, or WBS level. Unfortunately, the amount of time and complexity necessary to construct a complex cost model restricts the level of detail that can be included. Nonetheless, the model should be built from the lowest level summing up to a total contract cost. Only the lowest level should contain "hard entered" data. All higher levels should contain formulas and cell references. 3.3.4 Planning for Negotiation Support Using a cost model for negotiation support means making adjustments to data within a model to determine changes as a result of incremental negotiations. For the model to reflect the outcome of negotiations, negotiated data should be entered into the model as it becomes available. This requires planning where and how negotiation information will be entered. The model can be used in one of two ways. The first option is to include a negotiated position in addition to the contractor and FAA positions. The negotiated position will be a third column showing the information that has been agreed to in negotiations. This will allow for comparison of the negotiated position to the other two positions and highlight the gains and losses that have resulted from the negotiation. Table 3-1 shows an example of a cost model that is constructed using the three positions. Table 0-1. Comparisons
The other option is to construct the model with only the contractor and FAA positions. During negotiations the analyst will simply input negotiated information directly into the FAA position. If this approach is used, the prenegotiation model should be saved under a separate file name or directory. By keeping the original model separate from the model being used in negotiations, the original model can be used as a reference point should questions arise. If data in the proposed position are not changed, the proposed position can be compared to the negotiated position. 3.3.5 Constructing a Hierarchy The final step in planning a cost model is to sketch a hierarchy of how information will flow within the model. The model should begin with the level of detail (decided above) that should be included. Analysts should include time saving computations required to arrive at the negotiation level of detail. Figure 3-1 is an example of a hierarchy. The arrows show the direction of information flow. Figure 0-1. Sample Model Hierarchy
Level 2 represents the negotiation level of detail dictated by the CO. Level 3 contains all the backup information that would have either been calculated on paper or scattered throughout the model had it not been included in this level. Level 1 consists of summary exhibits. As stated above, this is just an example. Cost models may contain greater or fewer levels of hierarchy than the three discussed above depending on the negotiation level of detail and the specific proposal.
With the planning phase completed, it is time to begin constructing a cost model. The following sections describe the details involved in building the necessary exhibits and give a few tips that may help in creating the model. 3.4.1 Constructing Rate/Factor Tables Rates and factors are the percentages and dollar amounts used as building blocks for calculations throughout the model. They consist of both direct (e.g., labor) and indirect (e.g., overhead) rates. A rate table will list all the labor, fringe benefit, escalation, overhead, general and administrative (G&A), and cost of money categories and their respective rates. In addition, the table should include factors such as material attrition and obsolescence. Rate/factor tables are aids for automating the model. All the rates and factors are "hard entered" into the table. Higher level spreadsheets can access and use these rates by using the appropriate cell reference in the calculation. This precludes the necessity for "touching" the same data more than once. In addition, it allows the analyst to quickly update the base information which in turn drives the cost/price numbers at higher levels. Impacts of changes to input and base data can be quickly assessed, and changed items can be quickly definitized. The concept is that no matter what size the cost model, there will be only one source of rates. By setting up a model this way, new rates can be entered quickly into the table when rates change or an audit report is received. The table can also serve as an exhibit in the Cost/Price Evaluation Report to show differences between the contractor�s bid and FAA recommended rates. Usually, there will be two sets of rates, one each for the contractor and FAA positions. Two sets of rates can be included in the same table with separate sets of columns for each, or they can be separated into two rate tables, one for each position. If the cost model contains three positions (contractor, FAA, and negotiated), then the rate table may contain three sets of rates. Table 3-2 is a sample rate table that shows three positions for rates in one fiscal year. Finally, if the contractor has a forward pricing rate agreement (FPRA) with the FAA, then the rate deck may only contain one set of rates for those rates included in the FPRA.
An additional advantage of using a rate table comes when composite rates are used. The rate table can list all individual rates and then be used to calculate the composite rate. Using the rate table to calculate composite rates may require adding rows or columns, as appropriate. For example, if the composite is a departmental or functional labor rate, the rate table should have a row defining this group. Should any rates change or the integrity of the composite calculation change, the adjustment can be made directly on the rate table. 
The build-up format of a spreadsheet, whether by CLIN, SLIN, or WBS, should emulate that of the contractor. By looking at the pricing runs in a contractor�s proposal it should be evident how the contractor calculated a price for the item. The cost model should use the same basic format. The analyst may need to look at the basis of estimates (BOEs) to get a clearer picture of how the contractor arrived at the price. Since most contractors use the same program to create all of their proposals, there may be some cost elements (usually categories of labor) that are not used. The analyst may opt not to include these in the cost model. However, if the cost model is to be used as a template for later proposals, all cost elements should be included. Table 3-3 is an example of a CLIN spreadsheet.
The CLIN/SLIN/WBS spreadsheets should have totals and subtotals. Subtotals make the model easier to read and provide a quick assessment of key sub-elements. Important subtotals include total direct labor, total material, and total other direct costs. COs are always interested in knowing the total cost. Total cost in a cost model usually includes everything except profit/fee and cost of money. The bottom line in the spreadsheet should be the total price including profit/fee and cost of money. If acceptable to the CO, the rates do not need to appear on the CLIN/SLIN/WBS spreadsheets. The spreadsheet, for instance, would show overhead dollars, while a separate rate table would contain the overhead rate. The first step in the automation process is to determine what numbers are likely to change. These items will be the variables in the model. Direct labor hours, direct material dollars, and other direct costs will be the variables in most models. The relationships between the variables and other numbers will more than likely remain constant. These relationships can be defined using formulas such that new information only needs to be entered in one place. The cost model will recalculate the totals based on the change and the formulas. For example, labor hours change from 5 hours (Scenario A) to 10 hours (Scenario B). The hourly rate of $20 remains the same. The computer will automatically recalculate the labor dollars as $200 from $100. In this example, cell C3 would contain the formula: A3*B3.
3.4.4 Utilizing Links Between Cost Models 
Often, when constructing cost models to analyze large proposals, data must be split into several spreadsheet files in order to conserve memory. The spreadsheet files can be connected through the use of links. Links between the files enable the maintenance of a single rate table for the entire contract and the summation of data into a total contract price. A link occurs when a cell in the current file references information or a cell in another file. The reference may occur by itself or as part of a formula. Referring back to Figure 3-1 (Sample Model Hierarchy), the boxes might represent files while the lines represent links. 
When links are present in a spreadsheet, there are two ways to update them. If the destination file (file that contains the link formulas) is open while the source file is open, the data are automatically updated. Unfortunately, having all linked files open at once may slow the computer down considerably. If the source file is not currently open when the destination file is opened, the spreadsheet program will prompt the user to update the links. This allows for updating the links before making changes to the current file. (In most spreadsheets there is a menu command for updating links that will allow the user to select the specific file from which links can be updated.) A word of caution when using links: unless instructed otherwise by the user, spreadsheet applications look for linked files in the same file location and path names as when the links were made. It is essential that the computer knows where to find the file. Links contain the complete file name including path. If the path or directory changes, the computer program does not know where to look for the file. This is very important when saving the files to floppy disk or transferring them to another computer. When the destination and source files are originally stored under the same directory, changing directories or paths is not a problem if both files are moved to the same new directory or if the user manually updates the directories or paths by changing the links within the spreadsheet. 3.4.5 Creating Relevant Summary Exhibits Besides being a calculation tool, the cost model is intended to document the cost impact of proposal analysis, audit and technical recommendations, and negotiation information. To be useful, the model should have certain exhibits that summarize lower level data. For purposes of negotiations, writing modifications, and resolving differences in dollar amounts, there are certain exhibits that are quite useful. A CLIN summary is a list of the CLINs and their prices for a specific proposal. Since many modifications are written at the CLIN level, this summary page provides COs all the information they need, and the CO does not need to sort through several pages of spreadsheets. Table 3-4 is an example of a CLIN summary.
A Cost Element Summary gives the total contract cost broken into cost elements such as direct labor, direct materials, overhead, etc. This summary helps to show which element(s) contain the majority of the costs in the contract (e.g., labor intensive versus material intensive). The Cost Element Summary is helpful if profit is being calculated via a weighted guidelines analysis (see Chapter 12, "Profit/Fee Analysis"). All of the element totals that are needed to perform a profit calculation can be taken directly from this summary. Table 3-5 is an example of a Cost Element Summary.
If the contract has both recurring and nonrecurring costs, the CO may wish to see how much of the total cost falls into each category. This can be shown either at the total cost level or at the cost element level by having side by side columns representing the different types of cost. Similarly, when some of the work for a particular contractual action has already been performed, the cost model should segregate actual costs incurred from estimated costs to complete the work. This segregation is necessary because the total costs from the summary page are often used in the calculation of an appropriate profit/fee percentage and actual costs usually have a different profit/fee percentage than estimated costs.
3.5 Application Functions and Analytical Tools 3.5.1 Frequently Used Functions, Commands, and Capabilities Creating a cost model requires a degree of familiarity with spreadsheet programs. The descriptions below are to provide some information about various functions, commands, and capabilities found within most spreadsheet applications. They are not intended to provide detailed "How To" directions. Please consult the software users manual for such instruction. Functions are calculation tools used in worksheets to perform decision making, action-taking, and value-returning operations automatically. Included in this grouping are sum, round, if, and lookup functions. Sum is used to add numbers in a range of cells. The round function rounds off a number or the result of a calculation at a specified number of decimal places. The if function is a conditional function which is quite helpful in avoiding dividing by zero. Lookup functions can be used for locating rates on a table that relate to a specific criteria. Commands are instructions that tell the program to perform an action. Commands are usually grouped on menus. The copy/paste commands are among those most frequently used in spreadsheets. Often formulas or data are repeated in several places throughout the model. Using copy/paste saves time. Further, Paste special allows portions of the contents of a cell to be pasted. Paste special offers the choices of pasting only the values, formulas, or formats of a cell. Another command is search and replace. This command can find specified information in cells throughout the spreadsheet and replace it with values specified by the analyst. One use of the replace command is to change a link reference. Capabilities are options or features that pre-exist within the program. One feature provided by three-dimensional spreadsheets is the ability to group sheets. When worksheets are grouped, changes made to one sheet are made to all sheets in the group. Grouping can be used for changing formulas, adjusting print settings, formatting the cost model, and entering data. An option that makes entering data go a little quicker is to select manual calculation. This means that the program will only recalculate when the user commands it. The default setting is for automatic calculation where the computer recalculates after every change. Depending on the speed of the computer, manual calculation may save time. When utilizing a cost model, there are often several functions which must be performed repeatedly. These functions require the user to execute a combination of key strokes and/or mouse movements. Macro commands are a vehicle to combine multiple inputs from the user into one executable command. For example, an analyst must print out a section of a particular spreadsheet whenever there is a change in total dollars. To print the spreadsheet, the analyst must highlight the relevant area with the mouse, set certain print options such as margin widths, headers and footers, paper size etc., and then press the print button on the toolbar. To simplify this process, the analyst can develop a macro command. Instead of executing each of the key strokes and/or mouse movements independently, the analyst would just execute the macro command. As most spreadsheet applications differ in their specific treatment of macros, it is beyond the scope of this handbook to discuss in-depth how to create and implement macros. With most commonly used applications, however, macros are easily developed through a device called a macro recorder. When turned on, the recorder copies the user�s exact keystrokes and/or mouse movements and saves them onto a macro worksheet. When the macro recorder is turned off, then the user can assign a name and a short-cut key to the particular macro. Thus, macros can be very easy to develop, and considering the time advantages they yield, they are usually worth the extra few minutes it takes to create them. Depending upon the number of inputs and the desired function, macro commands can be very simple or very complex operations. Usually, the more complex and lengthy the desired function, the more time the macro will save. Whenever macros can be developed and utilized, it is almost always in the user�s best interest. The user must be cautious, however, to ensure that the macro is developed properly and works as intended before it is widely used. Macros are most commonly used to automate processes and perform repetitive functions such as developing print ranges and generating reports.
Many times when a cost model is created, errors occur. These errors are not necessarily due to problems with the program or formulas but due to data entry. As a way to check both the cost model and entered data, it is best to start by entering the data exactly as it appears in the pricing runs of the proposal. The totals calculated by the model should be the same as those in the proposal. While entering the data, the analyst should continuously check for errors. It is easier to trace the location of the error if it is found before reaching the total CLIN or contract cost. If any total is different, work backwards comparing the cost model to the proposal until the error is found. Other than data entry errors, this will help locate errors in formulas, rounding differences, and possibly errors in the proposal. If the error is an incorrect formula, be sure to make the change everywhere that the formula is used. For example, when entering data for CLIN 1 an error was found in the formula used to calculate G&A expense. The same error may or may not be noticeable in subsequent CLINs, but the formula should be changed in order to maintain continuity. Rounding errors may occur due to variations in decimal places. Unless specified, spreadsheets do not round off numbers. The other source of rounding errors comes from the proposal not showing all the decimal places used in rates and hours. When the proposal does not show this, the difference in total price may be significant. Ideally, the contractor may be able to provide pricing runs that show all decimal places used in calculations. However, insignificant rounding differences should not be a concern. If there are no data entry errors, the formulas are right, and eliminating rounding differences does not solve the problem (i.e., the cost model does not add up to the costs presented in the proposal), the problem might be the proposal itself. Do not eliminate the possibility that the calculation error occurred before the cost model was built. Finally, if time permits, the analyst should ask someone who is not involved with the creation of the model to compare the contractor position in the cost model to the proposal. Sometimes a fresh pair of eyes can find errors or make improvements that just cannot be seen by the person who created the model. The reviewer should keep in mind that in some cases only "hard entered" numbers will match exactly what was in the proposal.
3.7 Transforming Cost Models to Hard Copy Now that the model is built, the formulas have been verified, and the necessary summary exhibits have been added, it is time to think about transforming the model into hard copy. Will the template fit on a standard (8.5x11) sheet of paper? Will important information stand out from all the rest? Most programs have a default setting that declares page size to be 8.5x11. If it suits the format of the spreadsheet better, this size can be changed to legal size (8.5x14) or to executive size (7.25x10.5). For purposes of maintaining uniform files, the default size is preferred. It is highly unlikely that the model will fit perfectly into 8.5x11 sections the first time. Ways to modify the model such that it fits include choosing a smaller font size, adjusting column width and row height, adjusting the scaling (found under page setup), adjusting the margins, inserting page breaks and eliminating blank rows. As further aid, most programs allow for setting the print area and titles. Specifying the print area tells the computer to always print a particular range of the worksheet. This can save time because normally the range of a model to be printed does not change. Titles are rows or columns to be repeated on each page. With a cost model, titles are usually rows containing column headers that specify which position (proposed or objective) the numbers represent and columns containing the list of cost elements. In Figure 3-2, the shaded area represents titles, and the non-shaded area is the print range.
Figure 0-2. Sample Spreadsheet
To make the printed version more pleasing to the eye and important information easier to find, shading and borders can be added to the model. For example, a double line can be used to denote the final total. Also formatting the numbers such that they appear as dollar amounts, with as few decimal places as required, takes away some of the confusion. The example below, Figure 3-3, shows reformatted and as it would appear printed.
Figure 0-3. Printout of Spreadsheet
Finally, before printing, headers and footers should be added. Headers usually include such information as the file name, the date and time the model was printed, and the label "Source Selection Sensitive" or "Procurement Sensitive", if appropriate. Typical footers include the page number and the date and time, if it was not included in the header. Printing the date and time on the page provides a means of specifying the most recent data from the oldest data. This is very important to remember during negotiations, when numbers are constantly changing. Including the file name is especially important when there are several versions of the cost model. With the file name on the page, changes can easily be made by returning to that particular file.
The steps involved in planning, creating, and printing a cost model may appear to be fairly time-consuming. Many of the steps, though, once learned become intuitive. In the long run, an automated cost model containing formulas and data necessary for arriving at total contract price can save a significant amount of time. Remember, the key to a good cost model is careful planning before sitting down at the computer. As a final remark, every proposal is different, and therefore, every cost model will be different. There is no truly right or wrong method. The ideas and examples presented in this chapter are intended to be used only as a guide in creating cost models. The exact appearance of a cost model is at the discretion of the creator and user(s). | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
(e.g., compressing the file or using electronic mail) instead of saving it on a floppy disk are not always practical. Cost model files can be compressed (zipped) to reduce the amount of storage space needed, but this may damage some formulas and macro commands contained in the cost model. Damage caused by compressing the file is difficult to repair. 