Replicating a cost curve over x years for y years

[Gti182]

Hi All,

I'm looking to replicate an existing cost curve representing the spend pattern of a project spanning 7 years (FY20-FY26) - added a working example of the model which should give a clearer idea.

For my example i have the details for a 7 year project with annual FY20-FY26 costs (F5:L5) totalling $200m. I've then calculated the % of total costs falling into each year (Q5:W5) and this generates the shape of the curve in cell N5 via sparklines.

Now the fun part, adding scenario's for what the annual costs would look like if the same £200m project was accelerated by x number of years.
e.g. the 7 year project is accelerated by 2 years i.e. 5 years total. The total project cost remains at $200M by needs to be phased to the same/similar curve of the 7 year project i.e. 7 year costs need to be condensed into 5 years following the same spending pattern.

In my example I've manually entered the weighting %'s (yellow cells) for 3 - 6 year projects (rows 6 - 9) by trying to mirror the shape of the curve in cell N5. This then calculates the costs by year (blue cells).

The challenge is to try and automate %'s in the yellow cells.

[Gti182]

bump back to 1st page

[MrShorty]

After another day, I will venture a few thoughts on this. I find that many programming problems are all about taking a vague notion (like "I have a vague shape and I want to get the computer to automatically replicate something similar to that shape.") and put into something concrete so that I can tell the computer how to perform that vague task. You haven't given a lot of detail into how you chose the numbers you give, so I could be missing something here. I would expect to approach this problem something like this (still in kind of vague terms, because I cannot know what details are important to your project):

1) First, I need some way to quantify the shape of the sparklines. (Note: My version of Excel does not support sparklines, so I cannot see them, but I can see the shape of the values in columns Q:W). This is more of a maths exercise than a programming exercise. I see a "shape" that has a peak/vertex/maximum in the middle and decreasing to both sides, so I think of different shapes that I know of. Many conic sections have that kind of shape (https://www.mathsisfun.com/geometry/conic-sections.html ) and usually can be formulated by simply defining the "vertex" and the "width" of the conic. Another group of curves with that kind of shape are many of the statistical distributions (like the bell/Gaussian/normal distribution), again where I can fairly easily define the curve in terms of the maximum point and the width. I expect there are many other ways, too, depending on your exact needs.
2) Then fit my equation to the 7 year scenario to get a feel for the shape of the known case.
3) Then adjust the parameters of the equation to predict the shape of the other scenarios.
4) Fine tune and evaluate the process until I am comfortable with it.

It's still kind of vague, but that's the framework I would expect to use. A lot of the work is the pre-programming stage of figuring out how to quantify the vague notion of shape. Once you have figured out how to quantify shape, then the programming steps of fitting and adjusting can be done.

[Gti182]

Hi MrShorty,

Thanks for the reply. I possibly overcomplicated my original example having relooked at it.

I suppose to simplify the concept down to it's bare bones is i'd like to create different scenarios for a spending project taking X amount of months (variable) to complete costing Y (fixed).

I've added a simplified example which should hopefully show the challenge more clearly:
1) A given project starts in Jan19 and ends in May24 (65 months) and costs $200k (row 4).
2) What would the spending look like (per month) if the project was to be delayed to Feb20 but end sooner in Jul22 (30 months) (row5)
3) The challenging part in point 2 above is that the costs now span 3 years vs. 5 years in point 1 but i'd like to keep the same spending pattern.

Hopefully that's a bit clearer, i'm trying to solve for "X" marked cells in the example

[MrShorty]

I'm not sure I see much difference to the problem. You still have a fixed cost over variable time, and you want to "keep the same spending pattern". The key programming step is exactly how to describe to the computer/spreadsheet what this spending pattern looks like in a quantitative way that the computer can work with. The new spending pattern I see is:
Early -- low per month spending.
Middle -- moderate per month spending.
Late -- low per month spending except for the last few months where the spending is high.
The challenge is how to take those vague notions of low/moderate/high and early/middle/late into numbers that the computer can work with. I have no idea how you would want to translate those concepts. A different idea from a single formula like I previously suggested:
Early become first 24/65=37% of the project duration spends 14000. Then decide how to break that up over the first 37% of the project duration.
Middle becomes next 24/65=37% of the project duration and spends 138000. Then decide how to break that up over the middle 37% of the project duration.
Late becomes the final 17/65=26% of the project duration and spends the remaining 48000. Again, how would you want to break that up over the final 26% of the project duration.

So basically I break the project up into 3 stages with a fixed cost for each stage, figure out what fraction of the total duration belongs in each stage, and assign that cost to that stage. But I cannot know if that is a good way to divide the project up or if that adequately preserves the spending pattern. But maybe it will be illustrative of how one might think through the process. The key step still seems to me to be how to describe in concrete terms the spending pattern you want to preserve.

[Gti182]

Thanks for the update MrShorty, these were my thoughts too and quite difficult to formulate. I quite like the idea of breaking up into smaller chunks or stages so might have a play around with that idea, failing that i might have to do something more manual.

thanks again