Supplier Margin Estimator from Published Accounts – Excel XLSX

BENEFITS OF THIS DOWNLOADABLE EXCEL DOCUMENT

1. Estimate a supplier's gross and operating margin, revenue per head and your share of their revenue from accounts they have already filed publicly.
2. Model what your intended price reduction would do to the margin they earn on your account, including the reduction at which it reaches zero.
3. Calibrate an ambitious ask from an absurd one before the meeting, and compare three bidders in the same tender on structure rather than on headline price.

FINANCIAL ANALYSIS EXCEL DESCRIPTION

Supplier Margin Estimator from Published Accounts is an Excel template (XLSX) available for immediate download upon purchase.

Negotiations run on assertion until somebody introduces a fact. This model
introduces one, from a source the supplier cannot dispute: their own filed
accounts.

WHAT IT DOES

Enter revenue, cost of sales, operating costs, headcount and your own
annual spend. All of it is public for most incorporated suppliers, from
Companies House in the UK, the local commercial register across most of
Europe, or the annual report of a listed group.

The model returns gross margin, operating margin, revenue per head and
your share of their revenue. It then applies the group margin to your own
spend to estimate the gross and operating profit they earn on your
account, and models what the price reduction you intend to seek would do
to that margin – including the reduction at which their operating margin
on your account reaches zero.

The worked example shows why this matters. A supplier filing a twenty-two
per cent gross margin and a five per cent operating margin can absorb a
seven per cent price reduction at the gross level, but the same reduction
takes the operating margin on that account below zero. Knowing that before
you open your mouth is the difference between an ambitious ask and one
that will either be refused or agreed and then taken back through scope,
service level and variation orders.

THE SHEET THAT MATTERS MORE THAN THE MODEL

A full second sheet is devoted to reading the result honestly, because the
arithmetic is easy and using it well is not.

Filed accounts are group level, not account level: a supplier serving you
at ten per cent can be filing eighteen because another part of the
business is more profitable. They lag by nine to eighteen months. Intra-
group recharges and transfer pricing move profit between entities entirely
legitimately, so a thin margin at the trading entity is not evidence of
hardship. Abridged accounts hide the useful line altogether. All four
limitations are stated plainly, because a finance business partner will
find them in about four minutes and it is considerably better if you found
them first.

The same sheet states what not to do with the output. Never quote these
numbers at the supplier. It invites a correction you cannot verify, moves
the conversation onto their ground, and makes an adversary of someone you
may need on your side in eighteen months. The model exists to calibrate
your own position – and, most valuably, to compare several bidders in the
same tender, where the differences in margin structure and revenue per
head tell you who is efficient and who is merely cheap. WHO THIS IS FOR

Category managers and procurement leads who negotiate commercial terms and
have to defend the outcome to a finance function. Consultants who need a
defensible model in the first week of an engagement. Finance business
partners who validate what procurement claims. It assumes professional
judgement and supplies the arithmetic, not the other way round.

HOW THE FILE IS BUILT

One Excel workbook, self-contained. A read me sheet stating what the model
does, how to use it and what it is not. The working sheets, with a worked
example filled in throughout and designed to be overwritten. A licence
sheet.

Every cell is unlocked. Every formula is visible and editable. Nothing is
password-protected, because a model a professional cannot adapt into their
own situation is worth nothing. The colour convention is consistent across
the whole catalogue: a yellow fill with blue text is an input you supply,
black text is a formula, green text is a reference to another sheet.

WHAT IS DELIBERATELY NOT IN IT

No benchmarks. No market prices, no index forecasts, no industry margin
ranges, no assumed cost of capital held as a constant. Every field of that
kind is an input, left empty or left as an example, and labelled as an
input. A fabricated benchmark is worse than no benchmark, because a
fabricated number gets reported and then defended.

This is educational and commercial material. It is not legal, tax,
financial or accounting advice and it creates no professional
relationship. Where the subject touches contract wording, classification
or regulation, the file says plainly where your own adviser has to take
over.

LICENCE

Use inside one organisation by up to ten named users, with unlimited
internal adaptation. No resale, redistribution or use as the basis of a
training or consultancy product supplied to third parties. The author
terms inside the file prevail over any more permissive default marketplace
terms.

Source: Best Practices in Financial Analysis, Supplier Relationship Management Excel: Supplier Margin Estimator from Published Accounts Excel (XLSX) Spreadsheet, Davide Sferrazza