SmartHelping / Inventory Accounting / Excel
Inventory in a 3-Statement Financial Model
See how inventory purchases, supplier payments, and cost of goods sold flow through the Income Statement, Balance Sheet, and Cash Flow Statement. A focused Excel template for understanding the accounting and forecasting the timing of inventory cash requirements.
Immediate download after purchase. By purchasing, you agree to the Terms of Service.
See the template in action
Walk through inventory across the three statements.
A focused inventory forecasting example
Define the activity and payment timing.
The template keeps the account structure focused so you can trace the inventory calculations and their effect on each financial statement.
Income Statement inputs
Enter expected revenue, cost of goods sold, operating expenses, and interest to work through net income.
Inventory purchase frequency
Define the number of months between inventory purchases in the inventory detail schedule.
Purchase amounts and timing
Follow the amount and timing of each inventory purchase separately from the cash used to pay for it.
Supplier payment split
Model inventory paid for entirely upfront, or a portion upfront with the remainder paid in the following month.
The three-statement connection
Separate the purchase, the expense, and the payment.
Income Statement
COGS records the cost of inventory sold during the month and reduces profit. It also reduces the inventory balance, even when the supplier was paid in an earlier month.
Balance Sheet
Inventory increases by the full purchase amount. Cash decreases by the amount paid, while any unpaid portion increases accounts payable. Paying the remaining balance later reduces both cash and accounts payable.
Cash Flow Statement
In this template’s detailed cash flow presentation, COGS is added back to net income and actual inventory payments are deducted. This replaces the expense timing with the timing of cash paid to suppliers.
Retained earnings and cash
Net income flows into retained earnings. Cash follows the cash flow calculation after the inventory payment adjustments, allowing the statements to connect through the accounting equation.
Follow the inventory balance
Track what is purchased and what is sold.
Ending inventory = Beginning inventory + Inventory purchases − COGS
This simplified relationship isolates inventory purchases and sales. The timing of supplier payments affects cash and accounts payable, rather than changing the value of the inventory purchase itself.
Inventory and cash can move at different times
A purchase can increase inventory before the full supplier payment is made. The unpaid amount remains in accounts payable until it is settled.
Use the accounting equation as a check
Assets = Liabilities + Owners’ Equity. Follow both sides of each transaction to understand why the Balance Sheet stays in balance.
A simple purchase and payment example
Buy $100 of inventory. Pay $60 now and $40 next month.
| Timing | Inventory | Cash | Accounts payable |
|---|---|---|---|
| Purchase month | +$100 | −$60 | +$40 |
| Following month | No change | −$40 | −$40 |
This example isolates the inventory purchase and its payment; it excludes sales, COGS, and other transactions.
When the inventory is purchased
Debit inventory $100, credit cash $60, and credit accounts payable $40. Total assets and total liabilities each increase by $40.
When the remaining balance is paid
Debit accounts payable $40 and credit cash $40. The payment settles the liability without adding another inventory purchase or another COGS expense.
Understand the timing behind the forecast
Build a clearer inventory cash flow model.
Trace the accounting flows
Use the additional calculation detail to understand how inventory moves through the three statements.
Compare profit with cash requirements
See why inventory paid for ahead of sales can use cash before its cost appears in the Income Statement.
Work through supplier payment terms
Follow the split between current cash payments and amounts due in the following month.
Apply the logic to your forecasting work
Use the focused example to understand the inventory schedule before adapting the logic to a broader financial model.
Also included in these bundles
Explore more accounting and inventory templates.
Broader financial forecasting
Explore models built for specific businesses.
Industry-Specific Financial Models
Explore forecasting templates built around the operating drivers of different industries.
Explore industry modelsQuestions before you start
A few useful details.
Can I pay for inventory partly upfront?
Yes. The template supports a portion paid upfront and the remaining amount paid in the following month. It also supports paying the full amount upfront.
Why is COGS added back in this template’s cash flow calculation?
The template reverses the COGS expense included in net income, then deducts actual inventory cash payments. This follows payment timing directly. When adapting the model, avoid also applying inventory and payable adjustments for the same transactions, which would count their cash impact twice.
Does paying a supplier create another expense?
Settling the payable reduces cash and accounts payable. The payment itself does not create another inventory purchase or another COGS charge.
Does net income equal the change in cash?
No. Net income flows to retained earnings, while the cash balance reflects the cash flow calculation, including the timing of inventory payments.
Is this a focused inventory example or a broad operating forecast?
It is a focused template designed to explain inventory within a three-statement financial model, using a limited set of accounts and additional calculation detail.
Understand inventory across the statements
Connect inventory, profit, and cash.
Inventory 3-Statement Excel Template — $45, one-time purchase.
