I use pivot tables to compile order quantity data from different
customers.The orders are for different products with paramaters like style ,
colour , size etc.
For all such products we have stock quantity ready with us and also under
production(fixed total quantity )
As different customer orders come in - HOW can I allocate stock / production
on some priority basis for these customers - And thus to know if we have
overbooked in some colours / sizes .
Can anyone please help
Thanks Rohin

I would have a running total for each product.

I would start by getting all outstanding orders for the first product and
get a count of the stock quantity for that product.

the 1str row of the speadsheet would contain just the Stock quantity. the
columns of the spreadsheett would be as follows

Production Rate is the number made each day
I would use conditional formating on column D to make the column a different
color depending on the number of days to fill and order. For example
Anything less than 5 days make Green
Anything between 5 to 10 days Yellow
Anything greatter than 10 days red.

Row
1 Production 75
Rate
2
Order Quantity Total in Days to
3 Number Ordered Stock Fill Order

4 125

5 14678 24 =C4-B5 if(c5=0,1,C5/B$1)

6



"Rohin Bhatia" wrote:

I use pivot tables to compile order quantity data from different
customers.The orders are for different products with paramaters like style ,
colour , size etc.
For all such products we have stock quantity ready with us and also under
production(fixed total quantity )
As different customer orders come in - HOW can I allocate stock / production
on some priority basis for these customers - And thus to know if we have
overbooked in some colours / sizes .
Can anyone please help
Thanks Rohin

Dear Joel
Thanks for ur reply - i think I need to clarify a bit more
I have say 5 products - and for each - different style / different colour /
5 different sizes etc
Thus my stock data itself would be atleast 5 rows and maybe around 10 plus
columns.
Also i would have similar 5 rows for total prodn planned.
lets assume full prodn will be done and then all orders executed - this
process of manufacturing may take say a month.
Meanwhile , we keep getting orders from different customers - could be even
say only 3 products and say only 4 sizes etc.
Thus , as the orders keep coming in , we need to block the quantity against
total old stock and new prodn planned ( this can be treated like stock 2
).Now - how do I track and allocate ? thus maybe after a certain number of
orders I can see that in some product / some style / some colour - I CAN NO
LONGER BOOK MORE.
Await reply
Thanks - rohin


"Joel" wrote:

I would have a running total for each product.

I would start by getting all outstanding orders for the first product and
get a count of the stock quantity for that product.

the 1str row of the speadsheet would contain just the Stock quantity. the
columns of the spreadsheett would be as follows

Production Rate is the number made each day
I would use conditional formating on column D to make the column a different
color depending on the number of days to fill and order. For example
Anything less than 5 days make Green
Anything between 5 to 10 days Yellow
Anything greatter than 10 days red.

Row
1 Production 75
Rate
2
Order Quantity Total in Days to
3 Number Ordered Stock Fill Order

4 125

5 14678 24 =C4-B5 if(c5=0,1,C5/B$1)

6



"Rohin Bhatia" wrote:

I use pivot tables to compile order quantity data from different
customers.The orders are for different products with paramaters like style ,
colour , size etc.
For all such products we have stock quantity ready with us and also under
production(fixed total quantity )
As different customer orders come in - HOW can I allocate stock / production
on some priority basis for these customers - And thus to know if we have
overbooked in some colours / sizes .
Can anyone please help
Thanks Rohin

Don't use 5 row, instead us more columns. Excel support up to 240 column per
worksheet. Using forms to enter data is better than putting data directly
into the cells. You enter the forms than write a macro to copy into the
corrrectt cells. this way you don't update the worksheet if too many orders
are place.

I don't know how many diffferent production lines you have. But if Red and
Blue are coming off the same production line then they havve to be included
in the same total (multiple columns) but have different stock quantities.

The easy way to stop orders using the fonditional formating colors. But a
Worksheet_Change function can be written to add a pop up window when too many
orders are placed. But this doesn't stop the order from getting into the
worksheet. The better way is to design a form.

"Rohin Bhatia" wrote:

Dear Joel
Thanks for ur reply - i think I need to clarify a bit more
I have say 5 products - and for each - different style / different colour /
5 different sizes etc
Thus my stock data itself would be atleast 5 rows and maybe around 10 plus
columns.
Also i would have similar 5 rows for total prodn planned.
lets assume full prodn will be done and then all orders executed - this
process of manufacturing may take say a month.
Meanwhile , we keep getting orders from different customers - could be even
say only 3 products and say only 4 sizes etc.
Thus , as the orders keep coming in , we need to block the quantity against
total old stock and new prodn planned ( this can be treated like stock 2
).Now - how do I track and allocate ? thus maybe after a certain number of
orders I can see that in some product / some style / some colour - I CAN NO
LONGER BOOK MORE.
Await reply
Thanks - rohin


"Joel" wrote:

I would have a running total for each product.

I would start by getting all outstanding orders for the first product and
get a count of the stock quantity for that product.

the 1str row of the speadsheet would contain just the Stock quantity. the
columns of the spreadsheett would be as follows

Production Rate is the number made each day
I would use conditional formating on column D to make the column a different
color depending on the number of days to fill and order. For example
Anything less than 5 days make Green
Anything between 5 to 10 days Yellow
Anything greatter than 10 days red.

Row
1 Production 75
Rate
2
Order Quantity Total in Days to
3 Number Ordered Stock Fill Order

4 125

5 14678 24 =C4-B5 if(c5=0,1,C5/B$1)

6



"Rohin Bhatia" wrote:

I use pivot tables to compile order quantity data from different
customers.The orders are for different products with paramaters like style ,
colour , size etc.
For all such products we have stock quantity ready with us and also under
production(fixed total quantity )
As different customer orders come in - HOW can I allocate stock / production
on some priority basis for these customers - And thus to know if we have
overbooked in some colours / sizes .
Can anyone please help
Thanks Rohin

Dear Joel
sorry bothering u again - i think i would like to further simplify .
Lets assume I have stock 1 say 3000 pcs and stock 2 say 5000 pcs ( Both
these quantities are fixed ) Prodn line etc is not realy relevant .
lets say complete 8000 pcs is ready with me and after this we start
booking ?
Now would it be easier to solve the problem ?
Pls help -
Regds
Rohin


"Joel" wrote:

Don't use 5 row, instead us more columns. Excel support up to 240 column per
worksheet. Using forms to enter data is better than putting data directly
into the cells. You enter the forms than write a macro to copy into the
corrrectt cells. this way you don't update the worksheet if too many orders
are place.

I don't know how many diffferent production lines you have. But if Red and
Blue are coming off the same production line then they havve to be included
in the same total (multiple columns) but have different stock quantities.

The easy way to stop orders using the fonditional formating colors. But a
Worksheet_Change function can be written to add a pop up window when too many
orders are placed. But this doesn't stop the order from getting into the
worksheet. The better way is to design a form.

"Rohin Bhatia" wrote:

Dear Joel
Thanks for ur reply - i think I need to clarify a bit more
I have say 5 products - and for each - different style / different colour /
5 different sizes etc
Thus my stock data itself would be atleast 5 rows and maybe around 10 plus
columns.
Also i would have similar 5 rows for total prodn planned.
lets assume full prodn will be done and then all orders executed - this
process of manufacturing may take say a month.
Meanwhile , we keep getting orders from different customers - could be even
say only 3 products and say only 4 sizes etc.
Thus , as the orders keep coming in , we need to block the quantity against
total old stock and new prodn planned ( this can be treated like stock 2
).Now - how do I track and allocate ? thus maybe after a certain number of
orders I can see that in some product / some style / some colour - I CAN NO
LONGER BOOK MORE.
Await reply
Thanks - rohin


"Joel" wrote:

I would have a running total for each product.

I would start by getting all outstanding orders for the first product and
get a count of the stock quantity for that product.

the 1str row of the speadsheet would contain just the Stock quantity. the
columns of the spreadsheett would be as follows

Production Rate is the number made each day
I would use conditional formating on column D to make the column a different
color depending on the number of days to fill and order. For example
Anything less than 5 days make Green
Anything between 5 to 10 days Yellow
Anything greatter than 10 days red.

Row
1 Production 75
Rate
2
Order Quantity Total in Days to
3 Number Ordered Stock Fill Order

4 125

5 14678 24 =C4-B5 if(c5=0,1,C5/B$1)

6



"Rohin Bhatia" wrote:

I use pivot tables to compile order quantity data from different
customers.The orders are for different products with paramaters like style ,
colour , size etc.
For all such products we have stock quantity ready with us and also under
production(fixed total quantity )
As different customer orders come in - HOW can I allocate stock / production
on some priority basis for these customers - And thus to know if we have
overbooked in some colours / sizes .
Can anyone please help
Thanks Rohin

There are two different type of Production. One is "Custom Built" and the
other is "Standard Product". In either case you have a Production Rate.
Your problem is that you don't want to take new orders if they don't get
filled within a certain period of time.

So you have a production line. Certain employees that ship/make certain
items.

the stock cannot be fixed, but changes after every order.


the problem still simplifies to the following table. You have to determine
how many different columns you need. the flag to determine how many orders
to take is still based on the Days to Fill Order.

Row
1 Production 75
Rate
2
Order Quantity Total in Days to
3 Number Ordered Stock Fill Order

4 125

5 14678 24 =C4-B5 if(c5=0,1,C5/B$1)



"Rohin Bhatia" wrote:

Dear Joel
sorry bothering u again - i think i would like to further simplify .
Lets assume I have stock 1 say 3000 pcs and stock 2 say 5000 pcs ( Both
these quantities are fixed ) Prodn line etc is not realy relevant .
lets say complete 8000 pcs is ready with me and after this we start
booking ?
Now would it be easier to solve the problem ?
Pls help -
Regds
Rohin


"Joel" wrote:

Don't use 5 row, instead us more columns. Excel support up to 240 column per
worksheet. Using forms to enter data is better than putting data directly
into the cells. You enter the forms than write a macro to copy into the
corrrectt cells. this way you don't update the worksheet if too many orders
are place.

I don't know how many diffferent production lines you have. But if Red and
Blue are coming off the same production line then they havve to be included
in the same total (multiple columns) but have different stock quantities.

The easy way to stop orders using the fonditional formating colors. But a
Worksheet_Change function can be written to add a pop up window when too many
orders are placed. But this doesn't stop the order from getting into the
worksheet. The better way is to design a form.

"Rohin Bhatia" wrote:

Dear Joel
Thanks for ur reply - i think I need to clarify a bit more
I have say 5 products - and for each - different style / different colour /
5 different sizes etc
Thus my stock data itself would be atleast 5 rows and maybe around 10 plus
columns.
Also i would have similar 5 rows for total prodn planned.
lets assume full prodn will be done and then all orders executed - this
process of manufacturing may take say a month.
Meanwhile , we keep getting orders from different customers - could be even
say only 3 products and say only 4 sizes etc.
Thus , as the orders keep coming in , we need to block the quantity against
total old stock and new prodn planned ( this can be treated like stock 2
).Now - how do I track and allocate ? thus maybe after a certain number of
orders I can see that in some product / some style / some colour - I CAN NO
LONGER BOOK MORE.
Await reply
Thanks - rohin


"Joel" wrote:

I would have a running total for each product.

I would start by getting all outstanding orders for the first product and
get a count of the stock quantity for that product.

the 1str row of the speadsheet would contain just the Stock quantity. the
columns of the spreadsheett would be as follows

Production Rate is the number made each day
I would use conditional formating on column D to make the column a different
color depending on the number of days to fill and order. For example
Anything less than 5 days make Green
Anything between 5 to 10 days Yellow
Anything greatter than 10 days red.

Row
1 Production 75
Rate
2
Order Quantity Total in Days to
3 Number Ordered Stock Fill Order

4 125

5 14678 24 =C4-B5 if(c5=0,1,C5/B$1)

6



"Rohin Bhatia" wrote:

I use pivot tables to compile order quantity data from different
customers.The orders are for different products with paramaters like style ,
colour , size etc.
For all such products we have stock quantity ready with us and also under
production(fixed total quantity )
As different customer orders come in - HOW can I allocate stock / production
on some priority basis for these customers - And thus to know if we have
overbooked in some colours / sizes .
Can anyone please help
Thanks Rohin