This is a pro forma excel question. It shouldn't be that hard but I'm struggling mightily with it, and pretty sure what I've come up with on my own is not correct.

Really appreciate your help! Thank you.

PRO FORMA ANALYSIS OF A STRIP CENTER My company is considering putting in an offer on a property located in a small college town that has the following attributes: 7,000 square feet of total rentable space Built in 2007 100% fully leased Four tenants - Subway, Dominos, Starbucks, and Campus Liquor Average blended rental rates for the tenants is $12.50/sqft/yr Rental rates are projected to increase annually by 5% after remaining fixed for the first two years of the analysis period Total operating expenses for the center are $5.25/sqft/yr and are projected to increase by 3% per year for the entire analysis period - operating expenses include property management costs and property taxes We are able to collect 90% of all operating expenses from the tenants not including our collection reserve. (NOTE - Setting this up can be tricky - be careful about circular references in Excel!) All four leases are long-term leases but we project that we will experience some rent collection difficulties due to summer-time lulls in activity. We will assume 3% of rents and reimbursable expenses will be uncollectible Capital expenditures are estimated to total $1.00/sqft/yr Initial Cap Rate = .075 (7.5%) Reversion Cap Rate = .070 (7.0%) Sales Costs at reversion = 2% of gross proceeds Building is depreciable over 39 years (straight line) Debt Info: 70% LTV 20 year amortization period 5.25% interest rate Assume a Three-year Hold with Sale at the end of year 3. From this information, create a detailed pro forma for the hold period (preferably using Excel). If you feel that certain information is missing that is essential to completing the pro forma, identify what it is and then make an educated assumption to fill the data void so you can proceed with the pro forma development. Ultimately, you will want to determine the following: 1) What is the approximate price I should offer for this property based on the cap rate given and applied against the project NOI for Year 1 determined using the information above? 2) What is the IRR for the project from the standpoint of the equity investors? Assume sale of the property at the end of year 3's operations. Disregard taxes to investors (for now!) but if you will feel better knowing about taxes, assume they are 30% to the equity investors. 3) Summarize key items you may have had to assume to arrive at your answers and let me know if you would pursue this purchase or not if the equity investors generally demand a 12% return on their investment. Submit your completed pro forma including the answers to the items requested above through the Mid-Term Exam assignment link included in the Week 6 packet of materials. I award lots of partial credit so give it your best shot! This is meant to be a challenging learning exercise