Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weighting Costs over time
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weighting Costs over time
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 "cardan" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time interval data and weighting | Excel Discussion (Misc queries) | |||
COMPARE CURRENT MORTAGAGE COSTS TO NEW MORTAGAGE COSTS | Excel Discussion (Misc queries) | |||
COMPARE CURRENT MORTAGAGE COSTS TO NEW MORTAGAGE COSTS | Excel Discussion (Misc queries) | |||
weighting a value? | Excel Discussion (Misc queries) | |||
How do I calculate Costs from a Time. ie 16:00 - 12:00 = 4:00hrs x £5.50rate per hour = £22.00 | Excel Worksheet Functions |