Who is the French Bakery?

This is a case study analysis on data that belongs to a French bakery. The French Bakery provided the daily transaction details of customers from 2021-01-01 to 2022-09-30 and wants help forecasting their sales in order to ease their production planning. The French Bakery data set was created by Matthieu Gimbert, and yes this data set is from a credible online organization named Kaggle and has over 9.7/10% Usability.

[French Bakery Original Dataset] (https://www.kaggle.com/datasets/matthieugimbert/french-bakery-daily-sale)

The French Bakery bakes thousands upon thousands of pastries everyday and they are all beloved by their community. After a few months the bakery wanted to forecast their sales and get a more detailed view of what their supply and demand needs are. In this Notebook I have devised an exploratory data analysis of the “French Bakery Sales” that were recorded from January 1st, 2021 all the way through September, 30th, 2022. I am working to find an algorithm to find relationships between items bought, the days they are sold and the quantity being sold. Most people know this process as (ARM) or Association Rule Mining. Once I found the relationships between these items then I was able to forecast the sales for the upcoming year so that the French Bakery could make production adjustments.

How was the data organized?

For this project I collected my data from a second party who received information from an organization that’s led studies in that bakery. I broke everything into compartments starting with years to months, then quantity by transaction, and finally I separated the highest and lowest selling products. I then utilized the Excel spreadsheets to clean and process the data. Afterwards, I used Tableau to showcase all of the collected data in breathtaking visualizations. Lastly, The R Markdown Notebook allowed me to bind all of my findings together. During this process I had to ensure that my data had integrity and maintained it during my analysis. I triple checked my data for missing values, uniformity, unbiased sampling and negative values.
However, there seemed to be a tremendous amount of profit and pastry loss due to the amount of negative values returned.

A Steady Month
A Steady Month

Challenges

In this project I used a lot of structured thinking, which helped me add a framework to the production planning problem that I used in an organized and logical manner. You can think of structured data as a framework that makes everything easily searchable and more analysis-ready. Furthermore, I did discover a few trends in this data before and after analyzing.

  • Price Differences
    • There are high selling products at extremely low prices like, (Coupe, Traditional Baguette, and Banette).
    • Yet, there are potential high sellers being overpriced like, (Gal Frangipane, Plat 7.60E(which I believe is a pastry with gold flakes), and Triangles).
    • The sum price differences in these comparisons range anywhere between four hundred and eighty seven thousand dollars for Traditional Baguettes to eleven thousand dollars for the Plat 7.60E. Not every pastry is going to be a home run but promoting even the smallest items can be a powerful tool for any business.
  • Profit and Pastry Loss
    • We saw Profit loss for reasons unknown(but possible explanations include; promotion, item mis-rings, and even refunds).
    • We saw pastry and profit loss due to Inconsistent Unit Prices for the same pastry items. I averaged out the prices for those pastries and calculated sales and did my best to show what I believed the possible profits could be.

Data Relationships

There were a tremendous amount of relationships in this data but I have highlighted only a few to get a transparent view of the French Bakery.

  • Rush Hour
    • The main rush hours for the store occurred between 8am to 1pm and once more during the hours of 5pm to 7pm most weekends.
      • During most weekends we saw over 47,116 pasties sold usually around 11am. Moreover, on the weekends at 5pm they collectively sold over 11,410 pastries. Saturdays, Sundays, Mondays, and Thursdays saw the most traffic throughout the entire month for both years.
FrenchBakeryDashboard
FrenchBakeryDashboard
  • Profit Trends
    • Months with holidays included in them, seem to have a rise in the overall monthly profit but, not on the celebratory days themselves.
    • Most individuals who buy certain items seem to pair them with others. For instance, when it comes to types of paired items customers usually purchase a Traditional Baguette with a purchase of Pain Au Chocolat.)
    • Using Tableau I was able to determine the current trends of the most pastries sold vs the least and because of that data I was able to chart and plot better possible outcomes.
    • After completing multiple types of calculations, using pivot tables to closely investigate each compartment I saw a few revenue trends. Some revenue trends I saw were; The Calculated Average Daily Profit $2,398.05, The Calculated Sum of Pastries Sold 233,509, The Calculated Sum of Unit Price 388,961.11, and The Calculated number of loss items 2140. Consistency is prevalent for most parts of this data but due to null, misplaced, and incorrect values they were far and in between.

Conclusions

French Baker
French Baker

The French Bakery runs and handles peak hours, months and years relatively well but, understanding the supply and demand needs of the bakery is important.
It is quite challenging predicting production planning completely without knowing how much it costs to make each of the articles that the “French Bakery” sells. I paid extra attention to detail when forecasting sales figures however, detailed production planning will require more background information. Some needed changes include; Production costs for each pastry, adjusting each unit price to be uniform throughout each quarter and, adding in explanations or a subsection of missing and lost values.
For the Lost profit that equated to over two thousand dollars, they could have been used and labeled as discounted items or promotions, caterings and even employee food. Leaving items in their ledger that only equal negative revenue suggest not only revenue loss but all food waste as well.
Therefore, without adding monetary input for future production planning, I can say that adding a separate business account would be the most productive plan that adds and subtracts items and money for a variety of reasons.
To Take It Further
* We did notice a few top selling items that would do extraordinarily well when coupled with a lower priced item. Introducing discounted “bogo” prices and promotions for the top selling items would increase the overall revenue, the bakeries foot traffic during rush hours and, the number of items sold for each ticket.
* Inviting people to browse the least selling pastries will also be a new gateway into higher unit prices once all of them become uniform.
All of these new insights and possible price adjustments will allow the French Bakery to not only flourish among the bread baking community but also stay on the “Top 10 Must Visit Bakery List”. Their Traditional baguettes, Croissants and Pain Au Chocolat have stolen the show and have people coming back for more everyday. The French Bakery offers delicious food at great prices and in turn have earned over one million dollars in bakery sale profits. I’m excited to see how they continue to stand out among the crowd and implement new production and sales techniques.

References

Original dashboard and year by month made by Victoria De La Cruz. French Baker Image by Marinda (thecustombakeryboxes.com). Original Dataset by Mathieu G. on Kaggle (https://www.kaggle.com/datasets/matthieugimbert/french-bakery-daily-sale).

                                  Published by Victoria De La Cruz