Background to the project (Assumed names): 7Eleven fuel sites has experience less sales volume and they believe it is because of their competitor CX. For this purpose, we are going to check how close are 7eleven sites to CX and if their price has any impact on 7eleven fuel sites. For this purpose, I have geolocations for 7eleven sites and CX sites. You will have to the find the distance between the two sites and then check if at any sites price has impact the 7eleven sales. The key question is that since Coles Express (CX) has brought their fuel pricing back in line with the market (they were way above the average market price ) what has been the impact on our stores (7eleven) that are located close to Coles Express compared to those that are not located near them and what is the size of this impact in terms of fuel transactions and therefore combined merchandise sales.
For this task you only have to look at last 5 months. You can simply calculate the distance and then divide the distance into categories such as less than 500m, between 1km-2km, 2km-3km, 3km-4km and more than 5km.
You can use your own skills to help understand the impact.
Example done in [login to view URL]: This is example for you to help. You can replicate the similar and add more.
Copy of [login to view URL]: This is geolocation data for own sites and competitor sites
Merchandise sales & transactions (Jan2019-Sep2019).xlsx: This is merchandise sales and transaction data to see growth in merchandise sales and transaction within the distance calculated.
Fuel transactions (Jan2019-Sep2019).xlsx: These are fuel transactions data to see if the fuel transactions have been impacted
The merchandise and fuel data is only for own sites. For competitor sites- you can find the competitor fuel price and geolocation in "COPY of DATA"
You are to give submission in powerpoint presentation with different charts and tables explaining what has been the impact on our stores (7eleven) that are located close to Coles Express compared to those that are not located near them and what is the size of this impact in terms of fuel transactions and therefore combined merchandise