“Bargain... anything a customer thinks a store is losing money on. ” - Kin Hubbard

Compute the total cost of delivered materials required to make 2,000 yd³ of concrete with a mix ratio of 1:2:2 with a w/c ratio of 0.4 (assume that concrete weight 150 lbs/ft³). The first step would be to copy and convert your concrete proportioning worksheet developed in Assignment 6 to compute the mix components for a given volume. You may use the worksheet shown below as a template.

Next, use the ArcGIS concrete material database (Concrete_materials) to estimate the distance from each material vendor to the Engineering Science Building (the delivery point) and collect data for unit cost ($/ton/mile) and inventory volume (tons) for each concrete material. With data for unit cost, volume, and delivery distance for each material, estimate the minimum delivery cost for the required concrete materials. The total cost of each delivered material may be estimated as:

Total Cost ($) = Unit Cost ($/ton/mile) x Volume (tons) x Distance (miles)

For convenience, vendors require the estimated delivery distance to be rounded up to the next mile (for example, 5.24 miles is 6 miles). Also, note that if one vendor cannot supply all the material, several vendors may be used. For simplicity, compute the minimum cost for each material and then add the results together to estimate the minimum total cost.

1. Connect to the CPGIS server and add the Shelby County 2009 aerials to your map (click here for details on connecting to the CPGIS server).
    

2. Download the zipped geodatabase file (Concrete_materials.zip). Unzip the file into your local Documents/ArcGIS folder.
    

3. Add all the layers (Construction_site and Materials) from the site geodatabase over the Shelby County aerials (click here for an example of adding data to a map).
    

4. Right-click on the Materials layer in the Table of Contents and click on Open Attribute Table. You should notice that the eight data fields available. We are interested in Cost_($/ton), Volume, Material, and Name.
   
   
   
    

5. Right-click on the Material field and Sort Ascending to order the labels of the surveying grid points.
   
   
   
    

6. Export this database to an Excel file by clicking on the Table Options (top-right button on the Table window) and then on Export.. The following window will appear:
   
   
   
    

7. Click on the folder button on the right-hand side of the window and change the file type and name. It is important that the Save as type: is text file. This makes it easy to import into Excel.
   
   
   
    

8. Open Excel and open the Export_Output.txt file as input. Use a comma as your column delimiter and you should get a spread sheet that looks like the following:
   
   
   
    

9. Add a column for distance between each vendor and the Engineering Science building at the University of Memphis.
    

10. Using the Identify button and locate each vendor on the map and the Engineering Science building at the University of Memphis.
     

11. Click on the Measure button at the top of the ArcMap interface. The following window will appear:
    
    
     

12. Click on the + button and then use the pointer to measure the distance between the U of M and each material vendor.
     

13. Add the distance data to the Excel spreadsheet.
     

14. Using the following  equation: Total Cost ($) = Unit Cost ($/ton/mile) x Volume (tons) x Distance (miles) compute the cost for each vendor to deliver material.
     
15. Use your spreadsheet to estimate the minimum cost to supply the required materials for 2,000 yd³ of concrete.
     

16. Print out the coversheet and a copy of your spreadsheet. 

Print coversheet for homework

This website was originally developed by Charles Camp for CIVL 1101.
This site is Maintained by the Department of Civil Engineering at the University of Memphis.
Your comments and questions are welcomed.