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

Hi Winnie

I tried to follow your logic.

With your formula, you will get a wrong result if open and forecast
numbers are equal.

Try if this formula yield the correct result:

=C26+MAX(C10,C41-C10)

Best regards,
Per

On 1 Maj, 15:14, 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

Hi Per,

Thanks for replying.

I have not used the MAX function before.

I followed your logic and that does seem the best route to follow, I changed
the C41-C10+C26 from my original formula to your formula and ended up with

=IF(C10<C41,C41+C26,C26+MAX(C10,C41-C10))

so that if the Open order qty was less than the Forecast qty it adds
Forecast to Shipped, otherwise it will add the maximum of either Open or
Forecast minus the Open to the Shipped.

Thanks as always your responses give me the correct solution and you
highlighted the incorrect logic that my original formula would do.

PS if you see 2 different responses its because I could not see that my
earlier reply had been posted.

Best Regards
Winnie


"Per Jessen" wrote:

Hi Winnie

I tried to follow your logic.

With your formula, you will get a wrong result if open and forecast
numbers are equal.

Try if this formula yield the correct result:

=C26+MAX(C10,C41-C10)

Best regards,
Per

On 1 Maj, 15:14, 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

.

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

Hi Russell,

Thankyou for taking time out and replying.

The forecast is received every week from our customer.

Our FD and CEO wanted to see the projected sales for this customer for our
FY, based on current orders, shipped and forecast so this was the only way I
could think of, I needed to take into account the current orders from the
existing forecast.
You were correct in that it was not really an excel problem but needed the
logic checking out.

Per has put me on the right track.I would not have thought to use MAX and
that my original formula did not take into account if values were equal.

I can now with confidence issue my figures and justify it. Whether it be
correct is how you look at it, as we know a forecast is only a forecast.

Thankyou and Regards
Winnie


"Russell Dawson" wrote:

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