Spring 2016 Excel Test

• If you have not yet done so, please hand in your written portion.
• You may print this document if you wish.
• Please follow all instruction. If you don't understand an instruction, please ask for help.
• You may use your book and notes for this portion of the test.
• Please save frequently.
• All data should be properly labeled and formatted.
• When you finish, make sure that you email a copy of your work to danbennett360@gmail.com.
• All formulas should allow for a change of assumptions.

General Setup

1. Save and open this file
2. [2 points] Rename the file ExcelTest_LastFirst.xlsx
3. [1 point] Place your name in cell B1 of the Car Loan worksheet.

A Car Loan

• On the Car Loan worksheet, you will compute details related to a car loan.
• Data should be entered in light blue cells.
• All other cells should be calculated.
• All cells should be properly formatted, currency as currency, percent as percent, ...
  1. [2 points] Merge cells D2:F2 and add the text Credit Rating Table. This should be bold and centered.
  2. [1 point] Place a thick border around the Credit Rating Table (cells D2:F8).
  3. [3 points] Arrange the table in D4:F8 so a vlookup can be performed on the Credit Rating column.
  4. [4 points] In cell B7 compute the minimal percent down by looking up the Credit Rating in the Credit Rating Table
  5. [2 points] In cell B8 compute the Down Payment based on the Price of the Car and the Percent Down
  6. [2 points] In cell B9, compute the Amount Financed based on the Price of the Car and the Down Payment
  7. [4 points] In cell B11, Lookup the Apr based on the Credit Rating and the Credit Rating Table
  8. [4 points] In cell B12, compute the monthly payment based on the Amount Financed, the APR and the Years Financed
  9. [4 points] In cell B13, place the word Yes if the Payment is less than the Desired Maximum Payment, otherwise place the word No.
  10. 

Candy Computations

• Go to the worksheet labeled Candy
• This contains data from This web page
• Cells A33:B34 contain the number of calories in one gram of fat and sugar.
• For these computations, you should use mixed references whenever appropriate.
  1. [3 points] In column H compute the Calories From Fat using the information in A33:B34 and the Total Fat in each candy bar.
  2. [3 points] In column I compute the Calories From Sugar using the information in A33:B34 and the Total Sugar in each candy bar.
  3. [3 points] In column J compute Other Calories for each candy bar by subtracting the Calories From Fat and the Calories From Sugar from the Calories.
  4. 
  5. [2 points] in Cells H30:J30 compute the average of each column.
  6. [2 points] Display the averages you just calculated accurate to one decimal point.
  7. 
  8. [5 points] Insert a Column chart to display Calories From Fat, Calories from Sugar, and Other Calories for each candy bar.
  9. [6 points] Format the chart so that
     • The y axis is labeled Calories
     • There is no x axis label.
     • The Char Title is Calories for Several Candy Bars
     • The data for each column is displayed.
     • The Legend is on the left hand side of the chart.
  10. 
  11. [10 points] Insert a 3D Pie chart displaying the Average of Calories from Fat, Calories from Sugar and Other Calories using
      • Chart style 3
      • An Appropriate Title
  12. 

Data Tools

• Go to the worksheet labeled Data
• This sheet represents graduation rates in Pa for public schools 2013-2014.
• The data is from here
• I have edited this data, mostly removing columns to make it easier to process on the test.
  1. [1 point] Freeze the panes so that the school name and the column headings do not scroll off screen.
  2. [2 points] Convert the data in A1:I684 to a table.
  3. [2 points] Add a total row and use table tools to compute:
     • The total of column B, Total Grads
     • The average of column C, Total Cohort.
  4. [1 point each] Answer the following questions on the Answers worksheet cells C6:C9.
     1. What is the School Name of the school which has the largest Total Cohort?
     2. How many schools did not have any graduates?
     3. What is the School Name of the school which had a 100% Graduation Rate and the most Total Grads?
     4. How many schools have more that 200 students in the Total Cohort but have a Total Graduation Rate less than 70%?
  5. [3 points] Show all schools where the Total Grads are between 170 and 180. Sort these by Total Graduation Rate largest to smallest.