Ronalds Recipes

When my cousin and I started Sauce it Up, it was in my home kitchen with 10-15 bottles at time. We were meticulous about measurements and were confident that when it was time to scale up in an industrial kitchen, it would be a walk in the park. We had a googlesheet with everything we needed and off we went. The first run at it was a disaster; the product tasted the same, but we just overproduced on some recipes and underproduced on others leaving us unprepared. The next day I went to create a small R script that would help us understand where we went wrong and how we could fix it. Fast forward 2 years, that script has evolved into a package that outputs a flexdashboard that we use pre and post batch to assess things like our productivity, break even and ingredient pricing. In the following series of posts, I will walk you through how I used R and its connectivity to googlesheets to ultimately create a package that helped us answer many of our small business questions. I will do so from the lens of Ronald Rutabaga who is contemplating a part-time career in catering. The questions are universal to any business whose input prices are constantly in flux.

Problem Statement

Ronald Rutabaga is thinking about starting a catering business as a side hustle and he recently got offered a gig to make 50 plates of each of his known dishes. The three dishes he is offering are:

  1. Butter Chicken and Naan
  2. Jerk Chicken with Rice and Peas
  3. Chicken Souvlaki and Potatoes

Ronald is a data freak and collects data on the input prices over time and the exact measurements of all his recipes, as well as other data points in a googlesheet. The problem is that all of the data are in different tabs and he does not know how to summarize the data into something that he can act on. Before Ronald takes on this job, he needs to answer the following questions.

  1. What is the quantity of each ingredient I need to order from my vendors? (Shopping List)
  2. How much does it cost to cater this event?
  3. How many plates of each dish do I need to sell to break even on the raw materials?
  4. What do I need to charge in order to guarantee a minimum profit margin of 50%?

Ronald has given us access to his Google sheets below.

https://docs.google.com/spreadsheets/d/1WLVX0BMv9r7Y1VJjXOc9lgiW2NYBLr1HOebYzTfqz00/edit#gid=0

Data

To answer Ronald’s questions we want to inspect the following tabs in his googlesheet TODO: Add definitions of each column type

  • recipes_4_serving: This tab breaks down each recipe and how much of each ingredient you require to make 4 servings
  • historical_prices: This tab stores the cost per unit over time of each ingredient Ronald needs for all of his recipes

First we want to load all the libraries

# To connect to our googlesheets
library(googlesheets4)
# To slice and dice our data
library(dplyr)
# Used to make our tables pretty 
library(kableExtra)
# Round all digits to 2
options(digits=2)

Next read in all of our data from google sheets

  • Depending on whether your googsheet is public or not will determine how you connect to it.
  • Our sheet is public, therefore we don’t need to be authorized before accessing it via the googlesheets4 package
  • If you are working with a private googlesheet you will need to authorize yourself. See link and link for more on authenticating with googlesheets
# Put googlesheets4 into a deathorize mode as no need to indicate token
sheets_deauth()
# Store the spread sheet url as a variable
recipe_ss <- "https://docs.google.com/spreadsheets/d/1WLVX0BMv9r7Y1VJjXOc9lgiW2NYBLr1HOebYzTfqz00/edit#gid=0"

Now let us read in the data from the respective tabs and see what they look like. We will also do a little cleaning to set the date column as a date type so that we can use it when filtering by date.

# Read in all of the data on each sheet into dataframes
# The ingredients measured in this data frame represents 4 servings
recipes <- sheets_read(recipe_ss, sheet = "recipes_4_servings")

# Display the data 
kable(recipes) %>% 
  kable_styling()
recipeitemunitsunit_type
butter_chickenchicken_breast0.90kg
butter_chickenhot_indian_sauceitup1.50serving
butter_chickenonion0.40kg
butter_chickentomatoe_paste0.18kg
butter_chickenwhipping_cream0.10L
butter_chickennaan_reyat1.00unit
jerk_chickenchicken_thigh_leg0.90kg
jerk_chickensaucy_jerk_sauceitup1.50servings
jerk_chickenrice0.20kg
jerk_chickencoconut_milk0.40L
jerk_chickenkidney_beans0.10kg
chicken_souvlakichicken_breast0.90kg
chicken_souvlakifreaky_greek_sauceitup1.50servings
chicken_souvlakipotatoes0.40kg
chicken_souvlakioil0.02L
chicken_souvlakigarlic_powder0.05kg
chicken_souvlakiblack_pepper0.05kg
# The historical price of the ingredients in a dataframe
# Using just the head function to see what the data frame looks like
historical_price <- sheets_read(recipe_ss, sheet = "historical_prices")

# Convert into a useable date - Can come back to this later
historical_price$date <- as.Date(historical_price$date)
kable(head(historical_price)) %>% 
  kable_styling()
dateitemcost_per_unitunit_typeselling_pxno_units
2019-01-01chicken_breast6.7kg10.01.50
2019-01-01hot_indian_sauceitup0.7serving7.010.00
2019-01-01onion2.2kg2.00.90
2019-01-01tomatoe_paste3.5L1.30.37
2019-01-01whipping_cream8.0L2.00.25
2019-01-01naan_reyat0.5unit5.010.00

Question 1: What is the quantity of each ingredient I need to order from my vendors (Shopping List)?

To answer question one we will need to

  1. Define how many plates of each recipe we will be making
  2. Make a data frame from the variables you created
  3. Create a function to create a shopping list
  4. Display the shopping list
# Define number of plates of each recipe that you want to calculate off of
butter_chkn_plate <- 50
jerk_chkn_plate <- 50
chkn_souvlaki_plate <- 50

# Create dataframe from the above for easy wrangling within our function below 
all_plates <- as.data.frame(cbind(rbind(butter_chkn_plate,jerk_chkn_plate, chkn_souvlaki_plate), c("butter_chicken", "jerk_chicken", "chicken_souvlaki")), stringsAsFactors = FALSE)
colnames(all_plates) <- c("no_dishes", "recipe")
all_plates$no_dishes <- as.double(all_plates$no_dishes)

# We can now use the above numbers to calculate the total amount of each ingredient we require
# Create a function to do this taking in the all_plates_df and recipe_df
generate_shopping_list <- function(all_plates_df, recipe_df){
  recipe_df %>% 
    # join the all_plates with the recipe
    left_join(all_plates_df, by = c("recipe")) %>% 
    mutate(total_units = units * no_dishes)  %>% 
    group_by(item, unit_type) %>%
    summarise(total_units = sum(total_units))
}

generate_shopping_list(all_plates, recipes) %>% 
  kable() %>% 
  kable_styling()
itemunit_typetotal_units
black_pepperkg2.5
chicken_breastkg90.0
chicken_thigh_legkg45.0
coconut_milkL20.0
freaky_greek_sauceitupservings75.0
garlic_powderkg2.5
hot_indian_sauceitupserving75.0
kidney_beanskg5.0
naan_reyatunit50.0
oilL1.0
onionkg20.0
potatoeskg20.0
ricekg10.0
saucy_jerk_sauceitupservings75.0
tomatoe_pastekg9.2
whipping_creamL5.0

With a shopping list in hand Ronald calls his suppliers to see what it will cost him to buy the quantities of ingredients he needs. We can move onto question 2.

Question 2: How much does it cost to cater this event?

We will calculate total cost in the same way we created a function to generate our shopping list. To do so we we will need to:

  1. Store a data frame of quoted prices of ingredients
  2. Generate a table that stored the detailed cost by ingredient
  3. Summarize this detailed table to determine the total cost for Ronald
# Now lets calculate how much the entire cook will cost overall 

# First lets create a data frame of the the most recently quoted prices
# We will use to 
reccent_quoted_px <- historical_price %>% 
  filter(date == as.Date("2019-01-01"))

# At the finest detail we will calculate the cost of each ingredient by recipe
# We do this by creating a function that takes in the number of plates
calculate_px_per_plate <- function(all_plates_df, recipes_df ,prices){
  all_plates_df %>% 
    left_join(recipes_df, by = c("recipe")) %>% 
    left_join(prices, by = c("item")) %>% 
    select(-unit_type.x, -date, -selling_px, -no_units, -unit_type.y ) %>% 
    mutate(total_cost = (no_dishes * (units/4))*cost_per_unit)
}

detail_px_per_plate <- calculate_px_per_plate(all_plates, recipes, reccent_quoted_px)

detail_px_per_plate %>% 
 kable() %>% 
  kable_styling()
no_dishesrecipeitemunitscost_per_unittotal_cost
50butter_chickenchicken_breast0.906.6775.00
50butter_chickenhot_indian_sauceitup1.500.7013.12
50butter_chickenonion0.402.2111.06
50butter_chickentomatoe_paste0.183.508.04
50butter_chickenwhipping_cream0.108.0010.00
50butter_chickennaan_reyat1.000.506.25
50jerk_chickenchicken_thigh_leg0.903.3337.50
50jerk_chickensaucy_jerk_sauceitup1.500.7013.12
50jerk_chickenrice0.202.506.25
50jerk_chickencoconut_milk0.400.010.04
50jerk_chickenkidney_beans0.103.684.59
50chicken_souvlakichicken_breast0.906.6775.00
50chicken_souvlakifreaky_greek_sauceitup1.500.7013.12
50chicken_souvlakipotatoes0.400.221.10
50chicken_souvlakioil0.026.671.67
50chicken_souvlakigarlic_powder0.052.001.25
50chicken_souvlakiblack_pepper0.0526.1216.32

Using the detailed price per plate data frame we will aggregate further to determine how much it will cost to cater the event

sum(detail_px_per_plate$total_cost)
## [1] 293

Additionally, we can calculate the cost per plate for each recipe. Although it was not explicitly asked, I think that Ronald will appreciate this extra data point.

detail_px_per_plate %>% 
  group_by(recipe) %>% 
  summarise(cost_per_recipe = sum(total_cost), 
            no_dishes = mean(no_dishes)) %>% 
  mutate(cost_per_plate = cost_per_recipe/no_dishes) %>% 
  kable() %>% 
  kable_styling()
recipecost_per_recipeno_dishescost_per_plate
butter_chicken123502.5
chicken_souvlaki108502.2
jerk_chicken62501.2

Perfect we now have all the data we need to answer the remaining questions

Question 3: What does he need to charge in order to guarantee a minimum profit margin of 70%?

Profit margin is a measure of profitability and is calculated by finding the net profit as a percentage of revenue. Ronald wants to determine a price per plate using this number. Let’s give it a shot.

# Same aggregation as above just add the cost per plate
detail_px_per_plate %>% 
  group_by(recipe) %>% 
  summarise(cost_per_recipe = sum(total_cost), 
            no_dishes = mean(no_dishes)) %>% 
  mutate(cost_per_plate = cost_per_recipe/no_dishes, 
         # Rearrange the profit margin formula and you can back into how much you should charge
         charge_per_plate = cost_per_plate/(1-0.70)) %>% 
  kable() %>% 
  kable_styling()
recipecost_per_recipeno_dishescost_per_platecharge_per_plate
butter_chicken123502.58.2
chicken_souvlaki108502.27.2
jerk_chicken62501.24.1

Ronald can charge up to 8 dollars a plate. To make things easier when quoting the client, he decides to charge $8.50 for all three plates. Armed with how much he will charge, Ronald can now calculate how many plates he needs to sell to break even.

Question 4 :How many plates of each dish does he need to sell to break even on the raw materials

To answer this, we can reuse the aggregation we did above but this time create a new column for break even dishes

detail_px_per_plate %>% 
  group_by(recipe) %>% 
  summarise(cost_per_recipe = sum(total_cost), 
            no_dishes = mean(no_dishes)) %>% 
  mutate(cost_per_plate = cost_per_recipe/no_dishes, 
         charge_per_plate = cost_per_plate/(1-0.70), 
         break_even_plate = cost_per_recipe/8.50) %>% 
  kable() %>% 
  kable_styling()
recipecost_per_recipeno_dishescost_per_platecharge_per_platebreak_even_plate
butter_chicken123502.58.214.5
chicken_souvlaki108502.27.212.8
jerk_chicken62501.24.17.2

Perfect, we have answered all of Ronald’s questions. It doesn’t look like he needs to make that many plates to break even.

Conclusion

Could this have been done solely in a spreadsheet with obtaining the same result? Absolutely. What I meant to demonstrate was the ease in which one can write code to answer the questions that they have regarding their business. The real added value will come from building on what we have already done. This post is just the first in Ronald’s journey; we will add functions to his package that will allow him to analyze prices over time, assess his recipes based on feedback and help him target his market better. As always, if you have any questions, please feel free to drop me a line.