Annual Budgeting Challenge – The Engineering Consulting Group

BASED ON A REAL CASE STUDY

The company is a UK engineering consulting group, carrying out large civil engineering projects such as the Elizabeth Line in London and Heathrow Terminal 5.

Requirement

• The existing annual budgeting model to be setup for the next year.
• The budgeting process is due to start on 15 September. Today is 15 August.
• The existing Excel model consolidates 400 operating units, rolling up by 4 levels to Group. The spreadsheets are to be located in a folder structure representing the group structure, protected by normal Windows Active Directory security.
• Consolidation is by external links. There are a total of 377 million external links and formulas.
• The model was first constructed the previous year.
• The budget templates are to be partly populated by pre-populated data, allocation of shared services, some of which are allocated from budget data that will be gathered from the inputs, Year-to-Date actuals from the latest reported monthly accounts, charge out rates and utilisation % of consultants’ fees (their average chargeable time)
• The group structure has changed since last year, and is further expected to change during or shortly after this budgeting process.
• Six lists of data are required as drivers and essential data from various departments (eg. HR, billing forecasts, staff salaries, fixed assets). These are not ready. They may be ready by the 2nd or 3rd week into constructing the budget model.
• The budget template is being modified/improved. It is also not ready. This may be ready by the 3rd week.
• The budget workbooks are to be individually password protected. NB: staff salaries and charge out rates are listed.
• The person who built the model has left the company.
• The budgeting process is to begin on 15 September, and close on 15 October. Budget packs to be presented to the Board by 15 November.
• This is required to be a live process in real-time. The existing consolidation (with links) updates immediately as any formula-based model would do. This is necessary as budget holders collaborate with their subordinates throughout the process in real-time. A batch process will not be appropriate.
• The budgeting models is required to work (be usable) next year and subsequent years without any major modification.
• Only authorised persons can see/update the budgets. Currently this is effected by the workbooks being password protected, and the usual Windows Active Directory permissions that control access to the folders where the templates are located. The finance team have full access to the folder set.

Question

What Excel skills does a competent candidate need to have to successfully take on this assignment?

How would an employer decide what skills are needed?

How would an employer verify that the candidate has the required skills?

Evaluation of the Problem

Itemise and explain the main issues that need to be overcome, that the candidate needs to identify.

Is this the job of the employer? The agency? Or the candidate to self-determine?