Posted: November 20th, 2015

Excel

Excel

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 provides the potential market for the new homes. Assume that a buyer can afford a home if the monthly payment including principle, interest, property taxes, and property insurance (PITI) is no more than 28% of their monthly income. Assume the annual property tax rate is 2.5% and homeowner’s insurance will be 0.8% of the home’s value. 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 for a 30 year mortgage based on interest rates over the range of the last 20 years (e.g. 4%, 5%,…,10%) and home prices from $50,000 to $400,000 (e.g. $50,000, $100,000,…,$400,000). Finally, calculate the maximum affordable home price for each group 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 (e.g. $50,000 range).
Report structure
Your report will be in two sections (50-point each):
Section 1: Data. You should construct 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 Excel file. 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

PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET A GOOD DISCOUNT