How to Calculate Inventory Within a 3-Statement Financial Model

Inventory is complicated and figuring out the right formulas to use so the three statement model balances can be difficult even if you understand the Accounting entries. This will show the proper financial forecasting of all the items that effect inventory over time. This template was designed to let the user see deeper into the inventory portion of financial statements.

$45.00 USD

The template will be immediately available to download after purchase. This is included in the Accounting Templates for Excel and inventory bundle.


inventory forecasting

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.

More Accounting Spreadsheets / Calculators / Tools: