You have been hired to construct a spreadsheet that will help a family decide which house they should purchase. The family will use these criteria for narrowing down their list to a few finalists:

1. Monthly mortgage and monthly tax payments
2. The towns rank
3. Lot size
4. Number of bedrooms and bathrooms
5. Inspection Rating
6. Sprinkler system

You are given the following data on the Worksheet named Houses:

Column	Description of Data
A	Town
B	Cost of home
C	Lot Size
D	Inspection rating
E	Number of bedrooms
F	Number of bathrooms
G	Whether the house has a sprinkler system

In addition, for some towns, the following data is given:

Town	Rank
Montclair	67
Summit	22
South Orange	45
Millburn	9

For any towns not in the table, use 100 as the Rank. The decision on what house to choose is based on the number of points awarded for monthly payments, location and various features. Below are the rules for awarding points.

a) Monthly payment points: These are based on a budget you have for paying the real estate tax and mortgage payments.

Real Estate Tax The annual real estate tax is based on the size of the lot. The real estate tax costs $4,500 for every 5,000 square feet. For example, if the lot size is 10,000 square feet, the annual tax would be $9,000. If the lot size is 11,000 square feet, the annual tax would still be $9,000 as the tax is calculated on every 5,000 square feet and not any fractional part of 5,000.

Mortgage Payments The cost of the home will be paid monthly over 30 years at an interest rate of 3.25%.

Calculate the total monthly payments for the mortgage plus the real estate taxes.

Budget - The budget for these total monthly payments is $6,500. If the budget is met (monthly payments are less than or equal to $6,500), award 35 points. Otherwise, award 5 points.

b) Location points Determine the rank of the town by using the table above. For all towns not in the table, use 100 as the rank. I should be able to change the town name and your formula should still work.

After calculating the rank, use the following to determine the number of points for location:

1. if the Total number of bedrooms is greater than 4, then the following table gives the number of points based on rank:

Rank	Points
0 19	60
20 - 40	50
41 - 65	40
66 - 79	30
80 and above	20

1. If the Total number of bedrooms is less than or equal to 4, then the following table gives the number of points based on rank:

Rank	Points
0 19	55
20 - 40	45
41 - 65	35
66 - 79	25
80 and above	15

c) Features points Test the following rules for features points in the order given using the first one that applies:

1. If the Town Rank is less than 10, award 30 points.
2. If the total number of bedrooms plus bathrooms is greater than 7 and the house has a sprinkler system, award 26 points.
3. If the lot size is greater than or equal to 30,000 square feet or the Inspection Rating is greater than 17, award 21 points.
4. Otherwise, award 10 points.

d) Finalist rule - Use the following rule for deciding which houses are finalists. Enter the word Finalist if any of these rules is met or enter Not if none are met:

1) total points are greater than 90 OR

2) the cost of the home is less than $650,000 OR

3) the lot size is between 20,000 and 30,000 square feet inclusive

Construct your spreadsheet so that it has columns where at least the following values are displayed; feel free to add any other columns you need for your calculations:

1. The number of points awarded for payments. (13 pts)
2. The number of points awarded for location. (10 pts)
3. The number of points awarded for features. (9 pts)
4. The total number of points awarded. (2 pts)
5. Either the word Finalist or Not to denote if the house is a finalist. (7 pts)

Make sure your spreadsheet is laid out in an organized, logical manner using good spreadsheet design techniques. (assumptions 4 pts, absolute addressing 3 pts)

Remember, the formulas must work even if I change any piece of data given.

6. Each column should have a heading with a description that is text wrapped. (2 pts)

7. If you use VLOOKUP, you must name the cell location of the table and use it in the VLOOKUP function. (3 pts)

8. All answers should be displayed to 0 decimal places.

When you are finished, the total values and Finalists are shown below.

Town	Monthly Real Estate Tax	Monthly Mortgage	Total monthly pmts	Paymnt pts	Location pts	Features pts	Total points	Finalist?
Livingston	1125	4026	5151	35	15	21	71	Not
Summit	3000	5440	8440	5	50	26	81	Not
South Orange	1125	2894	4019	35	35	10	80	Not
Montclair	750	3590	4340	35	25	10	70	Not
Millburn	1875	5114	6989	5	60	30	95	Finalist
Edison	750	2589	3339	35	15	10	60	Finalist