EXCEL DATA ANALYSIS

Description of excel work: Hi,

My company provides photos for estate agent branches. We have recently been told one of our contracts is going to increase by 300% in order volume. The challenge this presents is that we need to know where in the UK we need more photographers. The requirement for additional photographers can come for 2 main reasons:
- A new branch ie new location
- A current photographer is too busy or will be too busy with the growth

Due to this we need to do some analysis on where we may need more photographers in the UK AND which areas/photographers or on the limit of capacity. In order to find these answers we need to bring together a few different reports.

I will provide the following reports (all separate so you will need the correct skills to collate the data correctly):
- 20,000 previous orders (Date or order, branch location & name, photographer name etc)
- All branches related to the growing contract
- Expected volumes by region
- Capacity per photographer
I also need to point out that some of the reports are provided by the client and other data is from our own systems. This means there are some subtle differences ie Branch Brand names "Abbotts" which is the same as "Abbotts Countrywide" etc

I want to achieve the following:
- Produce a list of all the branches related to this contract
- Associate the "Region" with each branch
- Show how many orders these branches have place in the past / show which branches have not ordered
- Use the past order volumes to produce a predicted ratio of order volumes based on the regional predictions
- Show which photographers have been used for previous branch orders
- Show which photographers have not received work from this client but could do work in the area
- Show a capacity for each photographer ie max order volume
- Calculate a risk metric to guide my team on which areas/photographers to focus on first

Regards

Rolf