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.

Google Online Preview   Download