Before you open the ACS Template
Open a blank Excel spreadsheet. Use the following path:
File/Options/Trust Center/Trust Center Settings/Macro Settings
Make sure the Enable all macros is checked.
If the macros are not enabled, the spreadsheet will not work. If the spreadsheet still does not work with this setting chosen, then ask your computer administrator if there are network settings preventing the macro from running.
Example: The city of Orlando
The data can be found within the American Fact Finder home page:
http://factfinder.census.gov/faces/nav/jsf/pages/community_facts.xhtml (control + click to follow link).
Select the Housing tab then select Selected Housing Characteristics… under the most recent American Community Survey.
All data from the American Community Survey can be downloaded into an Excel format for easy use.
Hit OK; once the report fully loads hit Download, and then open the file. Make sure pop-ups are allowed for this site. Open the file and save it to wherever you want it stored for this assignment. Open the “ACS Template” Excel file provided.
The “ACS Template” file has three tabs: “ACS Template,” “ACS Input 1,” and “ACS Input 2.”
The first download is copied into “ACS Input 1.” The area copied does not need to be the same size as in the template.
Click on the green arrow in the upper lefthand corner to highlight the entire sheet and then hit Copy. Open the “ACS Template” file and click on the “ACS Input 1” tab. Highlight the entire sheet by hitting the arrow in the upper lefthand corner, and then hit Paste.
The supply portion of the for-sale analysis is now complete. On to demand.
Go back to the American Fact Finder screen. Above and to the right of the Download button is Back to Community Facts; hit that to retrieve the next data set.
The next data set is the Financial Characteristics … Download this file and retrieve as before. Repeat the same procedure as with the first download and Paste into “ACS Input 2”.
That’s it. All the calculations should be complete and the analysis finished.
Result Table
The result table is above. The left side of the table is for homeownership and the right side is for renter households.
The upper left hand corner indicates the effective date of the ACS data. The amount in yellow, $58,300, is the 2015 median income for Orange County, the county for the city of Orlando. The median income is from the Rent and Income Limits charts on the FHFC website.
Cells in yellow can be changed. Below the median income are the three bands that you can choose. The bands shown in this example are 0.0% to 30.0%, 30.1% to 60.0% and 60.1% to 120.0% of the Area Median Income. The next column shows the dollar amounts for the bands chosen. In this case, $0 to $17,490 for the first band, $17,548 to $34,980 for the second band, and $35,038 to $69,960 for the third band.
Both the owner and renter scenarios assume a 30% housing expense ratio. The % of income at 22% below is the percent of income for principle and interest only, leaving room for insurance and taxes.
Looking to the left of the chart, in the area highlighted green (shown above) the first band is for houses up to $65,300, the second band up to $130,400 and the last band $261,000. Affordability is based on a 3% downpayment and a 4.50% interest rate. This analysis is very sensitive to the interest rate; for example, if you change the interest rate to 8.00% there is a shortage of housing units in that price range within the first band.
Within the first band, there are 4,461 owner households with incomes between $0 and $17,490. The next column indicates that there are 5,745 housing units affordable within this band, resulting in a surplus of 1,283 housing units. The other bands indicate similar surpluses.
The analysis for renters indicates 15,109 renter households earning up to $17,490 and only 2,535 rental units affordable to them (rent of up to $437 per month) resulting in a shortage of 12,574 rental units. The other bands indicate surpluses. This analysis is not affected by the interest rate.