Excel Data Analysis + Extraction and Visualization

We have a large dataset in excel that we need put together in a easier format to manipulate and run analytical reports on.

Have a detailed excel file that I need someone who is very good at creating pivot tables and manipulating data. Also someone who can easily extract data from PDF files.

Our platform is buying a series of 77 retail stores – salon/barbershops in the Denver and Seattle region. This could go up to 93 if we include Phoenix.

We have information on all of these stores in the following format – see excel file example. Each store is in a tab format.

We also have lease information for all of the stores – this is in a separate Dropbox.

We need the following done:

•Consolidate all stores by region into an easy to manipulate pivot table so I can normalize all the stores by all the different line items

For each store – we have lease information in PDF – this needs to be put into the excel file for each store so we can then see when the leases expire and also if there are any termination fees

We will want to run statistics on all stores
Quartile analysis
90% percentile stores in terms of
Gross margin
Labor costs
Variable vs. Fixed
Rent Expense
Distribution analysis of stores by year for the past 3 years
Create buckets based on stores and lease expiry
Based on this analysis we will label the stores into buckets determining which one to close


Extra to be done (to be decided later)
Put all stores on a map (google maps)
Hover over to see which ones we will decide to close