Financial Model Templates with Inventory Accounting Modules

 When I start to build a financial model template, it is important to look at key modules that need to be included. For example, in a SaaS model it is not likely inventory needs accounted for, but in a business that sells goods to consumers, such as a bike shop, inventory is an important component to account for and requires specific accounting logic. This page will list every single projection model I've built that includes inventory logic.

Relevant Templates:

There are levels of complexity that may need to be included in regard to inventory modeling. The first technique is targeting the cost of goods sold and building an algorithm that lets the user of the template enter the frequency inventory is purchased in months. For example, is inventory purchased every 3 months, or every 5 months? Based on that number, the model will add the total value of cost of goods sold for that number of months in the future and populate the value in the first month of that series. You will see this algorithm in many of the templates above.

A more advanced technique builds upon the logic above and lets the user define a schedule for when those inventory purchases are paid for. This could be a certain percentage in the month of purchase, a certain percentage in month 2, and so on for any configuration of payment terms. This is important and will affect cash flow no matter which method is being used.

One final way I've structured inventory is letting the user define the initial number of months inventory is purchased for at startup. Then, after that initial period, the cash goes out for inventory based on the cost of goods sold each month. It is more primitive but does let you plan out the cash flow prior to stabilization and then assumes you have enough working capital to pay for inventory after this initial inventory.

If your inventory is purchased and sold and paid for all in the same month, then that would require no offsets and the cost of goods sold would accurately represent the right timing for when cash leaves.

There are varying degrees of complexity that can all be built within the structures above, but having that foundational logic is adequate for every case I've ever come across for clients that have inventory heavy businesses.

How This Fits Into a 3-Statement Model?

I actually built an inventory forecasting helper template that has all the logic required to integrate inventory into the income statement, balance sheet, and cash flow statement. Many of the templates above also have this accounting logic coded in for a 3-statement model. The primary line items that are affected are current assets (inventory balance), statement of cash flows (showing actual cash paid for inventory depending on all factors and zeroing out inventory-based cost of goods sold if starting from net income).

Whenever the inventory is purchased (no matter if terms were used or not), the inventory balance goes up by the full purchase amount, whenever that happens. If you are buying with terms, then there will also be an accounts payable account. As inventory is paid for, this reduces cash and reduces accounts payable (asset down and liability down). As you may know, Assets always equal Liabilities + Owners Equity. As the business makes sales and recognizes the cost of goods sold, the inventory balance on the balance sheet is reduced by the cost of goods sold amount. On the balance sheet, this lowers inventory (asset) and lowers equity (COGS lowering net income, which lowers retained earnings).

You may also be interested in more accounting spreadsheet tools. 

Article found in Accounting and Finance.