I have a workbook that contains about 100 worksheets that house data I need to combine into one master file. Each of the worksheets are formatted the same with the data contained in the same column(s)/row(s). I need to take the info and create a row of information for each client. The Data column, I would like to transpose so the data is in a row and the client name would be the first column and then the last column would be Average Raw Score. I have attached a sample worksheet that contains the current layout and the layout I would like to have on a master worksheet. I tried to add an attachment but I am not allowed to on this site, no idea why.
Data resides D10:D18
Client name - B2
Average Raw Score - B33
Any help on this will be greatly appreciated!
REnny
<tbody>
</tbody>
Data resides D10:D18
Client name - B2
Average Raw Score - B33
Any help on this will be greatly appreciated!
REnny
RISK RATING MATRIX | COMPOSITE RISK RATING | ||||||||||
Borrower Name | smith Realty | Date | 8/31/2016 | ||||||||
Business Name | business name | Loan Officer | loan officer name | ||||||||
Loan # | S1403 | ||||||||||
1) Enter data into Table 1 Column D (Data) and Column E (Date Reported). Score is calculated automatically. | |||||||||||
2) Using Table 2 Average Raw Score, round up or down according to potential risk of loan. | |||||||||||
3) Assign a composite risk rating based on a combination of quantitative and qualitative assessment. | |||||||||||
TABLE 1 QUANTITATIVE ASSESSMENT | |||||||||||
Credit Criteria | Indicator | Source | Data | Date Reported | Score | ||||||
Loan to Value | Total Loans Outstanding | DownHome | $20,869 | 8/31/2016 | 29.1% | ||||||
Collateral Value | Green collateral files | $71,750 | 1/8/2010 | ||||||||
Credit Score | FICO Score (Transunion) | Transunion† | $683 | 2011 | 683 | ||||||
Debt Service Coverage Ratio (Including Loan) | Yearly Operating Income | Financial statements* | $96,651 | 2013 | 4.474584722 | ||||||
Yearly Loan Payments | DownHome | $21,600 | 2016 | ||||||||
Current Ratio | Current Assets | Financial statements* | $127,582 | 12/31/2012 | 0.846 | ||||||
Net Worth | Current Liabilities | Financial statements* | $150,780 | 12/31/2012 | -23198.47 | ||||||
Profitability (# consecutive years) | # Years that (Income - Expenses) > $0 | Financial statements* | 1 | 2013 | 1 | ||||||
Time in Business | # Years | DownHome | 6 | 6 | |||||||
† If borrower has a FICO score on file that is < 3 years old, this should be used. If their FICO score is > 3 years old, a new soft pull credit check should be run. | |||||||||||
* Alternately tax returns, income statements and balance sheets can be used. | |||||||||||
TABLE 2 RISK RATING MATRIX | |||||||||||
Credit Criteria | Risk Rating | Risk Rating 1 | Risk Rating 2 | Risk Rating 3 | |||||||
Loan to Value | 1 | 60% and below | 61%-79% | 80% and above | |||||||
Credit Score | 3 | 750-850 | 700-749 | 650-699 | |||||||
Debt Service Coverage Ratio | 1 | >1.31 | 1.21 - 1.3 | 1.1 - 1.2 | |||||||
Current Ratio | 3 | >1.51 | 1.11-1.50 | 1.00-1.10 | |||||||
Net Worth | 3 | Over $50K | $25K-50K | <$25K | |||||||
Profitability | 3 | >3 years | 2 | 1 | |||||||
Time in Business | 1 | >5 years | 3-5 years | <3 years | |||||||
Total Raw Score | 15 | ||||||||||
Average Raw Score | 2.1 | ||||||||||
TABLE 3 QUALITATIVE ASSESSMENT | |||||||||||
Character | Capacity | Capital | Collateral | Cash Flow | |||||||
Current Layout per worksheet above | |||||||||||
This is the layout I would like to have on a master | |||||||||||
Client Name | Total Loans Outstanding | Collateral Value | FICO Score (Transunion) | Yearly Operating Income | Yearly Loan Payments | Current Assets | Current Liabilities | # Years that (Income - Expenses) > $0 | # Years | Average Raw Score | |
smith realty | $20,869 | $71,750 | $683 | $96,651 | $21,600 | $127,582 | $150,780 | 1 | 6 | 2.1 | |
<tbody>
</tbody>