# Food distribution Excel sheet (free): routes and expiry

> Free Excel sheet for food distributors: lots and expiry alerts, sales to shops on routes, collections, shop balances with aging, and rep targets and commission.

Source: https://softwiro.com/blog/food-wholesale-trade-free-excel-online.html
Language: English
Softwiro, softwiro.com

---

Free Excel template

# Food wholesale and distribution in Excel (free): routes, expiry and collection + online

Track lots and expiry, shop balances and rep performance for every route in one sheet.

15 November 2026 5 min read

## In short

- Buy by lot with an expiry date; the sheet suggests the lot expiring first and warns when one is sold after expiry.
- Sell by the carton and the unit to shops, each tied to a route and the route's rep.
- Each shop has a balance, debt aging and a credit-limit warning; collections reduce it.
- Reps are compared with their monthly target, with collection rate, commission and rank.
- Charts and an expiry alert list show what to clear first and its value.

You distribute food to shops and supermarkets through reps on routes, and you want to know without walking the warehouse which batch expires soon and what it is worth, who owes how much, and which rep met the target and how much they collected. The free [food wholesale and distribution sheet](https://softwiro.com/templates/food-wholesale-trade) does this from logs you type: purchases by lot, sales and collections. You can also try it online from the template page.

## The problem it solves

A distributor buys one item in several deliveries with different expiry dates, then sells it by the carton and the unit to shops on credit along a rep's route. If you do not know the balance of each lot you sell the newest and leave the oldest to expire; if you do not tie each shop to its route and each route to its rep you cannot tell who is weak at selling or at collecting. The fix is to type every movement once and let the sheet calculate the rest.

## How it works, step by step

1. In الإعدادات (Settings) write today's date, the days for "near expiry" (30 in the example), the first month of the summary and the month to judge the reps.
2. In الخطوط والمندوبين (Routes and reps) write each route, its rep, monthly target and commission rate. In المحلات (Shops) write each shop with its route and credit limit.
3. In الأصناف (Items) write the code, name, category, units per carton, cost, price and minimum.
4. When goods arrive, write one row per lot in المشتريات (Purchases): supplier, item, lot number, expiry date, cartons and cost.
5. When you sell, write in المبيعات (Sales) the shop, item, lot, cartons and any loose units. The sheet suggests the lot expiring first.
6. Write every payment in التحصيل (Collections), then open تنبيه الصلاحية (Expiry alerts), المحلات and الخطوط والمندوبين.

The rules: units = cartons x units per carton + loose units, and line value = units x (carton price / units per carton) x (1 - discount). Lot balance = the lot's cartons in units - what was sold from it. Cost of a sale comes from the cost of that very lot.

## What each sheet does

- Summary: the key figures, a six-month table and four charts.
- Items: stock in cartons and units and its value, expired and near-expiry quantities, value at risk, earliest expiry and status. 200 items.
- Purchases: each lot's balance, days to expiry and status (fine, near, expired or finished). 500 lots.
- Sales: route, rep, suggested lot, value and profit with error warnings. 1,200 rows.
- Collections: payments with the shop's route and rep. 800 payments.
- Shops: balance due, debt aging and credit limit. 300 shops.
- Routes and reps: sales against target, collection, commission and rank. 20 routes.
- Expiry alerts: expired and near lots, soonest first, with their value.

## How it differs from the batch and expiry inventory sheet

We also have the [barcode, batch and expiry inventory sheet](https://softwiro.com/templates/batch-expiry-inventory), built for the stock itself in a shop or warehouse. This sheet is for a distributor selling to shops on routes: it adds each shop's account, debt aging, collections and rep performance and commission, next to lot and expiry tracking. Storage and shelf-life rules are, as always, according to the competent authority's requirements.

## A walk-through with the sample numbers

These are fictional sample data in the sheet, not a real business: 10 items, 15 lots, 8 shops and 4 routes, and today is 15 November 2026.

- Carton and unit sales: invoice B-203 sold 6 cartons of tea at 5% off: 6 x 1,320 x 0.95 = 7,524. Invoice B-206 sold 20 cartons of biscuits plus 12 units, which is 732 units for 6,222.
- Totals: purchases 542,150, sales 193,490.5 against a cost of 174,039, so profit is 19,451.5, a margin of about 10%.
- Expiry: milk lot M2606 expired on 10 November with 9 cartons left, worth 3,780. Juice lot J2608 expires in 20 days with 31 cartons worth 4,836. Cheese lot C2610 expires in 27 days with 17 cartons worth 5,712. The value at risk is 14,328, and expired plus near stock is 3.9% of stock value.
- Warnings raised: one row sold milk from M2609 while M2606 expires sooner, showing "closest expiry: M2606". Another sold milk from M2606 on 14 November, after it expired, showing "sold after expiry".
- Stock: rice is out (27 cartons bought, 27 sold), and pasta and tea are below their minimum so they show "reorder". Stock is worth 368,111 at cost.
- Collections: 105,000 collected (about 54% of sales) and 91,490.5 owed by shops. Al-Nour grocery owes 13,496 (3,000 old + 15,496 sales - 5,000 paid) against a limit of 10,000, so it shows "over limit", and 3,544 of it is older than 30 days.
- Reps in November: Ahmed sold 24,900 (83% of his 30,000 target), collected 13,000 and earns a commission of 130. Yasser sold 19,920 (99.6%) but collected only 5,000. Islam sold 13,590 (68%) and collected 23,000.

## Common mistakes

- Typing the lot number in Sales differently from Purchases, so the quantity is not taken from the right lot.
- Repeating the same item and lot in two purchase rows. Add the quantity to the first row.
- Forgetting today's date, so an expired lot looks fine.
- Leaving discounts unrecorded, so profit looks bigger than it is.
- Giving a shop a route that is not in Routes and reps, so it counts for no rep.
- Judging a rep on sales alone and ignoring collection.

## When to move to a system

The sheet is enough for a distributor with one warehouse and a few reps. When reps and shops multiply and you need invoices issued from the rep's phone at the shop and alerts on the phone, look at [Accountwiro](https://softwiro.com/systems/budoor.html), or [ask for software built your way](https://softwiro.com/contact).

## Your next step

[Open the template](https://softwiro.com/templates/food-wholesale-trade), download the blank copy, type your items, routes and shops, then log your latest delivery with its lot and expiry date and open "تنبيه الصلاحية" to see what to clear this week.

## Free cheat sheet for this field

Terms, abbreviations, tables and diagrams over several pages, in Arabic and English. Download it as a PDF or images, including a phone-size version.

[![Cheat sheet: food distribution (1/3)](https://softwiro.com/uploads/cheatsheets/food-wholesale-trade/en-1.jpg)](https://softwiro.com/uploads/cheatsheets/food-wholesale-trade/en-1.jpg)[![Cheat sheet: food distribution (2/3)](https://softwiro.com/uploads/cheatsheets/food-wholesale-trade/en-2.jpg)](https://softwiro.com/uploads/cheatsheets/food-wholesale-trade/en-2.jpg)[![Cheat sheet: food distribution (3/3)](https://softwiro.com/uploads/cheatsheets/food-wholesale-trade/en-3.jpg)](https://softwiro.com/uploads/cheatsheets/food-wholesale-trade/en-3.jpg)

0 downloads · 3 pages

## When your work grows: a custom system instead of the sheet

A sheet is a great start. When more than one person works on your data, when it grows, or when you need alerts and reports of your own, we build you a custom system at competitive prices. This is what changes:

| In the sheet | In a custom system |
| --- | --- |
| One file that one person edits at a time, and copies travel by WhatsApp. | A program with a login for each user and permissions you set; everyone sees the same data live. |
| You type and check every number by hand, and one wrong cell breaks the result. | Screens built around your steps: each person records only their part, and the calculations run on their own. |
| Limited size; the file slows down as the data grows. | Holds years of data without slowing down, with instant search and filters. |
| Fixed columns and reports, as the sheet was designed. | Reports, alerts, printing and WhatsApp built around how you work. |
| Backups and security are your own responsibility. | A secured server with automatic backups and a log of who changed what and when. |

### Extra features in the app

- A login for each user with permissions, and a log of who changed what and when
- Automatic reminders and alerts by WhatsApp and email
- Photos, files and documents kept with each record
- Instant search and filters, with reports exported to PDF and Excel
- Works from a phone anywhere, with automatic backups

[Ask for your custom system](https://softwiro.com/contact)

## Questions this raises

### How does the sheet track expiry?

Each lot in Purchases has an expiry date and its days are counted from today's date in Settings. It is near expiry when that many days or fewer remain, expired after the date, and it appears in Expiry alerts with its value.

### How do I sell by the carton and the unit?

Write the units per carton in Items, then in Sales write the cartons and any loose units. The sheet calculates total units and the value at the unit price.

### How is a rep's commission calculated?

Commission = what was collected from the shops on the rep's route in the month set in Settings x the rate typed for the route. The rate is an example, so set it by your agreement.

### How is it different from the batch inventory sheet?

That sheet is for the stock itself in a shop or warehouse. This one is for a distributor selling to shops on routes, and adds each shop's account, debt aging, collection and rep performance.

### Does it replace food storage requirements?

No. It is a recording and calculation tool for stock and sales. Storage, shelf-life and handling rules follow the competent authority's requirements.

## Have a system to build?

Softwiro designs, builds, and operates production systems. Describe what you need and we will come back with a scope rather than a slogan.

Start a project

[All writing](https://softwiro.com/blog.html)
