Ag Bus 260
Midterm 1
130 Points
1/23/2014
Dr. Hurley
Instructions: Locate your test folder which can be found at:
\\cp-file-cafes\cafes$\OpenToAll\AGB260-Hurley. Before you open the file, you should rename the file to be your last name-first name.XLSX, e.g., Hurley-Sean.XLSX. Inside this folder you will find an Excel spreadsheet that has five Tabs which read: Question A, Question B, Question C, Question D, and Question F. Each one of these relates to the questions below. Follow the instructions given below. Examples have been provided for a couple of the questions to give you an idea what type of answer I am looking for, but in no way are they necessarily the full answer. When you are finished with the exam save it to your folder on the O drive and then email me a copy.
Question A (Total Points: 25): Please build a 50-by-50 subtraction table in the Question A Tab which tells you the difference between the corresponding value in the first column with the corresponding value in the first row(5 points). For example, suppose in cell A2 you have the value 1 and in cell C1 you have the value 2. Then, you should get -1 in cell C2 because 1 –2 = -1. Figure 1 provides an example of what the subtraction table should look like when you are completely finished with this question. Every cell in the subtraction table that is not in Column A or Row 1 that is equal to zero should have a Border around it (5 Points). Every cell in the subtraction table that is not in Column A or Row 1 that is greater than zero should have a Blue fill behind it (5 Points). Every cell in the subtraction table that is not in Column A or Row 1 that is less than zero should have an Orange fill behind it and the number should be Bold and Italic (5 Points). Finally, for every cell in the subtraction table that is not in Column A or Row 1 that is between -1 and 1 inclusive should have a Green fill with Dark Green text (5 Points). Make sure that this last requirement dominates all the other requirements.
Figure 1: Example of Final Output for Question A.
Question B (Total Points: 15): Copy the values of the first 11 rows and columns from the Question A Tab into the Question B Tab (5 points). Note there should be no formatting copied over. If you do copy formatting, please erase the formatting. Excluding the cells in column A and Row 1 and any blank cells, fill the top 37 cells with a Purple color(5 points).Excluding the cells in column A and Row 1 and any blank cells,fill the bottom 37 cells with a Black fill and a White Font for the number (5 points). Please see Figure 2below for an example.
Figure 2:Example of Final Output for Question B.
Question C (Total Points: 60): Your boss has provided you with an unformatted spreadsheet that provides information on employees and their last year’s sales metrics. The individual has tasked you with formatting this spreadsheet in a more professional manner and is very particular on how it should look. Around the whole table should be a Dark Black Border (5 Points). In the top row of the table, you should have only Two Cells that span the total table. One of the cells should read General Employee Information and be over the cells that read Employee, Employee ID, and Date Hired, while the second cell should be entitled Last Year’s Metrics and be over the cells containing Revenue Earned Last Year and Sales Commission (5 points). Each of these two cells should be Centered, Bold, and be in 14-Point Times New Roman Font (5 points). Between the first row and second row of the table should be and a Solid Orange line (5 Points). In the second row of cells in the table, the text should be Bold, Horizontally Centered, 14-Point Times New Roman Font, and the Vertical Alignment should be in the Center (5 Points). The second and third rows in the table should be separated by a Double Solid Line. Starting with a Gray fill for the cells in row 3 of the table and Blue fill for the cells in the row 4 of the table, alternate the fill from Gray to Blue for the rest of the rows below in the table (5 points). In the Employee ID column, you need to make sure the ID numbers all have the Same Number of Characters and at least one leading 0 for each number(5 Points). In the Date Hired Column, all the Dates should be set up in the Short Date format (5 points). In the Revenue Earned Last Year column, all the numbers should be formatted as having Dollar Signs and show Two Decimal Places (5 Points). In the Sales Commission column, all the numbers should be formatted as Percentages with Two Decimal Places (5 Points). All the cells in rows 3 through 8 in the table should be formatted as Tahoma 12-Point Font(5 Points). All the columns of cells should be separated by Dotted Lines (5 points). Please see Figure 3 below for an example of the completely formatted table.
Figure 3: Example of Final Output for Question C.
Question D(Total Points: 10): What information is in Cell D6 in the Question D Tab(5 Points)? Please place your answer in cell A1. In cell B1, briefly explain how you figured the answer out(5 Points).
Question E (Total Points: 20): Please rename the Question F Tab to Question E (5 Points). I have placed somewhere in the question F Tab that you just renamed a number. It is your task to find out what that number is. Here is a hint to help you figure out what it is. I have named the cell HiddenNumber. In cell A1, please tell me what the number is that I have hidden in the spreadsheet (5 points). In cell A2, tell me the actual cell designation (not what the cells name is), e.g. Q35, that the hidden cell is located in (5 Points). In cell A3, please briefly explain how you found the actual cell (5 Points).