ExcelExam_starter

Grading Rubric – Excel 2010

Final Exam

 

Description – Use appropriate formulas and cell references where possible.

Pts

Your Score

Download ExcelExam_starter.xlsx, enter your name and the date on the Documentation sheet, and save as ExcelExam_solution.xlsx

1

 

Convert the City, State column to a City column, a State column and a Zip column.

6

 

Insert a blank column to the left of Customer Name to add a Customer ID to each record. 

Write a formula in cell A2 to create the Customer ID in the form First

InitialofCustomerName+ LastFourDigitsofPhone+ STATE. Include a function to make sure all of the characters in the label will always be capitalized. For example for the first customer the label would be S4253CA. 

Copy this formula down the column to create labels for all customers. (Do not copy/paste values to remove the formulas)

10

 

Format the data as a table named Customers and format with the style of your choice.

Format the phone number and the Amount Owed appropriately.

6

 

Rename this worksheet Original Data. Create a copy of this worksheet and name it SORTED. 

On the SORTED worksheet sort all data in alphabetical order by City and then in descending order by Amount Owed.

Use the Totals feature to add totals to the table, and subtotals for each City.

10

 

On the Summary sheet write formulas to determine the following, using named ranges to simplify the formulas:

  1. Total Amount Owed  (2 pts)
  2. Average Amount Owed  (2 pts)
  3. Largest Amount Owed for any customer  (2 pts)
  4. Number of Customers in each state (4 pts)
  5. Total Amount Owed by Customers in each state. (4 pts)
  6. Percent of Total Amount Owed by all customers for each Total Amount Owed by Customers in each state. In other words, each amount calculated in step 5 divided by amount calculated in step 1 (4 pts)

18

 

Copy the Customer ID, State, and Amount Owed from the Original Data sheet to Columns A3:C3 of a blank worksheet that will be named LOANS.

4

 

Enter the APR (Annual Percent Rate) data below as a lookup table on the LOANS sheet, then write a formula in Column D to display the correct APR for each loan (Use either a Lookup function or nested IF() be sure that the formulas automatically change if the table values change):

AK: 6.8%

CA: 7.4%

HI: 8%

OR: 7.7%

WA: 7.2%

8

 

Write a formula in Column E to calculate the monthly payment to pay off each loan in 2 years, with the interest compounded monthly. Label the column 2YR/Mnth Payoff Amount. Format the label to wrap text.

7

 

The owner would like to offer new loans to each customer whose monthly payment is less than 4.5% of the Amount Owed. Write a formula in Column F to display TRUE for each customer that is eligible for another loan and FALSE for each customer that is not.

Label the column appropriately.

Use conditional formatting to format the column to display green if the result is TRUE.

10

 

Go back to the sheet named Original Data.  From the Original Data create a Pivot Table on a new worksheet (named Pivot) that will show average Amount Owed and the total Amount Owed by State.   Create a pie chart that show the Amount Owed for each state as a % of the total.  Save the chart on the Pivot Sheet.

12

 

On the Summary sheet, create a Column chart of the Amount Owed for each state, with appropriate labels and formatting. 

Highlight the column of the state that has the highest average amount owed.

8

 

Submit the completed file to the assignment dropbox.  

 

 

TOTAL POSSIBLE POINTS:

100

 

 

Please enter your name and date in Documentation Worksheet.