Mind critiquing my model?
Greetings,

I don't know much about commercial real estate, but I'm trying to read and learn more. I created the linked model with formulas I found in a book I am reading. I was hoping to find a fast way to project earnings on a specific property while taking the majority of costs into account. 

My request - I was hoping some of you could mess around with the model and see if it is producing realistic results. Any advice would be appreciated.

https://dl.dropboxusercontent.com/u/73400499/Commercial%20Real%20Estate%20Calculations.xlsx
[{"id": "d5rpvno", "text": "First of all, great job jumping in feet first. It looks like you are thinking about this in the right ways. My advice would be to simply clean up your model. A clean, rigorous model requires clean, rigorous thought. \n\nInstead of putting everything on one sheet, with information spread all over the place and black fills to hide stuff, you should use tabs to hide detailed tables (like amortization tables or month-by-month operating budgets, for example) or to hide formulas that you don't need to access all the time. Have an executive summary tab, showing all of the numbers that you would use to analyze the deal. Everything else should be hidden in the workbook. My pro forma template has tabs for summary, debt, equity, project costs, operating income, operating expenses, and taxes. They all feed through to the summary. It might sound complicated, but most of those tabs only have 5-6 fields that you need to enter.\n\nTo be honest, I could never give you proper feedback on the inner workings of this model because it's sort of confusing to follow. ", "replies": [{"id": "d5ru510", "text": "Plus the sheet is protected so F2 wont show me formulas so we cant critique it that way", "replies": [{"id": "d5rvv2w", "text": "Sorry about that. I hope my other response clears up some of my calculations used.", "replies": [], "depth": 3}], "depth": 2}, {"id": "d5rvlcu", "text": "Thanks for the advice. It definitely needs to be cleaned up. I started out just taking notes from the formulas in the books and kept building it without a real solid plan. My idea was to structure it so you could basically rank a property's quality, plug in the SF, plug in the average market prices, and get the resulting metrics. \n\nCould you comment on some of the assumptions I made? -\n(most of these are modeled towards office/retail/apartments)\n\n1) Annual mortgage payments = 4.5% of price/----------/\n2) Acquisition transaction costs = 5% of price for buyer/----------/\n3) Yearly upkeep = 3% of price/----------/\n4) Property appraised for taxes = 75% of price/----------/\n5) Monthly Utilities = Square feet * 12%/----------/\n6) Property mgmt cost = 10% of rent/----------/\n7) Initial capital Injection = 10% of price/----------/\n8) Resale transaction cost = 6% of price (for seller)/----------/\n----------/\n\n", "replies": [{"id": "d5rvquk", "text": " Practical Application: ----------/ 1000 SF office building (dead average quality for city)/----------/ Rented at $17/SF/year (city average)/----------/ Acquired at $110/SF (city average)/----------/ Appreciation rate = 1% yearly/----------/ Inflation rate = 3% yearly/----------/ Holding period: 5 years/----------/ NOI = $9492/----------/ Cash Flow = $4542/----------/ Cap rate = 8.6%/----------/ _____________ Sale occurs______/----------/ ROI at exit = 45% ($19588)", "replies": [{"id": "d5rweci", "text": "If you want someone to help you, i'd make it easiest for someone to look at the inner-workings. Many people on this forum already use or have access to corporate level spreadsheets. So not one is going to be stealing your spreadsheet and stealing big money dealzz from you\n\nI'd post the unprotected sheet. Who knows, someone may come back with a totally updated sheet that looks smooth and effective.", "replies": [], "depth": 4}], "depth": 3}, {"id": "d5s0q7e", "text": "Why don't you start with ONE business plan, and then create different versions of your model to cover an outsale scenario, a recap, etc. The simplest is a buy and hold rental model. If you don't care as much about IRR you can further simplify by not modeling an exit, but instead treating your project as cash-flowing in perpetuity. \n\nBe careful about where you use rules of thumb, especially in a proforma. Use them sparingly. Some of your cost variables tend to vary dramatically depending on the project, such as the costs of 'yearly upkeep', utilities, and debt service.  Only use rules of thumb for back-of-the-envelope math, like when doing a quick cap rate calc. Budget as accurately as possible for your proforma based on comps. In short, these big assumptions reduce the likelihood that your model will reflect reality.\n", "replies": [{"id": "d5s7hmd", "text": "Thanks again for the info. Do you know anywhere that I could pull comp info from for commercial properties? While I'm nowhere near ready to invest, I'm trying to take an applied approach to learning. It seems like most of the info I would need is behind a $10,000+ paywall, though.", "replies": [{"id": "d5sc930", "text": "Yeah, you don't need that stuff. I do $100-200MM deals and seldom use commercial subscription services. If you just need real world data points for practice, use Craigslist for pricing/sales info. Join biggerpockets and ask for expense and market data from people in your area. Look for things like cap rates for comparable properties (which allows you to back into a fair market price), or expense info for different building systems. \n\nYou obviously don't need all this stuff to get started. Proforma ten different deals tomorrow while you wait for people to respond to you on biggerpockets. Do each one from scratch. Seriously. Start with a fresh sheet. Work through income. Then expenses. Then layer on debt. It probably won't make good financial sense, so then layer on some mezzanine debt or additional equity. Each model should take you 20-30 min from scratch. Forget your property factor tables, forget the market-wide economic assumptions. Just focus at the property level on how variables affect your returns. Master the basics. \n\n\nMake sense?\n\n", "replies": [{"id": "d5t1d8s", "text": "I think so. Will need to explore those websites a little bit further. Thanks again.", "replies": [], "depth": 6}], "depth": 5}, {"id": "d5wmfuu", "text": "tomorrow go on loopnet and request 10 property packs on investment properties.  Look at how they set this stuff up and then make your own.  \n\nAlso try to make friends with some brokers.  They will just give you comp data if you aren't being a pain in the ass.  If they don't, make friends with another one.  Just be up front and say you are learning etc etc.  \n\nedit-  also in regards to comp data.  start collecting property packs!  comp data is always very limited.  When you are going back to look at sales and have full marketing packs with proformas/past performance/rent rolls your comps are so much more helpful.  They are always very helpful in estimating expenses.  Property level operating data is hard to get no matter what you pay, when stuff goes up for sale is  your chance to get it and collect it for later use.", "replies": [], "depth": 5}], "depth": 4}], "depth": 3}], "depth": 2}], "depth": 1}]