Inventory

What is inventory? #

Inventory refers to the goods and materials a business holds for sale or as part of its production process. The financial accounting treatment of inventory is closely tied to cost of goods sold (COGS). COGS are the direct costs attributable to the production of the goods sold by a company, and will include the purchase price of the inventory and labor costs and other direct costs involved in bringing the inventory to its current location and condition. As inventory is sold, its cost is moved from the balance sheet to the income statement as COGS.

Managing inventory levels and forecasting inventory purchases can have an important impact on cash. While COGS is an expense on the income statement, COGS matches the recognition of the expense of the goods sold with the revenues from the sale of the goods (an application of the matching principle in accounting) and may not reflect actual cash outlays in those periods. If businesses have lead-times in producing products or services, or have to incur expenses to purchae inventory in periods prior to when they are sold, then forecasting the cash impact of inventory purchases and inventory balances is important in creating a financial forecast.

The timing of those inventory purchases will be based on demand forecasts and production process realities. It may take 2 months to produce something before it is ready to sell, for example, an outlay of cash to purchase inventory well before the revenues from selling that product are realized. Inventory management is important: too little inventory risks lost sales and customer dissatisfaction, too much inventory can lead to increased holding costs and risk of inventory write-offs for products that are not sold.

Types of Businesses with Inventory #

Inventory is a key asset for a wide range of businesses, particularly those in the manufacturing, retail, and wholesale sectors.

• Manufacturing: Companies hold raw materials, work-in-progress, and finished goods as part of their production cycle.
• Retail: Retailers maintain inventory of goods for resale to consumers, aiming to match supply with consumer demand patterns.
• Wholesale: Wholesalers hold large quantities of goods for resale to retailers or other businesses, often in bulk.

Inventory on Financial Statements #

Inventory balances are displayed on the balance sheet under current assets, indicating the value of goods that are ready or will be ready for sale within the fiscal year. The valuation of inventory is critical and must reflect either its cost or its market value, whichever is lower, according to the principle of conservatism in accounting. This valuation affects not only the balance sheet but also the income statement through COGS. Inventory balances increase when goods and materials are purchased and decrease when cost of goods sold are recognized.

Understanding the intricacies of inventory and its effect on the financial statements and business operations is essential for stakeholders to assess a company's operational efficiency and financial health. It requires a careful balance of procurement, management, and sales strategies to align inventory levels with business goals and market demand.

How to use #

The Standard Financial Model, Starter Financial Model, and Runway and Cash Forecasting Tool all share a core component in the Forecast sheet that handles the calculations of inventory purchases and resulting inventory balances.

The inventory secion is optional and be easily removed if desired, or simply ingored if none of the inputs on Get Started are used. The model uses forecasted sales, the desired minimum stock levels, and the lag time from purchase order to being available for sale to forecast when to place purchase orders for inventories, and then uses the payment inputs to calculate when the purchase orders are paid for. From that, the model calculates inventory levels, inventory purchases, work in progress, and all the related balance sheet accounts. This focuses on the cash impact of purchasing the inventory required for the forecasted sales; the inventory calculations do not calculate the number of units of the SKUs, but it could be edited to add that in based on the dollar inventory forecast created in this section.

How it works #

The inputs used in calculating inventory is located on the Get Started sheet. Each input is optional, and they can be combines to mimic advanced inventory purchasing forecasting decisions:

• Number of Months lead time from purchase to in inventory. If applicable, enter in positive numbers only. This takes the forecasted COGS and applies a lead time from when purchases are made to when they are added to inventory. This takes the forecasted purchases and creates a lag to when they are added to inventory. Work in progress and finished goods are considered a subset of the inventory and are not split out specifically.
• Safety Stock - Ending Inventory as % of COGS. If applicable, a percentage of the current month's COGS can be kept in inventory, as "safety stock". This can be used in addition to the lead time assumptions if desired.
• Minimum Order Quantity (MOQ). If applicable, the dollar value of the minimum order quantity. For example, if the inventory calcs say you need to purchase $500 of inventory, and your minimum order from your supplier is $1500, the model will buy $1500 of inventory and then run it down until next time it needs inventory.
• Percentage of Inventory Purchase paid at time of purchase. This lets you determine when the inventory purchases are paid, and set how much is paid at time of purchase.
• Remaining Inventory Purchases, paid 0 days post-purchase. The model allows you to assume the Inventory Accounts Payable so that you can pay for inventory after purchase. The model calculates the payables based on the other two options above first, then assumes the remaining flows through this timing.

Value of Inventory Balances #

This is used to create the inventory calculations on the Forecast sheet, broken into a calculation of inventory balances and the value of inventory accounts payable, if relevant:

• Inventory Value, Beginning of Period. If applicable, value of inventory at beginning of period
• New Purchases, Inventory, manual entry. Purchases of Inventory entered into the Forecast sheet in the core revenue and expenses entry section at the top of the sheet.
• New Purchases, Inventory, based on lead time. Automatically calculated using assumption of # of months lead time from purchase to sale (on Get Started sheet). The hard part to this formula is where if estimates sales based on the lag from when inventory purchase is made until it comes available to sale, and if an order has been places but not yet delivered, does not continue buying.
• New Purchases, Inventory, based on safety stock (optional). If necessary to purchase more to fulfill on inventory needs. This will add more inventory if needed by the safety stock assumption
• New Purchases, Inventory, based on minimum order quantity (optional). If necessary to purchase more to fulfill on inventory needs and satisfy the minimum order quantity (MOQ), from Get Started sheet. Only used if the MOW assumption is not equal to zero, and if inventory purchases is necessary so that inventory does not go negative
• New Purchases, Inventory (optional). If the Actuals are used, this will pull from the actuals, and the total will not use the forecast above
• Total New Purchases, Inventory, manual entry. Sum of the above.
• Cost of Goods Sold. COGS related to inventory from above. This will reduce inventory balances.
• Inventory Disposals (optional). If the Actuals are used in Hooks, this will the sale or loss of inventory if relevant, based on the relative changes in inventory, work in progress, and cost of sales.
• Inventory Value, End of Period. If applicable, value of inventory at end of period. Is this negative in early months? That's likely because of a lead time in your inventory purchasing that is not picked up for early months sales. Edit your forecast so that you do not selling until you have enough lead time for your inventory, or add in an opening inventory amount (on Statements) that covers the early inventory costs

Inventory Accounts Payable #

If the assumptions for when inventory is paid for are used, this will calculate the value of unpaid inventory pur

• Inventory Accounts Payable, Beginning of Period. Inventory accounts payable, beginning of period
• New Inventory Purchases. This takes the total purchases for inventory above, adds it to payables. This gets reduced by the different payables timing below.
• Paid at time of Purchase optional. Uses the input from Get Started to calculate how much of the inventory purchases are paid at the time of purchase. Model default is for all purchases to be paid at time of ordering inventory.
• Not applicable or Paid at time of Purchase (optional). If the two options above results in less than 100%, the remaining will be paid using a Days Accounts Payable assumption, and calculated on this line.
• Net New Inventory Accounts Payable (optional). If the Actuals are used, this will pull from the Actuals and the total will not use the forecast above
• Inventory Accounts Payable, End of Period. Inventory accounts payable, end of period

Finished Inventory #

The above inventory section calculates the total value of inventory, including work-in-progress. This optional section shows what the finished inventory would be, to make sure that the inventory purchases account for what is needed to sell in each period

• Finished Inventory Value, Beginning of Period: Value at beginning of period.
• Additions to Finished Inventory: uses the lag from Get started, if necessary. New additions to finished inventory during period.
• Manual Input, Additions to Finished Inventory (optional). In case there are additions to finished inventory not captured in the forecast above, or in the opening balance sheet. Often if there are purchases made prior to the start of the forecast, there may be additions due to enter into finished inventory that would not otherwise be known or captured.
• Cost of Goods Sold and Inventory Disposals: Cost of sales plus disposals of inventory
• Finished Inventory Value, End of Period. Value of finished inventory, end of period

Usage Considerations #

A few notes on how to use these calcs:

• The model uses the "Product & Materials" category on Forecast to represent the cost of sales associated with the direct production of the good held in inventory. You can allocate other cost of sales - fulfillment, shipping, customer support, etc. - to a different cost of sales category to separate them from inventory accounting. You can also check the box in the "Per Row Data Flags" column to choose whether to include that category into inventory accounting.
• You can also input inventory purchases into the Forecast sheet in the core revenue and expense entry section at the top, either inputting them manually, using the drivers, linking from your own inventory calculations, allowing you to create your own logic for calculating inventory.
• The model calculates the value of inventory but not the number of SKUs. You can create your own calculations of volume levels using the COGS per SKU, or your own custom logic.
• This handles all types of inventory using the same production assumptions. You can create as many different inventory balance calculations as necessary for the level of detail you need.