How to Calculate Inventory Within a 3-Statement Financial Model

Inventory is complicated. No matter what aspect you are talking about with it, there are often difficulties and it can be daunting to get right in the context of proper financial reporting of its value and change over time. This template was designed to let the user see how it needs to flow through all aspects of the financial statements.

Pricing

The template will be sent after purchase via the PayPal/purchaser e-mail. This template is included in the Accounting template category bundle here (3rd category down)

To truly understand all the impacts purchasing inventory has, a solid working knowledge of financial statements is required. The key driving force is the Accounting equation: Assets = Liabilities + Owners Equity. That is the logic I always dive back to myself whenever there is a mystery around how something should work in double-entry accounting.

In this model, I did a little extra calculation detail so the user can see every flow of impact on each of the 3 statements (Balance Sheet, Cash Flow, and Income Statement). The Balance Sheet is usually the most difficult to figure out.

In this template, the user has the ability to enter data for the Income Statement, including expected revenues, COGS (cost of goods sold), OpEx/interest, and get down to a Net Income. I tried to only focus on the minimum number of accounts needed to fully describe how inventory flows. 

In the Income Statement, the relevant piece of data related to inventory is the cost of goods sold, which is the value of inventory that is leaving each month. This will reduce the value of inventory on the balance sheet. On the cash flow statement, the COGS line item will be added back as a non-cash item and the actual cash used to purchase the inventory will be what is reduced out. The reason this is important is because often times inventory will be purchased ahead of time and then depleted over months. For a financial forecasting model, that is important to show because it has implications for cash availability and the timing can be critical.

For when inventory is purchased, this specific template allows the user to enter the number of months between purchases and if inventory is paid for 100% up front or some up front and some the month after. This is done on an 'inventory detail' tab, which will often vary from each situation.  The point is, the user will have to define when the actual cash has gone out to pay for the original purchase of inventory each time as well as when the actual purchase was made and what the total amount was. 

The actual accounting entry descriptions and logic are as follows:

1. The full inventory purchase amount raises the value of inventory on the balance sheet (even if it is not all paid for up front).  So, debit to inventory and credit to cash.

2. However, if some of the inventory is paid for on account and not due until some time in the future, then the accounts payable line item must be used. In this case, the entry is debit to inventory for the full purchase amount, credit to cash for the amount (if any) that was paid up front, and credit to accounts payable. i.e. if $100 of inventory is purchase, and 60% is paid for up front while 40% is paid in the following month, then raise inventory balance on the balance sheet by $100, lower cash by $60 (this will be an item on the cash flow statement that is named something like 'cash paid for inventory', and then the $40 difference is credited to accounts payable in that same month. The net effect on assets is a raise of $40 and the net effect on liabilities is a raise of $40 (they balance!).

3. When the rest of the inventory is actually paid for in the following month, cash goes down by $40 (a credit to an asset will reduce it) and accounts payable goes down by $40 (a debit to a liability will reduce it).

If you follow the concepts in steps 1-3, then you should be able to handle the accounting of nearly all inventory forecasting within the context of a 3-statement financial model.

The rest of the template shows how net income flows to retained earnings as well as increases the cash balance on the Balance Sheet. The goal here was to show as little other detail as possible outside of the key elements of what is going on with inventory specifically.

Here is me building this template from start to finish:


Screenshots of Template: