2. (30 points) Table 1 (on page 4) shows monthly prices for three agricultural products, Wheat, Grain Sorghum and Alfalfa from Jan 2006 to December 2007 Add a new worksheet to your spread sheet, name it "2-Initial Data" and enter following data into Excel. Add another worksheet titled "2- Moving Average" and find a 3-Month Moving Average (titled 3-mo moving) a 5-Month Moving Average (titled 5-mo moving) a 3-Month Weighted moving Average (titled 3-mo Weight) a 5-Month Weighted Moving Average (titled 5-mo-Weight) for all three products. (Hints: 1: Your first weighted average for each product will be in month 3. Multiply the price for month 3 by three. This total is added to the price in month 2 multiplied by 2 and the price in month 1 multiplied by 1. The total of these should be divided by 6 (the sum of the weights 1, 2 and 3). 2: for each product add its name to column title, for ex-ample for 3-month moving average of wheat, the column is titled "wheat 3-mo Weight". 3: you need to reference to cells/Range on "2-Initial Data worksheet. 4: Your table should have 24 rows, and 12 columns for moving averages). Prepare line charts that shows the 5-month moving average, and the 3-month weighted moving average prices for each product (Hint: you need to present three charts total each for one product) X Post Atte X MUGA X e Ellucian X Look Upx Homew ntent/2084107/viewContent/31127505/View Alfalfa Ston 103 105 104 115 119 125 4.15 158 Table 1: Price For Wheat, Grain Sorghum and Alfalfa Month Wheat S/bu Grain Sorghum Sewt Jan-06 3.75 3.26 Feb-06 3.85 3.55 Mar-06 4.03 3.58 Apr-06 3.58 May-06 4,62 3.89 Jun-06 4.49 3.68 Jul-06 4.70 3.99 Aug-06 4.45 3.65 Sep-06 4.58 4.32 Oct-06 5.10 5.34 Nov-06 5.07 5.88 Dec-06 4.95 6.02 Jan-07 4.58 6.15 Feb-07 4.86 6.53 Mar-07 4.74 6.19 Apr-07 4.72 6.01 May-07 4.51 6.03 Jun-07 5.02 6.17 Jul-07 5.28 5.78 Aug-07 6.02 5.65 Sep-07 7.45 5.88 Oct-07 7.81 5.96 Nov-07 7.71 6.3 Dec-07 8.95 6.89 142 133 150 152 146 165 169 152 142 140 150 148 135 144 143 159 152 4 14 2. (30 points) Table 1 (on page 4) shows monthly prices for three agricultural products, Wheat, Grain Sorghum and Alfalfa from Jan 2006 to December 2007 Add a new worksheet to your spread sheet, name it "2-Initial Data" and enter following data into Excel. Add another worksheet titled "2- Moving Average" and find a 3-Month Moving Average (titled 3-mo moving) a 5-Month Moving Average (titled 5-mo moving) a 3-Month Weighted moving Average (titled 3-mo Weight) a 5-Month Weighted Moving Average (titled 5-mo-Weight) for all three products. (Hints: 1: Your first weighted average for each product will be in month 3. Multiply the price for month 3 by three. This total is added to the price in month 2 multiplied by 2 and the price in month 1 multiplied by 1. The total of these should be divided by 6 (the sum of the weights 1, 2 and 3). 2: for each product add its name to column title, for ex-ample for 3-month moving average of wheat, the column is titled "wheat 3-mo Weight". 3: you need to reference to cells/Range on "2-Initial Data worksheet. 4: Your table should have 24 rows, and 12 columns for moving averages). Prepare line charts that shows the 5-month moving average, and the 3-month weighted moving average prices for each product (Hint: you need to present three charts total each for one product) X Post Atte X MUGA X e Ellucian X Look Upx Homew ntent/2084107/viewContent/31127505/View Alfalfa Ston 103 105 104 115 119 125 4.15 158 Table 1: Price For Wheat, Grain Sorghum and Alfalfa Month Wheat S/bu Grain Sorghum Sewt Jan-06 3.75 3.26 Feb-06 3.85 3.55 Mar-06 4.03 3.58 Apr-06 3.58 May-06 4,62 3.89 Jun-06 4.49 3.68 Jul-06 4.70 3.99 Aug-06 4.45 3.65 Sep-06 4.58 4.32 Oct-06 5.10 5.34 Nov-06 5.07 5.88 Dec-06 4.95 6.02 Jan-07 4.58 6.15 Feb-07 4.86 6.53 Mar-07 4.74 6.19 Apr-07 4.72 6.01 May-07 4.51 6.03 Jun-07 5.02 6.17 Jul-07 5.28 5.78 Aug-07 6.02 5.65 Sep-07 7.45 5.88 Oct-07 7.81 5.96 Nov-07 7.71 6.3 Dec-07 8.95 6.89 142 133 150 152 146 165 169 152 142 140 150 148 135 144 143 159 152 4 14
