New Model
The appendices B, C, D and E have been made to show the new calculations while there are not major changes which were made in the calculations. However, minor changes have been made in the excel sheet such as the discounting rate has been rounded to 10% for all the debt. On the other hand, the hedge fund has been excluded from the scenario. Moreover, the third source is assumed as external equity. In addition, the pro forma of the company is indicating that the company will have negative cash flows initially while they will be converted in to significant positive amounts. Finally, the construction budget has been made by adding the per floor cost to the initial uses of sources to reach the final balance. However, the cash flows valuation still indicatesthat the project is not feasible as the value of NPV is still negative after discounting the inflows at a rate of 10%.
Appendices
Appendix A
| Pro Forma | 4Q 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 |
| Rent Roll | $80,451 | $551,876 | $710,648 | $768,735 | $768,735 | $768,735 | $845,609 | $930,169 | $1,023,186 |
| Vacancy (10%) | -8,045 | -55,188 | -71,065 | -76,874 | -76,874 | -76,874 | (84,561) | (93,017) | (102,319) |
| Effective Gross Income | 72,406 | 496,689 | 639,583 | 691,862 | 691,862 | 691,862 | 761,048 | 837,152 | 920,868 |
| Op Ex @ $4.50 | -37,634 | -150,273 | -167,699 | -185,126 | -185,126 | -185,126 | (34,247) | (37,672) | (41,439) |
| CFO | 34,772 | 346,416 | 471,884 | 506,736 | 506,736 | 506,736 | $726,801 | $799,481 | $879,429 |
| First Mortgage | -20,282 | -81,128 | -81,128 | -81,128 | -81,128 | -81,128 | (116,288) | (127,917) | (140,709) |
| Second Mortgage | -25,875 | -103,500 | -103,500 | -103,500 | -103,500 | -103,500 | (145,360) | (159,896) | (175,886) |
| Bridge Loan | -22,500 | -90,000 | -90,000 | -90,000 | -90,000 | -90,000 | (127,500) | ||
| CFAF | ($33,885) | $71,788 | $197,256 | $232,108 | $232,108 | $232,108 | $337,652 | $511,668 | $562,834 |
Appendix B
Sources and Uses Table
| Sep-08 Purchase | Jan-09 Construction Funding | Dec-09 Year End | Jan-10 2nd Phase Construction | Dec-10 Year End | |
| Sources | |||||
| First Mortgage From Seller | $1,081,705 | ||||
| Second loan form bank | 1,035,000 | ||||
| raise equity outside the firm | 600,000 | ||||
| Equity | 563,885 | ||||
| Total Sources | 2,245,590 | 1,035,000 | |||
| Uses | |||||
| Purchase Price | |||||
| 1,802,705 | |||||
| Capital Improvements Contingency Developers Fee | 125,000 | 794,000 | 483,000 | ||
| Leasing Fee | 50,000 | 20,000 | |||
| Mortgage origination fees | 40,000 | ||||
| Legal Costs | 50,000 | ||||
| Interest Expense | 33,885 | 103,500 | 68,001 | ||
| Cost of equity 10% assumed | 60,000 | ||||
| Total Uses of Funds | 2,161,590 | 794,000 | 123,500 | 483,000 | 68,001 |
| Surplus/Deficit Cash | 84,000 | 241,000 | -123,500 | -483,000 | -68,001 |
| Cumulative Cash Balance | $84,000 | $325,000 | $201,500 | ($281,500) | ($349,501) |
Case Analysis Steel Street Harvard Case Solution & Analysis
Appendix c
Pro Forma
| Pro Forma | 4Q 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 |
| Rent Roll | $80,451 | $311,183 | $342,301 | $376,531 | $414,185 | $455,603 | $501,163 | $551,280 | $606,408 | $667,048 | $733,753 |
| Vacancy (10%) | -8,045 | -31,118 | -30,284 | -30,283 | -30,282 | -30,281 | -30,280 | -30,279 | -30,278 | -30,277 | -30,276 |
| Effective Gross Income | 72,406 | 280,065 | 312,017 | 346,248 | 383,903 | 425,322 | 470,883 | 521,001 | 576,130 | 636,771 | 703,477 |
| Op EX @ $4.50 a | -37,634 | -96,458 | -65,925 | -65,924 | -65,923 | -65,922 | -65,921 | -65,920 | -65,919 | -65,918 | -65,917 |
| CFO | 34,772 | 183,607 | 246,092 | 280,324 | 317,980 | 359,400 | 404,962 | 455,081 | 510,211 | 570,853 | 637,560 |
| First Mortgage | -108,171 | -108,171 | -108,171 | -108,171 | -108,171 | -108,171 | -108,171 | -108,171 | -108,171 | -108,171 | -108,171 |
| Second Loan | 0 | -103,500 | -103,500 | -103,500 | -103,500 | -103,500 | -103,500 | -103,500 | -103,500 | -103,500 | -103,500 |
| Cost of equity 10% assumed | -60,000 | -60,000 | -60,000 | -60,000 | -60,000 | -60,000 | |||||
| CFAF | ($133,399) | ($88,064) | ($25,578) | $8,654 | $46,309 | $87,730 | $193,292 | $243,410 | $298,540 | $359,183 | $425,890 |
This is just a sample partial case solution. Please place the order on the website to order your own originally done case solution.
Related Case Solutions & Analyses:
Zopa: The Power of Peer-to-Peer Lending
The Rejuvenated International Monetary Fund
Fuel Cell Technology and Market Opportunities
Cortlandt Town Center
Corporate Solutions at Jones Lang LaSalle (2001)
Environmental Quality International in SIWA
Techniques to Detect Corporate Expense Fraud via Forensic Accounting
RESTRUCTURING SONY
Cola War
Carrefour S.A.
