BIforRest
library(readxl)
library(ggplot2)
data <- read_excel("/Users/aniketsingh/Downloads/biforTIPS.xlsx", sheet = "Sheet1")mydata <- datadata## # A tibble: 36 × 10
## Year Month Total_Revenue Total_Food_Cost Total_Labor_Cost Rent
## <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 2020 1 31200 9360 9984 1200
## 2 2020 2 29250 9068. 9652. 1200
## 3 2020 3 30750 10455 10148. 1200
## 4 2020 4 34320 9266. 9266. 1200
## 5 2020 5 33000 11220 11220 1200
## 6 2020 6 26220 8128. 9177 1200
## 7 2020 7 31920 10214. 8938. 1200
## 8 2020 8 29250 9068. 9945 1200
## 9 2020 9 27750 7492. 8880 1200
## 10 2020 10 31080 9324 9324 1200
## # … with 26 more rows, and 4 more variables: POS_System_Cost <dbl>,
## # Maintainence_Cost <dbl>, Avg_Ticket_Size <dbl>, Gross_Profit <dbl># Create a new variable to store the total revenue for each year and month
mydata$Year_Month <- paste(mydata$Year, mydata$Month, sep = "-")
mydata$Year_Month <- as.Date(paste(mydata$Year_Month, "01", sep = "-"), "%Y-%m-%d")
total_revenue <- aggregate(Total_Revenue ~ Year_Month, data = mydata, sum)
# Create a barplot of the total revenue by year and month
ggplot(total_revenue, aes(x = Year_Month, y = Total_Revenue, fill = factor(format(Year_Month, "%Y")))) +
geom_bar(stat = "identity", color = "black", size = 0.2, alpha = 0.8) +
scale_fill_brewer(palette = "Set1") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
labs(title = "Total Revenue by Year and Month",
x = "Year and Month", y = "Total Revenue",
fill = "Year")## Warning: Using `size` aesthetic for lines was deprecated in ggplot2 3.4.0.
## ℹ Please use `linewidth` instead.
## This warning is displayed once every 8 hours.
## Call `lifecycle::last_lifecycle_warnings()` to see where this warning was
## generated.
library(ggplot2)
library(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(tidyr)
# Create a new variable to store the total revenue for each year and month
mydata$Year_Month <- paste(mydata$Year, mydata$Month, sep = "-")
mydata$Year_Month <- as.Date(paste(mydata$Year_Month, "01", sep = "-"), "%Y-%m-%d")
# Aggregate the data by month and year
total_revenue <- mydata %>%
group_by(Month, Year) %>%
summarise(Total_Revenue = sum(Total_Revenue), .groups = "drop")
# Create a data frame with the revenue data in a long format
revenue_long <- total_revenue %>%
mutate(Year_Month = as.character(paste(Year, Month, sep = "-")),
Month = factor(Month, levels = 1:12, labels = month.abb)) %>%
pivot_wider(names_from = "Year", values_from = "Total_Revenue") %>%
gather(key = "Year", value = "Revenue", `2020`:`2022`)
# Create a barplot of the total revenue by year and month
ggplot(revenue_long, aes(x = Month, y = Revenue, fill = Year)) +
geom_bar(stat = "identity", position = "dodge", color = "black", size = 0.2) +
labs(title = "Total Revenue by Year and Month",
x = "Month", y = "Total Revenue", fill = "Year") +
scale_fill_brewer(palette = "Set1") +
theme_minimal() +
theme(axis.title = element_text(size = 14, face = "bold"),
axis.text = element_text(size = 12),
legend.title = element_text(size = 14, face = "bold"),
legend.text = element_text(size = 12)) +
guides(fill = guide_legend(reverse = TRUE))## Warning: Removed 72 rows containing missing values (`geom_bar()`).
df <- mydata
library(dplyr)
# Calculate yearly summary statistics
yearly_summary <- df %>%
group_by(Year) %>%
summarize(
Total_Revenue = sum(Total_Revenue),
Avg_Monthly_Revenue = mean(Total_Revenue),
Total_Food_Cost = sum(Total_Food_Cost),
Avg_Monthly_Food_Cost = mean(Total_Food_Cost),
Total_Labor_Cost = sum(Total_Labor_Cost),
Avg_Monthly_Labor_Cost = mean(Total_Labor_Cost),
Total_Rent = sum(Rent),
Avg_Monthly_Rent = mean(Rent),
Total_POS_System_Cost = sum(POS_System_Cost),
Avg_Monthly_POS_System_Cost = mean(POS_System_Cost)
)
# Calculate overall summary statistics
overall_summary <- df %>%
summarize(
Avg_Monthly_Revenue = mean(Total_Revenue),
Avg_Monthly_Food_Cost = mean(Total_Food_Cost),
Avg_Monthly_Labor_Cost = mean(Total_Labor_Cost),
Avg_Monthly_Rent = mean(Rent),
Avg_Monthly_POS_System_Cost = mean(POS_System_Cost)
)
# Print results
print(yearly_summary)## # A tibble: 3 × 11
## Year Total_Revenue Avg_Monthly_Revenue Total_Food_Cost Avg_Monthly_Food_Cost
## <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 2020 372780 372780 112911. 112911.
## 2 2021 376590 376590 113260. 113260.
## 3 2022 450480 450480 132278. 132278.
## # … with 6 more variables: Total_Labor_Cost <dbl>,
## # Avg_Monthly_Labor_Cost <dbl>, Total_Rent <dbl>, Avg_Monthly_Rent <dbl>,
## # Total_POS_System_Cost <dbl>, Avg_Monthly_POS_System_Cost <dbl>print(overall_summary)## # A tibble: 1 × 5
## Avg_Monthly_Revenue Avg_Monthly_Food_Cost Avg_Monthly_Labor_… Avg_Monthly_Rent
## <dbl> <dbl> <dbl> <dbl>
## 1 33329. 9957. 10272. 1200
## # … with 1 more variable: Avg_Monthly_POS_System_Cost <dbl>library(ggplot2)
ggplot(df, aes(x = interaction(Year, Month, lex.order = TRUE), y = Total_Revenue, group = 1)) +
geom_line(color = "blue") +
geom_point(color = "red") +
labs(title = "Monthly Revenue Trend",
x = "Year-Month",
y = "Revenue") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
library(ggplot2)
monthly_average_revenue <- df %>%
group_by(Month) %>%
summarize(Average_Revenue = mean(Total_Revenue))
ggplot(monthly_average_revenue, aes(x = as.factor(Month), y = Average_Revenue)) +
geom_bar(stat = "identity", fill = "steelblue") +
geom_text(aes(label = round(Average_Revenue, 1)), vjust = -0.3, size = 3) +
labs(title = "Average Monthly Revenue",
x = "Month",
y = "Average Revenue") +
theme_minimal()
library(ggplot2)
monthly_avg_ticket_size <- df %>%
group_by(Year, Month) %>%
summarize(Average_Ticket_Size = mean(Avg_Ticket_Size))## `summarise()` has grouped output by 'Year'. You can override using the
## `.groups` argument.ggplot(monthly_avg_ticket_size, aes(x = as.factor(Month), y = as.factor(Year), fill = Average_Ticket_Size)) +
geom_tile() +
geom_text(aes(label = round(Average_Ticket_Size, 1)), size = 3) +
scale_fill_gradient(low = "lightblue", high = "darkblue") +
labs(title = "Average Ticket Size per Month",
x = "Month",
y = "Year",
fill = "Avg Ticket Size") +
theme_minimal()
library(dplyr)
library(tidyr)
monthly_costs <- df %>%
group_by(Year, Month) %>%
summarize(
Total_Food_Cost = sum(Total_Food_Cost),
Total_Labor_Cost = sum(Total_Labor_Cost),
Rent = sum(Rent),
POS_System_Cost = sum(POS_System_Cost),
Maintainence_Cost = sum(Maintainence_Cost)
) %>%
gather(key = "Cost_Category", value = "Amount", -Year, -Month) %>%
mutate(Year_Month = paste(Year, as.factor(Month), sep = "-"))## `summarise()` has grouped output by 'Year'. You can override using the
## `.groups` argument.# Create stacked bar chart
ggplot(monthly_costs, aes(x = Year_Month, y = Amount, fill = Cost_Category)) +
geom_bar(stat = "identity") +
labs(title = "Monthly Cost Distribution",
x = "Year-Month",
y = "Total Cost",
fill = "Cost Category") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 90, vjust = 0.5, hjust = 1)) +
scale_fill_manual(values = c("Total_Food_Cost" = "darkorange", "Total_Labor_Cost" = "forestgreen", "Rent" = "purple", "POS_System_Cost" = "darkred", "Maintainence_Cost" = "steelblue"))
library(dplyr)
library(ggplot2)
monthly_gross_profit <- df %>%
group_by(Year, Month) %>%
summarize(Gross_Profit = sum(Gross_Profit)) %>%
mutate(Year_Month = paste(Year, as.factor(Month), sep = "-"))## `summarise()` has grouped output by 'Year'. You can override using the
## `.groups` argument.ggplot(monthly_gross_profit, aes(x = Year_Month, y = Gross_Profit, group = 1)) +
geom_line(color = "darkblue", size = 1) +
geom_point(color = "darkred", size = 2) +
labs(title = "Gross Profit Over Time",
x = "Year-Month",
y = "Gross Profit") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 90, vjust = 0.5, hjust = 1))
geom_smooth(method = "lm", se = FALSE, color = "darkblue", linetype = "dashed", size = 1, quiet = TRUE)## Warning in geom_smooth(method = "lm", se = FALSE, color = "darkblue", linetype =
## "dashed", : Ignoring unknown parameters: `quiet`## geom_smooth: na.rm = FALSE, orientation = NA, se = FALSE
## stat_smooth: na.rm = FALSE, orientation = NA, se = FALSE, method = lm
## position_identitylibrary(ggplot2)
ggplot(df, aes(x = Total_Revenue, y = Gross_Profit)) +
geom_point(color = "darkgreen", size = 2) +
geom_smooth(method = "lm", se = FALSE, color = "darkblue", linetype = "dashed", size = 1, quiet = TRUE) +
labs(title = "Total Revenue vs. Gross Profit",
x = "Total Revenue",
y = "Gross Profit") +
theme_minimal()## Warning in geom_smooth(method = "lm", se = FALSE, color = "darkblue", linetype =
## "dashed", : Ignoring unknown parameters: `quiet`## `geom_smooth()` using formula = 'y ~ x'
ggplot(df, aes(x = interaction(Year, Month, lex.order = TRUE), y = Avg_Ticket_Size, fill = factor(Year))) +
geom_bar(stat = "identity") +
labs(title = "Average Ticket Size Over Time",
x = "Month/Year",
y = "Average Ticket Size") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplot(df, aes(x = interaction(Year, Month, lex.order = TRUE), y = Gross_Profit, group = 1)) +
geom_point(color = "darkgreen") +
geom_line(color = "darkgreen") +
labs(title = "Monthly Gross Profit Over Time",
x = "Month/Year",
y = "Gross Profit") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplot(df, aes(x = factor(Month), y = Total_Food_Cost)) +
geom_boxplot(fill = "skyblue") +
labs(title = "Monthly Food Costs Distribution",
x = "Month",
y = "Food Cost") +
theme_minimal()
ggplot(df, aes(x = factor(Month), y = Total_Labor_Cost)) +
geom_boxplot(fill = "lightgreen") +
labs(title = "Monthly Labor Costs Distribution",
x = "Month",
y = "Labor Cost") +
theme_minimal()
ggplot(df, aes(x = factor(Month), y = POS_System_Cost)) +
geom_boxplot(fill = "orange") +
labs(title = "Monthly POS Costs Distribution",
x = "Month",
y = "POS System Cost") +
theme_minimal()
df %>%
pivot_longer(cols = c(Total_Food_Cost, Total_Labor_Cost, Rent, POS_System_Cost, Maintainence_Cost),
names_to = "Cost Type", values_to = "Cost") %>%
ggplot(aes(x = Month, y = Cost, fill = `Cost Type`)) +
geom_col(position = "stack") +
labs(title = "Monthly Total Costs", x = "Month", y = "Cost") +
scale_fill_brewer(palette = "Set3")
library(GGally)## Registered S3 method overwritten by 'GGally':
## method from
## +.gg ggplot2ggcorr(df %>% select_if(is.numeric),
label = TRUE, label_size = 2, label_round = 2,
hjust = 0.8, size = 2)## Warning in cor(data, use = method[1], method = method[2]): the standard
## deviation is zero
library(dplyr)
library(ggplot2)
library(reshape2)##
## Attaching package: 'reshape2'## The following object is masked from 'package:tidyr':
##
## smiths# Calculate total cost for each year
df_summary <- df %>%
mutate(Total_Cost = Total_Food_Cost + Total_Labor_Cost + Rent + POS_System_Cost + Maintainence_Cost) %>%
group_by(Year) %>%
summarise(Total_Revenue = sum(Total_Revenue),
Total_Cost = sum(Total_Cost))
# Melt data to long format for stacked bar chart
df_melt <- melt(df_summary, id.vars = "Year", measure.vars = c("Total_Revenue", "Total_Cost"))
# Create stacked bar chart
ggplot(df_melt, aes(x = Year, y = value, fill = variable)) +
geom_bar(stat = "identity", position = "stack") +
scale_fill_manual(values = c("Total_Revenue" = "green", "Total_Cost" = "red")) +
labs(title = "Total Revenue and Cost by Year", x = "", y = "") +
theme_minimal()
library(ggplot2)
df %>%
ggplot(aes(x = Total_Revenue, y = Avg_Ticket_Size, size = Gross_Profit, color = Gross_Profit)) +
geom_point(alpha = 0.7) +
scale_color_gradient(low = "#F8766D", high = "#00B0F6") +
ggtitle("Revenue and Average Ticket Size") +
labs(size = "Gross Profit") +
theme_bw()
ggplot(df, aes(x = Avg_Ticket_Size, y = Gross_Profit)) +
geom_point() +
geom_smooth(method = "lm", se = TRUE) +
xlab("Average Ticket Size") +
ylab("Gross Profit") +
ggtitle("Relationship between Average Ticket Size and Gross Profit")## `geom_smooth()` using formula = 'y ~ x'
# Calculate the average gross profit, revenue, and costs
mean_gross_profit <- mean(df$Gross_Profit)
mean_total_revenue <- mean(df$Total_Revenue)
mean_total_costs <- mean(df$Total_Food_Cost + df$Total_Labor_Cost + df$Rent + df$POS_System_Cost + df$Maintainence_Cost)
# Print the results
cat("Mean Gross Profit:", mean_gross_profit, "\n")## Mean Gross Profit: 9433.717cat("Mean Total Revenue:", mean_total_revenue, "\n")## Mean Total Revenue: 33329.17cat("Mean Total Costs:", mean_total_costs, "\n")## Mean Total Costs: 23895.45Reducing food costs: Minimize food waste by implementing inventory management practices, such as First In First Out (FIFO) and tracking expiration dates. Renegotiate contracts with suppliers to get better rates or consider changing suppliers if a better price is available. Regularly review menu items and remove low-margin or low-selling dishes to focus on more profitable offerings. Implement portion control to reduce over-portioning, which can lead to waste and increased costs. Reducing labor costs: Improve staff scheduling by analyzing the busiest and slowest times to ensure adequate staffing without overstaffing. Cross-train employees so they can perform multiple tasks, which can help during peak times or when someone is absent. Implement time management and productivity improvement strategies to increase the efficiency of your workforce. Consider offering performance-based incentives to motivate employees to work efficiently and reduce errors. Reducing POS system costs: Shop around and compare different POS system providers to find the most cost-effective option with the features you need. Negotiate with your current POS provider for a better rate or a reduction in fees. If feasible, invest in a one-time purchase POS system rather than a subscription-based system to reduce recurring costs.
# Apply the cost reduction percentages
optimized_df <- df
optimized_df$Total_Food_Cost <- df$Total_Food_Cost * 0.85
optimized_df$Total_Labor_Cost <- df$Total_Labor_Cost * 0.85
optimized_df$POS_System_Cost <- df$POS_System_Cost * 0.50
# Recalculate the gross profit
optimized_df$Gross_Profit <- optimized_df$Total_Revenue - (optimized_df$Total_Food_Cost + optimized_df$Total_Labor_Cost + optimized_df$Rent + optimized_df$POS_System_Cost + optimized_df$Maintainence_Cost)
# Calculate the mean gross profit after optimization
mean_optimized_gross_profit <- mean(optimized_df$Gross_Profit)
# Print the result
cat("Mean Optimized Gross Profit:", mean_optimized_gross_profit, "\n")## Mean Optimized Gross Profit: 13301.29