I am working on a spreadsheet that will automatically calculate our inventory after it is counted. I would like to come up with a formula that calculates a weighted average price based on the FIFO (first in first out) inventory valuation method.
For example: On the counted inventory tab of the attached spreadsheet I would like a formula in the price column that would calculate the weighted average price of of the 2,400 SMA-16136-GR-DS. The price of this for inventory should be $1.1118. ($1.1118=((1,000*$1.25)+(530*$1.051)+(870*$0.99))/2400).
I would like to be abe to apply this formula across all of the items we hold in inventory. So when I enter the quantity the price will automatically populate. One of the problems I run into is that some items will be priced from many different purchases throughout the year and some will be based off only one (see item in Location #2).
Any guideance on how I can accomplish this would be appreciated.
Bookmarks