Ag Bus 260
Midterm 2
125 Points
5/28/2015
Dr. Hurley
Instructions: Locate your test folder which can be found at:L:\Shared\AGB 260. Before you open the file, you should rename the file to be your first name-last name.XLSX, e.g., Sean-Hurley.XLSX. Inside this folder you will find an Excel spreadsheet that has 4worksheet tabs related to each of the questions below. Follow the instructions given below and reproduce the graphs to the best of your ability. All graphs and text box answers should be saved in the spreadsheet. When you are finished with the exam, save it to your folder on the L drive and then email me a copy.
Question 1 (Total Points: 40):Please reproduce Figure 1 below as closely as possible from the data given in the Question 1 worksheet tab. The Line chart was made from US Avocado Prices on a ton basis.
Figure 1:
1A. You should create a line chart with the dates ranging from 1988 to 2011 and increasing as you read from left to right. The first date shown should be 1989. The dates should be slanted at negative 45 degrees and have two years between each. It should be noted that you may need to resize your chart to accomplish this last task(5 Points).
1B. You should create a chart title that reads US Avocado Prices Per Ton. You should have a dark green double line border that is 6 points in width and has a join type that is round. The title should have a green 18 point glow around it(5 Points).
1C. You should have a legend that shows up on the top of the chart(5 Points).
1D. Youraxis numbers should be on the left, be formatted as currency with two decimal places. The high point on the axis should be $3,200 and the low point should be $0.00. There should be $400 between each number(5 Points).
1E. Your lines representing each state should have Markers that are size 8 where California’s are Square, Hawaii’s are Diamond, and Florida’s are Triangles (5 Points).
1F. Add a Trend Line to your California data. Put in the equation for the line and the R-Squared term. This equation should be located above the second line from the top, while the R-Squared equation should be below this line. Both equations should be a Blue 12 Point Font(5 Points).
1G. Your vertical axis major gridlines should be black with a width of 1.25. Make sure the line representing $0.00 is also black with a width of 1.25(5 points).
1H. The background to the chart should be a green preset light gradient. The border to the chart should be a triple red line that has a 5 point width(5 points).
Question 2 (Total Points: 35): Please reproduce Figure 2 below as closely as possible from the data given in the Question 2 Tab.
Figure 2:
2A. Starting in cell A14, you should create a new table from the original data that reduces each number by a factor of 1,000,000, i.e., divide by 1,000,000. From this table you need to create a Column Chart that has Fruit & Nut and Field Industries (5 points).
2B. The title should be San Luis Obispo Fruit and Nut vs. Field Industry. Your bottom axis should be the years and slanted at 45 degrees. The years should go from 2004 to 2013(5 points).
2C: You should have Field Crops on the Secondary axis. The secondary axis should have a maximum value of $60.00 and a minimum value of $0.00. Your primary axis shouldhave a maximum value of $600.00 and a minimum value of $0.00. Both bottom axis should have $0.00(5 Points).
2D: Your gap width for your Fruit & Nut crop should be 10%, while your gap width for your field crop should be 50%. Your Fruit & Nut crop columns should be gold, and your Field crop columns should be orange(5 points).
2E. Your data labels for Field crop should be rotated 270 degrees and located in the center of the column. Your data labels for Fruit & Nut crops should be slanted 45 degrees and located at the outside end of the column(5 points).
2F. Your legend should be inside the chart where Fruit & Nut is above the $500.00 line and Field is below the $500.00 line(5 points).
2G. The background to your chart should be Blue Tissue Paper with a 3-D format of Art Deco for the Top and Bottom Bevel (5 points).
Question 3 (Total Points: 25): Please reproduce Table 1 below as closely as possible from the data given in the Question 4 Tab.
Table 1: Example of Table Built from Data on Question 4 Tab
3A. You must transform the column data into tabular form and put the tabular information starting in cell A14(5 points).
3B. You must create a table out of the data that you transformed in 3A making sure that the years are in descending order. The header row should be orange with alternating blue and lighter blue background rows for the rest of the rows. The top row should have a white font (5 Points).
3C: The numbers in the last row should be averages of the numbers in the columns and should have a light blue background like the one above it. The word Average should be written in cell A25 and be bold. The top of the final row should have a black double line separating it (5 points).
3D: Your Planted Acres, Harvested Acres, and Production in Tons should be formatted to have commas and no decimal places. The last two columns should be formatted as currency with two decimal places. Your Yield Per Acres in Tons column should be formatted to show two decimal places(5 Points).
3E. Your last column and row should be bold. There should be no drop down arrows in the first row.(5 points).
Question 4 (Total Points: 25):
4A. Please create a histogram out of the numbers presented in the question 4 tab (5 points).
4B. The column chart should have the following title: Histogram of Numbers (5 points).
4C. The numbers on the x-axis should be in ascending order from left to write (5 points).
4D. Above each bar, should be the count of each number (5 points).
4E. The highest number on the vertical axis should be 180 (5 points).