Where do you get the forecast figures from?
With all the data available to you you must have info on the orders placed
each month. Can't those figures alone enable you to work out your FY
projection.
I can't see how differentiating at what stage of the supply process each
order is at can help you. Are you making this too complicated?
I don't see this as an Excel problem as such but obviously it will help once
you can decide what data to use.

--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"winnie123" wrote:

Hi there,

I am trying to project FY sales based on open orders,shipped orders and
forecast.

I have pulled together the 3 lots of data and used SumProduct to calculate
totals for each product by FY months (for this i have used Vlookup against
original data).

I have ended up with a sheet that looks like below

Open Orders
Columns C-W Row 3 have the Product Names
Column B Row 4 down to 15 have Oct,Nov ending with Sep (this is text)
C4 to W15 have the totals
Row 16 is the totals of each product

Shipped Orders
Columns C-W Row 19 have the Product Names
Column B Row 20 down to 31 have Oct,Nov ending with Sep (this is text)
C20 to W31 have the totals
C32 has the totals for each product

Forecast
Columns C-W Row 34 have the Product Names
Column B Row 35 down to 46 have Oct,Nov ending with Sep (this is text)
C35 to W46 have the totals
C46 has the totals for each product

I then want to pull all the sets of totals to create a projected sales for
this FY

using the same format as above, at first I thought to just add each of the
totals up but in some cases I have Open orders and forecast for the same
month, that I dont want to be added together.So I thought that I would assume
if Opens orders is greater than Forecast then add Open Orders to shipped
orders, if the open order is less than Forecast then subtract the open orders
from the forecast and add the shipped qty.

Using Apr which is on rows 10,26 and 41 the formula I have for the 1st
product is

=IF(C10C41,C10+C26,C41-C10+C26)

Is this the correct way or is there an alternative way, I am not sure my
logic is correct.

Thanks
Winnie