Excel Application and Modelling

You work for a local bank. You are in charge of finding more data about the loans that your bank is lending, and more descriptive information about the types of loans, and the clients of your bank.

a. Start Excel, open Unit 06 Assessment Starting File and save as Unit 06 Assessment LastFirst.
b. Copy the Accounts worksheet and rename the new worksheet as Breakdown. Using the Breakdown worksheet, create a table named Breakdown. Sort the table descending by Term (Largest Number should be at the top). Convert the table to a range. Add subtotals (in tables) showing the average Monthly_Pmt_Amt and average Loan_Amt by Term. Only Show 36-Month Term and 60-Month term, and the values related to loan_amt and monthly_pmt average.
c. Using the range A1:N400 from the Accounts worksheet, insert a PivotTable on a new worksheet named Fico. Name the PivotTable Fico. Create a PivotTable to show the average Fico_Score by Loan_Status. Change the column label to Average_Fico_Score, format the column with number format and 0 decimals. Filter by Home_Ownership and display Rent only.
d. Using the range A1:N400 from the Accounts worksheet, insert a PivotTable on a new worksheet named Interest_Rate and name the PivotTable Interest_Rate. Create a PivotTable to show the total Int_rate by Purpose. Show the Int_Rate as a maximum. Change the column heading to Max_Interest_Rate and format the column with percentage format and 0 decimal places. Insert a slicer using the Addr_City field, and position it so the upper left corner is at cell E3. Change the height of the slicer to 5.0 inches and the width to 1.0 inches. Change the Buttons of the slicer: height to .2 inches and width to .5 inches. Change the slicer style to Slicer Style Light 4.
e. Using the range A1:N400 from the Accounts worksheet, insert a PivotTable on a new worksheet named Collect and name the PivotTable Collect. Create a PivotTable with a calculated field named Total_Collect to show the total_collect by loan status. The calculated field will show the calculation Loan_Amt multiplied by Int_Rate, and use Currency format and 2 decimals. Filter by State to show only Texas, New York, Ohio, and Florida Change the PivotTable style to Pivot Style Light 10. Copyright © 2016 Dennis Conrad
f. Create a Pivot table using the Contacts sheet. Name the new sheet and pivot table States. Find the count of cities represented. Use the states as the rows and count the cities that are represented. Rename the Column of the count Cities_Represented, and fit the text to the column. Only select the states AZ, CA, MI, VA. Do not show the grand total and change the pivot table to style light 7
g. Save the file, exit Excel, and submit your file to your instructor as directed.