Formula for dividing calculated variance over a month 1

[Tadynn]

Hi all,

Excel 2k

Just wondering if anyone can help me with this one..........

I have created a worksheet in excel that currently shows the daily forecast total of orders targeted to process for the rest of the month. As each day's orders are completed, I will go in and update the forecasted figure for that day to the actual figures. From here, I want to divide whatever the variance may be between these two figures over the remaining days of the month, then add it to each forecasted figure from tomorrow onwards. Please see below for an example:

FORECAST VALUE:

January
Fri 27th Mon 30th Tues 31st
Orders 1000 1000 1000


Total
3000

ACTUAL VALUE
(Friday updated to actual value. Variance is 100 orders, there are 2 days left for the rest of the month, so the variance has been divided by 2 and then added to the forecasted values for these days):

January
Fri 27th Mon 30th Tues 31st
Orders 900 1050 1050

Total
3000

I don't know if this can be done through a formula or VB code.

Any ideas on how I do this one?


Rgrds, Tadynn

 

[GlennUK]

You can do this quite easily, but you will need to keep original forecast, actual, and adjusted forecast in separate rows, with the variance in yet another row.

01-Jan 02-Jan 03-Jan 04-Jan
Forecast 900 800 800 1400
Adjusted Fcast 600 810 810 1410
Actual 600
Variance 300 0 0 0

The forecast figures for the month go into row 2 ( labelled Forecast ), and when actuals are available they go into row 4 ( labelled Actual ). The formulae in row 3 ( labelled Adjusted Fcast ) spread the variance over the remaining days of the month, but of course day 1 won't have any variances to work with so the formula is different for 01-Jan than for the rest of the days of the month. So the formula for cell B3 is :

=IF(B4<>0,B4,B2)

but for cell C3 it is:

=IF(C4<>0,C4,C2+SUM($A$5:B5)/COUNTBLANK($B$4:$AF$4))

which is then copied across the rest of row 3 up to column AF ( which corresponds to 31-Jan ).

The formula for Variance ( row 5 ), is like this, which is the formula for cell B5:

=IF(B4=0,0,B2-B4)

which is then copied for the rest of row 5, up to column AF.

Try it and see what you think.

Cheers, Glenn.

 

[Tadynn]

Thanks GlennUK

After fiddling around with it for a bit. It worked a treat, here's a star for you.


Rgrds, Tadynn