Forecasting Sales Figures and Consolidate Sales Across Cities in One Single Sheet

Hi All,

Am appearing for a test for one of Business Organizations which has given test to complete as below
We've given you a data dump of two city P&Ls, some trip data and a P&L line item key (all
data has been obscured for confidentiality). Using this, create a flexible forecast model which
includes the following:
1. On two new tabs, create a historic monthly P&L and forecast through the end of 2014 for
each of the cities provided (use only the line items in the P&L key provided). Focus on
making the formulas dynamic and scalable so that we can add one, two or fifty additional
cities with minimal effort.

2. On another new tab, again using dynamic formulas, create a consolidated total company
P&L for the historic and projected periods that can easily accommodate new cities as well.

3. For all historic and forecast periods, also pull in and forecast relevant car type data

4. Create one or two visual displays of car type data and one or two visual displays of
financial data for the historic and/or forecast periods

Additional information and underlying principles:

● Imagine how many cities we are in today, and where we will be in the next 1, 2, 4 years -
scalability is paramount. Endeavor to make the process as robust as possible such that adding
new cities in the future creates minimal friction.

● Complexity is not synonymous with quality or intelligence. Your model should seek to
achieve all of the objectives above, but a new user should be able to jump in with little in the
way of a learning curve, and it should be easy to audit formulas and model flow.

● Feel free to organize the individual and consolidated P&Ls in any fashion you'd like in
order to facilitate crisp analysis

The main evaluation criteria for the model include business insight, scalability of the
solution, flexibility of model inputs, reasonableness of model and ease of use / cleanliness of
display

For the original Excel File attached and result prepared is attached for reference, Am looking for help in

1. Consolidation of Excel Data of different Cities using dynamic Formulas - Use of SUMIFS with only part of string in Master Data
2. Want expert advice to understand if using TREND Formula is okay for Sales Forecasting or something else can be applied
3 Which formulas to use to ensure scaleability of cities is easy
4. for my understanding of only forecasting of City 1 & 3 is okay or other cities need to be forecasted for as well ?

Thanks for your urgent help