In addition to the option of importing job-estimating information from BidWorx^®, ConEst^®, ProEst^®, and WinEst^® estimating software into BIS, you can also import job estimates from a Microsoft^® Excel^® spreadsheet.

This process describes using the Budget Import process in BIS. It involves setting up a template that will permit the program to recognize data elements. Clearly, to be able to use an import template repeatedly, the source (the estimate) spreadsheet format should always be organized the same to ensure that the BIS import template works each time without needing modification.

This document will examine two examples in detail:

Example 1: You can use a spreadsheet format that has fields corresponding to: Cost Codes, Cost Code Description, along with the Labor, Materials, Subcontract, Other, Equipment, Hours, and Quantity for each of the Cost Codes.

Example 2: You can use a field condition process that simplifies the spreadsheet.

Example 3: Difficulties with Cost Codes with “Leading Zeroes”.

Example 1: Corresponding Columns

(Not Using Field Conditions)

1. Examine the screen shot below.

2. Once the spreadsheet is ready for import, it must be saved as a CSV (Comma delimited) file in the Save as type area.

Note: After, clicking Save, several prompts will appear in reference to saving .CSV files in Excel; Click OK or Yes on each prompt.

3. In BIS, navigate to Job, Budget, and Import for Estimate.

4. Note that the Estimate Tab is initially grayed out. On the “New” Import from Estimate screen, enter a Code (template ID), provide a Description, and enter the Job Number. Browse to, and select the desired CSV (estimate) file. (Note: The Estimate Tab will then be available). The Estimate Tab will now show the information imported from the estimate CSV file.

Note: The example above includes the “Totals” information from the .CSV file.

5a. On the Main Tab, match the fields from the Estimates Tab to corresponding items.

5b. The “Start at line number” will skip the specified number of rows at the top of the Estimate screen.

5c. The “Skip records when the following condition occurs” entry will eliminate those rows that meet the condition entered on that line. (See Example 4 below.)

6. When you are satisfied, click on the Import button in the lower right-hand corner of the form. A screen may appear indicating duplicate Cost Codes. However, pressing the Total button will combine duplicate Costs Codes. (See the illustrations below.)

The first two cost codes are the same, but have different amounts.

After pressing Total, the first two items are combined.

7. Click on Import. You’ll receive a prompt to add Cost Codes to the Job Budget.

Click Yes, and then OK at the prompt indicating that the Cost Codes were added to the Job Budget.

8. To review the imported data, select the Budget tab for that job in Budget and Change Orders. Cost Code information can also be modified, deleted, or added in this area.

Note: Billing Codes can be associated with specific Cost Codes to create the Schedule of Values using the Create from Budget feature under Schedule of Values.

Example 2: Using Field Conditions

As mentioned earlier, you can use field conditions to greatly simplify the spreadsheet information. Note the example below uses a single letter to identify the type of cost.

Steps 1 thru 4 are the same as described earlier.

5. From the Main Tab, match the Fields from the Estimates Tab, and enter any necessary Condition information.

Continue with Steps 6 thru 8, also as described earlier.

Example 3: Maintain Leading Zeros in Cost Codes

To maintain leading zeros in fields while converting an Excel spreadsheet to a .CSV file:

1.   In Excel, highlight or select the fields to maintain the leading zeros.

2.   Right click and select Format Cells.

3.   Select Custom for the Category list.

4.   Select “0” from the Type list.

5.   In the Type field (at the top of the list) put in the number of zeros equal to the total number of integers needed in that field. Click OK.

Conclusion

It is almost as easy to import Job Estimates from Excel Spreadsheets as it is from popular estimating programs using the steps described above.