Ian Buchanan

CUSTOMER PRICING & MARGIN REVIEW TEMPLATE.

Designed primarily for margin retention, recovery & elevation for customers on contract pricing which is being reviewed on a quarter or half-year cycle. Critical when inflation rates are NOT low single digit. An Excel template incorporating complex formulas showing current contract prices, current standard prices (for basing), sales, cost of sales, gross margin. VBA macros implemented for sales ranking, auto-refresh and PDF save/form clear. Principal objectives of this template is to highlight the IMPACTS of price changes, up or down, on both A. Full supply basket gross margin, and B. Customer spend. Crucial for both profitability and customer retention, keeping the basket competitively priced.

Designed collaboratively with Key Account Manager. Adopted immediately across the team when finalised.

Reduced the time required for typical customer price review from one day to one hour. Efficiency via automation.

BASKET PRICE COMPARISON TOOL.

Designed within Excel for the purpose of being a self-service tool for all members of the sales teams within the catering division. Using advanced formulas for price stream selection (Standard/Promo/Bespoke) and purchases saving calculations by order/by month – projected/annual – projected. Required multiple stages of development to achieve a weekly refresh routine status. VBA macro buttons included to facilitate 1. Save to PDF, 2. Clear of template, 3. Transfer of current (pro rata) competitor pricing to a central consolidation file for analysis, insights, new pricing formulation.

Described by several of the catering sales controllers as the best innovation they had seen in the catering division within the last decade.

CUSTOMER SAVINGS & GROSS MARGIN CALCULATOR.

A versatile template developed within MS Excel which calculates both A. Customer Savings, and B. Gross Margin. An effective ‘Balancing Tool’ when there are clear objectives for both parties, customer & supplier, namely Purchase Savings & Gross / Product Margin. Deployment of complex formulas for customer bespoke price modelling. Often blended, being a mix of ‘price match’, cost plus, list price less discount. Flexibility included to apply pricing by individual products, by category, by storage, by brand owner.

Used collaboratively with the Key Account Managers & Catering Sales Managers to great effect.