Question a (Total Points: 120): the Data Presented in This Problem Is USDA Data on Rice

Ag Bus 260

Final

300 Points

6/7/2016

Dr. Hurley

Instructions: Locate your test folder which can be found at: L:\Shared\AGB 260-03. Before you open the file, you should rename the file to be your first name-last name, e.g., Sean-Hurley. Inside this folder you will find an Excel spreadsheet that has fourtabs which read: QuestionA,QuestionB, and QuestionC. Each one of these relates to the questions below. Follow the instructions given below. Anytime you are told how to do something, you must do it based on the specifications to get full credit. If you are not told how specifically to do the problem/question, you are free to find the answer any way you would like. You may need to add rows or columns to get your answers. You need to explicitly highlight in your spreadsheet in some form the answer to each question so it can be found quickly. In some instances it may be best to put your answer in a textbox next to the work you did to find the answer to the problem. When you are finished with the exam, save it to your folder on the L drive and then email me a copy. For any questions calling for specific colors, if you are color blind, please create a textbox indicating so next to the particular answer and choose whatever shades you want when a specific color is asked for.

Question A (Total Points: 120):The data presented in this problem is USDA data on rice production across the US. You need to generate all the data in the columns with formulas. Any data not generated by a formula or if you need to add an extra column of information will only get 50% of the points.

A1. In cells F2:F8219, you need to write a single formula that will put the correct Measured In value from cells E2:E8219. It should be noted that any cell in column E that has the word ACRES in it is measured in ACRES. Cells N2:N25 shows what each Measured in should be for the corresponding Data Item in Column E. If you use a Vlookup function you will only get 10 points (20 points).

A2. In cells I2:I8219, you need to write a single formula that takes the information in cells H2:H8219 and returns only the information in the parenthesis. If there are no parenthesis, then you should return Not Specified. Cells R2:R47 shows what should occur for each Domain Category in Column H. If you use a Vlookup function you will only get 10 points(20 points).

A3. In cells J2:J8219, you need to write a single formula that takes the information in cells H2:H8219 and classifies it as either Not Specified, Chemical, or Fertilizer. Cells Q2:Q47 shows what should occur for each Domain Category in Column H. If you use a Vlookup function you will only get 10 points (20 points).

A4. In cells A2:K8219, highlight every row using conditional formatting a light green color that has a $ or a $/CWT in cells F2:F8219. If you do not use conditional formatting, then you will only get 5 points(10 points).

A5. In cells B8221:B8223, count the number of times each of the corresponding items in cells B8221:B8223 shows up in cells J2:J8219 (10 Points).

A6. In cells B8229:K8252, find the average value from column K for each corresponding pair of state (Column C), data item (Column E), and period (Column B). Your period should be dynamic and change with cell A8226. Make sure to round your answers to one decimal place. If an error occurs the cell should be left blank(40 points).

Figure 1: Example Answer for Question A6 Given the Period Selected is Year

Question B(Total Points: 130): The data presented in this problem is USDA data on acres harvested of rice.

B1.In cells M2:V119, write a single formula that sums up the total acres harvested for each corresponding state for each corresponding year. If you are unable to do this problem, you could paste the values into the spreadsheet from the Question B Helper Tab. If you do so, you will only get 1 point. If you need to write more than 1 formula, you will only get 5 points(10 points).

B2.In cells M122:V145, write a single formula that takes the acres harvested in cells M2:V119 and finds the averages of the total acres harvested for each corresponding state for each corresponding period. You need to use cells L122:L145, or you will lose 10 points. If you need to write more than 1 formula, you will only get 10 points. If you are unable to do this problem, you could paste the values into the spreadsheet from the Question B Helper Tab and get 3 points(30 points).

B3. Given the information from the table you created in part B2, you need to recreate Figure 2 as closely as possible. Each of the following is a must for the figure. If you do not do any of the criterion listed below, you will lose up to four points per occurrence. A) Using the data in cells L121:R126, create a cluster column chart (4 points). B) The horizontal axis should have the years on them (4 points). C) The Arkansas data should be represented by a secondary axis and have a gap width of 40% (4 points). D) The fill for the Arkansas bar should have a transparency of 50%. Your horizontal data labels should be at 45 degrees (4 points). E) Your horizontal axis should have a grey 11 point glow with 60% transparency (4 points). F) Your left vertical axis title should say Other States, while your right vertical axis title should say Arkansas; there should be no horizontal axis title (4 points). G) Your Chart title should be Average Rice Acres Harvested that has a white background fill and a green border (4 points). H) Both your vertical axes should be in Thousands and have commas (4 points). I) Your Arkansas vertical axis should start at 0 and increase by 300 until it reaches a maximum of 1,800. Your Other States vertical axis should start at 0 and increase by 100 until it reaches a maximum of 600. You should note that these increments are in terms of 1,000 (4 points). J) Your chart background should be Parchment with a green border that has a 4-Point width and Hard Edge Top and Bottom Beveling (4 points)(40 Points Total).

Figure 2: Example Answer for Question B3

B4. Using a single formula and cells Y4:Z27, write the following statement for cells AA4:AA27—Average acres harvested from [Beginning Year] to [Ending Year] where the terms in bracket represent the information from columns Y and Z. For example, in cell AA4, you should have Average acres harvested from 2006 to 2010. Do the same thing for AA31:AA88(10 Points).

B5. Using a single formula, the offset function, and the table you created for question B1, find the five year average of the acres harvested for each statelisted in AB3:AG3for each period. If you need to use cells AB2:AG2 or Y4:Z27, you will lose 15 points. If you use more than 1 formula, you will lose 15 points. You should have the same results as question B2(30 points).

B6. Using a single formula, the offset function, and the table you created for question B1, find the two year average of the acres harvested for each state listed in AB30:AG30for each period. If you need to use cells AB29:AG29 or Y31:Z88, you lose 5 points. If you use more than 1 formula, you will lose 5 points (10 points).

Figure 3: Example of problem B5 answer

Figure 4: Example of problem B6 answer

Question C (Total Points: 50): The data in A1:DO11 represents the acres harvested by each state for each corresponding year.

C1. In cell B13, create a drop down list for all the states. In cell B14, create a drop down list for all the dates. Choose Louisiana and 2009 for each of these corresponding cells(10 Points).

C2. In cell B16, write a single formula that will dynamically pull the harvested acres for the state and year chosen in cells B13 and B14. If the value ends up being zero, you should write No Acreage in the cell. The value should change when B13 and B14 change(10 Points).

C3. In cell B18, write a single formula that will dynamically pull the cell designation for the harvested acres for the state and year chosen in cells B13 and B14. The value should change when B13 and B14 change (10 Points).

C4. Given any state chosen in B13 and a year chosen in B14, in the table defined by A1:DO11 make the one cell to the left and one cell to the rightof the selected state’s harvested data purple. Make the one cell above and one cell below the selected state’s harvested data yellow. Make the cell of the selected state’s harvested data blue with a green border. See Figure 5 for an example of Louisiana and 2009. Make sure that this dynamically changes based on the state and date you put in for cells B13 and B14(20 points).

Figure 5: Example of problem C’s answer