This is a pain in the ass question. I am rebuilding a Monthly Pro Forma that I intend to use for all multi-family ground-up development deals going forward. What I am trying to achieve here is the ability to change two key inputs ("Construction Start Date" and "Months of Construction") and create a 'sliding scale' across my Monthly Pro Forma. I imagine this is the kind of stuff that ARGUS Developer is for, but my firm does not have that software.
On my Inputs Tab of my workbook, I have successfully tied my "Construction Start Date" and "Months of Construction" inputs to my "Units Delivered" line in my Monthly Pro Forma, which filters down to my NOI. This was pretty easy using an IF statement to deliver a proportionate amount of units 6 months after the Construction Start Date. (So for a 200 unit project with 16 months of construction, units would start delivering month 7 through month 16, equating to 20 units per month).
Where I am stuck is modeling out the Cost Distribution across the Construction Phase, while still being able to keep a "sliding scale". I have seen this done two ways:
1) (less common, not versatile, extremely accurate) Cost percentages are ‘hard keyed’ into the construction phase (example: 1% Month One | 1% Month Two | 2% Month Three | 5% Month Four | etc.)
2) (more common, more versatile, less accurate) The costs are evenly spread out by the # of months of construction. (example: $1 million architect fee on 10 months of construction would be$ 100,000 a month).
As many of you know, construction costs distributions tend to represent a bell curve (like this:
http://imgur.com/a/KY1fQ). The Bell curve will be accurate enough for the purposes of this model, so I am wondering if there is a way to model a bell curve for construction costs that CONTRACTS or EXPANDS when changing the “Months of Construction” on the Inputs Tab? As I said earlier, I have the unit deliveries locked down, all I am missing to get this Pro Forma up and running are these construction costs. I am happy to share the model once complete.
=(NORM.DIST(current period,duration/2,standard deviation,TRUE)-NORM.DIST(previous period,duration/2,standard deviation,TRUE))/(1-2NORM.DIST(0,duration/2,standard deviation,TRUE))cost
You sir are a saint.
RE Dev nailed it. It should be a normal distribution (bell curve). I didn't check the formula though
Currently playing around with it. For my pruposes, it would make more sense to use FALSE since I am looking for the cost at each period, not a cumulative, right? Also, what would you plug in for the standard deviation (right now doing numberofmonths/9.1). Getting close, but my sum of all the costs is exceeding the total cost.
Here is what I have so far with 7 months: http://imgur.com/a/2fxZM
14 months: http://imgur.com/a/GNUut
I figured it out and thought I would share for anyone who might be interested: http://imgur.com/a/HMyeG
The idea is to lay this out across a monthly proforma.
In a real-world scenario, this bell curve would be backloaded (begin building towards the end of the project), so it is a bit innacurate to normally distribute it. HOWEVER, this just adds an extra layer of contingency, since the last thing you want to do is underestimate your construction loan interest build up.
Here's where I originally sourced it: http://www.mrexcel.com/forum/excel-questions/759078-non-normal-skewed-p…
I also came across the kind of model you're looking for - the ability to slide the cost back and forth instead of normally distributed. This is ideal, but I didn't feel the need to get that specific. Link below.
http://www.mrexcel.com/forum/excel-questions/556463-auto-distribution-d…
Additionally, http://www.adventuresincre.com has some modeling info that is helpful.
an easier way to do it without using a normsdist function that will make someone else's head explode that is auditing your model is to just bell curve your costs and express them as a percentage of hard cost budget spent in every period. It may look like this for a 10 month schedule: 5%, 10%, 20%, 30%, 20%, 10%, 5% and then just run a vlookup against those values. This is how I have seen it done in most development models, especially considering your hard costs usually trickle out imperfectly from a bell curve for 6 months or so past completion to fund final change orders, bills that are on a 30-day billing cycle lag, etc.
This is how I do it. I have a dist curve tab for durations of 1 month to 36 months, and then index match to it in my draw schedule.
How did you build this tab? I have seen this in other models but the distribution is always just inputs. For example, for a 4 month construction period, the inputs are 20, 30, 30, 20. I would like to understand how to build this myself just for my own knowledge. Thanks.
but then if you want to adjust your construction timing (say, 18 to 20 months), you need to manually adjust each distribution.
Vel eveniet voluptatibus soluta cupiditate vitae. Aut velit in et doloribus. Magnam et voluptatum porro cupiditate itaque dolor sunt.
Nihil quia reprehenderit dolorem reprehenderit quaerat a. Doloremque vero fugiat praesentium reiciendis.
Doloribus culpa illo eos minima non consequatur quo. Officiis sapiente accusamus ut aspernatur nihil. Corrupti soluta dolore quaerat distinctio iure. Possimus at minima aliquam error nam rem possimus.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...