Weighting Costs over time

[cardan]

I am working on a financial analysis and I have a cost in cell that it
divided amongst a group of other cells for time, ie a manufacturing
cost of $1,000,000 that is going to occur over 10 months is equally
divided into $100,000 amongst those 10 months. However, sometimes the
costs are not so linear. I need a formula(s) that will weight the costs
towards the front, back or do a bell-curve to reflect more accurate
outflows. Any suggestions? Thanks! It is greatly appreciated!

 

[Bill Pfister]

Cardan, there are many ways to accomplish this but it really depends on what
you're trying to accomplish and how accurate you want to be.

One approach is to have several input "shapes" (coefficient streams) to
choose from - these can be manually entered or derived from the standard
distributions (normal, lognormal, etc.). Then you can select the shape and
multiple your principle amount by the shape to get your cost stream. You can
also enter manual coefficient streams or derive streams from prior experience
(with or without growth factors). You goal is generally to model real life
as closely as possible, given the amount of accuracy that is necessary.

Here's an example of some coefficient streams. Unfortunately, this is
difficult to surmise from this format but it should be better if you paste
the example into a text editor.

Months 1 2 3 4 5 6 7 8 9 10 Check
Custom 0.05 0.10 0.15 0.05 0.05 0.10 0.20 0.10 0.15 0.05 1.000
Every Other 0.20 0.20 0.20 0.20 0.20 1.000
Linear 0.10 0.10 0.10 0.10 0.10 0.10 0.10 0.10 0.10 0.10 1.000
Normal dist 0.00 0.00 0.05 0.24 0.40 0.24 0.05 0.00 0.00 0.00 1.000

For the Normal dist, you can use something like "=NORMDIST( H$5, 5, 1, 0
)", where H$5 tells the formula which month it is in.

Good luck and let me know if I can provide a more thorough answer.

Regards,
Bill