Help with homework question please

UnAmigoSecreto

New Member
Joined
May 12, 2014
Messages
2
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:

Income







Applicant
Applicant Salary
Salary Deductions
Spouse Salary
Salary Deductions
Gross Income
Net Income
Status
Michael James
8,560.00
2,295.00
6,490.00
1,777.50
15,050.00
10,977.50
Qualified

<tbody>
</tbody>

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.
Loans


Total Loan Amounts
Monthly Payments

Applicant
Bank
Credit Card
Credit Union
Bank
Payment
Credit Card
Payment
Credit Union
Payment
Total Loan Payments
Michael James
$15,780.00
$1,100.00
$14,890.00
$ 439.65
$105.42
$ 414.11
$ 959.17

<tbody>
</tbody>









(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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
May 12, 2014
Messages
2
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.

 

Watch MrExcel Video

Forum statistics

Threads
1,130,210
Messages
5,640,872
Members
417,174
Latest member
diegomuser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top