Exp22_Excel_Ch11_Cumulative – Client FICO Scores

If you are looking for affordable, custom-written, high-quality, and non-plagiarized papers, your student life just became easier with us. We are the ideal place for all your writing needs.

Order a Similar Paper Order a Different Paper

Please complete the assignment using the instructions as well as the final result image.

The Excel file should look like the final result png at the end if you follow all the steps correctly.


Client Summary FICO Scores Formula Documentation
Select ID and Month 0 Poor B5
ID T246L 580 Fair A8
Month September 670 Good B8
FICO Score 740 Very Good
800 Exceptional
Month FICO


Old ID New ID Initial Last Last Name Name City State Total Locations
123 K FARSI Kansas City, MO Client Locations
246 T LOZANO Liberty, MO
267 J SMITH Overland Park, KS
892 D AUCHTER Topeka, KS
615 B ORLOV Kansas City, MO
738 S LIANG Kansas City, KS
861 V NOWAK Liberty, MO
984 E WILLIAMS Kansas City, KS
240 N ROSSI Independence, MO
188 S GARCIA Overland Park, KS
329 L KORHONEN Topeka, KS
690 B KENDRICK Kansas City, KS
369 J HANSEN Independence, MO
492 J JENSEN Liberty, MO
503 F MURPHY Kansas City, MO
704 D HAIR Topeka, KS
875 T JABAL Kansas City, KS
318 E YILMAZ Olathe, KS
321 A MARTIN Kansas City, MO
440 A VALDEZ Liberty, MO


Record ID January February March April May June July August September October November December Month Filter December
K123F 745 750 750 740 739 735 735 737 737 740 740 740 Top 4
T246L 815 817 825 815 817 820 820 825 822 825 825 830
J267S 641 641 650 650 655 655 658 660 670 670 671 671
D892A 740 735 738 740 740 740 738 738 735 735 735 737
B615O 585 590 595 595 595 600 600 590 584 586 586 590
S738L 825 835 835 838 838 840 835 835 831 832 834 829
V861N 790 790 790 795 795 795 798 798 800 795 795 795 Summary Very Good
E984W 655 645 650 648 640 644 650 646 638 638 640 640 Average
N240R 700 690 695 685 680 683 683 685 685 700 704 704 Count
S188G 695 680 680 685 695 680 680 685 685 685 690 680
L329K 835 835 835 820 822 822 825 813 810 810 814 811 Criteria
B690K 795 790 790 785 775 775 778 778 780 785 790 790 December December
J369H 580 575 577 577 570 570 565 575 575 575 580 585
J492J 660 665 665 669 672 672 672 680 680 680 675 678
F503M 635 638 638 640 640 640 630 635 635 627 627 627
D704H 755 755 750 740 732 732 740 740 742 745 755 755
T875J 805 815 813 795 795 795 795 800 800 800 805 805
E318Y 732 735 732 735 735 740 740 735 735 742 742 745
A321M 665 670 670 672 672 670 675 675 675 680 680 695
A440V 767 765 765 760 760 772 772 772 765 765 779 785

Grader – Instructions Excel 2022 Project

Exp22_Excel_Ch11_Cumulative – Client FICO Scores

Project Description:

You are an analyst for a bank in the Kansas City area. Your manager, Skyler Oakley, assigned 20 client case files for you to review. You downloaded the clients’ FICO scores for one year. FICO is a three-digit score representing one’s credit rating. Lenders use FICO scores to determine a person’s likelihood to repay a loan. The first few tasks are to complete the Clients worksheet. In addition, you will perform an advanced filter to focus on clients with an Exceptional credit score and focus on Very Good credit scores for a particular month. Last, you will set up a worksheet to enter a client ID and look up that person’s information.

Steps to Perform:



Points Possible


Start Excel. Download and open the file named
Exp22_Excel_Ch11_Cumulative_FICO.xlsx. Grader has automatically added your last name to the beginning of the filename.



You will use the Clients worksheet through Step 8. When you download data from the database server, the clients’ last names display in all capital letters. You want to make it easier to read the last names.

In cell E2, insert the text function that displays the upper- and lowercase letters for the last names in the range D2:D21. Using the range in the function argument creates an array of last names so that you do not have to copy the function down the column.



Your next task is to join the first initial and last name in another column.

In cell F2, insert the TEXTJOIN function that combines the first client’s initial in cell C2 with the person’s last name in cell E2. Use a period and space as the delimiter. Copy the function to the range F3:F21.



You want to create updated client IDs.

In cell B2, insert the CONCAT function that combines the initial in cell C2, the old ID in cell A2, and the first letter of the last name in cell D2. Use a nested LEFT function to retrieve the first letter of the last name. Do not include any delimiters. Copy the function to the range B3:B21.



The City column contains city names and state abbreviations. You will separate the data into two columns.

Select the range G2:G21 and convert it to columns. Use the comma as the delimiter and deselect other delimiter check boxes. Within the Wizard, make sure the two columns are formatted as Text.



When you converted text to columns, spaces remained to the left of the state abbreviations. You want to remove those spaces.

Select the range H2:H21. Use Find and Replace to find a space and replace it with nothing.



Your next task is to create an array of the different locations where clients live.

In cell J3, insert the UNIQUE function to list the unique city and state abbreviations in the range G2:H21. Nest the UNIQUE function within a SORT function to sort the results by the state abbreviation.



You want to count the number of unique rows.

In cell K1, insert the ROWS function to count the number of unique locations. Nest the UNIQUE function within the ROWS function using the same array that you used in Step 7.



The Scores worksheet contains the client FICO scores for the entire year. First, you want to create an array of row numbers for the clients.

Display the Scores worksheet. In cell A2, insert a ROW function that uses the range A2:A21 as the array. The results should then subtract the row number for A$1.



You are ready to create the criteria range so that you can perform an advanced filter.

Copy the range A1:N1 to cell A23 to create the column labels for the criteria range. In cell C24, type
>=800. Copy the criteria to the range D24:N24.



Next, you will create the column headings for the output range.

Copy the range A1:N1 to cell A26.



You are ready to perform the advanced filter.

Perform the advanced filter by copying data to the output area. Use the appropriate ranges for list range, criteria range, and output range.



You created a summary area where you can enter the month and then display an array of the top four FICO scores for that month.

In cell P3, insert the LARGE function. Nest the INDIRECT function in the array argument to refer to the contents in cell Q1. Cell Q1 contains the name of a month, which is also the range name for the respective month’s FICO scores. Nest the SEQUENCE function in the k argument of the LARGE function to create an array of the top four FICO scores for the respective month. Change cell Q1 to
April and notice that the top 4 FICO scores for April are different.



You want to create the criteria for the FICO scores in the Very Good credit rating category.

In cell P14, type
>=740. In cell Q14, type



Now that you entered the criteria, you want to calculate the average score.

In cell Q9, insert the DAVERAGE function. Use the range C1:N21 for the database argument, cell Q1 for the field argument, and the range P13:Q14 for the criteria argument.



Next, you want to count the number of times the scores within the criteria range occur.

In cell Q10, insert the DCOUNT function. Use the same arguments that you used for the DAVERAGE function. Change cell Q1 to
October. The results of the LARGE, DAVERAGE, and DCOUNT functions should change.



You will insert functions to look up data from the Scores worksheet. Your first task, however, is to assign range names to each client’s FICO scores.

Select the range B2:N21 in the Scores worksheet. Create range names from the selection using the left column.



You will create a summary section on the LookUp worksheet.

Display the LookUp worksheet. In cell A8, insert the TRANSPOSE function. Select the Scores worksheet and the range C1:N1 for the array of months.



Next, you want to display FICO scores based on the ID entered in cell B3, which corresponds to a range name.

In cell B8 on the LookUp worksheet, insert the TRANSPOSE function. Nest the INDIRECT function using cell B3 as its argument. The result is an array of 12 FICO scores for the client.



You want to create a line chart to depict the 12-month period of FICO scores for the client.

Select the range A7:B17 and insert a line chart. Paste the chart so that its upper-left corner is inside cell C7. For the chart title, type
=LookUp!$B$3 in the Formula Bar.



Cell B3 contains a customer ID, and cell B4 contains a month. Based on that data entry, you want to retrieve the FICO score.

In cell B5, insert the INDEX function. The array argument is range C2:N21 on the Scores sheet. The row_num argument contains a nested XMATCH function to look up the ID in cell B3 and compare it to the range B2:B21 in the Scores worksheet. The column_num argument contains a nested XMATCH function to look up the month in cell B4 and compare it to the range C1:N1 in the Scores worksheet.



You want to make sure the formulas and chart are correctly constructed.

In cell B3, type
D704H. In cell B4, type
May. Observe changes in the function results and in the line chart.



You are ready to document formulas on the LookUp worksheet.

In cell H2, insert the FORMULATEXT function to display the formula that is in cell B5.
In cell H3, insert the FORMULATEXT function to display the formula that is in cell A8.
In cell H4, insert the FORMULATEXT function to display the formula that is in cell B8.



Save and close
Exp22_Excel_Ch11_Cumulative_FICO.xlsx. Exit Excel. Submit the file as directed.


Total Points


Created On: 08/11/2022 1 Exp22_Excel_Ch11_Cumulative – Client FICO Scores 1.1

Are you stuck with another assignment? Use our paper writing service to score better grades and meet your deadlines. We are here to help!

Order a Similar Paper Order a Different Paper