Drinks Distributor Model: Route Economics & Working Capital – Excel XLSX

BENEFITS OF THIS DOWNLOADABLE EXCEL DOCUMENT

1. Shows what each delivery really earns: gross profit, cost to serve and breakeven drop value by customer segment.
2. Treats duty as a pass-through so margins stay honest while the working capital it ties up is fully funded.
3. Finds the peak funding need month by month, including the stock build ahead of summer and Christmas.

CONSUMER PACKAGED GOODS EXCEL DESCRIPTION

Drinks Distributor Model: Route Economics & Working Capital is an Excel template (XLSX) available for immediate download upon purchase.

The Drinks Wholesale / On-Trade Distributor Model plans a beverage wholesaler that supplies bars, restaurants, hotels and clubs the way the people running one actually think: outlets times drops per week times drop value, trade terms that leak margin, trucks that cost money every time they stop, and a working-capital cycle that peaks before Christmas.

What it does: you describe four customer segments (active outlets, net new outlets, drops per outlet per week, average drop value, trade discount, retrospective rebate, debtor days and bad debt) and four product categories (list price, supplier cost and duty per case, supplier rebate, stock days and supplier payment days), plus a product mix for each segment. The model builds Year 1 month by month with a seasonality index for summer and December, then Years 2 and 3 annually, all the way to net profit, a direct cash flow, a working-capital facility and a balancing balance sheet.

Main features: a duty / excise toggle that treats duty as a pass-through, so gross profit is unchanged but revenue, margin % and the working capital you fund move correctly; delivery route economics with drops per route per day, a core fleet, crew, running cost per km and peak hire when routes exceed capacity; warehouse cost per case; customer and supplier retrospective rebates accrued monthly and settled quarterly; debtors by segment, forward-cover stock and creditors by category; automatic facility draw and repay with the Year 1 peak funding need and the month it happens; a KPI dashboard with gross and net margin, cost to serve per drop, revenue per route-day, cash conversion cycle and bad debt; segment economics with a minimum order value test and a breakeven drop value; and two sensitivity grids on drop size and trade discount. 3,450 live formulas, zero errors after a full recalculation, and every headline figure independently re-derived outside Excel.

How to work with it: overwrite the blue input cells on the Assumptions sheet, starting with the yellow key drivers. Every other sheet calculates. Ten model checks on the Dashboard should all read OK. The worked example is a fictional regional distributor with about 585 outlets, 155 drops a day and nine trucks: 21% gross margin on duty-inclusive revenue (27% excluding duty), 3.5% net margin, 38 debtor days and a November peak funding need of about $831k.

Why you need it: in drinks distribution the profit is decided by drop size, trade terms and the cost of each delivery, and the cash is decided by duty sitting in debtors and stock. This model puts all of that on one page before you sign a new account, change a discount or add a truck. Simplifications are disclosed on the cover: one delivery cost per drop for all segments, days-based balances, and sales tax excluded. It ships fully unlocked, every formula visible, no macros and no external links.

Currency-agnostic; treat $ as your currency. For planning only, not financial advice.

Source: Best Practices in Consumer Packaged Goods, Integrated Financial Model Excel: Drinks Distributor Model: Route Economics & Working Capital Excel (XLSX) Spreadsheet, g59076599o70