Help with homework question please

[UnAmigoSecreto]

1. Design a spreadsheet that accepts income data from applicants for each housing community. You may accept at least ten but no more than fifteen applications for each community:

[TABLE="width: 583"]
<tbody>[TR]
[TD]Income
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Applicant
[/TD]
[TD]Applicant Salary
[/TD]
[TD]Salary Deductions
[/TD]
[TD]Spouse Salary
[/TD]
[TD]Salary Deductions
[/TD]
[TD]Gross Income
[/TD]
[TD]Net Income
[/TD]
[TD]Status
[/TD]
[/TR]
[TR]
[TD]Michael James
[/TD]
[TD]8,560.00
[/TD]
[TD]2,295.00
[/TD]
[TD]6,490.00
[/TD]
[TD]1,777.50
[/TD]
[TD]15,050.00
[/TD]
[TD]10,977.50
[/TD]
[TD]Qualified
[/TD]
[/TR]
</tbody>[/TABLE]

Monthly deductions from salary include PAYE tax, health surcharge, union dues and payments to pension plan. No tax is deducted should the applicant’s salary be $5,000 or less. A flat rate of 8% is deducted on the amount in excess of 5,000. Health surcharge is fixed at $155.00 per month while Union dues are 2% of salary. Pension deduction is 15% of salary. In a small number of instances, applicants’ spouses are unemployed. If the net income meets the qualifying income for the housing community applied for as specified by the Housing Construction Corporation, the applicant’s status would be “Qualified”, else “Not qualified” should be recorded. All deduction rates are subject to change.



(a) You must enter income data and use appropriate formulae to calculate deductions, gross income, and net income and determine whether the application qualifies for consideration.

In another section of your spreadsheet, information on applicants’ loan indebtedness should be recorded and calculated. Total amounts owed with respect to bank loans, credit card loans and credit union loans must be revealed.
[TABLE="width: 588, align: right"]
<tbody>[TR]
[TD]Loans
[/TD]
[TD="colspan: 7"]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD="colspan: 3"]Total Loan Amounts
[/TD]
[TD="colspan: 3"] Monthly Payments
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Applicant
[/TD]
[TD]Bank
[/TD]
[TD]Credit Card
[/TD]
[TD]Credit Union
[/TD]
[TD]Bank
Payment
[/TD]
[TD]Credit Card
Payment
[/TD]
[TD]Credit Union
Payment
[/TD]
[TD]Total Loan Payments
[/TD]
[/TR]
[TR]
[TD]Michael James
[/TD]
[TD] $15,780.00
[/TD]
[TD] $1,100.00
[/TD]
[TD] $14,890.00
[/TD]
[TD] $ 439.65
[/TD]
[TD] $105.42
[/TD]
[TD] $ 414.11
[/TD]
[TD] $ 959.17
[/TD]
[/TR]
</tbody>[/TABLE]









(i) Repayment on bank loans is calculated as equal monthly payments over three years at a simple interest of 8% of the starting loan amount.
(ii) Repayment on credit card loans is calculated as equal monthly payments over 18 months at a simple interest of 10% of the starting loan amount.
(iii) Repayment on Credit union loans is calculated as equal monthly payments over three years at a simple interest of 5%. An additional $300.00 is added to the loan repayment amount each month for the purchase of shares in the credit union.

Some applicants do not have credit cards, and just a few do not have loans with the credit union.

All interest rates are subject to change.

(b) You must enter loan information for each applicant and use appropriate formulae to calculate monthly repayments. Insert a column to calculate each applicant’s total monthly payments on loans.

In an appropriate section of your spreadsheet, information on each applicant’s monthly expenses must be calculated. Each applicant must provide realistic estimates of monthly expenses on groceries, utilities (water, telephone and electricity), transportation, and miscellaneous expenses. A column stating the applicant’s number of dependants must be included. Miscellaneous expense is calculated as number of dependants x $75.00

(c) You must enter expense information for each applicant. Insert a column to calculate each applicant’s total monthly expenses.

For each housing community, insert at table which lists each applicant’s Net Income, Total Monthly Loan Repayments, and Total Monthly Expenses and Balance. Balance is calculated as Net Income – sum of Repayments and Expenses. Add a column that states whether the applicant has been approved or not approved for housing allocation. An application is approved if the Balance is equal to or exceeds one half of Net Income.

Create a suitable chart that compares each applicant’s net income, total loan payment, and total monthly expenses (from the named housing community). Give an appropriate name to this chart.

 

[PaddyD]

Hi - welcome to the board.

It is possible, but unlikely, that someone will do your homework for you. You are more likely to get help if you take us through what you have done, where you get stuck etc.

 

[UnAmigoSecreto]

Hi - welcome to the board.

It is possible, but unlikely, that someone will do your homework for you. You are more likely to get help if you take us through what you have done, where you get stuck etc.

I have completed the first part under the INCOME SECTION. Somehow when I reach to calculating for the LOANS SECTION, I don't get the correct value displayed in the guidelines above.