• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Stock Allocation for Backlog orders

tiangandrew75

New Member
Please Help.

these attach file is original data from the system.
in the attach file have backlog, open po and inventory tab.
i need help to do allocations in the backlog tab at column Z.
please kindly help help on the formulas.
your effort is very much appreciated.
thank you in advance
 

Attachments

  • Allocation.xlsx
    481.2 KB · Views: 9
Hi vletm,
my apologies.
need your help as i do not know the allocation formula.
therefore have attach the file for your reference
in the attach have 3 tabs - backlog, open po and inventory.
have also highlighted those columns you may need to refer to.
the backlog orders are by FIFO.
please kindly help on the allocation formulas.
thank you in advance.
 

Attachments

  • Allocation.xlsx
    481.2 KB · Views: 9
Hi vletm,

thank you for your reply.
backlog is base on FIFO.
the concept is, Stock + PO - backlog SO

example,
part code - 13122
back log - 3400pcs
Stock - 11748pcs
PO - 26640pcs
therefore we can allocate to backlog of 3400pcs

hope these explain.
thank you.
 

Attachments

  • Allocation.xlsx
    480.6 KB · Views: 6
tiangandrew75
Did You explain somewhere .... if cannot allocate something?
I someway find those Your numbers, but ...
eg what is needed output? ... especially if NOT!
SAMPLE ( not nice )
Z-column has 'can allocate' values (based Your rule)
AJ:AM - columns has those Your named values ... except AM, is empty.

If would like to have LESS rows for result,
then could use columns AO:AT-way.

If would like to have NONE formulas then ... use VBA.
... but before any above,
Need to know exact what?
 

Attachments

  • Allocation (1).xlsb
    195.4 KB · Views: 12
Hi vletm,
my apologies for late reply.
firstly, thank you for your reply.

my thinking and suggestion is that,
Open PO + Stock = overall stock
the overall stock qty will minus off the SO qty after allocations
the overall stock will reduces after allocate to each SO lines by its own
can excel do these?
can kindly help
thank you in advance
 

Attachments

  • Allocation (1).xlsb
    195.4 KB · Views: 10
tiangandrew75
Did You answer to my question?
What do need to modify?
Which version?
With Excel can do many things - not all,
as well it could give needed as well as correct results,
but only after - someone would explain what?
 
Back
Top