How can I make an excel daily running inventory sheet?

I work for a company where we build one circuit board. On the circuit board, there are 40 or so parts. Some of the individual parts get used more than one time on every board. So I was looking to find a way to be able to put in "a number" of boards we've done for that day and then the excel program would tell me how many of each individual parts I've used that day and also subtract from the amount that I currently have in stock so I know when it is time to reorder. Any help would be highly appreciated.

Thanks,

MK

For a given part


Stock Balance =Opening stock - (no.of boards * no. of this part per board)

Create a table of Type of Board, with List of Parts for each type of board and number of a given part on a given board.

Originally Posted by JohnTopley

For a given part


Stock Balance =Opening stock - (no.of boards * no. of this part per board)

Create a table of Type of Board, with List of Parts for each type of board and number of a given part on a given board.

We only do one board and it contains the same amount of parts. The only difference is that for example part a is used 2 times on the board, part b my be used 8 times on the board and part c maybe only once, and so on. So I don’t know how to go and program so that when I complete one board it automatically subtracts the amount of parts from my running inventory number.

.
Here is a small example project for you to dissect.

These are the formulas used to achieve the numbers :

Please Login or Register  to view this content.