Study the information about cows and coliform (including the given data) below and
then (using Excel) draw up the necessary graph as per instructions underneath these.
About cows and coliform
Cows are an important source of biological contamination of fresh water by enteric (intestinal)
pathogens, such as anthrax, salmonella, tetanus, giardia and E. coli O157:H7, present in the
cow's manure. It is expensive and time consuming to test for each of the approximately 100
waterborne enteric pathogens, so an indicator species, fecal coliform (FC), is tested instead.
Cows produce about 1011 (100 billion) colony forming units of fecal coliform per cow per day,
an order of magnitude greater than sheep and hogs.
The Granger Drain is a system of above-ground drainage ditches and subsurface tile drains and
pipes, covering about 18000 acres that eventually discharges into the Yakima River in east�central Washington State. This semi-arid region receives about 8 inches of rainfall per year,
and is supplemented with about 36 inches of irrigation water per year, mostly delivered by the
traditional method of water running across the ground in furrows (rills). In addition to orchards
and various crops, this area also hosts a number of dairies and animal feed operations (feedlots).
The Yakima Valley is one of the most productive agricultural areas on Planet Earth.
Waterborne pathogens are quickly washed into the Granger Drain system, and hence into the
Yakima River, during the irrigation season. In order to quantify the relationship between cows
and contaminated runoff, the South Yakima Conservation District measured fecal coliform
concentrations in water from drains at the outlets of 8 different sub-basins within the Granger
Drain system from 1990 to 1992. Repeated measurements of fecal coliform are given as mean
values in the table. The Washington State Department of Ecology reviewed the data and
compared the FC counts with a number of other variables. The acreage covered by dairies and
feedlots was the only parameter, out of several studied, which correlated well with the fecal
coliform data.
Reference: Washington Department of Ecology, "Granger Drain Fecal Coliform Bacteria Total
Maximum Daily Load Assessment and Evaluation"; WA DOE Report, 2001, draft.
Fecal coliform in the Granger Drain, WA
sub-basins within the larger Granger drain system
acreage of dairies and feedlots within the sub-basin
fecal coliform counts in colonies per 100 millilitres.
sub-basin # acreage fecal coliform means
(acres) (cfu/100ml)
1&2 89 6161
3 106 7477
4 52 4336
5 38 3612
6 0.01 3065
7 91 5446
8 50 3800
Instructions:
Using your knowledge about the elements of a proper and non-misleading graph and Excel,
draw up the relevant graph (correct type) to show the correlation between the acreage of cows
and the amount of FC in the water as measured over the 8 sub-basins.
Apply an A4 landscape layout and follow the exact design as below.
Surname,Name: Student number: Group:
Your graph area must cover at least 80% of the A4 landscaped page.
Your graph itself must cover at least 90% of the graph area in the above layout.
The data table must be used as given above. I.e. sorted in ascending order per sub�basin number.
The correlation must be described in detail and your answer motivated.
Name the tab in your Excel workbook “Cows vs Coliform”.
Data table Graph area
Description
of
correlation.
Save your file as Group_Surname,Initials_Studentnumber_Cows.xlsx. (E.g.
Sharks_Letolo,S_2020123456_Cows.xlsx)
Upload your file to Blackboard using the “Submission: Cows and Coliform graph”
link.
To create the graph, follow these steps:
1. Open Excel and enter the data provided in the table (sub-basin #, acreage, and fecal coliform means) into three columns.
2. Select the data in the columns (excluding the headings) and insert a scatter plot. Make sure to label the axes correctly (acres on the x-axis and fecal coliform counts on the y-axis).
3. Add a trendline to the scatter plot to show the correlation between the acreage of cows and the amount of fecal coliform in the water.
4. Format the graph to cover at least 90% of the graph area in the provided layout.
5. Write a description of the correlation observed in the graph. Motivate your answer by explaining why the acreage of cows is correlated with the amount of fecal coliform in the water based on the information provided.
6. Name the tab in your Excel workbook "Cows vs Coliform" and save the file as per the given naming convention.
7. Upload your completed Excel file to Blackboard using the specified link for submission.