ALLOCATION OF ORDERS - Pivot table

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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)

 

[Guest]

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

 

[Guest]

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