22R2002_SOL_Attachment X10 Tutorial.docx

DoDEA Transportation Division Attachment X10 “Tutorial” How to fill out Attachment X1 “Routes”

The tabs on the bottom of Attachment X1 are identified as Summary Page; Route Sheet Example; and Route 1 through Route 30.

Tab “Summary Page” This Tab self calculates. It is a summary of all the routes proposed on Tabs “Route 1 through Route 30.”

The Offeror does not need to make changes to this tab unless they propose over 30 routes. If that is the case, then the offeror needs to add an additional row for each route over 30.

Note: If proposing over 30 routes, complete the instructions for Adding an Additional Row in the summary AFTER you have added the route sheet.

Add Additional Route Sheet:

Step 1: In the bottom route hand corner of Attachment X1 Select the Tab “Route 30”;

then Select the “+” button.

Step 2:

A new Tab will be inserted after the Tab “Route 30”;

Double Click the new Tab and the current name of The tab will be Highlighted. Type in “Route” and route number.

These numbers shall be in chronological order.

For Example: After Route 30 comes Route 31.

Step 3:

Select Tab : Route 30”.

Once in the tab, Select the cell at the top left corner of the workbook.

Once you select this cell, it will highlight the whole workbook.

Step 4:

Right Click and select “Copy”

Step 5:

Select New Route tab that was just created.

Once in the tab, Select the cell at the top left corner of the workbook.

Once you select this cell, it will highlight the whole workbook.

Step 6:

Right Click and select “Paste”

Step 7:

Staying on the new Tab, Select Cell B1 and enter the Route Number

Now you have successfully added a new Route Sheet

To add an Additional Row in Tab “Summary Page” :

Step 1:

Highlight row 42 of the summary spreadsheet.

Then right click and select “Insert” this will add one row.

If you need to add additional rows, repeat Step 1.

Step 2:

Highlight Cells C41 through G41.

Once Cells are highlighted right click and select “Copy”

Step 3:

Highlight Cells C42 through G42.

Once Cells are highlighted right click and select “Paste”

Step 4:

Highlight Cell C42.

The equation will appear at the top

The equation is:

=IFS(‘Route 30’!A32=“AM”,”x”, ‘Route 30’!A60=“PM”,”x”)

Update equation to add the appropriate Route Number.

Example for Route 31 the equation is:

=IFS(‘Route31’!A32=“AM”,”x”, ‘Route31’!A60=“PM”,”x”)

Step 5:

Repeat Step 4 for Cells E42 and G42.

Step 6:

Insert Route Number in Cell B42.

Repeat steps for every row you must add.

Step 7:

Double Click each blue cell and update equations to include all new rows.

In this example:

Cell C43 the equation will be:

=SUM(D12:D42)

Cell E43 the equation will be:

=SUM(F12:F42)

Cell C43 the equation will be:

=SUM(H12:H42)

Tab “Route Sheet Example” The Offeror does not need to make changes to this tab It is to serve as an example of how to fill out each route tab.

Tabs “Route 1 through Route 30”Cells I4 & I32:

Do not fill in (shows total length of route) If Cell I4 or I32 turns red, then fill in Cell H9 or H37 Cells I3:

Do not fill in (shows total passenger count) Cell I2:

Enter bus passenger capacity size, the Offeror wishes to use for route

Cell A2 and Cell A10:

Select Route Type used for Route. A drop down menu will be given for you to use.

Column C:

Enter Stop Numbers, the Offeror wishes to use for route

Columns D, E and F:

Cells auto populate based on stop number inserted in Column C.

DO NOT FILL OUT CELLS IN GREY

Column G:

Enter Time of Pick up or Drop off, the Offeror wishes to use for route

The last stop shall be entered into the cells highlighted in orange.

NOTE: AM runs the last stop will always be the school.

PM and LA runs the first stop will always be the school.

How to fill out Attachment X2 “Personnel”

The tabs on the bottom of Attachment X2 are identified as Personnel List Example; Personnel List; and Personnel Action Plan.

Tab “Personnel List Example” The Offeror does not need to make changes to this tab. It is to serve as an example of how to fill out the Personnel List tab.

Tab “Personnel List” Column F:

For each personnel list if they meet the age requirement.

Column D:

For each personnel list their position.

NOTE: You must select from the drop down Menu.

Column C:

For each personnel list their first name.

Column B:

For each personnel list their last name.

Columns G through J:

For each personnel list if the meet the following credintals by selecting X, if they do not keep cell blank.

Column K:

For each personnel required please list their drivers license class.

Column AC:

List ALL Subcontractors in yellow cells only.

Column U:

Select “X” if the employee is under the prime, if not leave cell blank.

Column W:

Select “X” if the employee is under a subcontractor, if not leave cell blank.

Column Y:

If the employee is under a subcontractor, then type in from the list in Column AB based on which Subcontractor you are usng.

Example from above:

“ABC Drivers” type “Sub 1” “123 Contractor” type “Sub 2”

If not a subcontractor leave cell blank.

Tab “Personnel Action Plan”

Fill in the # of personnel you plan to get for each area here.

Fill in as applicable your plan to obtain personnel. Use up to but no more than 500 words per section.

Example:

Your proposing using one subcontractor.

Fill in Cells C9; C21; C69; and C81. (up to 500 words for each cell)

Also, complete as applicable for your plan to retain personnel (below these cells rows 69 through 128).

How to fill out Attachment X3 “Vehicles”

The tabs on the bottom of Attachment X3 are identified as Vehicle List Example; Vehicle List; Vehicle Specification Form Ex (Example);Vehicle Specification Form; and Vehicle Action Plan.

Tab “Vehicle List Example” The Offeror does not need to make changes to this tab. It is to serve as an example of how to fill out the Vehicle List tab.

Tab “Vehicle List”Column F:

For each vehicle list yes or no.

Column E:

For each vehicle list the Passenger Capcity of the vehicle (not to include the driver).

Column B:

For each vehicle list the Make of the vehicle.

Column D:

For each vehicle list the Year of the vehicle.

For each vehicle list the Model of the vehicle.

Colums H through Q:

List any vehicle specifications in the orange columns.

Anything noted in an orange cell with an “*” is a required specification.

For each vehicle list “Yes” or “No” for each specification that the Offeror listed in the Orange Cells.

Example: If the offeror listed GPS in the orange cell in Column L and vehicle 1 does have a GPS they select “Yes.” Vehicle 2 does not have GPS they select “No.”

Column R:

Type “Yes” where each vehicle will be used.

Type “No” if it will not be used for these routes.

Example: if a vehicle is being used for Daily Commute type “Yes”.

Column AC:

List ALL Subcontractors in yellow cells only.

Note: this should match the list in your personnel attachment X2.

Column U:

Select “X” if the employee is under the prime, if not leave cell blank.

Column W:

Select “X” if the employee is under a subcontractor, if not leave cell blank.

Column Y:

If the employee is under a subcontractor, then type in from the list in Column AB based on which Subcontractor you are usng.

If not a subcontractor leave cell blank.

Column AE:

List ALL passenger capacity in yellow cells only.

Example: if all the vehicles you are proposing are 45 passenger vehicles then you will like 45.

The passenger capacity already listed in the cells can be replaced with what you have. This was put there as an example.

Passenger capacities should only be listed as a number no letters should be put into these cells.

NOTE: Only fill in the yellow cells. The cells in Column AF is an equation that should not be updated.

Tab “Vehicle Specification Form Ex (Example)” The Offeror does not need to make changes to this tab. It is to serve as an example of how to fill out the Vehicle Specification Form tab

Tab “Vehicle Specification Form” Column F:

Identify if the Government will have access.

Column E:

Disctibe when it will be used.

Columns A & B:

These cells are auto popuated based on what you filled out in your Vehicle List

List the name of the product.

Column D:

Write a discription of the product.

Tab “Vehicle Action Plan”

Fill in the # of vehicles you plan to get for each area here.

If not in need of vehicles type “0” in cell.

Fill in as applicable your plan to obtain vehicles. Use up to but no more than 500 words per section.

Example:

Your proposing using one subcontractor.

Fill in Cells C18; C31; C83; and C86. (up to 500 words for each cell)

Also, complete as applicable for your plan to retain vehicles (below these cells rows 83 through 147).

How to fill out Attachment X4 “Risk”

The tabs on the bottom of Attachment X4 are identified as Action Plan Example; Action Plan Drivers; Action Plan Vehicles; Action Plan Fuel; Action Plan Unions; and Action Plan Pandemic.

Tab “Action Plan Example” The Offeror does not need to make changes to this tab. It is to serve as an example of how to fill out the Action Plan Example tab

Tabs “Action Plan Drivers; Action Plan Vehicles; Action Plan Fuel; Action Plan Unions; and Action Plan Pandemic”Address the Action Plan to address the Problem identified in the orange cell.

Use up to but no more than 500 words.

Address the Impact of the Problem identified in the orange cell.

Use up to but no more than 500 words.

Insure the Problem in the orange cell matches the tab name you are in.

Address the Risks Associated with your Proposed Action Plan for the Problem identified in the orange cell.

Use up to but no more than 500 words.

Address the Timeline for Execution of your Action Plan for the Problem identified in the orange cell.

Use up to but no more than 500 words.

How to fill out Attachment X5 “Price” The tabs on the bottom of Attachment X5 are identified as Summary and Daily Commute.

Tab “Summary” The Offeror does not need to make changes to this tab it is self tabulated.

Tab “Daily Commute” The Offeror will fill in each cell highlighted in yellow. No other cells shall be updated.

Note: When filling in the prices in the yellow cells do not use an equation. Instead type in the price to the second decimal place for accurate pricing.

Example: In Cell F17 instead of using the equation “=F9*1.03” type in the value of that equation and round to the second decimal point.

image5.png image6.png image7.png image8.png image9.png image10.png image11.png image12.png image13.png image14.png image15.png image16.png image17.png image18.png image19.png image20.png image21.png image22.png image23.png image24.png image25.png image26.png image27.png image28.png image29.png image30.png image31.png image32.png image33.png image34.png image35.png image36.png image1.png image2.png image3.png image4.png