Menu Analysis (National Corn Chip Day!)

Project Overview:

• The primary objective of this project was to display new Microsoft Excel skills developed from coursework recently completed in my Maven Analytics learning path.
• I analyzed restaurant menu transactions that occurred from January 1, 2023 to March 31, 2023.

Project Task:

1. VLOOKUP & Index/Match for data collection from multiple sheets.
2. PivotTables and Date/Time functions to aggregate hourly, daily, and weekly, and quarterly sales.
3. Countifs, Sumifs, Averageifs, Unique, and CountA to aggregate sales and quantity of food ordered.
4. Created interactive weekly and quarterly sales reports that included data validation lists along with PivotCharts and graphs.

KEY FINDINGS:

• Highest amount of food ordered occurred on January 29th, 2023 with 1003 items sold.
• Possible correlation: National Corn Chip Day and NFL AFC/NFC championship games
  • Mexican food orders increased by 23% from previous week.
    • Chips and guacamole orders increased by 154% from previous week.
      • Context: Chips and guacamole orders were declining by an average of 16% weekly prior to the week of January 29th.
    • Chips and salsa orders increased by 41% from previous week.
      • Context: Chips and salsa orders were declining by an average of 8% weekly prior to the week of January 29th.
  • American food orders increased by 17% from previous week.
    • Cheeseburgers orders increased by 58% from previous week.
      • Context: Cheeseburger orders averaged 3% growth weekly prior to the week of January 29th.
    • Hot dogs orders increased by 50% from previous week.
      • Context: Hot dog orders were declining by an average of 13% weekly prior to the week of January 29th.

Additional Findings for Q1:

• Busiest times for food orders were the 12pm, 1pm, 5pm, and 6pm time blocks.

  • Those 4 hours accounted for 50% of all transactions for Q1.
    • Restaurant time range of orders occurred from 10:50am to 11:00pm throughout Q1.

• American

  • Burgers and Fries accounted for 65% of total American food transactions.
    • Fries account for 21% of total transactions, but generates $3,997 in sales compared to hamburgers and cheeseburgers which account for 21% and 23%, but generate about $8,000 in sales each.

• Asian

  • Edamame accounted for 18% of total Asian food transactions, but only generated $3,100 in sales.
    • Context: Salmon roll accounted for 9%, but generated $4,844 in sales.

• Italian

  • Generated the most money $49,463 due to the highest average pricing per menu item.
    • American = $10/item
    • Asian = $13.50/item
    • Mexican = $11.80/item
    • Italian = $16.75/item

• Mexican

  • Chicken Tacos was the least popular item on entire that accounted for 4% of total Mexican menu transactions
    • Generated $1,470