So I have modeled out our dev budget and proforma projection and encountered a problem that I hope any could help me with.
our model has acutal inputs and projection based on s curve/linear/manual. The problem I encounter for a given cell that is either populated based on current date with actual or proforma numbers. Our budget remaining ties into the cost item for our projection tab. So as we proceed forward with a project the cost curve budget decreases in steepness because actual number populate the model and decrease the cost number to be projected.
The projected and actual numbers don't sink up. How does any get around this.
e.g. we have a norm dist of 10,40,40,10 over 4 months and a budget remaining/projected of 100
let's say the first month we are on budget...$10, but then the remain projected cash flows become 36,36,9 do users have to manually change the steepness?
IRRelevant CRE_Erector any input?
Not the one to ask on this, but I think the guys over at Adventures in CRE had a pretty clean solution to this specific issue. May want to take a peek over there while you wait on other responses.
If you're doing what I think you're doing, which is using a normdist function to model the s-curve, then you need to create two separate rows. One will have the s-curve with the normdist function and then the second row will have the adjusted percentages. The normdist function in an s-curve never sums to 100% so you have to adjust it.
Something like: [Row 1] S-Curve: Sum of %'s, then formulae for %'s [Row 2] Modified S-Curve: take the s-curve % and add it to (1-the sum of the s-curve) then divide by the construction duration
e.g. your S-curve %'s sum to 98%. So take (1-.98) and divide by the number of construction months then add back the s-curve % above it.
There's another way to do the normdist s-curve with a z-score row but either way you'll have multiple rows to get to the accurate %'s.
I learned modeling doing the z-score then getting the stand.dev off that, but for this I used adventures in cre.
So my problem is that i have an actual/projected with 300 line itmes, why i choose the adventures in cre model, I can do line items in a copy and past method. So as we approach the current date in the project and the actual budget is being paid the periods we predicted in the projection are becoming actual numbers. I used budget remaining per line item which has given me the problem off as the dev project moves forward in time the budget decreases but the curve slowly steepens. so the month after the actual is below where it should be at the duration(middle) of the curve.
any way to get around this? ALso my model incorporates numbers, and not percentages as a result of the standard distribution function.
Share the draw tab of the model, and let's see what we've got to work with.
Can't share draw tab yet, new job.
But theoretically I have 3 tabs(that we have to worry about). Actual, Projected and Combined. It is as simple as if=column month = current month("today") then projected, if not actual.
What my boss wants is a way to have new projections based on the norm.dist. As an example lets say for a line item we project a duration of 8 mos and a budget of 1,000,000. While we can account for variance with actual/projected, on the combined tab if you use a shorter duration to account for actual month to projected end month we get a new bell curve. But I'm not sure it is an accurate project at say, 3 months to end date and we have 600,000 in remaining budget. If I use the original cost s-curve the percentage of monthly normal distribution falls very short, and if i use a new duration of 3 with budget remaining it sums up but I'm not sure a bell curve is that accurate. I am basically trying to automate this function so dev folks don't have to manually adjust.
I have automated the new duration for levelized costs, but can't seem to get the s-curve to work, expect in the one scenario.
I might be missing something, but this sounds overly complex to me. Can't you just do two separate tables with cost inputs/forecasts for actual and budget, and then just do a simple if statement based on the month it is? You can have the S-Curve % equations the same in both instances, just one feeding in over the other depending on the date qualifier. Then you can layer in an additional function that adjusts the forecast based on prior actuals and that shouldn't create a circular ref.... Again, I may be over simplifying but this seems easiest to me. If you try to get too fancy with the normdist it can do more harm than good in my experience...
i mean i essentially have this.
how do you create or adjust the forecast based on actual priors? I'm stuck with how to model the adjusted forcecast. Either the budget decreases over time and therefore based on static norm dist percentages you woudlnt' be forecasting the correct numbers, given the same end date.
Or you create a new curve based on current month, same end month, so shorter duration. But the beginning on the curve is going to be pretty low no?
In magnam aut pariatur est consectetur asperiores. Voluptatibus tempora et molestiae deserunt quia dignissimos.
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...