All Industries, Financial Model, General Excel Financial Models

5-year Financial Projections, Accounting, Accounts Payable, Bookkeeping, CFO, Controlling, Excel, Financial Model, Financial Projections, Financial Reporting, Financial Statements, Forecast, Forecasting, General Ledger, Inventory, Management, Pro-Forma, Three Statement Model | Three Financial Statements

 

Video Tutorial:

Inventory is one of the more difficult things to get right when doing a 3-statement financial model forecast for a business that has to buy inventory for multiple months at a time and/or pay for that inventory at different times instead of 100% just upfront. This template was designed to show all the math and logic for how inventory transactions affect and are accounted for on the Balance Sheet, Income Statement, and Cash Flow Statement so that all items balance.

The most important thing to remember is the Accounting equation Assets = Liabilities + Owners Equity. Inventory affects all of these in some way. The actual data input for this is pretty simple. You will just enter the starting inventory balance, the cost of goods sold (COGS), and update some logic about the frequency of inventory purchases as well as how many months’ worth of future COGS are purchased each time.

Just a reminder of the affected accounts (this model will show all the math and logic as well):

1. Inventory is purchased—Inventory Balance goes up, cash goes down, if all or some portion was bought on account, the difference raises accounts payable (a liability).

2. Cash payment for inventory—Cash goes down and accounts payable goes down by the same amount.

3. Inventory is sold—the cost of goods sold expense is entered and will reduce the inventory value on the Balance Sheet. This also affects net income and in conjunction retained earnings. The cash flow statement zeroes this out so inventory is not accounted for twice (COGS is added back and inventory purchases are reduced). Net Income flows to the cash flow statement as its own item.

This model has all of those logical formulas set up in the proper months so you can see exactly how the values of all relevant accounts should be changing over time. I tried to keep it as simple as possible so the main thing you see is inventory only. I had to include items for the income statement so the net income could be derived on some level, but those entries are completely arbitrary as far as revenue / OpEx / interest.

The template will go out for a period of up to 60 months.