How to Use Excel to Build a Quoting System
By Robert Karr
Many businesses dealing directly with clients need to provide price quotations for work projects. The basic elements of each bid are often the same boilerplate text. Expensive software may include the necessary forms, but a simple Excel spreadsheet will provide much of the same utility. Setting up the basic template takes a little work, but it can be a one-time process. Changing the individual data for each new quote takes very little time.
List the specific items that must be included in your quotation system. These should include the company name, address, telephone number, email and website if available. There will be a place for the date and customer contact information. Depending on the type of business, the bid may need items such as Part #, Description, Quantity, Cost and a space for an extended total for each part. There will be a spot for the name of the person preparing the quote, when it expires and someplace for the customer to sign.
Open a blank spreadsheet in Excel. In cell A1, type “Quotation” and press the “Return” key. Choose a simple font in the menu with a large size—perhaps 36. Highlight cells A1 to D1. Right-click on this group of cells, and in the drop-down menu that appears, choose “Format Cells.” On the alignment tab, click on “Merge Cells.” Now merge the cells for range A3 to D10 in the same way. Within these cells insert the company information. In F3, type “Date:” and insert a function for the current date in E3 by typing “=Today()” In cell A11 type “Customer:” Merge the cells B11 to D11, B12 to D12, B13 to DB13, B14 to D14 and B15 to D15 individually.
Assuming you need to provide lists of parts and quantities, put these titles in cells A18 and D18. Merge cells B18 to C18 through B32 to C32 as above. Head this column “Description” and center it. “Cost” can go in column E18. Column F18 can be labeled “Extended.” In each row below this last column, insert a formula to multiply the quantity times the cost. For instance, cell F19 would read “=D19 * E19.” Format these cells as currency. Zero dollars will appear until you have data in the appropriate columns.
Type “Subtotal” in cell a33, “Tax” in cell A34 and “Labor” in cell A35. Type “Total” in A37. To finish this part and create the calculations, in F33, insert the formula “=Sum(F19:F32). E34 holds the tax rate as a percentage. Create the calculated tax in f34 by inserting “=E34F33” and format this cell as currency. For the labor cost, insert the number of hours in D35 and the hourly wage cost in E35. Set up the calculated labor cost in F35 with this formula: “=d35e35.” Finish off the calculations by adding F33, F34 and F35 in cell F37: “=sum(F33:F35).”
Add cells for the name of the preparer, the date the quote expires and wording such as “To accept this quotation, sign and date here.” Highlight each column of the quotation elements from top to bottom and insert borders.
Save the worksheet as a template, not as an Excel spreadsheet. Choose “File/Save As” and Excel template as the type of document.
Tips
- After each step, save the file so if errors are made, the whole process doesn’t have to be repeated from the beginning.
- If the rows for Parts aren’t enough, or too many, simply insert or delete rows.
- Change the column headings to match business needs.
- View examples of the complete spreadsheet, with all formulas and the way the spreadsheet might look in actual use in the Additional Resources section.
- In the steps above, for commands or items to be typed, ignore punctuation marks (period or quotes).
Warnings
- As developed, the cells in the spreadsheet are not protected. If more than one person works on quotations, consider protecting some cells that should not be altered.
- Do not save the first real quotation as a template—save it as a regular spreadsheet file, giving it the customer’s name for easy tracking.
- If sales tax is charged on labor as well as parts, the tax line should be below these two totals.
Writer Bio
Robert Karr has been a writer, indexer, reference librarian, computer programmer and Web designer. He has a Master’s Degree in Library Science. Karr has 30 years experience in reference and research and has been writing professionally for 25 years, focusing on the library, medical and computer areas.