Major Sales and Distribution Group – Budgeting and Forecasting systems overhaul


Greenfield created and implemented a robust Excel VBA based budgeting and planning system for the 500+ branches and the area, region and head office structure. 

The annual longer term planning system was also rigorously streamlined. Management at all levels were briefed so that the new system was thoroughly understood by all.


Key software packages used: Excel VBA, Lotus123

 In more detail, the project involved:

  • This FTSE200 group had significantly expanded recently via acquisitions and mergers.   The group has a four-level business structure: HQ – Regions - Areas – Branches (over 500) and their previous semi-manual process for determining the overall group annual operating plan (AOP) was becoming very difficult to manage.
    Sales, costs and profit/loss forecasts per Branch were collected, aggregated to Region then Area then HQ levels with negotiation and debate being required at all levels.
  • After initial consultation with the Finance and IT directors, and other representatives of the business including recently acquired companies, we developed a completely robust Excel VBA based budgeting system of the group, working in 2 stages. 
    • Stage 1:  The Finance and HR IT systems are accessed to capture the current sales/ finance data, current staff information and capital expenditure data per Branch, then the model automatically creates Branch spreadsheets, which the Branch managers then access to input their sales, costs and profits forecasts for the next 12 months.
    • Stage 2:  Once each Branch has created their initial Branch AOP spreadsheets, the Area Managers utilise the Area Aggregation “press button only” routine to immediately aggregate all their branches’ AOPs, to have an Area AOP.  Each Area’s AOP can similarly be aggregated to a Region’s aggregated AOP, and Regions to a Group AOP. 
  • The model allows the directors to immediately discuss the entire Group’s forecasts at all levels for the next 12 months, including changing various branch inputs in order to arrive at AOP forecasts that all can agree on.]
  • The group now has an easy to understand, streamlined and rigorous IT based annual planning system. 
T: +44(116) 289 4451   M: +44(7801) 709 054   E: info@GreenfieldEnterprises.co.uk   W: www.GreenfieldEnterprises.co.uk  |  Sitemap
Registered Office: Greenfield Enterprises Limited, 40 Gwencole Crescent, Leicester LE3 2FH United Kingdom.   Company Reg. Number: 4069567 VAT Reg. 754 4746 07