I am trying to find a solution to a quite challenging problem and need some help.
I am having a table with forecasted costs per Unit for a 10-year period and want to distribute those amounts according to an alternative scenario (front-loaded, back-loaded, normally distributed, equally and custom). To add to the complexity the he forecasted amount per Unit is not to be released at the same year (end date differs as well) ie. Unit 1 has a forecasted cost of 250k and it is to be expensed starting from year 3 and end at year 8 while for e.g. Unit 2 with a total forecasted cost of 500k will start at year 2 and end at year 6.
Q1: Let's assume I want to spread the total costs on an annual basis. how do i do for every scenario (front-loaded, back-loaded, normally distributed, equally and custom) given that the start and end dates differ?
Q2: What will happen if i want to do the splitting of total forecasted cost in months instead of years.
q3: I am assuming for front-end and back-end I need to have a table that will define what percentge is "unloaded" at each year. but how do i make sure this is not "violated" as i the range of periods for each unit differs? e.g. if i say that front-end means that 40% goes on year 1, 35% on year 2 and 25% on year 3 but a specific unit has a span of 2.5 or even 2 years??
q4: how am i going to distribute the amount in months or years normally for each unit?
i'd appreciate some help here as it is quite a challenge given that i need all scenarios to work for each unit amount independent of the duration of the period that the amount is to be distributed.
thank you in advance for your time and support!
kind regards,
Jim
