Hi,
I have this forecast data that's generated monthly. However, the monthly forecast is disaggregated into weekly buckets for planning purposes.
The weekly planning cycle is always 2 weeks out from the planning week which is indicative in the formula shared below where 14 days is added.
For disaggregation of the forecast, I manually adjust the cells based on the week of the month, which is done for all 16 weeks to align.
I need to apply a formula that looks up the month based on the week. and then break down the forecast from the specific month into weekly buckets factoring in the number of weeks that remain in the month.
For example;
the 1st week in the upload section is 28th Aug. Therefore the formula applied should look up August to identify the August forecast and divide the number by 4 to get the weekly breakup. The next week will be the 1st week of Sep., which the formula should recognize and look for Sep forecast. The process should continue for the subsequent weeks.
Not sure if this is possible with Excel formulas. However, felt like giving it a try.
I have this forecast data that's generated monthly. However, the monthly forecast is disaggregated into weekly buckets for planning purposes.
The weekly planning cycle is always 2 weeks out from the planning week which is indicative in the formula shared below where 14 days is added.
For disaggregation of the forecast, I manually adjust the cells based on the week of the month, which is done for all 16 weeks to align.
I need to apply a formula that looks up the month based on the week. and then break down the forecast from the specific month into weekly buckets factoring in the number of weeks that remain in the month.
For example;
the 1st week in the upload section is 28th Aug. Therefore the formula applied should look up August to identify the August forecast and divide the number by 4 to get the weekly breakup. The next week will be the 1st week of Sep., which the formula should recognize and look for Sep forecast. The process should continue for the subsequent weeks.
Not sure if this is possible with Excel formulas. However, felt like giving it a try.
| Backlog | Final Forecast After RunRate, Growth & Deals (Monthly) | For Forecast Upload | ||||||||||||||||||||||||||||||||||||||||||||||||||
| SKU No. | Trade Orders | DEMO / Warranty | Aug-23 | Sep-23 | Oct-23 | Nov-23 | Dec-23 | Jan-24 | Feb-24 | Mar-24 | Apr-24 | May-24 | CUSTID | PRDID | 2023-08-28 | 2023-09-04 | 2023-09-11 | 2023-09-18 | 2023-09-25 | 2023-10-02 | 2023-10-09 | 2023-10-16 | 2023-10-23 | 2023-10-30 | 2023-11-06 | 2023-11-13 | 2023-11-20 | 2023-11-27 | 2023-12-04 | 2023-12-11 | ||||||||||||||||||||||
| 3GZ25A-KGJ | 256 | 980 | 993 | 1238 | 874 | 584 | 493 | 516 | 372 | 201 | 171 | 0290000999 | 3GZ25A-KGJ | 501 | 249 | 249 | 249 | 249 | 310 | 310 | 310 | 310 | 219 | 219 | 219 | 219 | 219 | 146 | 146 | |||||||||||||||||||||||
Excel Formula:
[TABLE]
[TR]
[TD]=14+(TODAY()-WEEKDAY(TODAY())+2)[/TD]
[TD]=AL$3+7[/TD]
[TD]=AM$3+7[/TD]
[TD]=AN$3+7[/TD]
[TD]=AO$3+7[/TD]
[TD]=AP$3+7[/TD]
[TD]=AQ$3+7[/TD]
[TD]=AR$3+7[/TD]
[TD]=AS$3+7[/TD]
[TD]=AT$3+7[/TD]
[TD]=AU$3+7[/TD]
[TD]=AV$3+7[/TD]
[TD]=AW$3+7[/TD]
[TD]=AX$3+7[/TD]
[TD]=AY$3+7[/TD]
[TD]=AZ$3+7[/TD]
[/TR]
[TR]
[TD]=IFERROR(ROUNDUP(SUM($B4:$C4)+(X4/4),0),"NA")[/TD]
[TD]=IFERROR(ROUNDUP(Y4/4,0),"NA")[/TD]
[TD]=IFERROR(ROUNDUP(Y4/4,0),"NA")[/TD]
[TD]=IFERROR(ROUNDUP(Y4/4,0),"NA")[/TD]
[TD]=IFERROR(ROUNDUP(Y4/4,0),"NA")[/TD]
[TD]=IFERROR(ROUNDUP(Z4/4,0),"NA")[/TD]
[TD]=IFERROR(ROUNDUP(Z4/4,0),"NA")[/TD]
[TD]=IFERROR(ROUNDUP(Z4/4,0),"NA")[/TD]
[TD]=IFERROR(ROUNDUP(Z4/4,0),"NA")[/TD]
[TD]=IFERROR(ROUNDUP(AA4/4,0),"NA")[/TD]
[TD]=IFERROR(ROUNDUP(AA4/4,0),"NA")[/TD]
[TD]=IFERROR(ROUNDUP(AA4/4,0),"NA")[/TD]
[TD]=IFERROR(ROUNDUP(AA4/4,0),"NA")[/TD]
[TD]=IFERROR(ROUNDUP(AA4/4,0),"NA")[/TD]
[TD]=IFERROR(ROUNDUP(AB4/4,0),"NA")[/TD]
[TD]=IFERROR(ROUNDUP(AB4/4,0),"NA")[/TD]
[/TR]
[/TABLE]
