Inventory management

[anaand01]

I have to record the orders received from various customers in master sheet for spare parts.
1. for all orders received , part wise summation (of orders recd from different customers) is to be done and order placed on my principal supplier who is only one supplier.
2. The order gets a Order number and is updated against all the parts.
2.The customer gives a monthwise schedule for part delivery. how do I maintain the same.
3. The stock gets added when received and deleted when billed.
4. The customer invoice also gets updated against the billed part.
Please suggest how do I maintain the excel sheets and the formulas to be used.

 

[vletm]

anaand01
How would do this manually? ( = with paper and pencil)
You should have all those datas (as real as possible sample data) to upload here with needed results (and how those would calculate).

 

[anaand01]

Thanks vletm, I am uploading a sample file where in there are three sheets, the first sheet is the format of order received from customer. it is populated for one customer with two part numbers ,
second sheet is the format I have made to record all the orders received from different customers. After receiving the orders , I have to register the order on my supplier by consolidating the part numbers. when the parts are received, the same are to be retailed out to customers who have ordered.
Third sheet is towards Schedule, - all the orders are Schedule orders for next few months. I have to order according to schedule ( i.e. before 10 days as lead time to receive part is 10-15 days)
in this sheet , I have two methods by which I can maintain records.

could you suggest me any better way to do so,

Thank you

 

[vletm]

anaand01
hmm ... yes, manually ...

Where is a custom list with their details?
Where is a list which have all parts with those details?
Where is a list which shows all orders and deliveries?
... and so on ...
All of those should have some kind of connection to almost all sheets ...
that no need to write nor copy same information many times!

What to do if some day someone would order more than two parts?
... or ...
How many parts would be there?
How many customers would be there?
... if more than two ... that gotta take care at once.

> suggest >
Please, sit and think
... eg for few hours,
... ... what and how, You would to do something with Excel?

 

[anaand01]

thank you , I will think over and revert

 

[anaand01]

1. Sheet 1 shows the two orders recd from my customers. There are 35 customers and everyone is sending me the order through email. Format being same. there are about 60K parts.

2. I have to enter all the part numbers , description and qty in one sheet, as well as I have to record the schedule for delivery for those parts.

3. on receiving the order, I place order on my Principal for all the part numbers. there can be same part ordered by more than one customers , the same has to be clubbed together and order placed on my Principal in CRM system given by Principal. On placing order, SAP order number is generated.

4. this SAP order is to be written down against the Customer order , to ensure 100% orders received from Customer are registered in Principal system.

5. The parts are received from Principal which may be full quantity or partial quantity. These parts are taken into CRM as received . This also is to be recorded in Del Qty column to know the back order with principal.
6. Now the received part are to be retailed out according to the schedule given below each order. Retail invoice is generated thro CRM.
7. Order on Principal is to be placed as per schedule. i.e. for Sept 18 scheduled qty, order to be placed 15 days ahead of Sept 18. say July 15th

so my problem is how to keep a record of all these transactions so as to know
a. Schedule qty of each part Customer wise
b. record the receipt of scheduled qty
c. dispatch of scheduled qty to respective customer
d. pending qty customer wise.

please do not go by my sample recording sheet. please consider the Customer orders , how they are received. and suggest how to record the entries so as to effectively deliver the parts to customer as per schedule and should be able to pull out report of pending parts.

following is the flow chart -

1. Receive order from Customers as per format
maintain schedule which is monthwise
2. Register order on Principal for getting these parts - in CRM system provided

3. Receive the parts - update as received in CRM

4. Sell this parts to Customers according to their order. - Retail invoice bill generated through CRM.

5. to follow up for pending parts ordered by Customers.

 

[vletm]

anaand01 - shortly ...
Did You try to answer to my questions? (#4 Reply)
This not 100m run!
This is much longer run!
+ You wrote many times SAP ... cannot You get all of those from it?