A home builder is planning a new subdivision. Write a paper addressing the cost of homes to be built in this subdivision using the information provided below. Your recommendation should be based on the results of analyzing the data provided below. You will describe your analysis and results as well as present the results of your analysis.

The table below provides the distribution in household income for the city.

	Number	Percent
Households	405,887	100.0
Less than $10,000	46,058	11.3
$10,000 to $14,999	28,462	7.0
$15,000 to $24,999	61,545	15.2
$25,000 to $34,999	59,989	14.8
$35,000 to $49,999	69,799	17.2
$50,000 to $74,999	72,213	17.8
$75,000 to $99,999	32,724	8.1
$100,000 to $149,999	23,245	5.7
$150,000 to $199,999	5,942	1.5
$200,000 or more	5,910	1.5

The distribution above provides the potential market for the new homes. Assume that a buyer can afford a home if the monthly payment (PITI) is no more than 28% of their monthly income. Assume the annual tax rate is 2.5% and monthly homeowner's insurance will be $100. Mortgage rates over the last 20 years have ranged from approximately 4% to approximately 10% with an average of 6.75%.

To analyze the data, determine the maximum monthly payment each distribution group can afford. Also create a data table of monthly payments based on interest rates over the range of the last 20 years and home prices from $50,000 to $400,000. Finally, calculate the maximum affordable home price for each group with more than 50,000 households using the average interest rate from the last 20 years. Use this information to come up with a recommendation. Note that the subdivision will not be completed for at least 10 years so the interest rates (and consequent affordability) may change considerably over time. Your recommendation should be of the form houses from $50,000 to $100,000 or $350,000 to $400,000.

Report structure

Your report will be in two sections:

Section 1: Data. You should print the table of maximum monthly payments, the data table, and the maximum affordable home prices in this section. They may be labeled Table 1, Table 2, and Table 3 for reference in the paper. Any additional calculations you want to include also go in this section.

Section 2: Report. The first part of your report should describe the calculations you did and the rationale for any decisions (for example, how many rows and columns in data table, how you determined a specific income value) you made, particularly in regard to the three required tables. The second part will summarize any observations or findings you have. The last part will present your recommendations. This section should be between one half and one page long, single spaced.

Grading

The report will be graded for clarity (how easy it is to understand you), completeness (no significant gaps in the information provided) and correctness (the values and descriptions are correct). Your conclusion should be supported by both the calculation results and the report narrative.

Final Comment: the purpose of this exercise is to provide you with experience using Excel (and particularly the what if analysis functions) and experience in constructing a coherent report. While future exercises may be focused on coming to the "correct" conclusions, this is not the case with this exercise. The correct conclusion is one that is supported by the presented data and narrative.