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!
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"
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:
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
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.
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.
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:
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.
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.