spreadsheet screenshot

Work Project Bid Creation and Negotiation

Using spreadsheets to make real-time changes to bids

Implement the following steps to transform our skeleton spreadsheet into a working tool for preparing bids for a job that matters to you.

STEP 1: Download the skeleton and input items in each bid category

Download the skeleton bid spreadsheet HERE that is a start of a bid Eric put together for a ventilation system for his attic.

Edit the cells that are specific to Eric's sample with your own content. Describe the job you're planning to bid for. Then move category by category and add relevant content to labor, materials, insurance, discounts, taxes, etc. You can feel free to change any columns you want to make the bid correct for your application.

STEP 2: Implement basic multiplication formulas for calculating extnded price

These formulas are located in the cells in the extended price columns and should multiply the unit quantity times the unit price. So the extended price for 5 hours of labor at $45 per hour would be 5*45. Use the following image to help you. NOTE that the colum numbers and rows are different in the image than your template to prevent your brain from turning to mush!

Spreadsheet sample

Remember that once you have a formula in one cell, you can use "formula paste" to copy the formula in a smart way to other cells. Select a cell with a formula you want to copy. Hover your mouse over the lower left corner where there is a little black square. Your mouse cursor will change to a cross hairs. This means you can click and drag to the cells in which you want to copy the formula. Remember to check your formulas each time, since the pasting doesn't always work.

STEP 3: Calculate sub-totals for each expense category

Clients like to know how much they are paying for labor versus materials, etc. Use the sum() function to calcluate the sub-totals for each category. Use the following image as a guide"

Spreadsheet sample

STEP 4: Calculate the subtotal across all categories in pre-tax subtotal

This will be used for calculating discount amounts and taxes. In this case, we are creating a sum, but the cells aren't next to one another, so we have to select the cells individually and create a total that way:

Spreadsheet sample

STEP 5: Calculate discount amounts

Format your percent columns using the percent data type accessible on the home ribbon. Once we have the percents correct, we need to calculate the discount amount using the current subtotal from all the expense categories. We'll use an absolute reference to the subototal column by placing a $ in front of the column letter and row number so that when we copy that formula into other cells, we're still multiplying by the same subtotal cell.

Here's the sample of calculating the amount

Spreadsheet sample

Don't for get to create a sum for the discount total. Note that the amounts are in () to indicate that they are negative. This is a discount on an expense, so it should be negative.

STEP 6: Calculate the subtotal after discounts

Before we calculate taxes, let's create a subtotal after discounts have been applied. NOTE that we ADD the discount value to the subtotal before discounts since the discounts are NEGATIVE. Adding a negative value to a positive is the same as subtraction, which we want.

Spreadsheet sample

STEP 6: Calculate taxes based on the subtotal after discounts

Taxes are based on the value after discounts, so multiply the subtotal after discounts by the tax percentage.

Spreadsheet sample

STEP 7: Calculate the grand total

Once we have taxes summed up, we can add the total taxes to the subtotal after discounts. Here's the screen shot:

Spreadsheet sample

STEP 8: Format cells that you can edit during the bid conference

Color code the cells that you can edit with the client in a distinctive color. The last thing you want is to accidentally edit a cell with a formula in front of a client and have to fumble around to recreate it. In this example, we're making editable cells green with a thin border.

Spreadsheet sample

STEP 9: Prepare to create a bid with a client assigned to you

Now that you have your spreadsheet all ship-shape, tinker with the editable cells to see how the total should all change when any one cell is edit.

Great work!