Integrating Inventory Into a 3-Statement Financial Model

This template is designed to illustrate how the inventory account effects all areas of a 3-statement financial model.

by Jason Varner
smarthelping.com - Freelancer - I make my living working in Excel and G-sheets.Follow 140

34 views|Start the discussion!|Bookmark Add to cart - $45.00

Description
A 3-statement model includes an Income Statement, Balance Sheet, and Cash Flow Statement. Each statement has a place for inventory and depending on payment terms as well as purchasing frequency, the logic can be difficult to get right so that all the statements balance out.

When inventory is purchased, the inventory account on the balance sheet will go up by the full purchase amount. The cash used to make this purchase will reduce assets (via the cash account). If only a portion of the inventory is paid for up front, then the difference between purchase amount and paid amount will go into accounts payable. When the rest of the cash is paid in the following month (or whatever month in the future), the cash account goes down and the accounts payable goes down.

The inventory account on the balance sheet will go down by the value of cost of goods sold (COGS) each month. COGS is an income statement line item. The reason why a cash flow statement is needed is because COGS doesn't always equal the purchase amount, especially when there are lead times for inventory purchases and/or when inventory is purchased for multiple months at a time.

This Excel template shows all the innerworkings of all relevant accounts that have to do with inventory so that the logic is able to be followed clearly and applied to any forecast that involves inventory dynamics.

The logic for when purchases are made and how often has some basic logic around this, but if that doesn't fit, the user can hard code in their expected purchase amounts by month and when the actual cash is paid.

This is a fairly simple template, but the amount of hours that some people end up putting into figuring this logic out can be great. Using this as a starting point should cut that time down considerably.

This Best Practice includes
1 Excel model and 1 Tutorial Video