How to Cost & Price a Firm Fixed-Fee Government Contract on an Excel Spreadsheet

Use cell formulas on summary sheets to automatically total the latest cost.

When you use an Excel spreadsheet to prepare cost and price information for a firm fixed-fee proposal, you can easily see changes to your total bid when any expense amount increases or decreases. Government agencies commonly contract work by firm fixed-fee agreements. The proposal process sometimes involves multiple changes before bid submission that require flexibility and the ability to quickly adjust the total cost. To easily recalculate fixed-fee bid amounts, record similar expenses on individual worksheets and link them together in a single workbook.

Things You'll Need

• Supplier quotations
• Manpower breakdown
• Company financial data

Instructions

Enter Cost Information

1. Create a worksheet to capture all costs for the project. Review the government solicitation for any special instructions related to the cost worksheet and include specific data requested. In the absence of a specific format, create individual worksheets for labor, materials, other direct costs, indirect rates and a summary. Create a formula in one cell of each to reflect the total cost from that worksheet.

2. Create a classification column for the labor worksheet and make one row for each classification you include in the technical proposal. Add columns for the hourly wage, number of regular time hours and number of overtime hours. Multiply the value of the hourly wage by number of regular hours and the number of overtime hours by 1.5 times the hourly wage. Add the two totals together to determine the total direct labor cost.

3. Calculate the total material cost by recording all quotes received for the project. Include columns for quantity and unit of issue along with unit price to allow adjustment of costs in case the requirement changes. Add a note to identify the source of the price quote.

4. Build a separate worksheet for other direct costs required to complete the work. Include items such as project vehicles, telephone charges and equipment rental. For easy changes at the last minute, add three empty rows before the total cell. Use these rows to enter costs for any forgotten items so the total cell updates when you enter their unit cost.

5. Create a table for indirect costs and fees. Enter general and administrative percentages, fringe benefit rates, overhead percentages and material overhead rates as well as a fixed-fee percentage. When you link the rate worksheet to other portions of the proposal workbook, it allows the single cell to adjust all associated costs when a rate changes. To link the data from this sheet to other worksheets, use the format "sheetname!celladdress." For example, to multiply cell A1 of the summary sheet by the fringe benefit rate located in cell B1 of the indirect costs worksheet, enter this formula on the summary sheet: "=A1*indirectcosts!B1."

Build a Summary Cost Worksheet

1. Create a summary worksheet to pull in totals from direct and indirect cost worksheets. Make a labor section that links to the total cell of the labor worksheet. Multiply this total by the fringe benefit rate and overhead rate from the rate worksheet.

2. Link to the total of the material worksheet to obtain the direct cost information you need for the material section of the summary. To calculate the amount of the material overhead, multiply the direct cost total by the material overhead rate from the rate worksheet. Add the direct and indirect material amounts to calculate the total material cost. The total of the other direct cost worksheet should also link to the summary sheet.

3. Create a subtotal line on the summary sheet that adds labor, fringe benefits, overhead, material, material overhead and other direct costs. Multiply the subtotal by the general and administrative rate included on the rate worksheet. Add this amount to the subtotal to determine the total of direct and indirect costs.

4. Multiply the subtotal by the fee percentage from the rates worksheet to determine the fixed-fee proposal amount. Add the previous subtotal and the fee to determine the total cost of the proposal. If the amount exceeds budgetary limits, revise the fee percentage on the rate worksheet to stay within limits or determine where to cut other costs.

References

• Wisconsin SBIR; Cost Proposal; September 2009
• Florida Gulf Coast University: Excel 2007 Tutorial
• National Renewable Energy Laboratory: Price/Cost Proposal Form
• Naval Research Laboratory Broad Agency Announcement: Proposal Preparation