Overview
Bluff Kup Cafe & Thai Kitchen, an established business in Melbourne (Figure 1), has seen continuous revenue growth. However, inefficient inventory management leads to substantial inventory costs. The competitive local landscape demands retaining regular customers and attracting new patrons.
In response, I've applied business analytics to harness historical data from the point-of-sale (POS) system, with a specific focus on enhancing business performance. This project serves as a case study showcasing the practical application of business analytics in a real-world business setting, using Bluff Kup Cafe and Thai Kitchen as an example. The ultimate goal is to provide recommendations for improving the business, particularly in the realm of inventory management.
Inventory Issues
Inventory management is a pivotal factor driving revenue in the hospitality industry. Restaurants, in particular, need to maintain substantial inventory levels to offer a diverse menu to customers (Jung & Yoon 2012). However, many food ingredients are perishable and may expire before being fully utilized, leading to financial losses and food waste. Given the critical role of inventory management in a restaurant's income, this project focuses on addressing issues in this area.
Ineffective Inventory Review Policy
The restaurant currently employs a periodic inventory review policy, which is favored by employees as it doesn't demand frequent monitoring. However, this approach has led to instances of specific ingredients running out of stock, resulting in customer dissatisfaction. Furthermore, allowing employees to determine replenishment quantities based on assumptions has introduced subjectivity and bias into the inventory policy.
To address these challenges, the business might consider transitioning to an objective and quantitative inventory policy (Wisner et al., 2016).
Food Waste
Food waste is another substantial concern for the restaurant, largely stemming from an inability to accurately predict demand, resulting in excess inventory. In the food industry, maintaining hygiene and safety is paramount (Glanz et al., 2007). Consequently, significant ingredients occasionally go to waste because they perish before being efficiently used.
To mitigate these challenges, the business can explore the adoption of predictive models and statistical methods (Stefanovic 2014) to better cope with demand uncertainty.
Data Collection & Preparation
Data Obtaining
FoodStory is an application designed to streamline employee processes, encompassing order and inventory management. It also provides businesses with the ability to monitor historical data generated by staff during operations. All data generated within the system is stored in a relational database and can be accessed using Structured Query Language (SQL).
For the purposes of improving inventory management, the report focuses on historical sales data from January 2015 to December 2016. The initial data structure includes six variables: _payment_id_, _create_date_, _menu_group_name_, _menu_name_, _price_, and _quantity_.
Data Cleaning
The data cleaning process is vital for addressing anomalies in the initial data. Initially, it's crucial to ensure that there are no missing values in the data. Additionally, the manager has made changes to some menu_group_names and menu names over three years of operation. For example, menus like _209 Green Chicken_, _602 Green Chick Curry_, and _702 Green Chick Curry_ represent the same item. To enhance data consistency, these names are manually adjusted to _Green Chicken Curry_.
Additional Variables Adding
Although unusual and missing values are corrected in the data cleaning process, the data also needs to be enriched to make it more valuable and meaningful. In this case, the main ingredient of each menu is manually determined. For instance, _Chilli Fish_ and _Crispy Fish Salad_ require one unit, 200 grams, of _barramundi_ to produce. By adding this variable, the demands of the main ingredients are aggregated. Instead of analysing need for individual menus, the aggregation of need for a group of menus requiring the same main ingredient seems to be more beneficial to forecasting in terms of accuracy (Chatfield 2016).
Data Analysis by R Programming
Pareto Principle
To gain a deeper understanding of business changes and their impact, descriptive analytics is employed. Initially, an R function named RevCon is developed to calculate the percentage of total sales for each ingredient and the cumulative percentage of total sales. The function takes a directory as its primary input, where processed data is stored in CSV files. Below is the code for the function (Code 1).
RevCon <- function(directory) {
# Import and merge file.csv from directory
files_list <- list.files(directory, full.names=TRUE)
no_of_files <- length(files_list)
Data.df <- data.frame()
for(h in 1:no_of_files) {
Data.df <- rbind(Data.df, read.csv(files_list[h]))
}
# Change create_date to date format
Data.df[,2] <- as.Date(Data.df$create_date,
format='%d/%m/%Y')
# Aggregate sales by invntories
Contri.df <- aggregate(price ~ main_ingredient,
Data.df, sum)
# Calculate contribution in percentage
RevCon.df <- data.frame(Contri.df,
round(100*(Contri.df$price/
sum(Contri.df$price)),digits = 3))
colnames(RevCon.df)[1:3] <- c("Inventory","Total_Sales",
"Contribution")
# Sort contribution and calculate cumulative
RevCon.df <- RevCon.df[with(RevCon.df,
order(-Contribution)), ]
RevCon.df <- data.frame(RevCon.df, cumsum(RevCon.df[,3]))
colnames(RevCon.df)[4] <- "Cumulative"
# Print results
RevCon.df
}Demand Variability & Volatility
Moreover, a function named COV, written in R, is utilized to compute various statistical measures, including mean, standard deviation, and coefficient of variance (COV). This aids in uncovering underlying insights within the data. Identifying outliers is another crucial step because raw data can contain extreme values or errors. To address outliers, a five-period centered moving average is used. After this treatment, the data is processed to calculate the mean, standard deviation, and COV for each item. Here's the function code (Code 2).
COV <- function(directory) {
# Enable xts package
library(xts)
# Import and merge file.csv from directory
files_list <- list.files(directory, full.names=TRUE)
no_of_files <- length(files_list)
Data.df <- data.frame()
for(h in 1:no_of_files) {
Data.df <- rbind(Data.df, read.csv(files_list[h]))
}
# Change the type of object in main_ingredient and create_date
Data.df$main_ingredient <- as.character(Data.df$main_ingredient)
Data.df$create_date <- as.Date(Data.df$create_date,format='%d/%m/%Y')
# Create a list of distinct inventories
Inv_List <- unique(Data.df$main_ingredient)
Inv_List <- as.character(Inv_List)
# Create all dates to replace missing dates
All_dates = seq(as.Date(as.yearmon(min(Data.df$create_date))),
as.Date(as.yearmon(max(Data.df$create_date))), by="day")
# Create emtry an data frame
Output <- data.frame()
# Calculate weekly means, SDs and COVs of individual ingredients
for (i in 1:length(Inv_List)){
Menu_List <- subset(Data.df, main_ingredient == Inv_List[i])
Menu_List[,2] <- as.Date(Menu_List$create_date,
format='%d/%m/%Y')
Menu_List <- aggregate(quantity ~ create_date, Menu_List, sum)
#Add missing dates
Menu_List = merge(data.frame(date = All_dates), Menu_List,
by.x='date', by.y='create_date',
all.x=T, all.y=T)
colnames(Menu_List)[1:2] <- c("create_date","quantity")
Menu_List[is.na(Menu_List)] <- 0
# Use xts package to aggregate daily into weekly demand
Menu_List <- as.xts(Menu_List$quantity,order.by
=as.Date(Menu_List$create_date))
Menu_List <- apply.weekly(Menu_List,sum)
colnames(Menu_List)[1] <- "Weekly"
# Calculate IQR to identify any outlier
IQRs <- quantile(Menu_List$Weekly, prob = 0.75) -
quantile(Menu_List$Weekly, prob = 0.25)
Lower <- quantile(Menu_List$Weekly, prob = 0.25) -
1.5*IQRs
Upper <- quantile(Menu_List$Weekly, prob = 0.75) +
1.5*IQRs
# Treat the outlier by applying 5-day centred moving average
for (l in 1:length(Menu_List[,1])){
if (Menu_List[l,1] < Lower) {
Menu_List[l,1] <- round(mean(Menu_List[l-2:l+2,1],
digits = 2))
} else if (Menu_List[l,1] > Upper) {
Menu_List[l,1] <- round(mean(Menu_List[l-2:l+2,1],
digits = 2))
} else if (Menu_List[l,1] == 0) {
Menu_List[l,1] <- 0
} else {
Menu_List[l,1] <- Menu_List[l,1]
}
}
# Calculate weekly means, SDs and COVs of individual ingredients
Menu_List <- data.frame(Inv_List[i],
round(mean(Menu_List), digits = 0),
round(sd(Menu_List), digits = 0),
round(100*(sd(Menu_List)/mean(Menu_List)),
digits = 2))
# Add the results to data frame
Output <- rbind(Output, Menu_List)
}
# Rename columns
colnames(Output)[1:4] <- c("Inventory","Weekly Avg.","SD","COV")
# Sort ascending COV
Output <- Output[with(Output, order(COV)), ]
# Print the result
Output
}Recommendations
Inventory Segmentation
ABC analysis is a widely used inventory management approach that categorizes items based on their characteristics. Class-A items are those with a high impact on income or greater demand volatility, while Class-B and Class-C items have lower impact and a stable market.
As per the analysis, the main items are classified as shown in Table 3. In practical inventory management, Class-A items require daily or twice-weekly monitoring for availability. Class-B inventories can be reviewed at the end of each week, and a monthly inventory review suffices for Class-C items. This approach reduces the time and attention dedicated to monitoring all items, ultimately improving efficiency (Muckstadt & Sapra 2010).
Class A: Chicken, Prawn, Beef, Pork belly, Duck, Beer, White wine, Jasmine rice, Corkage, Barramundi, Mix of entrée, Roti, Calamari, Red wine, Salmon, Scallop
Class B: Lamb, Cabbage, Soft shell crab, Coconut rice, Soft Drink, Potato, Coffee, Vegetable
Class C: Brown rice, Thai dessert, Ice cream, Sparkling wine, Papaya, Pork, Quail, Tofu, Liqueur, Sparkling water, Satay sauce, Tea, Golden bag, Rose wine, Champagne, Chocolate, Pumpkin cake, Crispy ruby, Sticky rice, Cake
Inventory Review Policy
Recognizing that a one-size-fits-all inventory management policy may not effectively handle items with varying economic values and volatility (Bartmann & Bach 2012), the business can adopt a hybrid approach. For Class-A inventories, a continuous inventory management policy is implemented. Staff must ensure that the inventory level remains above the reorder point every day.
In contrast, Class-B and Class-C items are managed using a periodic review policy. The timing of inventory review is either weekly or monthly, depending on their classification. The statistical reorder point can be calculated using the following equation (Wisner et al. 2016). This hybrid approach optimizes inventory management by tailoring it to the specific characteristics of each item.
ROP = Mean + (Z value + Standard Deviation)
For example, the statistical reorder point for chicken is 187 units, calculated based on the average demand of 142 units and a standard deviation of 27 units per week during the lead time (as detailed in Table 2). With a cycle service level of 95 percent and a Z-value of 1.64, the manager should place an order when the remaining inventory dips below 28 kg (where 1 unit equals 0.15 kg). This ensures that 95 percent of the weekly chicken demand can be met while the inventory awaits replenishment in the following week.Discussion and Conclusion
The manager should be aware of several considerations. Firstly, regular data updates are vital for better results (Shumway & Stoffer 2010). Secondly, inventory capacity in storage areas is a practical constraint (Dye et al., 2007). Consequently, calculated reorder points and predictive models may not always align with real-world limitations.
In addition, qualitative analysis and domain expertise are equally essential for addressing inventory challenges. Management should view quantitative methods as decision support tools rather than sole reliance (Runkler 2012). Successful inventory management often requires a balanced integration of both quantitative and qualitative insights.
In conclusion, this paper leverages business analytics, utilizing historical data from Bluff Kup Cafe and Thai Kitchen to offer recommendations for enhancing inventory management. The study advocates the adoption of statistical inventory review policies to foster a data-driven culture within the organization. This approach encourages employees to apply objective methods in inventory management. Furthermore, the use of association rules provides management with valuable insights into customer behavior and stock performance.
Ultimately, the analysis highlights that customer demand is not solely time-dependent, as other variables can also influence consumers' meal choices. This nuanced understanding can lead to more effective inventory management strategies.
References
Bartmann, D & Bach, M F 2012, *Inventory control: models and methods*, Springer Science & Business Media, Germany.
Chatfield, C 2016, âSome More Advanced Topicsâ, *The analysis of time series: an introduction*, CRC press, USA, pp.255Â276.
Dye, C Y, Ouyang, L Y & Hsieh, T P 2007, âDeterministic inventory model for deteriorating items with capacity constraint and timeÂproportional backlogging rateâ, *European Journal of Operational Research*, vol. 178, no. 3, pp.789Â807.
Glanz, K, Resnicow, K, Seymour, J, Hoy, K, Stewart, H, Lyons, M & Goldberg, J 2007, âHow major restaurant chains plan their menus: the role of profit, demand, and healthâ, *American journal of preventive medicine*, vol. 32, no. 5, pp.383Â388.
Jung, H S & Yoon, H H 2012, âWhy do satisfied customers switch? Focus on the restaurant patron varietyÂseeking orientation and purchase decision involvementâ, *International Journal of Hospitality Management*, vol. 31, no. 3, pp.875Â884.
Muckstadt, J A & Sapra, A 2010, âInventories Are Everywhereâ, *Principles of inventory management: When you are down to four, order more*, New York: Springer, USA.
Runkler, T A 2012, *Data Analytics Models and Algorithms for Intelligent Data Analysis*, Vieweg+Teubner Verlag, Germany.
Shumway, R H & Stoffer, D S 2010, âCharacteristics of Time Seriesâ, *Time series analysis and its applications: with R examples*, Springer Science & Business Media, USA.
Stefanovic, N 2014, âProactive supply chain performance management with predictive analyticsâ, *The Scientific World Journal*, vol. 2014.
Wisner, J D, Tan, K C & Leong, G K 2016, âInventory Managementâ, *Principles of Supply Chain Management: A Balanced Approach*, 4th Edition, Cengage Learning, USA, pp.207Â246.
