Paragon ICT Lessons
SPREADSHEET SBA 2012 GUIDELINES
Save the spreadsheet as LoanApplication 01 as you complete each numbered task.
CLARENDON COURT
1. In Microsoft Excel rename Sheet1 to Clarendon Court by double-clicking the name (i.e. Sheet1) then typing the name of the community. Press the Enter key to apply the new name.
2. In cell A1 type: Housing Construction Corporation
3. In cell A2 type: Clarendon Court
4. In a few rows below the 2nd title of the sheet, type the label: Qualifying Income
5. In the cell beside the label, Qualifying Income, type: $12,500
6. In a few rows below the Qualifying Income, create the Income table as shown in requirement 1 of the project.
Please note:
i) The cells beside the label Income, and above the column headings Applicant Salary to Status, are merged.
To merge the cells, do the following:
- Highlight the cells
- Click the Merge & Center button on the toolbar
ii) Some column headings appear as two lines. This is because the text in the cell is wrapped.
To wrap text, do the following:
- Select the cells
- Click the Wrap Text button on the toolbar.
7. Between Applicant Salary and Salary Deductions, insert blank columns for each of the following headings: PAYE, Health, Union Dues, Pension
To insert a blank column, do the following:
- Right click on the Salary Deductions cell and select Insert
- Click Entire Column
8. Repeat step 7 to insert the same headings between Spouse Salary and Salary Deductions.
9. Rename Sheet2 as Rates
10. On the Rates sheet, create the following table:
|Deduction Rates |
|PAYE Tax |8% |
|Health Surcharge |$155.00 |
|Union Dues |2% |
|Pension Plan |15% |
To merge the cells for the Deduction Rates heading, do the following:
i) Highlight both cells
ii) Click the Merge & Center button on the toolbar
11. Return to the Clarendon Court sheet and enter the names of the applicants, salary and their spouse’s salary.
Remember the following:
- Only a minimum of 10 applicants but no more than 15 should be entered in the community
- A small number of applicants’ spouses are unemployed, so 0 (zero) should be entered as their salary.
12. Enter the following formulas for the first applicant under the given column headings:
PAYE
i) Type =IF(
ii) Select cell with applicant’s salary amount
iii) Type >5000, (including the comma)
iv) Select cell with the applicant’s salary figure
v) Type *
vi) Click the Rates sheet and select the cell with 8% for PAYE Tax
vii) Press theF4 key to make the cell address absolute
viii) Type ,0) (including the comma and bracket)
ix) Press the Enter key to apply the formula
Health
i) Type =
ii) Click the Rates sheet and select the cell with $155 for Health Surcharge
iii) Press theF4 key to make the cell address absolute
iv) Press the Enter key to apply the formula
Union Dues
i) Type =
ii) Select cell with the applicant’s salary figure
iii) Type *
iv) Click the Rates sheet and select the cell with 2% for Union Dues
v) Press theF4 key to make the cell address absolute
vi) Press the Enter key to apply the formula
Pension
i) Type =
ii) Select cell with the applicant’s salary figure
iii) Type *
iv) Click the Rates sheet and select the cell with 15% for Pension
v) Press theF4 key to make the cell address absolute
vi) Press the Enter key to apply the formula
13. Copy and paste the formulas for the remaining applicants.
14. Repeat step 12 to enter the formulas to calculate the PAYE, Health, Union Dues and Pension for the spouse. Remember to select Spouse Salary instead of Applicant Salary.
15. Repeat step 13 for the remaining spouses.
16. Now enter formulas to calculate the following for the first applicant:
Salary Deductions
i) Type =Sum(
ii) Highlight the cells containing the amounts for PAYE, Health, Union Dues and Pension
iii) Type )
iv) Press the Enter key to apply the formula
v) Copy the formula to calculate the Salary Deductions for the remaining applicants
vi) Highlight all the cells with the Salary Deductions formula for the applicants then copy and paste the formulas to calculate the Salary Deductions for the spouses.
Gross Income
i) Type =Sum(
ii) Select cell with the applicant’s salary figure
iii) Type ,
iv) Select cell with the spouse’s salary figure
v) Type )
vi) Press the Enter key to apply the formula
vii) Copy and paste the formula to the remaining cells
Net Income
i) Type =
ii) Select cell with the Gross Income amount
iii) Type – (
iv) Select cell with the applicant’s Salary Deductions amount
v) Type +
vi) Select cell with the spouse’s Salary Deductions amount
vii) Type )
viii) Press the Enter key to apply the formula
ix) Copy and paste the formula to the remaining cells
Status
i) Type =IF(
ii) Select cell with the Net Income amount
iii) Type >=
iv) Select cell with the Qualifying Income figure
v) Press the F4 key to make the cell address absolute
vi) Type ,“Qualified”,“Not qualified”)
vii) Press the Enter key to apply the formula
viii) Copy and paste the formula to the remaining cells
17. In a few rows below the Income spreadsheet, create a table for Loans as shown in the project.
Please note the following:
- The cells beside the label Loans and across to the last column for Total Loan Payments are merged.
- Total Loan Amounts is merged and centered across the cells with the Bank, Credit Card and Credit Union headings.
- Monthly Payments is merged and centered across the cells with the Bank Payment, Credit Card Payment and Credit Union Payment headings
18. Copy the names of the applicants in the Income table and paste them in the Loans table.
19. Enter the Total Loan Amounts (i.e. Bank, Credit Card and Credit Union) for each applicant.
Remember the following:
- Some applicants do not have Credit Cards
- A few applicants do not have loans with the Credit Union
20. Go the Rates sheet and create the following table in another section:
|Simple Interest Rates on Loans |
|Bank Loan |8% |
|Credit Card Loan |10% |
|Credit Union Loan |5% |
21. Return to the Clarendon Court Sheet and enter the following formulas for each applicant:
Bank Payment
i) Type =(
ii) Select cell with the applicant’s Bank loan amount
iii) Type +(
iv) Reelect cell with the applicant’s Bank loan amount
v) Type *
vi) Click the Rates sheet and select the cell with 8% for Bank Loan
vii) Press theF4 key to make the cell address absolute
viii) Type ))/(3*12)
ix) Press the Enter key to apply the formula
x) Copy the formula to the remaining cells
Credit Card Payment
i) Type =(
ii) Select cell with the applicant’s Credit Card loan amount
iii) Type +(
iv) Reelect cell with the applicant’s Credit Card loan amount
v) Type *
vi) Click the Rates sheet and select the cell with 10% for Credit Card Loan
vii) Press theF4 key to make the cell address absolute
viii) Type ))/18
ix) Press the Enter key to apply the formula
x) Copy the formula to the remaining cells
Credit Union Payment
i) Type =((
ii) Select cell with the applicant’s Credit Union loan amount
iii) Type +(
iv) Reelect cell with the applicant’s Credit Union loan amount
v) Type *
vi) Click the Rates sheet and select the cell with 5% for Credit Union Loan
vii) Press theF4 key to make the cell address absolute
viii) Type ))/(3*12)) + 300
ix) Press the Enter key to apply the formula
x) Copy the formula to the remaining cells
Total Loan Payments
i) Type =Sum(
ii) Highlight the cells with the amounts for Bank Payment, Credit Card Payment and Credit Union Payment
iii) Type )
iv) Press the Enter key to apply the formula
v) Copy the formula to the remaining cells
22. In a few rows below the Loans table, create the following Expenses table:
|Expenses | |
|Applicant |No. of Dependents |
|Applicant |Net Income |Total Monthly Loan Repayments|Total Monthly Expenses |Balance |Status |
| | | | | | |
23. Copy and paste the names of the applicants into the Summary table.
24. Enter the following formulas:
Net Income
i) Type =VLookup(
ii) Select cell with the name of the first applicant
iii) Type ,
iv) Highlight the names of all applicants and across to the Status column in the Income table
v) Press F4 to make the cell range absolute
vi) Type ,15,False)
vii) Press the Enter key to apply the formula
viii) Copy the formula to the remaining cells
Total Monthly Loan Repayments
i) Type =VLookup(
ii) Select cell with the name of the first applicant
iii) Type ,
iv) Highlight the names of all applicants and across to the Total Loan Repayments column in the Loans table
v) Press F4 to make the cell range absolute
vi) Type ,8,False)
vii) Press the Enter key to apply the formula
viii) Copy the formula to the remaining cells
Total Monthly Expenses
i) Type =VLookup(
ii) Select cell with the name of the first applicant
iii) Type ,
iv) Highlight the names of all applicants and across to the Total Expenses column in the Expenses table
v) Press F4 to make the cell range absolute
vi) Type ,7,False)
vii) Press the Enter key to apply the formula
viii) Copy the formula to the remaining cells
Balance
i) Type =
ii) Select cell with the Net Income amount for the first applicant
iii) Type – (
iv) Select cell with the Total Monthly Loan Repayments amount for the first applicant
v) Type +
vi) Select cell with the Total Monthly Expenses amount for the first applicant
vii) Type )
viii) Press the Enter key to apply the formula
ix) Copy the formula to the remaining cells
Status
i) Type =IF(
ii) Select cell with the Balance amount
iii) Type >=(
iv) Select cell with the Net Income figure
v) Type *1.5),“Approved”,“Not approved”)
vi) Press the Enter key to apply the formula
vii) Copy and paste the formula to the remaining cells
FORMATTING THE SHEETS
Clarendon Court Sheet
1. Highlight cell A1, with the first sheet title, to the last cell in the column O.
2. Merge and center the cells.
3. Repeat steps 1 and 2 for the second sheet title.
4. Go the Income table
5. Highlight the entire table.
6. Go to Cells section on the toolbar and click Format
7. Select Format Cells from the menu options
8. Click the Border tab
9. Click the Outline and Inside buttons
10. Click Ok
11. Repeat steps 5 to 10 for the Loans, Expenses and Housing Allocation tables.
12. Highlight and bold the name and column headings for the Income, Loans, Expenses and Housing Allocation tables.
13. Format all dollar amounts to Currency with 2 decimal places in each table.
To do this:
i) Highlight the dollar figures in a table
ii) Go to the toolbar and click the down arrow beside General
iii) Select Currency from the list
Rates Sheet
1. Highlight and bold the name of each table
2. Border each table
CREATING THE SANGRE GRANDE VILLAS SHEET
1. Right-click the Clarendon Court sheet name
2. Select Move or Copy from the pop-up menu
3. Select (move to end) in the dialog box and click Create a copy
4. Rename the sheet Clarendon Court (2) as Sangre Grande
5. Change the following data on the Sangre Grande sheet:
a. The second title should read as Sangre Grande Villas
b. The applicants names, salaries and spouses’ salaries in the Income table
c. The applicants names, bank, credit card and credit union amounts in the Loans table
d. The applicants names, number of dependents, groceries, utilities and miscellaneous amounts in the Expenses table
e. The applicants names in the Housing Allocation table
CREATING THE PROVIDENCE GARDENS SHEET
1. Right-click the Clarendon Court sheet name
2. Select Move or Copy from the pop-up menu
3. Select (move to end) in the dialog box and click Create a copy
4. Rename the sheet Clarendon Court (2) as Providence Gardens
5. Change the following data on the Providence Gardens sheet:
a. The second title should read as Providence Gardens
b. The applicants names, salaries and spouses’ salaries in the Income table
c. The applicants names, bank, credit card and credit union amounts in the Loans table
d. The applicants names, number of dependents, groceries, utilities and miscellaneous amounts in the Expenses table
e. The applicants names in the Housing Allocation table
DELETING SHEET3
1. Right-click the Sheet3 name
2. Select Delete from the pop-up menu
CREATING THE CHART
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.