Video Tutorial:
If you have historical purchases of inventory units at varying prices, when you go to sell that inventory, the financial statement process will require you to enter a value for the cost of goods sold (COGS). That value is often required to be calculated using the FIFO accounting methodology. FIFO means the value of inventory sold is based on depleting the older inventory units that were purchased first.
Example: Buy 100 units at $20.00 and then buy 50 units at $14.00. If you then sell 75 units, those 75 will be valued at $20 (value of initial units purchased). If you then sell another 50 units, the remaining 25 from the original inventory purchase (of 100) will be valued at $20 and the next 25 will be valued at $14.00 to get the final COGS for that second batch sale you need to know the above logic in order to know what the actual COGS was for the sales batches.
Imagine having a whole bunch of purchases and sales of a given inventory item and having to figure all that out manually. It is time-consuming and difficult. The benefit of this inventory tool is the automation of all those manual calculations so that all the user needs to do is enter their purchase and sales history. The resulting COGS will automatically populate based on the FIFO methodology.
There is an automated total COGS per month calculation based on the date of each sale as well.
The two matrix tabs were required in order to automate this calculation. It was one of the more difficult pieces of logic I have ever did build. The formulas can easily be drug out for more than 350 batches if needed (but that should be sufficient for most organizations).
Similar Products
Other customers were also interested in...
Private Equity Fund Model (Investor Cashflows)
Private Equity Financial Model to analyze fund cashflows and returns available to Limited Partners (... Read more
Leveraged Buyout (LBO) Financial Projection Model
User-friendly financial model to project and analyse the financial outcomes (IRR, projected financia... Read more
Investment Holding Company 3 Statement Financial P...
3 statement 5 or 8 year rolling financial projection Excel model for an investment holding company h... Read more
All My Financial Models, Spreadsheets, Templates, ...
Lifetime access to all future templates as well! Here is a set of spreadsheets that have some of the... Read more
Small Business Playbook (Financial / Tracking Temp...
About the Template Bundle: https://youtu.be/FPj9x-Ahajs These templates were built with the ... Read more
Top 16 Google Sheet Templates
This is a bundle of all the most useful and efficient google sheet templates I have built over the y... Read more
Franchisor Licensing: Financial Model with Cap Tab...
Build up to a 10 year financial forecast with assumptions directly related to the startup and operat... Read more
Joint Venture and Fund Cash Flow Waterfall Templat...
Here are all the spreadsheets I've built that involve cash flow distributions between GP/LP. Include... Read more
Cumulative LP Distribution Joint Venture Waterfall...
A 6 Tier cash flow waterfall template. Plug in the distributable cash flow (+/-) and set the hurdle ... Read more
Investment Fund Preferred Return Tracker: Up to 30...
Track preferred returns for investors in a fund with this template. Premium joint venture tracking t... Read more
You must log in to submit a review.