Activity 5
AGB 421
Dr. Sean Hurley
100 Points
Due: 4/23/10
Learning Objectives
q To learn how to test simple statistical hypothesis using Excel
q To do research to find needed information
q To use Excel's data analysis tool to test various hypothesis
q To create Excel worksheets that will help test different hypotheses
Introduction:
Today’s activity will familiarize you with using Excel’s ability to test statistical hypotheses. You will be required to gather and analyze data from a USDA website regarding Bittermelon. To get full credit, you need to submit any spreadsheets you created and any write-up should either be in a separate Word document or in a text box in Excel.
Suppose you are a business analyst who works for Rabo Bank. The general loan officer has just received a report from a Cal Poly professor that seems to show that Bittermelon could be a lucrative crop and therefore a good crop to lend money to. The only problem with the analysis is that it only examined prices in 2008. The loan officer would like to expand his understanding of prices for Bittermelon over three years. You have been tasked to find if the last three years of historical price data would warrant the loan officer taking a risk in the crop.
To start your analysis, you need to find information regarding prices of Bittermelon. One location of this information is at: http://www.marketnews.usda.gov/portal/fv. Your first task is to gather data for prices from the beginning of 2006 to the end of 2008. You want to focus on terminal daily prices from San Francisco. Make sure that you create a separate worksheet for each year's data giving an appropriate label to signify what is in the worksheet. (10 Points)
Now that you have the data of interest, you want to splice together the three data sets you acquired into one worksheet entitled "Bittermelon Data". Your next goal is to delete any rows or columns that will not be useful to you. You want to focus on only California origin products. You want to only examine 30 and 40 lb carton boxes for both the high and low prices received. (10 Points)
Your next task is to calculate the per pound price for both the low and the high prices. Use an If statement to help you transform the per carton prices to per pound prices. (10 Points)
Now that you have the prices per pound, calculate the mean, variance, standard deviation, total observations, the maximum, and the minimum for each categories of prices (Low vs. High) using Excel functions. Verify your results with the data analysis tool. (10 Points)
Noticing that the Cal Poly professor used a Davis cost study located at http://coststudies.ucdavis.edu, you decide it would be in your best interest to examine it. Looking closely at the Ranging analysis worksheet, you see that the Davis extension agent who put the report together used a price of $6 per 30 lb carton. You want to test to see if this price is significantly different at the 95% level of confidence from the prices that have been received over the last three years for both the low price and high price categories. To do this, calculate the t-statistic and the t-critical value using Excel and write an if statement that compares the two to see if your hypothesis is true or false. Note: you should assume that you are using a two-tailed test. (10 Points)
Having found that the average of the last three years of data is significantly different than the base price used by the Davis study, you decide you want to check the price that seems to almost always give you a positive return. Use this value and test it to see if it is significantly different from your averages you calculated for the low and high price categories. You should notice that the actual value the Davis study uses should be significantly higher than your average. (10 Points)
Having stared out the data for awhile, you think you notice a pattern in the data. It appears that the prices on a per pound basis are larger for the 30 lb carton in comparison to the 40 lb carton. If this is true, then it would be in the best interest of a producer to package her product in 30 lb cartons. Create a new worksheet that separates the two types of carton. Examine for the high and low prices for each type of carton, the mean, variance, standard deviation, total observations, the maximum, and the minimum using Excel functions. (10 Points)
Make a histogram for the price per lb for each carton size and each price category. Put each of them on the same worksheet so you have a visual representation of the data located in the same place. (10 Points)
The final part of your analysis is to examine whether the means for the two carton sizes are statistically equivalent to each other. You will do this by testing to see if you should assume equal variances between the two types of carton. You can do this using an F test. Once you have decided whether the variances are equal or not, apply the appropriate hypothesis test using Excel's data analysis tool. Verify that you get the same result by creating a worksheet that will calculate the test statistic and the critical value. Write an If statement to check to see if the hypothesis of equal means holds. (10 Points)
In a paragraph, explain the results from your hypothesis testing. Would you consider this a safe crop to invest in? Please explain. One point will be taken off for each spelling or grammatical error. (10 Points)