Hello,
I am faced with a difficult (for me) modelling problem where I am trying to track final COGS (FIFO) and stock levels at multiple points in the distribution chain.
The situation is this: I'm trying to build a financial model for a company that resells simple consumer goods through a store as well as direct salespeople.
1) The company buys products and has them delivered to a storage facility.
2) Those products then take two paths:
For the model I need to determine Revenue and COGS (aggregated) for Final Sales
For management reporting I need to determine, on a specific date, the level of stock at either the Storage facility, the Store or with individual salespeople.
I've put together a sample file to show what I'm talking about as well as how I've started to structure the analysis. I'd appreciate some formula help as well as suggestions on how to structure the input sheet of transactions.
Tab FIFO V1 is the sample. I've tried a few other partial solutions which I've found at these links:
http://www.mrexcel.com/forum/excel-questions/682756-fifo-calculation.html
http://www.mrexcel.com/forum/excel-questions/663360-inventory-stock-profit-using-fifo.html
And through VBA
http://www.excelforum.com/excel-programming-vba-macros/866356-fifo-method-inventory-calculation.html
Any guidance will be appreciated.
Vikas
I am faced with a difficult (for me) modelling problem where I am trying to track final COGS (FIFO) and stock levels at multiple points in the distribution chain.
The situation is this: I'm trying to build a financial model for a company that resells simple consumer goods through a store as well as direct salespeople.
1) The company buys products and has them delivered to a storage facility.
2) Those products then take two paths:
a) Products sent to a store where they are purchased by customers
b) Products sent directly to salespeople who then sell them door to door
There are multiple product types which are also sold in different sizes.b) Products sent directly to salespeople who then sell them door to door
For the model I need to determine Revenue and COGS (aggregated) for Final Sales
For management reporting I need to determine, on a specific date, the level of stock at either the Storage facility, the Store or with individual salespeople.
I've put together a sample file to show what I'm talking about as well as how I've started to structure the analysis. I'd appreciate some formula help as well as suggestions on how to structure the input sheet of transactions.
Tab FIFO V1 is the sample. I've tried a few other partial solutions which I've found at these links:
http://www.mrexcel.com/forum/excel-questions/682756-fifo-calculation.html
http://www.mrexcel.com/forum/excel-questions/663360-inventory-stock-profit-using-fifo.html
And through VBA
http://www.excelforum.com/excel-programming-vba-macros/866356-fifo-method-inventory-calculation.html
Any guidance will be appreciated.
Vikas