Customer and Product Profitability Workbook

Your biggest customer may be your least profitable. Revenue rankings hide discount depth, rebates, small drop-size orders and 90-day terms. This workbook shows, from your own invoice lines, which customers and products actually earn money after those costs, and what a discount cap would change.

What is inside

• Settings: period, target gross margin, cost of capital, the A and B thresholds for ABC and the discount-cap test (the cap and the share of that volume you expect to lose).
• Customers and Products: up to 100 each, with payment days, rebate percentage, cost per order and cost-to-serve percentage per customer and standard cost per product.
• Sales: up to 2,500 invoice lines with date, invoice, customer, product, quantity, list price and net price. Credit notes are negative quantities, and a unit cost override is allowed per line.
• CustomerAnalysis and ProductAnalysis: revenue, discount against list, gross margin, rebate, cost to serve, cost of waiting for payment, contribution, class A, B or C, and flags such as LOSS AFTER COSTS and BELOW TARGET GM.
• Pareto: customers and products ranked by revenue with cumulative share and a chart for each.
• Leakage: the bridge from list-price revenue to contribution, gross margin by discount band, the cap what-if, the ten customers with the biggest margin gap and the ten products with the most discount given.
• Dashboard (four native charts) and Checks.

How it is deeper than the cheaper Marketplace Price and Margin Calculator and the Multi-Channel Profit and ROAS app: those look at channel and listing margin. This workbook looks at who buys and on what terms, going beyond gross margin to the costs of serving and waiting for each customer.

Facts and settings: cost to serve is a simple allocation you set, not activity-based costing. Unit costs are standard or overridden, not batch costs. Sales taxes are excluded. The cap what-if ignores competitors and customer reaction beyond the volume loss you enter. Use it to find questions to ask, not as proof.

What it is NOT: it is a calculation tool, not accounting, tax or legal advice. It is not connected to your bank or accounting system, it is not an FTA-approved system and it is not an e-invoicing tool (e-invoicing is on the Zeyto roadmap only). Where a rule matters it is a setting you enter, with a note to confirm it with the FTA, your accountant or your auditor.

Two workbooks: an EXAMPLE copy filled with invented data so you can see every sheet working, and a BLANK copy with the same formulas ready for your own data. Formulas only: no macros, no volatile functions, no add-ins. Input cells are coloured, formula sheets are protected without a password, and a Checks sheet turns green only when every list ties out.

How it was checked: recalculated with an independent Python formula engine and compared with a separate Python model, 1,038 outputs in all, for 36 invented customers and 20 products (every customer and product line, ABC class, Pareto order, bridge step, discount band, cap what-if and top-ten list). Because the 2,500-line workbook is slow to evaluate with that engine, the check was run on a copy with 1,000 invoice-line rows built from identical formulas, and the shipped file has 2,500 pre-built rows. There is no Excel or LibreOffice on the build machine, so it was not opened in them and the charts were not viewed on screen.

Limits: the numbers are only as good as the cost-to-serve and rebate percentages you set, and a customer who loses money may still be worth keeping for reasons the workbook cannot see. The example data has no customer that loses money after costs, so the LOSS flag appears only when your own data produces one.