Taking real-world data from a New York City coffee shop, this analysis of their transaction data for their locations in the Manhattan borough would provide critical business insights.

Microsoft Excel was their preferred method of analysis due to financial constraints. I took raw comma-separated values (csv) from their transactional data and created calculated columns for total profit per transaction, a date hierarchy (month, day of the week, and date) and converted granular timestamp data into hourly values.

Once the data was cleaned and the calculations were complete, this information was moved onto pivot tables to be utilized for in-app visualizations and charts. This data would help drive business decisions such as how long to stay open on certain days, which hours need the most employee coverage, and the top products at every location.

If you’re interested in taking a look at the Excel dashboard, you can download it here.