Unformatted text preview: CEE 3804: Computer Applications in Civil Engineering Spring 2011 Assignment 3: Pivot Table Practice and Linear Programming
Date Due: February 17, 2010 Instructor: Trani Problem 1
Use the construction company assets file provided in class (see syllabus page) and answer the following questions using Pivot
Tables. Clearly present the results of the pivot tables as a screen capture in your homework solution paper. Task 1:
What is the average value for the Caterpillar 160H vehicles?
What is the average number of miles in the Caterpillar Cat 775F equipment that is active?
What is the average number of miles and value of all the equipment owned by the company? Task 2:
What is the number of vehicles that are inactive at the company when the survey was taken?
What is the average value for Caterpillar Cat 775F located in Richmond that are inactive?
If the company sells al the Caterpillar Cat 775F inactive equipment in Richmond, what is the new average value of the remaining
Cat 775F equipment elsewhere? CEE 3804 Trani Page 1 of 3 Problem 2
You are in charge of a company that is willing to mine the Moon (yes the Moon) to extract metals to be used in the Earth. A
geotechnical engineering assessment of the rocky composition of the Moon at 4 proposed excavation sites produces the
Site Iron Copper Titanium Aluminum Tranquility 0.20 0.40 0.15 0.25 Erasmus 0.35 0.35 0.08 0.22 Copernicus 0.20 0.60 0.10 0.10 Galileo 0.30 0.45 0.10 0.15 The table presents the fractions of each product extracted from the Moon for each ton excavated.
Transporting all materials back to Earth requires a fleet of dedicated cargo ships. Transportation costs cannot be ignored.
Materials vary in density thus making transportation costs distinct. The estimated transportation and extraction costs for the
materials are: $13,500, $12,700, $15,000 and $11,200 per ton for Iron, Cooper, Titanium and Aluminum, respectively. Back on
Earth, the market price of each ton of material are: $17,500, $14,800, $17,200 and $14,200 for Iron, Cooper, Titanium and
Aluminum, respectively. According to market demand needs, it is desirable to produce 1,200 tons per month of Cooper and 800
tons of Titanium. Task 1:
Formulate the problem as a linear programming problem. Clearly state the decision variables, the constraint equations and the
objective function. Task 2:
Solve the in Task 1 using Excel solver. Comment on the answer obtained. How do you verify the answer? Task 3:
Suppose that market demands change. Modify the problem solved in Task 2 and assume that a minimum of 1,300 tons of Iron is
also needed. Solve and compare the answer with Task 2. CEE 3804 Trani Page 2 of 3 Problem 3
Refer to the water management pollution control problem described in the class notes and explained in class. One aspect that
was neglected in the previous problem is the cost to transport the removed materials from each treatment plant to a site that can
dispense the products safely. After a survey of pollution removal methods you find the following removal costs for each of the five
sources mentioned in the program:
Source Removal Cost ($/kg) Transportation Cost ($/kg) River A 244.2 120.2 River B 260.3 134.4 River C 205.8 136.3 City 1,456.1 128.2 Airport 543.3 234.9 Assume that under new stricter EPA rules we would like to remove at least 55,000 kgs. of the baseline pollution going into the
lake. Also, the airport and city managers want to participate in the pollution removal program by removing at least one half of
their baseline pollution allocations per year. Other plants need to remove at least one tenth of their pollutants as a minimum.
Formulate the problem as a linear programming problem. Solve the new problem and state the optimal cost. Task 2:
Solve the new problem using Excel solver and state the optimal cost of both removing and transporting the removed materials to
a processing site. Task 3:
For the problem in Task 1, assume that the city is exploring to invest in a new pollution control treatment plant and technology.
The new plant would cost $2,000,000 and if built, it will cut pollution generation by 75%. Using principles of engineering
economics (i.e., Net Present Value), state whether or not the investment will payoff in 30 years of operation. Assume an annual
interest rate of 6%. CEE 3804 Trani Page 3 of 3 ...
View Full Document