Formatting Project Budget Import Spreadsheet for CMiC Bid Items

Note: Project budgets are imported from an Excel template into CMiC via the bid items module. There are several formatted spreadsheets available to use. The spreadsheets are available on SwinNet on the Technology > Products & Services page, scroll to the bottom of the page to Important Documents

Note: Managing your Labor Productivity starts with setting up your Budget Import Spreadsheet correctly. Way before it even gets into CMiC

STEP 1: Select the Company Code

STEP 2: Change the Job Number to the project youare working on

STEP 3: Enter the Retainage Percentage (%). This value (normally 10%) applies to all line items except Fee

STEP 4: Click the Save My Spreadsheet Button. (This will save the document to your desktop. DO NOT DO FILE/SAVE AS, it will break the macro and your budget will NOT import)

STEP 5: Double check that the file name changed to include your job # and today's date. (If the file name looks like this: Budget Import Spreadsheet - - 1-1-2018, STOP 🛑 > go back to SwinNet and open a new template, the macro has been broken.)

STEP 6 Search up a Phase Codes needed for this project and type "Y" in column A to select that phase code. Each row will turn Yellow when selected

Note: Once all the phases are selected, filter the spreadsheet to only show "Y"s using the down arrow in cell A1

STEP 7: Enter a Category Code for each selected Phase Code

Note: If a Phase Code has multiple category codes associated, each must be entered as a separate line. Using Column Q, enter the number of times you want this Phase code duplicated then click the Add Duplicate Rows Button. (Remember to remove this number in Column Q before you click the Add Duplicate Rows button again.)

STEP 8: Choose your Phase Type. (We have defaulted where appropriate, but you can choose another from the list if needed.)

STEP 9: Choose your Forecast Method (A = Amounts / P = Productivity)

Note: THIS ONE IS SO IMPORTANT FOR LABOR PRODUCTIVITY!!! Think about your Forecast Method now.

* If cell is left blank, it will default to P (Productivity) because the category code is 71111

* A "P" (productivity) method is what makes the phase code show up on your Labor Productivity Report (Do you really want it there?)

* A "P" (productivity) method changes how the Cost Detail pop up works in Contract Forecasting. Cost Detail for Labor Producitivty tries to think for you by calculating what is left to install and how many hours it will take to complete it. Have you ever gotten behind in entering quanatities and noticed your Cost to Complete has doubled/tripled. That is because the system is trying to think for you because it is a P Forecast Method.

* If you don't need to do Labor Productivity (Frank is driving the forklift) then put an A (Amounts) in the Forecast Method cell so it will not show up on your LP reports and the Cost Detail in Contract Forecasting will forecast as an A method.

* If you don't need to do Labor Productivity (The scope of work is so small it is not worth tracking) then put an A (Amounts) in the Forecast Method cell so it will not show up on your LP reports and the Cost Detail in Contract Forecasting will forecast as an A method.

STEP 10: Enter the Weight Measure (WM) This column is for Manhours/Hours (Input)

Note: Use this for both category 71111 & 71151 and track the hours on the Quantity/Hours Tracking log. Even though you are not tracking LP on a 71151 employee, you can track hours.

*: If you are 50% complete with your project, but your PE has spent 75% of the hours, there might be a question to ask or a PCI to complete

STEP 11: Number of hours to complete the scope

STEP 12: Average combined rate for crew

STEP 13: Enter the Cost Budget Estimated Amount (Total). This is the total COST budget amount for this particular Phase & Category line item. Remember that the Fee Phase & Category line should have zero COST budget

STEP 14: Revenue Budget, what you are going to get paid by the owner (REMEMBER, this can be different than your Cost Budget)

* You estimated for 2 apprentices and 1 Journeymen, but were given 1 apprentice and 2 Journeymen, that is going to cost you more. If you can't get that difference from the owner then your Cost Budget is going to be higher. Tell the truth!! If you know that now you can set your budget up this way, or immediately create an NCTO PCI

* You estimated for 2 apprentices and 1 Journeymen, but were given 3 apprentice, that is going to cost you less. If you can still bill the owner for the full Revenue Budget then your Cost Budget is going to be lower. Tell the truth!! If you know that now you can set your budget up this way, or immediately create an NCTO PCI

STEP 15: Weight Measure for Output (How many CY, SF, LF etc. will it take to complete the scope) NOT LS, NOT HR

STEP 16: Total output for scope

STEP 17: Review Retention (the formula on the spreadsheet will pull from the field in the upper left hand corner, but if you have a phase code that will have a different retention you can change it here.

STEP 18: If a Phase Code has multiple category codes associated, each must be entered as a separate line. Using Column Q, enter the number of times you want this Phase code duplicated then click the Add Duplicate Rows Button. (Remember to remove this number in Column Q before you click the Add Duplicate Rows button again.)

STEP 19: Be carful, this will remove ALL duplicate rows, not just the one you added last. No need to put a number in any cell on this column.

STEP 20: Clear what you have done

STEP 21: Click Save My Spreadsheet to save your work

STEP 22: Click Finish & Create Bid Items to create the .csv format that will allow it to be imported into CMiC. The Macro will add IMPORT THIS FILE to make it easy to know which one to import.