|
ALLOCATION OF ORDERS - Pivot table
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 |
ALLOCATION OF ORDERS - Pivot table
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 |
ALLOCATION OF ORDERS - Pivot table
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 |
ALLOCATION OF ORDERS - Pivot table
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 |
ALLOCATION OF ORDERS - Pivot table
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 |
ALLOCATION OF ORDERS - Pivot table
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 |
ALLOCATION OF ORDERS - Pivot table
Dear joel
Thanks for all ur effort . I think i have failed to make u understand my problem. Le me simplify even more . Let me say that there is no prodn involved I have with me 10 Thou pcs say ready - covering 5 products - Each product has sa3 3 categories - each category has say 5 styles - each style has say 5 colours and then each colour further has say 5 sizes - THUS A LARGE NUMBER OF PARAMETERS INVOLVED. After all products are ready - I start booking and recieving orders - which could be for any product and not necessaraliy covering all parameters( Style / colour / size ) - example I may even get an order for just 4 total pcs. I want to allocate and block orders say on first cum basis - to know at any given time as TO WHAT ALL MORE I CAN BOOK SAFELY Thanks for ur patience Joel Rohin Bhatia "Joel" wrote: 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 |
ALLOCATION OF ORDERS - Pivot table
It doesn't matter what you are doing. Even if you Re-seller that buys items
and just ship them. You still need to calculate what your Production Rate is. How many items you can ship in one day. Work can't get done instantaneously. It you buy an item you only get 1000 units in a week, you can't sell 2000 units a week. Each item can have a different Production Rate, but it must have one. The Maximum number of units processed in a day (or week) Lets not worry about Production Rate. You need to have a tabulation of each item that is in stock. Right! Each item will be a diffferent column. Each order that is placed will reduce the numbers of items in the column associated with the item. If the item goes below zero then you need to have a warning when this happens. A Worksheet_Calculate function could be writen to chaeck worksheet for any negative numbers or conditional formating can be used to hightlight negative numbers. Negative numbers may be allowable if additional items are going to be placed in stock. I still think you should use forms to enter you data into the worksheet. Let the form check the quanitiy of items that arre in stock. Let the form indicate if you are overbooked. Private Sub Worksheet_Calculate() Columns("A:F").AutoFit End Sub 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 Put in stock 34 =C5 + B6 "Rohin Bhatia" wrote: Dear joel Thanks for all ur effort . I think i have failed to make u understand my problem. Le me simplify even more . Let me say that there is no prodn involved I have with me 10 Thou pcs say ready - covering 5 products - Each product has sa3 3 categories - each category has say 5 styles - each style has say 5 colours and then each colour further has say 5 sizes - THUS A LARGE NUMBER OF PARAMETERS INVOLVED. After all products are ready - I start booking and recieving orders - which could be for any product and not necessaraliy covering all parameters( Style / colour / size ) - example I may even get an order for just 4 total pcs. I want to allocate and block orders say on first cum basis - to know at any given time as TO WHAT ALL MORE I CAN BOOK SAFELY Thanks for ur patience Joel Rohin Bhatia "Joel" wrote: 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 |
| All times are GMT +1. The time now is 02:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com