Calculated expected completion based on sales orders

[imran1059]

Hi Everyone,

I have following line for sales order monthly forecast for 2018. Months on A1 to L1 and figures on A2 and L2

<colgroup><col style="mso-width-source:userset;mso-width-alt:2016; width:43pt" width="57" span="12"> </colgroup><tbody>
[TD="class: xl68, width: 57"]JAN
[/TD]
[TD="class: xl68, width: 57"]FEB[/TD]
[TD="class: xl68, width: 57"]MAR[/TD]
[TD="class: xl68, width: 57"]APR[/TD]
[TD="class: xl68, width: 57"]MAY[/TD]
[TD="class: xl68, width: 57"]JUN[/TD]
[TD="class: xl68, width: 57"]JUL[/TD]
[TD="class: xl68, width: 57"]AUG[/TD]
[TD="class: xl68, width: 57"]SEP[/TD]
[TD="class: xl68, width: 57"]OCT[/TD]
[TD="class: xl68, width: 57"]NOV[/TD]
[TD="class: xl68, width: 57"]DEC[/TD]

[TD="class: xl69"]14,155
[/TD]
[TD="class: xl69"]32,235 [/TD]
[TD="class: xl69"]14,570 [/TD]
[TD="class: xl69"]6,200
[/TD]
[TD="class: xl69"]3,900 [/TD]
[TD="class: xl69"]19,705 [/TD]
[TD="class: xl69"]6,055 [/TD]
[TD="class: xl69"]5,880 [/TD]
[TD="class: xl69"]3,540 [/TD]
[TD="class: xl69"]2,529 [/TD]
[TD="class: xl69"]6,270 [/TD]
[TD="class: xl69"]15,140 [/TD]

</tbody>

I need a formula in Cells A3 to L3 to forecast completion based on my expected sales orders. Formula should be as follows:

10% completion to be recognized in the month of sales order and 20% in next 4 months then final 10% is to be completed in 6th month. For example, for Jan 14,155 is expected sales order, so in January we expect to complete 1,415.50. Then in Feb, the completed work from January is 2,831 which is 20% of January's expected sales order + 3,223.50 is for the expected sales order for February. In March, we need to consider expected sales order for three months.

Thanks in advance for your help,
Imran.

 

[joeu2004]

To keep things simple, insert 5 columns to the left (which can be hidden), so that the first column with real data is column F, not column A.

Then enter the following formula into F3 and copy across:

=10%*(F2+A2) + 20%*SUM(B2:E2)
or
=SUMPRODUCT({0.1, 0.2, 0.2, 0.2, 0.2, 0.1}, A2:F2)

The latter provides the flexibility of having different percentages for each of the 6 months.

 

[imran1059]

Perfect Bundle of thanks .....