I have been tasked with creating an inventory system for my construction shop.
I have a general setup, but I'm having a hard time figuring out the exact formulas that would allow me to have the spreadsheet automatically start using the new price of the material when the old stock is used up.
To give a little more info, this is how our current system works:
We pre-buy lumber to have ready stock and log the amount, quantity, and prices of the current materials into the excel workbook under our purchases log.
Then on paper, we record what materials we use and what construction project they should be assigned to. At the end of the month, we then take those pages and record them into another sheet in the workbook. This records the material, date, quantity, and project.
The goal, and what I need some help on, is we would like to then create an auto-populating supply transfer page for our finance office that shows you the materials that were used in a specific date range. Ideally, this would show the price that we had originally paid for that material based on what remained in inventory at the time.
Does anyone have any formulas or ideas on tracking the changing prices in an automated way?
