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

Ag Bus 260

Final

300 Points

3/16/2016

Dr. Hurley

Instructions: Locate your test folder which can be found at: L:\Shared\AGB 260-01. 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 milk production for five different states. 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.Hopefully, you will not FIND() this problem too difficult.

A1. In cells A22377:A22381 provide the states that are in the data set in alphabetical order. It is ok to use another sheet to get your answer. In cells B22377:B22381, sum up the total MILK - PRODUCTION, MEASURED IN LB for each state and convert this to tons by dividing by 2000 (10 points).

A2. In cell D22376,use a single formula to calculate the average length of the data items in cells D2:D22374. If you need to use an extra column to get your answer, you will receive half credit(10 points).

A3. In cells M1:M15, use a formula to put which each data item is measured in. For example, you should notice that MILK - PRICE RECEIVED, MEASURED IN $ / CWT is measured in $ / CWT. Hence, cell M1 should have $ / CWT. Cell M2 should have LB. If you need to type this in separately, you will receive half credit.(20 points).

A4. In cells G2:G22374, allocate the correct unit of measurement for the data items listed in column D. For example, cell G2 should have PCT, cell G3 should have $/ CWT, etc. See Figure 1 for an example of the answer (20 Points).

A5. In cells E2:E22374, write a formula that strips out the “MILK – “ or “MILK, “ and what the data item is measured in, e.g., MEASURED IN PCT, to leave only the non-redundant data item part. For cell E2, this would be FAT TEST; for cell E3, this would be Price Received; etc. See Figure 1 for an example of an answer. If you use a Vlookup to do this problem, you will lose 10 points. If you add an extra row to get your answer, you will lose 15 points(25 points).

Figure 1: Example Answer for Question A4 and A5

A6. In cells A22392:A22404, list only once each Data Item 2 in alphabetical order. Make sure that you do not have PRODUCTION in this list. See Figure 2 for an Example of the answer. It is ok to use another sheet to get your answer (5 points).

A7. In cells A22391:M22404, create a dynamic table of the average of each Value in Column F,which will change when the data item, the state, the month, the beginning Year, and the Ending Year change. The table should have the following criteria: a) it should be based on the listed Data Item 2 in cells A22392:A22404; b) it should be based on the months listed in cells B22391:M22391; c) the State listed in cell B22387; d) the Beginning and Ending Years in cells B22388 and B22389, where the Beginning Year should have a >= sign and the Ending Year should have < sign. In your formula, you need to usecells B22391:M22391. If you use cells B22406:M22406, you will lose five points. Your values should be rounded to 4 decimal places, not formatted to look rounded. If you get an error, you should put N/A(30 points).

Figure 2: Example Answer for Question A7 and A8 for California between 1930 and 2016

Question B(Total Points: 120): The data presented in this problem is USDA data on monthly production of milk measured in pounds for the states of California, Idaho, New York, Pennsylvania, and Wisconsin. The data is ordered by year, then month, and then by state. It is helpful to know for this question that the data in Column E is structured in such a way that each month for a particular state is separated by 5 cells and one year is separated by the next for a particular state by 60 cells. Note that to convert pounds to tons, you need to divide by 2,000.

B1.In cellsJ3:U83, write a single formula that can be copied and pasted to transform the MILK - PRODUCTION, MEASURED IN LB data in Column E to milk production measured in tonsfor Wisconsin in tabular form. You may also want to create the formula starting in cell U3. If you need to sort the data to get your results, you will lose 15 points(30 points).

B2.In cells AC3:AN83, write a single formula that can be copied and pasted to transform the MILK - PRODUCTION, MEASURED IN LB data in Column E to milk production measured in tons for Pennsylvania in tabular form. In cells AV3:BG83, write a single formula that can be copied and pasted to transform the MILK - PRODUCTION, MEASURED IN LB data in Column E to milk production measured in tons for New York in tabular form. In cells BO3:BZ83, write a single formula that can be copied and pasted to transform the MILK - PRODUCTION, MEASURED IN LB data in Column E to milk production measured in tons for Idaho in tabular form. In cells BO3:BZ83, write a single formula that can be copied and pasted to transform the MILK - PRODUCTION, MEASURED IN LB data in Column E to milk production measured in tons for California in tabular form. If you need to sort the data to get your results, you will lose 5 points (10 points).

B3. For each of the tables you just created in questions B1 through B5, calculate the Maximum, Minimum, and Average for each year in cells X3:Z83 for Wisconsin, in cells AQ3:AS83 for New York, in cells BJ3:BL83 for Pennsylvania, in cells CC3:CX83 for Idaho, and in cells CV3:CX83 for Wisconsin (5 points).

B4. Using conditional formatting highlight the largest value with an orange fill for each year in the Wisconsin table you created in question B1. Using conditional formatting highlight the smallest value with an orange fill for each year in the Pennsylvania table you created in question B2 (5 points).

B5. Using conditional formatting highlight the duplicate values with ared fill and dark red font for each year in the New York table you created in question B3. Using conditional formatting highlight the top 10% of values with a yellow fill and dark yellow font for each year in the Pennsylvania table you created in question B4 (5 points).

B6. Suppose you want to investigate how much milk was produced in California and Wisconsin for the periods January - April, May - August, and September - December for each year. In cells CZ3:CZ245, write a formula that uses the information in cells DD3:DG5, to put the four month periods accompanied by the corresponding year where the year starts at 1935 and goes to 2015.The monthly period should be followed by a comma and a space before you put the year. The first three cells should have January - April, 1935; May - August, 1935; and September - December, 1935. See Figure 3 for an example of what the answer should look like (10 points).

Figure 3: Example Answer for Question B6 and B7 for California between 1935 and 2016

B7. Write a formula that sums the milk for each period given in Column CZ for California and Wisconsin. See Figure 3 for an example of what the answer should look like(15 points).

B8. In cell G86, write a formula that checks to see if the sum of your values in Column E divided by 2,000 is equal to the sum of all your values in the tables you created for questions B1 through B2. The formula should return Yeah if the sums are equal and Boo if the sums are different (10 points).

B9. Given the Maximum, Minimum, and Average information from the Wisconsin table you created in question B1, you need to recreate Figure 4 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 three points per occurrence. 1) You should create a stock chart out of the Maximum, Minimums, and Averages. 2) Your Maximum data points should have a green line connecting them, while your minimum points should have a red line connecting them. 3) Your averages should have grey triangle markers. 4) Your vertical axis should go from 250,000 to 1,450,000 and have grey gridlines every 100,000. Your numbers should have commas. Your font on your numbers should be red. You should have your values on your vertical axis denoted in Thousands and have the word showing up in the upper left corner. 5) Your vertical axis should show the years from 1935 to 2015, skipping by every five years. Your numbers should be at a 45 degree angle. Your font on your numbers should be green. 6) Your vertical axis title should be Tons. Your horizontal axis title should be Year. 7) Your legend should show up between your upper two vertical lines to the left portion of the graph and have the appropriate titles. 8) Your background to your chart should be Light Gradient- Accent 4 with the Type be Linear, and the direction being Linear Up. The top and bottom bevel should be a Convex format. 9) You should have a green rounded border that is 5 point in Width. 10) Your title for this chart should be WISCONSIN MILK PRODUCTION that has a white background and is 14 point Calibri Font(30 points total).

Figure 4: Example of answer for questionsB12

Question C (Total Points: 60): Suppose the data in question C has sales data in $100’s for each salesperson in your company. Your boss is having a hard time reading the table and would like you to set it up to highlight a particular cell given input of a particular salesperson’s name and year. It is Ok to use extra cells to achieve these tasks.

C1. In cell B17, create a drop down list for the salespeople’s names based on the list given in B2:B15. In cell B18, create a drop down list for dates that are in cells B2:AF2. Choose Ignacio in cell B17 and 1988 in cell B18. You should note that your first item in your drop down list is a blank space. You should initially choose the name Ignacio and the date 1988(10 Points).

C2. In cell B36, return the person’s sales based on the information put into B17 and B18. Make sure that it returns the true sales formatted as currency rounded to two decimal places. Hence, for Ignacio in 1988, he had sales of $3,262.00. If a name or date is not chosen, put the statement Please choose a name and a date(10 Points).

C3. In cell B22, write the actual cell address in the table that corresponds to the name and the date in cells B17 and B18. Given any name chosen in B34 and a year chosen in B35, highlight in the table the sales made by the individual for the year using conditional formatting that has ayellow background in with a Font that is Black, Bold, and Italics, and has a line border (see Figure 5 for an example of Beatrice being selected for 1989). Make sure that this dynamically changes based on the person and date you put in(10 points).

C4. In cells A1:AG16, use conditional formatting to fill all blank cells with a green background (5 points).

C5. Given any name chosen in B17 and a year chosen in B18, in the table defined by B2:AF15 make the cell that has the selected person’s sales datafilled with a light green color and the values should be bold and italic using conditional formatting. Have all other sales data in the table have a white font so they are not showing(see Figure 5 for an example of Ignacio being selected for 1988). Make sure that this dynamically changes based on the person and date you put in(15 points).

C6. Use conditional formatting to fill the cell of the date selected and all cells related to the sales data for the people above the selected salesperson for that selected date with a dark green fill. Use conditional formatting to fill the cell of the salesperson selected and all cells related to the sales data for dates less than the date selected for that selected salesperson with a dark green fill. Make sure that your formatting for the previous question is showing(10 points).

Figure 5: Example of problem C’s answer

Extra Credit. In cell B25, write a formula that returns the ranking of the salesperson selected for the particular year that is selected. This should change dynamically based on the year chosen and the salesperson chosen. You should use RANK.AVG(). This must be completely correct to get the points (30 Points).