A Case Study Involving A Global IT Company Changing its Regional Structure

Section 1 – Case Synopsis and Scene Setting

Audience:??????????????????????

This is aimed at CEOs, CFOs, COOs and Directors of companies who are changing their geographical structure.

Context:?????????????????????????

A global company was re-structuring from 5 geographical regions down to 4.?The impact on supervisory levels and role combination was modelled.?Cost savings were then calculated following the introduction of the simplified structure.

Method:?????????????????????????

Excel was the chosen tool because of its flexibility and a widespread understanding of its use and outputs.

Ratios of supervisory staff to sales and engineering personnel were set as standard.?Then changing the mix of team members allowed the flexing of staff costs.?Salary mixes across the globe were also investigated as was role alignment between regions.

An iterative approach was used so that input from the FD, the regional directors and the regional managers could all be featured in changes to the model.

By using multiple versions it was easy to roll back to prior assumptions.?As time went by a lot more variables were added to the standard data input tab.?By using a single Excel click and selecting a scenario, the impact on changes readily fed into the one page output sheet.

Considerations:???????????

The project was highly sensitive and so precautions were put in place to ensure that data could not be accessed by those outside the project.

The geographical boundaries were subject to change and that affected the model design.?Currencies and exchange rates were also factored in.

?An iterative approach with rollback was used – as during the modelling it became apparent that some assumptions would need changing.

There was a very short timeline which meant deliberate avenues of investigation being taken and a broad base of objectives being met at a higher level and the detail left for others to understand.?Communication between the FD and team members needed to be optimised and focussed to make best use of the FD’s limited time.

It was a high profile exercise and was on the list of metrics that the EMEA Board were monitoring closely.?This meant that weekly updates were necessary.?In turn that would also involve focussing the messages as information was very often incomplete and wide ranging conclusions were not always able to be formulated.

The preferred output style was of a tabular format.?A grid that could easily allow scenarios to be compared ensured that the main outputs, i.e. cost savings and simplification of roles and structure was easily explained by the FD to the Executive levels.

Regional data was compartmentalised so that only regional directors had access to their own data – this being achieved by splitting the model into four separate files.?Subsequently, the regions would manage how each country submitted its own data and they operated their own inter-regional segregations.

Results

The first draft was unsuccessful as supervisory ratios were too ambitious and it became necessary to increase supervisory levels.

The re-grouping of countries into regions was not entirely easy as certain countries were more politically aligned to other regions.?There were almost weekly changes in viewpoint before a final decision was made literally one week before the budget deadline.

Re-assurance around the flexing of countries alignment to regions was met via model parameters being changed at the input scenario level.

Staffing costs for equivalent roles varied widely across geographic regions and even within countries in the same region.?There were a number of sensitivities to be addressed.

Regional definitions of role scope varied a great deal and this meant many additional mapping exercises.?A manager in one region would be a supervisor in another region.?Also the use of senior was open to personal choice and interpretation.

After two months of work, the 15th draft met with the expectations of the strategic level executives on their view of headcount across the globe.

The financial sensitivities and overall cost savings were met.?This included exchange rates and currency consolidation into the USD currency as set out by Group and loaded into the budget.

Conclusion:

The model was fit for purpose and was successfully rolled out across the globe.?Both country managers and regional directors could flex the tool and also they could only look at data that they owned themselves.

All financial and other metrics were used in generation of the full year plan.

Confidential information was segregated away from regional owners as Group consolidated across the globe.

Controls were a vital aspect of the project.?As well having its own internal integrity, the model also had to satisfy a very robust budget uploading template that Group had provided.

?

Section 2 – Business Processes and Model Design and Handling of Variables

As a project manager it was easy to identify the various stake-holders and to also ensure that those not involved in the modelling project were not able to access any information.

The FD and the model developer quickly worked out how best to maximise each other’s time – and that led to early morning working as well as late evening working.

Mini deadlines were put in place and that ensured that time was not wasted on un-necessary developments.

It was acknowledged that the FD would need to get involved in a certain amount of detail as the Excel developer was new to the company and had little exposure to the company structure.

As the project was highly sensitive, it meant that a lot of data was not forthcoming as there were many personal agendas across the globe and also within regions.

Scenario analysis and version control were vital tools – and there was always a need to present multiple options before settling on best practice.?Very often best practice would evolve over time – this was because the model began to take shape quickly and so gave feedback to the managerial and director level review process.

Existing models were of limited use as they were based on the current situation continuing and not flexible enough to deal with the new structure either in terms of physical geography or the wider structural impacts.

The approach used was simple – always create two or three models and then by chatting to directors and the FD decide which had approach had the best application and go with that.

Nothing was ever deleted and any changes could easily be rolled back if it turned out that assumptions had been made in error.

Section 3 – the hard work

This is presented in timeline order and in addition the significance of the area is rated from High to Medium to Low.

3a)?????????The ultimate metrics and model structure??(High)

The start point was in deciding the ultimate metrics – this was a table that showed the before and after position.?Both Financial metrics and number and mix of staff were used.?The output had to fit on to a maximum of two screens in Excel and one printed page.

The next stage was to flesh out the number of regions and countries within a region.?A standardised layout had to be used.?This ensured that each spreadsheet tab could fully accommodate each country in the region and also the various levels of personnel and management.?This was to become a crucial aspect to the modelling process.

3b)?????????Sources of data???????????(High)

Each country in the global organisation was listed and that was then mapped to each of the new regions.?This allowed for a quick and easy way to re?align countries between regions.

The regional tabs had a flexible approach and by using a Lookup to the mapped country, each country was pulled into the correct region – and more importantly each country could only appear in a region once.?In early work, countries had been added in manually – and it turned out that one country had been entered into two regions.

Personnel lists were provided and there were hundreds of job descriptions.?While there was some consolidating of roles, there were still too many to allow for easy analysis and flexing of headcount.?A first mapping was used that took the roles down to around 20 types.?Then a final mapping was used to drive each role down to one of 6 job titles.

Each country in the region then provided headcount data that could be mapped into the new structure.?By using a formula that looked for Country initials in a two letter format, e.g. “DE” and a job role and then a count, it was easy to generate a picture of the headcount apportionment.

3c)??????????Sense checking the data and outputs????????????(Medium)

At this point, the FD needed to get involved for each of the regions.?Levels of supervisory staff to personnel ranged widely – as in some countries there was less supervisory capability and in other countries personnel took on more self-ownership.?This was a factor later when salaries were considered – generally higher salaries equate with more ownership by team members and a reduced need for supervisory staff.

Raw data was not changed – and instead another column was added to the country mapping table that had various ratio considerations.?By enhancing the formulas in each country, the levels of supervisory staff was easily changed.

3d)?????????Excel considerations on headcount numbers????????????(Medium)

A further complication developed as Excel was storing too many decimal places – and it was necessary to use a ROUNDING function in the calculations to end up with whole numbers of staff.

It took a number of iterations to get a working model – and on many occasions the final output numbers would not work within the parameters set by the Executive levels.

3e)?????????Adjustments??(High)

The next decision was made that we needed an adjustments column in each region.?The advantages and disadvantages of using an adjustment column were discussed.?It was agreed that there was greater visibility in using the adjustments column – and also it made for much simpler formulas.?Given that each region had six to fifteen countries and had six staff roles, very soon there were a lot of calculations needed.

3f)??????????Layout considerations????????????(Low)

There were a number of blank rows created between countries – this ensured that each data set was 20 rows apart.?This simple logic saved an awful lot of time in de-bugging formulas.

The FD and modeller disagreed on this approach as it did greatly increase scrolling.?A workaround was used in that many of the un-used rows were minimised in height and so screens could hold a lot more visible data.

3g)??????????Output 1 Milestone??(High)

After around two weeks of development and multiple scenarios, the headcount was finally agreed.?While many country managers and regional directors were unhappy with the proposed reductions in headcount, they were ultimately held to the personnel reductions.?They did have some flexibility in their mix of personnel and that was sparingly applied.

By using the tabular approach a huge number of metrics were produced that showed how each country moved its headcount.?This was then wrapped up into regional level and finally wrapped up into global level.

3h)?????????Feedback?????????(Medium)

At this point there was a large level of dissatisfaction in the process.?Many stakeholders stated that using headcount metrics alone to drive and ratify solutions was not entirely accurate.?

With very senior staff involved, the modeller said the obvious thing to do was to add in financials to the headcount grid.?He acknowledged that while obvious, it was not going to be easy.

3i)???????????Political sensitivities????????????????(Medium)

A huge step back was taken as there were a number of ways in which the financial data could be captured.?Salaries varied widely between regions and even between countries in regions.?Also, with a large number of currencies, there was a need to convert everything into USD.

This confused local managers and regional directors as they only ever reported in local currency.

The modeller then had to decide on whether to have two spreadsheets – one in local currency and one in USD or to have to use complicated formulas that were calculated by the use of various selection boxes.

3j)?????????Currency development 1????????(High)

Given that the FD had greater understanding of both local and USD, the output tables were then modified so that each country was in local currency and then that was multiplied by the appropriate exchange rate before it entered the output table.

When this was rolled out to the regions, it was decided that the approach was overly complex and so had to be re-visited.

After much deliberation and numerous false starts the approach used was to have local currency and USD in the same regional tab in Excel.?Since headcount was being fixed this would satisfy both the FD and the regional directors and country managers.

At this point, the project really hit a wall as there was now just a sea of data in each tab and the outputs bore little resemblance to what was likely to happen in reality.

3k)?????????Currency development 2????????(Medium)

The solution was to work out a new set of output tables and to also create highly visual waterfall charts.?In this way each region could look at its own country split and the FD could look at the Regional split.?By splitting ownership of the financials, the local accountants took far greater care in the numbers – whereas before their attitude had been one of leaving control to Group.

Time pressure was now being felt as we were soon to be only one week away from the deadline.?Working extra hours only gets you so far, and a much simplified approach needed to be taken.

Many Excel formulas were completely re-written in order to give even greater flexibility.?Whereas previously exchange rates were held centrally, there was now an option to over-ride these on a country by country basis.?This was not open book though, and the maximum movement allowed was 10 percent.

After a few days of deliberate tweaking of headcount ratios, exchange rates and personnel mix we came to a financial savings number that was in line with Board expectations.

3l)???????????Explaining what is going on??(High)

Now that the Board members were happy with the final output, it was then necessary to create a bridging file to explain where all the cost savings had been generated.

This was luckily a case of pulling in data into a data extract spreadsheet and then literally crunching formulas on it.?This was iterative in approach as some board members were looking at company structure roles, others at regional differences, others at just the bottom line savings.

By pulling in the data and also assigning other fixed variables, it was easy to generate a large number of columns of data.?As we had already used waterfalls, there were other graph types used as well.?The numbers could then be analysed 3 or 4 different ways and all from the same data set.

Nothing much was changed – however the understanding of differences between countries and between regions became apparent.

The Board were happy with the final output and that then went to be loaded into the budget.

3m)?????????The Budget loading????(High)

Even though Group had set up the budget template and we were aware of it during model development it was not a simple data extract from the model and input into the budget loading process.

It also was not helped by the use of different exchange rates from those first published.?While not hugely impactful on a country level, there was certainly a regional impact and a global impact.?The scale of these adjustments was so large that Group had to issue adjusting lines to the budget subtotals for each region.

This was probably the most crucial aspect as otherwise it was just a lot of hard work and nowhere to go.

3n)??????????The Budget load preparation?????????????(High)

At this point, additional team members were engaged.?This did slow down the project as they were unaware of the regional splits and also the new bandings of roles.?Also there was a very mixed level of accounting knowledge and Excel ability.

Further, there was a need to fit the data into existing budget fields and they did not always align completely with the model.

After a great deal of mapping and combining of data along with cross referencing the budget files we were 90 percent complete.

A number of suggestions were offered to the FD along with corresponding impacts on cost savings across the regions.?The FD evaluated each option and balanced a simple mathematical view with one based more on activity.

This allowed the mapping of the final 10 percent of the files to be completed.

3o)???????????Budget load????(Medium)

This process was out of the project team’s responsibility as it was managed by Group.

There were some discussions back and forth with regards to coding – they were quickly resolved.

The process from start to finish was now complete.

??

Section 4 – Lessons Learned

Given the very short timescale for delivery (4 months from start to finish) and there being no existing Excel templates flexible enough, the project was an overwhelming success.

When looking back there are always things that could have been done better.

4A.?????????Engagement with Regional Directors

The 4 regional directors all felt the process had been thrust upon them and they had very little impact on the initial methodology.?This had led to poor engagement from them and very little buy in to the process.

The senior executives then had to step in very hard to get them to work through the process.?A lot of goodwill was lost in the strong arm techniques.

4B.??????????Not enough future proofing in the initial design

The early drafts all assumed a fixed county split.?When this proved wrong, a lot of changes needed to be made very quickly in order to give greater flexibility on country allocation to region.

Also, the currency factor was ignored and this again led to many changes needing to be made urgently.

Far too many formulas were made overly complicated simply from the FD not wanting an adjustment column.?Once it became clear that the formulas were too time consuming and too dependent on sensitivities, the FD very quickly embraced the adjustment column and admitted their initial decision was wrong.?This was a crucial stage in the modelling process – and without some frank discussions it is highly unlikely the project would have been completed on time.

4C.??????????The budgeting team were involved too late

As covered above, there was a need for additional resource to get the model output converted into a form usable by the budget team.

Quite simply, there was not enough discussion with the Budget Team when setting up the template and assigning roles to bandings.

The role up in the model then had to be un-rolled in order to fit the budget template.

4D.?????????Staff mix

When one member of the team was away on holiday, a less knowledgeable team member was tasked with inflating the numbers following RPI changes.

They had no idea what they were doing and so did nothing.?Consequently two weeks of steady work ended up being done in a day by the head modeller.?This created a great deal of disharmony in The Team and obviously led to huge short cuts being taken by the head modeller to get the numbers anywhere close to those expected.

The FD and Board appreciated the broad brush approach and realised data lines would be made more exact after the Board meeting.

4E.??????????Metrics and output format

While the FD and Board thought everything could be handled by way of tables, once the modeller produced graphs, it became apparent that they gave a much clearer indication on how costs had reduced year on year.?Consequently there was a need for a lot of graphs and all of this work had to be done as a matter of urgency and added to the time pressures of the project delivery.

??

Section 5 – Wrap Up

This case study was revolving around two key factors – cost reduction and structural re-organisation from changing the numbers and mix of staff.

The modelling project was very time pressured and very high profile.

A lot of solo development time was spent on the model. This was coupled with communications and meetings with senior managers, directors and Board members.

Staffing was difficult as there was a need to preserve confidentiality and so there was only a narrow talent of candidates that could be used.

The Budget aspect could have been managed earlier in the process and this would have saved a lot of last minute adjustments being made.

Better communication on exchange rates would also have reduced balancing line adjustments.

Despite difficulties along the way it was a highly successful project and delivered on time.

??

Section 6 – Next steps

If you are going through a company re-sizing, geographical re-structuring, or changing your company staffing matrix then I would love to help you.

• https://www.dhirubhai.net/in/paula-turk/
• ?? UK 07773 368195
• ??INT +44 (0) 7773 368195
• [email protected]

?=================================================================

#Excel modelling #finance #management consulting #Excel Taming #ExcelBeastNow

?

#paulaturk

C-SUITE ROADMAP

The Numbers Gal

FINANCE ON DEMAND

Finance Systems Corporate Expertise

? 2017, 2020 by Paula Turk – all rights reserved

??

#Excel modelling #finance #management consulting #Excel Taming #ExcelBeastNow

=================================================================

?