Calculating future expected returns based on weighted aging of current returns.

Hi,

I am lost trying to develop a way to forecast for future periods based on historical aging.

Each period a quantity of proposals is submitted and are valid for 12 months.
We track each proposal and indicate when it is accepted, aging it.
If a proposal is not accepted, there is no refusal date, it is simply just never taken up.

I am trying to calculate how many of the outstanding proposals are anticipated in each of the months left in the valid time period for the offers.
I am currently using averages, however I know this is not accurate for a number of reasons.

Some observations I know to be true that are causing the figures to be inaccurate.

The oldest period (Jan 23) does have 12 months lapsed, however the newest (Dec 23) only has 1 month lapsed with 11 months of unknown acceptance to be calculated, so any version of an average is not accurate.

If considering the newest period (Dec 23), in its first (and only month on offer), there was a higher acceptance than most offer periods have had over their entire offer period thus far. (Dec 23 has 66% acceptance in first month, oldest (Jan 23) with 12 months has 45% in total). Only one period (Mar 23) has a higher lifetime acceptance % at 69%.

The last aging bracket is a 6+ month bracket. I currently have accounted for this by utilising the average of "6+"/5*m outstanding [last month is accounted for so only 5 unknown]

I am sure I am not considering something as well as this is not my speciality by any means.

Any help would be appreciated.

In my proposal, I am using the % of number of proposal accepted over total proposal received for each corresponding month, e.g. in 1 Month column, the % (in cell E19) is total proposal accepted in 1 Month totalling 125 from the period Jan 2023 to Nov 2023 (11 months) over total proposal received for the same 11 months.

Formula for future expected returns: N5=IF(EDATE($B5,COUNTA($N$4:N$4)-1)>=$D$2,$K5*D$19,""), copy down and across to T16.

Answer obtained is 58.95, quite close to yours of 53.70.

This seems to be solid and well thought through! Thanks Josephteh!

You are welcome and thanks for the Rep!