Distillery financial model: production, inventory and cash

A distillery financial model connects production spending, aging inventory, approved releases and sales to cash. Start with a batch register and the owner's supplied yield and maturation assumptions, then compare those inputs with actual production and collections. New fills and current shipments need separate schedules because they can belong to different production cohorts.

Bring batch records and finance records together

Use a batch register showing fill date, quantity, storage location, recorded losses and approved release status. Add the sales and shipment export, production payments, packaging payments, customer receipts and opening balances. Carry the same batch or product identifier through the records wherever it is available.

The production owner supplies readiness dates, release quantities and expected recovery. Finance supplies costs and collection assumptions. Neither a calendar interval nor an inventory total establishes that a cohort is ready to sell. Keep approvals and assumption versions visible so a revised release case cannot be mistaken for an actual result.

The SBA's finance guide describes the balance sheet as a snapshot of assets, liabilities and equity and connects financial records with cash-flow planning. For this task, that means preserving inventory and receivable balances alongside monthly spending. A sales total alone cannot explain the cash committed to stock.

Define a consistent quantity basis

This illustrative Kentucky distillery uses case-equivalents on an owner-defined, consistent quantity basis. Aging stock and new fills are measured before the supplied recovery factor; recovered output and shipments use the corresponding saleable quantity. The factor represents the owner's expected physical recovery between release and saleable output. It is not an industry benchmark.

The owner provides a cohort release schedule for the month. The plan releases 1,200 case-equivalents; actual records show 900. A separately approved scenario releases 1,200 while retaining actual spending and all other actual assumptions. Each release must be supported by eligible cohorts; the schedule does not make a maturation forecast.

In Excel, put these input labels in column A, plan in B, actual in C and the release scenario in D. All money is USD.

Packaging is paid for shipped cases in this simplified month, and production payments occur when the new fills are made. There are no payable movements, financing receipts or other cash movements. Add actual payment schedules when those assumptions do not hold. This is an operational cash exercise; alcohol-tax calculations are outside its scope.

Calculate feasible shipments and cash

Shipments cannot exceed either demand or available saleable stock. Microsoft's MIN function reference documents choosing the smaller value. Its SUM reference documents the range addition used for production and packaging payments. Enter these formulas in B16:B25 respectively:

=B3*B4
=MIN(B7,B6+B16)
=B2-B3+B5
=B6+B16-B17
=B17*B8
=B5*B9
=B17*B10
=B14+B20*B13
=B20*(1-B13)
=B15+B23-SUM(B21:B22)-B11-B12

Copy the formulas across to D. Covirage is not affiliated with Microsoft.

All displayed outputs are exact for these inputs. Actual stock limits shipments to 100 opening cases plus 810 recovered cases. Increasing new fills would not remove that release constraint. The approved release scenario raises shipments by 190 cases and closing cash by $8,170: additional collections of 190 × $60 × 80%, less packaging of 190 × $5.

Prove quantities before interpreting cash

Actual recovery loss is 900 − 810 = 90 case-equivalents. The total quantity check is:

Opening aging + Opening saleable + New fills − Recovery loss − Shipments = Closing aging + Closing saleable

For actual results, 10,000 + 100 + 2,200 − 90 − 910 = 11,300 + 0. Reconcile the released quantity to individual cohorts as well; a matching total can still hide a wrong batch assignment.

The cash check is $80,000 + $53,680 − $26,400 − $4,550 − $15,000 − $10,000 = $77,730. Receivables also close: $10,000 + $54,600 − $53,680 = $10,920. Tie receipts and payments to the cash records before discussing a funding gap.

Inventory valuation and profit require a separate approved cost rollforward; current production payments are not current cost of goods sold. The broader manufacturing financial model explains production and capacity, while gross profit margin owns the profit calculation. Use FP&A analysis for the wider planning context.

Where it goes wrong

• All aging stock treated as available. Only owner-approved eligible releases enter saleable output.
• Recovery applied twice. If a recorded quantity is already saleable output, do not reduce it by the factor again.
• Inconsistent units. Establish the case-equivalent conversion before combining batch quantities with shipment cases.
• Cash spending called margin. Costs of new fills and costs released with sold inventory belong to different schedules.

Compare your distillery files in Covirage

Bring a file or Google Sheet with batch quantities, supplied release assumptions, costs, shipments and collections; Covirage maps the columns, and its tools compute and check the supplied cases against actual results. The external AI model explains the differences with every figure traced to its rows. Create a free Covirage account, which takes about a minute, and bring a sample of your own data.

See Covirage for industrial manufacturers for the industry context.