Please see the attachment and prepare financial analysis using spreadsheet and determine whether or not they should go with the project.

For the exclusive use of Z. Wu, 2016. REVISED APRIL 14, 2016 CRAIG FURFINE KEL854 Back to School: Real Estate Development of Off-Campus Student Housing \"Worth a closer look,\" they said in unison. Kimberly Slater and her longtime friend, Christopher Lenard, had just agreed to explore the possibility of developing off-campus student housing in Madison, Wisconsin, home of the University of Wisconsin's flagship campus. In the summer of 2012, the demographics and market conditions both looked good. However, a more careful analysis of the potential returns to the investment was warranted before the two friends would be willing to invest their money. The Partnership Kimberly Slater met Christopher Lenard in the 1990s in the Reserve Officers' Training Corps program at Purdue University, where they both studied mechanical engineering while also learning to fly fighter jets. Their years after the Air Force took them in different directions, but they kept in touch. Slater had settled in Florida, where she worked on a team developing advanced electrooptical systems for a major national defense contractor. Like many others around her, she invested in real estate on the side, occasionally buying small houses, refurbishing them during her spare time, and then reselling into the booming market. With residential property prices soaring throughout Florida during the early 2000s, Slater was able to save nearly $2 millionsomething she could never have accomplished on her engineering salary alone. As a result, when the market crash came, she saw it as an opportunity and not a crisis. Finding greater enjoyment from the handson work of refurbishing real estate, she quit her engineering job and decided to take a shot at being a real estate developer. In 2010 real estate development opportunities were few and far between, but she could see that with the economy in trouble, young people were going back to school. With this insight, she bought a small property near the Gainesville campus of the University of Florida, tore it down, and managed the development of a fifteen-unit luxury apartment building, Juniper Vista, which was designed to be used as off-campus student housing (Exhibit 1). Demand was strong for high-quality housing near the University of Florida campus, and so by May 2012, the soon-to-be completed project was 100% pre-leased for a fall 2012 occupancy. With the leases in place, Slater was able to sell the project to a local property portfolio manager at a substantial profit. Like most real estate developers, Slater wanted to reproduce the success she had 2014 by the Kellogg School of Management at Northwestern University. This case was developed with support from the June 2009 graduates of the Executive MBA Program (EMP-73). This case was prepared by Professor Craig Furfine with research assistance from Eric Nyman '13. Cases are developed solely as the basis for class discussion. Cases are not intended to serve as endorsements, sources of primary data, or illustrations of effective or ineffective management. To order copies or request permission to reproduce materials, call 800-545-7685 (or 617-783-7600 outside the United States or Canada) or e-mail custserv@hbsp.harvard.edu. No part of this publication may be reproduced, stored in a retrieval system, used in a spreadsheet, or transmitted in any form or by any means electronic, mechanical, photocopying, recording, or otherwisewithout the permission of Kellogg Case Publishing. This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. BACK TO SCHOOL KEL854 in Gainesville. She had a great building design that could easily be adapted to most locations. She knew how much it cost and how long it took to build. The only question remaining was where. After leaving the Air Force, Lenard had bought a sailboat and settled in the small town of Fontana, Wisconsin, on the shore of beautiful Lake Geneva. After working a few years as a project management consultant for a Midwestern integrated management company, Lenard, too, became interested in real estate. He spent several years as a real estate acquisitions analyst for a major asset manager and began pursuing his MBA during the evenings at the Kellogg School of Management, requiring him to commute twice weekly to downtown Chicago. By May 2012, the completion of his MBAwith majors in finance, entrepreneurship and innovation, and real estatewas in sight. His coursework had sparked a keen interest in trying a real estate project on his own. For capital, he had a $500,000 inheritance received from a great-aunt a few years earlier, and he had $250,000 in savings beyond what he thought necessary to save for his eventual retirement. Living in Wisconsin, he had become aware of the common perception that there was a shortage of housing that was convenient for students attending the state's flagship campus in downtown Madison. Moreover, the existing housing stock was largely of poor quality. Knowing about Slater's experience in developing student housing in Gainesville, he contacted his friend to discuss the possibilities of being equity partners on a new development project. The City of Madison Slater and Lenard began their research by learning the basic facts about Madison. Madison is the capital of Wisconsin and the county seat of Dane County. As of the 2010 census, Madison had a population of 233,209, an increase of 12.1% since 2000. The Madison Metropolitan Statistical Area (MSA) had a 2010 population of 568,593.1 The city had traditionally been a center of government and education, but over the past twenty years it had seen a boom in high-tech firms. The two largest employers in the area were still the State of Wisconsin and the University of Wisconsin (UW). Beginning in the early 1990s, however, an aggressive technology transfer program at UW had fostered the growth of technology startups. Companies were attracted to the city's highly educated work force: 48.2% of Madison's population over the age of 25 held at least a bachelor's degree and 20.9% had a graduate or professional degree.2 In 2009, in the midst of a recession, Madison had an unemployment rate of only 3.5% and Forbes magazine ranked the city number one in a list of top ten cities for job growth.3 Slater and Lenard also wanted to get a sense of the barriers faced by real estate developers. Of particular importance to Madison was that the downtown area is located on a narrow isthmus between two lakes (Exhibit 2). The State Capitol building is on a hill in the center of the isthmus, surrounded by office buildings for state government and associated businesses. UW was originally founded on a hill to the west of the capitol. UW had expanded westward along the shore of Lake Mendota. The lakes were natural barriers to development, reducing the amount of available land in 1 U.S. Census Bureau, http://www.census.gov (accessed July 1, 2012). City-Data.com, http://www.city-data.com/city/Madison-Wisconsin.html (accessed July 1, 2012). 3 \"In Pictures: 10 Cities Where They're Hiring,\" Forbes, January 5, 2009, http://www.forbes.com/2009/01/05/cities-jobs-employmentleadership-careers-cx_tw_0105cities_slide.html. 2 2 KELLOGG SCHOOL OF MANAGEMENT This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. KEL854 BACK TO SCHOOL the downtown area. Nevertheless, nearly 80,000 adults lived within three miles of campus (Exhibit 3). Current Housing Options Slater and Lenard then wanted to know whether the population of students could be expected to generate persistent high demand for off-campus housing. Lenard knew just the right person to speak with. At the previous summer's Geneva Lake Sailing School's Dinghyfest Regatta, Lenard had met Matthias Yamato. Yamato was not only another sailing enthusiast but also the assistant director of campus housing at UW, with a wealth of information on the UW student body and its housing options. From Yamato, Slater and Lenard learned that student enrollment at UW had been highly stable for nearly a decade (Exhibit 4). Of the approximately 42,000 undergraduate and graduate students, however, UW housed only 6,828 on campus during the 2011-12 school year. This total included about 88% of the freshmen class, with 80% of the on-campus residents being either freshmen or transfer students.4 UW had recently constructed two new residence halls, Smith Hall (2006) and Ogg Hall (2008), and was expanding Lakeshore Hall (to be completed in 2013). The resulting on-campus capacity would grow to accommodate approximately 7,500 students and no additional construction was planned.5 This meant that the vast majority of students, and even some freshmen, resided off campus and would continue to do so for the foreseeable future. Some of the university's need for additional housing was met by private properties that operated like on-campus residence halls. The UW-Madison Private Housing Connections (PHC) program was developed in the fall of 2009 in a partnership between UW and five participating private properties. Some were newly constructed, while others were existing properties that were brought into the PHC program. These properties rented entire apartment units or provided roommate placement services. They also provided amenities normally found in on-campus housing, such as community rooms, programs designed to foster socialization, resident assistants, and in some cases, meals. The overwhelming majority of students were housed in traditional apartment buildings, in which no student services were provided, or in hundreds of vintage single-family-style homes that were approximately eighty to one hundred years old and had been divided into apartments (rented on a per-unit basis) or rented in their entirety to groups of students (on a per-room basis) (Exhibit 5). Thus, student demand played an important role in the near-campus rental market, which contained approximately 26,000 rental units.6 Market Data It was easy for Yamato to share with Lenard information on the rental cost of the two university-affiliated student housing options. On-campus housing costsinclusive of mandatory 4 Karen Herzog, \"UW Sets Sights on Campus Housing,\" Milwaukee Journal-Sentinel, September 24, 2011. UW System Physical Development Plan, 2011-2017. 6 There were 26,113 rental units in the five zip codes surrounding the UW campus as of March 2012, according to Madison Gas and Electric utility data. 5 KELLOGG SCHOOL OF MANAGEMENT 3 This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. BACK TO SCHOOL KEL854 fees but exclusive of optional meal plansvaried across dormitories and room types. Exhibit 6 details the high and low end of the pricing scale. Because on-campus housing was only provided during the academic year, Slater and Lenard calculated the equivalent monthly rate.7 The \"double\" rate was for a basic bedroom shared by two persons with a communal bathroom for the entire floor. Students had the option to pay more for a single-occupancy room, and rooms with private bathrooms were more expensive. Some of the newer residence halls had additional charges. The most expensive on-campus room was a single with a private bathroom in Smith Hall. Rental costs in the PHC program were more varied because of wider variances in room size, quality, and amenities offered. Yamato was forthright in admitting when there was something he did not know. When Lenard asked about off-campus options, Yamato quickly referred him to Grace Peters at the Campus Area Housing Listing Service (CAHLS). Peters provided Lenard with average rents in the nearby community. Beyond that, she directed Lenard to the CAHLS website, where a wealth of current and historical information about off-campus housing choices was available.8 A more detailed analysis was possible with the unit-specific information available. For example, Slater and Lenard understood that rental averages were not sufficient to predict what rent they could hope to receive. For the proposed luxury student apartment complex they had in mind, they expected to charge among the highest rents in the market. Using the prices advertised on the CAHLS website, Slater and Lenard calculated a distribution of asking rents for the 2011-12 school year (Exhibit 7). The website had listings for 1,636 units, so it seemed like a reasonable proxy for prices throughout the campus area. One limitation was that the data were asking prices, not contracted prices, which may have been lower. Another limitation was that the data provided by the service did not indicate what utilities were included in the rent and whether parking was available and/or included. These variables could substantially affect the value of the apartment. The rental listing service also provided limited historical information on rents in the market (Exhibit 8). Rents in the area had been growing between 2.5% and 5% over the past several years. To look at the market for off-campus student housing more finely, Slater and Lenard looked more closely at housing options in the five zip codes closest to campus. Although not every offcampus student lived in one of these zip codes, it seemed reasonable to assume that a majority of students, especially undergraduates, wanted to be able to walk or bike to campus. Therefore, taken in aggregate, the data on multifamily housing in these zip codes was indicative of the student housing market in downtown Madison. Peters referred them to Brock Morga, the community research liaison for Madison Gas and Electric. The local utility company had devised a methodology that allowed one to measure the vacancy rates within any subset of its service area.9 It was immediately apparent that these areas had extremely low vacancies and that the vacancy level had been trending lower in recent years (Exhibit 9).10 7 Annual fee divided by 12. Campus Area Housing Listing Service, http://housing.civc.wisc.edu (accessed July 1, 2012). 9 Madison Gas & Electric counted a rental unit as vacant if both the gas and the electric service were inactive, or if the utilities had been transferred to the unit owner's name. 10 The spikes in the second quarter of each year were due to unit turnover at the end of the academic year, when many landlords temporarily transferred the utilities to their own name. 8 4 KELLOGG SCHOOL OF MANAGEMENT This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. KEL854 BACK TO SCHOOL Given the low vacancy in existing off-campus housing, it came as no surprise to Slater and Lenard that there were no vacant land parcels for sale close to campus. Therefore, developing their apartment project would require them to acquire a parcel or parcels currently occupied by older buildings, raze them, and redevelop the site. The areas of interest were already zoned for highdensity residential property and typically were occupied by the vintage housing stock common throughout the downtown area. Thus, of particular interest to Slater and Lenard was the price at which the vintage homes sold. Searching past records in the multiple listing service (MLS) used by realtors, Slater and Lenard found 167 comparable sales of multifamily properties in an area around the UW campus (Exhibit 10). This area contained most of the rental housing units that would appeal to UW undergraduate students. Because the number of units and bedrooms per property varied greatly, it was essential to make a fair comparison between them in order to analyze trends. Ideally, properties could be compared by their cap rates11 (Exhibit 11), but because most of these properties were owned by small, private investors, public reporting of net operating income (NOI) and/or cap rates was spotty. Another possible means of comparison would be price per square foot, but reporting of square footage was also unreliable, as many of the listing brokers did not report this statistic in the MLS. Besides, Slater and Lenard knew that many properties had been renovated over the years, and so the exact square footage might not be known. In any case, students might not care all that much about square footage. What mattered to them most was the number of bedrooms, as this was how student housing rents were typically quoted. Thankfully, bedroom count was accurately reported in MLS listings and was able to be cross-checked with property tax records on the City of Madison Assessor's website. Thus, it was reasonably straightforward to calculate sales prices on a perbedroom basis (Exhibit 12). The Proposed Project Given Slater's familiarity with the design, she and Lenard planned to construct something very similar to the Juniper Vista property in Gainesville. By re-using the existing design, the developer would save money and time during the planning phase. The Madison property would consist of a single three-story building containing forty-two bedrooms in fifteen apartment units with a total square footage of 22,950, though only 87% of that space would be rentable. The units would be a mix of nine two-bedroom/two-bathroom units and six four-bedroom/four-bathroom units (Exhibit 13). The building would feature private bathrooms, open floor plans, in-unit laundry, high ceilings, and balconies. It would be constructed using traditional wood framing and did not have any elevators or common areas. The only notable design change for the Madison project was to create a single, secured entrance with climate-controlled interior hallways. This change was made because of Madison's colder climate and its urban setting, where tenants preferred a heated and secured entrance. Slater and Lenard did not believe these small changes would affect the building costs, so they assumed the Madison property could be built for the same $109 per square foot cost that Juniper Vista had. The partners further assumed hard demolition costs of $75,000, and total soft costs were estimated at 3.25% of total hard costs. 11 Cap rate, or capitalization rate, is defined as net operating income divided by sales price. It is a means of expressing the annual income yield per dollar invested in an asset. KELLOGG SCHOOL OF MANAGEMENT 5 This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. BACK TO SCHOOL KEL854 Slater and Lenard assumed that the construction cost and timeline would be approximately the same as those for Juniper Vista, so it was a simple matter to determine that if they could acquire the necessary land by the end of the year, the building could be ready for occupancy in time for the 2014-15 school year (Exhibit 14). Back-of-the-Envelope Feasibility To quickly check the project's financial feasibility, Slater and Lenard quickly did a back-ofthe-envelope calculation to determine approximately how much they could spend on acquiring the necessary land. They started by constructing a rent roll, which calculated the total income to be collected from the proposed property, assuming that the building was already in place and occupied (Exhibit 15). They assumed they could charge above-average rents because they were building a luxury property. For parking, they simply estimated the rental rates from a survey of advertised parking rents on various websites.12 Expenses were also estimated as if the building were currently in existence. With this information, a \"current\" income statement was easily constructed (Exhibit 16). Slater and Lenard assumed that upon completion, the building would be able to support permanent financing up to a 75% loan-to-value ratio (LTV) that required a minimum debt service coverage ratio of 1.25. Current mortgage rates for multifamily properties were 4.625% and were amortized over twenty-five years. From these assumptions, it was determined that they could spend a little over $1.6 million for site acquisition and the project would still be financially feasible (Exhibit 17). Based on the analysis of comparable multifamily sales, the average lot size was about 5,000 square feet. The proposed development required about 14,000 square feet of land, so on average, three existing properties would need to be acquired. Because the average sales price of the comparable properties was $366,223, Slater and Lenard believed the required land could be purchased for about $1.1 million, well below the $1.6 million threshold of feasibility. Next Steps Slater and Lenard were excited about the possibility of working together on a real estate development project. Slater, having made a profit on Juniper Vista, was ready to start scouting for land. Lenard agreed that it didn't hurt to start looking but took a more cautious viewpoint. In particular, he was worried that there was more to analyze than simply whether or not the project seemed feasible. That is, just because the project could move forward did not necessarily mean that it should. There were income-producing properties already on the land they would want to acquire; maybe it didn't make sense to raze them. Lenard sat down and fired up his laptop. Thankfully, the operating system needed to install the latest updates, which gave him plenty of time to think. Certainly, a more in-depth financial analysis 12 It was common practice in Madison for parking spaces to be an additional rental charge at apartment buildings. 6 KELLOGG SCHOOL OF MANAGEMENT This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. KEL854 BACK TO SCHOOL was warranted before he would invest a substantial sum of his own money into his first real estate project. He paused, thinking back to his real estate finance class, where his professor always cautioned, \"Skilled financial analysts can make a spreadsheet to justify anythingso think carefully about your assumptions.\" In particular, he was concerned that Slater and he had incorporated many assumptions straight from Florida. He also realized that none of their analysis had considered the fact that it would take time to build and that any profit on it would not be realized for a long time. And even if the project turned out to be profitable, did that mean that it was the best thing to do with his savings? KELLOGG SCHOOL OF MANAGEMENT 7 This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. BACK TO SCHOOL KEL854 Exhibit 1: Juniper Vista, Gainesville, Florida Source: Image provided by featured organization. 8 KELLOGG SCHOOL OF MANAGEMENT This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. KEL854 BACK TO SCHOOL Exhibit 2: UW-Madison and Its Surroundings UW-Madison State Capitol Central Business District Source: Image created by case author. Exhibit 3: Population Near the UW Campus Population 1-mi. 3-mi. 2011 total adult population 33,123 79,485 156,545 2011 total daytime population 93,313 183,179 307,275 22 26 32 2011 total households 13,576 38,493 82,217 % population change 1990-2011 15.5% 3.8% 4.8% 2011 median age total population % household change 1990-2011 5-mi. 27.4% 9.5% 10.1% 2011 median household income $15,239 $31,640 $41,218 2011 average household income $43,580 $61,873 $67,533 Source: LoopNet, http://www.loopnet.com (accessed July 1, 2012). KELLOGG SCHOOL OF MANAGEMENT 9 This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. BACK TO SCHOOL KEL854 Exhibit 4: Student Population 45000 40000 35000 Campus Total 30000 Undergrad Total 25000 Senior 20000 15000 Junior 10000 Sophomore Freshmen 5000 0 Source: University of Wisconsin Enrollment Report, Fall Semester 2011-2012. Exhibit 5: Typical Off-Campus Housing Choices SMALL APARTMENT BUILDING CONVERTED VINTAGE HOMES Source: Images provided by case author. 10 KELLOGG SCHOOL OF MANAGEMENT This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. KEL854 BACK TO SCHOOL Exhibit 6: Current Student Housing Choices and Prices UNIVERSITY-AFFILIATED HOUSING FEES On-Campus Residence Halls Annual Fee ($) Equivalent Monthly Rate ($) Double 6,779.00 564.92 Double with bath 7,538.00 628.17 Single 7,569.00 630.75 Room Type Single with bath 7,785.00 648.75 Smith Hall single 8,358.00 696.50 Smith Hall single with bath 8,552.00 712.67 UW-Madison Private Housing Connections (PHC) Program Room Type Lucky 101 Regent 101 PH Apartments Statesider The Towers on State Fee ($) Monthly Per-Bed Rate ($) Double 7,650.00/year 637.50 Single 12,150.00/year 1,012.50 Premium Single 15,300.00/year 1,275.00 Double 5,085.00/year 423.75 Single 7,479.00/year 623.25 Premium Single 8,352.00/year 696.00 1 Bedroom (1 person/bedroom) 1,375.00/month 1,375.00 2 Bedroom (1 person/bedroom) 2,380.00/month 1,190.00 3 Bedroom (1 person/bedroom) 2,550.00/month 850.00 4 Bedroom (1 person/bedroom) 2,860.00/month 715.00 5 Bedroom (1 person/bedroom) 4,210.00/month 842.00 Standard Room (2 people/bedroom) 1,195.00/month 597.50 Deluxe Quad (2 people/bedroom) 1,395.00/month 697.50 Premium Suite (2 people/bedroom) 1,495.00/month 747.50 Executive Suite (2 people/bedroom) 1,695.00/month 847.50 Presidential Suite (2 people/bedroom) 1,895.00/month 947.50 850.00/month 850.00 2 Bed/1 Bath (1 person/bedroom) Efficiency 1,650.00/month 825.00 2 Bed/2 Bath (1 person/bedroom) 1,900.00/month 950.00 Source: UW Division of University Housing, Projected 2012-13 Academic Year Rate. KELLOGG SCHOOL OF MANAGEMENT 11 This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. BACK TO SCHOOL KEL854 Exhibit 6 (continued) AVERAGE OFF-CAMPUS HOUSING RENTS Room Type Range ($) Average Monthly Rent ($) Average Monthly Per-Bed Rate ($) Efficiency/Studio 368-1,125 611 611.00 1 Bedroom 535-1,425 764 764.00 2 Bedrooms 635-2,345 1,127 563.50 3 Bedrooms 850-2,810 1,570 523.33 4 Bedrooms 1,150-3,720 2,082 520.50 Source: Campus Area Housing Listing Service, http://housing.civc.wisc.edu (accessed July 1, 2012). 12 KELLOGG SCHOOL OF MANAGEMENT This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. KEL854 BACK TO SCHOOL Exhibit 7: Rental Rates for Off-Campus Housing 2 Bedroom 1 Bedroom 40 20 20.00% 20.00% 0 20 0.00% 0.00% 0 200 400 600 800 1000 1200 1400 2400 40.00% 40.00% More 60 2200 60.00% 40 60.00% 2000 80.00% 60 1800 80 80.00% 80 1600 100.00% 1400 100.00% 1200 120.00% 100 800 120 1000 100 120.00% 600 Frequency 120 Frequency 140 Asking Rent Asking Rent 4 Bedroom 120.00% 50 100.00% 40 80.00% 30 60.00% 20 40.00% 10 20.00% 0 0.00% Frequency 60 800 1000 1200 1400 1600 1800 2000 2200 2400 2600 2800 3000 Frequency 3 Bedroom 35 30 25 20 15 10 5 0 120.00% 100.00% 80.00% 60.00% 40.00% 20.00% 0.00% Asking Rent Asking Rent Source: UW Housing Office, compiled from asking rents for apartments advertised on CAHLS website. Exhibit 8: Rent Levels and Their Recent Growth 6.00% 2500 5.00% 2000 4 Br 1500 3 Br 4.00% 1000 2 Br 3.00% 1 Br 2.00% Studio 1.00% 500 0 2007 2008 2009 2010 2011 2012 0.00% 1 Br Average Rent 2 Br 3 Br 4 Br Studio Compounded Annual Growth Rate, 2008-2011 Source: UW Housing Office, compiled from asking rents for apartments advertised on CAHLS website. Note: 2009 data unavailable. KELLOGG SCHOOL OF MANAGEMENT 13 This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. BACK TO SCHOOL KEL854 Exhibit 9: Vacancy Rates in Near-Campus Zip Codes 12.00 10.00 8.00 Capitol (53703) Far West (53705) 6.00 Far South (53711) Near South (53715) 4.00 Near West (53726) 2.00 0.00 Mar05 Mar06 Mar07 Mar08 Mar09 Mar10 Mar11 Mar12 Source: Compiled from Madison Gas and Electric company utility connections data. Exhibit 10: Near-Campus Sales Prices $2,500,000 $2,000,000 $1,500,000 $1,000,000 $500,000 $0 2005 2006 2007 2008 2009 2010 2011 2012 Source: Multiple listing service. 14 KELLOGG SCHOOL OF MANAGEMENT This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. KEL854 BACK TO SCHOOL Exhibit 11: Near-Campus Cap Rates 9.00% 8.00% 7.00% 6.00% 5.00% Campus Area 4.00% Greater Madison 3.00% 2.00% 1.00% 0.00% 2007 2008 2009 2010 2011 2012 2013 Source: MLS records, LoopNet.com, and Real Capital Analytics (accessed July 1, 2012), and author calculations. Exhibit 12: Near-Campus Sales Prices Per Bedroom $140,000 $120,000 $100,000 $80,000 $60,000 $40,000 $20,000 $0 2005 2006 2007 2008 2009 2010 2011 2012 Source: MLS records, LoopNet.com, and Real Capital Analytics (accessed July 1, 2012), and author calculations. KELLOGG SCHOOL OF MANAGEMENT 15 This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. BACK TO SCHOOL KEL854 Exhibit 13: Apartment Floor Plans TWO-BEDROOM LAYOUT FOUR-BEDROOM LAYOUT Source: Images provided by featured organization. 16 KELLOGG SCHOOL OF MANAGEMENT This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. KEL854 BACK TO SCHOOL Exhibit 14: Project Timeline Date Estimated Cost Hard Construction Construction Loan Payoff Completed Asset Value 8/31/2013 9/30/2013 8/31/2014 8/31/2014 $75,000 $2,576,550 Land Acquisition Demolition 12/31/2012 ?? ?? Timing Assumptions: Hard construction costs are financed with a construction loan. The first draw occurs to pay for the demolition, with twelve subsequent draws assumed to be evenly spread over twelve months (from September 2013 through August 2014) to cover all hard construction costs. Soft costs, equal to 3.25% of total hard construction costs, are assumed to be evenly spread over the entire twenty months. Exhibit 15: \"Current\" Rent Roll Sq Ft Rent/Mo ($) Rent/Year ($) Rent/Mo/Room ($) # of Units Bedrooms 2 Br/2 Ba 9 18 1,150 1,800.00 21,600.00 4 Br/4 Ba 6 24 1,600 3,250.00 39,000.00 812.50 Total 15 42 19,950 35,700.00 428,400.00 850.00 Apartments Unit Style Parking Spaces Total 15 97.50 1,170.00 15 1,462.50 17,550.00 900.00 KELLOGG SCHOOL OF MANAGEMENT 17 This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017. For the exclusive use of Z. Wu, 2016. BACK TO SCHOOL KEL854 Exhibit 16: \"Current\" Income Statement ($) Revenue Gross rental income 428,400.00 Parking income 17,550.00 Vacancy loss (2%) (8,919.00) Credit loss (1%) Total income (4,459.50) 432,571.50 Expenses Real estate tax Common area electric 86,725.25 3,500.00 Common area gas 2,000.00 Common area cleaning 2,600.00 Security system Landscaping maintenance Landscaping irrigation 860.00 1,800.00 696.99 Exterminating 600.00 Snow removal 3,000.00 Garbage service 2,400.00 HVAC service contract 2,100.00 General maintenance 3,000.00 Unit turnover cleaning 7,500.00 Insurance Management fee Total expenses Net operating income 10,000.00 34,605.72 161,387.96 271,183.54 Exhibit 17: Back-of-the-Envelope Financial Feasibility ($) NOI 271,184 Divide NOI by minimum DSCR = 1.25 216,947 Divide by 12 (monthly debt service) Supportable mortgage amount 18,079 3,211,451 Divide by 75% LTV 4,281,934 Construction cost 2,657,850 Supportable site acquisition cost 1,624,084 Note: Method adapted from David Geltner and Norman G. Miller, Commercial Real Estate Analysis and Investments (Cincinnati, Ohio: SouthWestern Publishing, 2001). 18 KELLOGG SCHOOL OF MANAGEMENT This document is authorized for use only by Zhaoxuan Wu in Real Estate Finance F16(2) taught by Patrick Iaboni, York University from October 2016 to April 2017