Overview
Little Chicken is a local fried chicken stand owned by your friend, Tom. Tom knows a lot about fried chicken, but not so much about business. In an attempt to boost his sales, Tom recently purchased a mini fridge. The refrigerator is consistently stocked with various beverages for his customers to take free of charge. Tom does not like to be controlling, so he allows his customers to take as many beverages as they need. Since purchasing the mini fridge, Tom has noticed a considerable increase in fried chicken sales but is concerned at how often he also restocks drinks.
Tom would like to evaluate the current state of his sales. He has been keeping an eye on how many drinks customers take each time they buy some chicken, recording the number of bottles alongside the quantity of chicken in their order. Tom is also interested in knowing whether his mini fridge decision has been more profitable for his stand. He has provided you with data on chicken order quantities before and after the purchase of the minifridge for comparison.
Tom purchases and sells his chicken for $0.99 and $3.99 per piece, respectively, and spends an average of $1.59 per bottle on drinks.
Instructions
Using the datafile linked below, compute the information Tom needs to evaluate the current state of his stand. Tom will need to know the following:
Total Sales for both periods (Before/After the purchase of the mini fridge)
Total Expenses for both periods, and
Total Net Income (Profitability) for both periods
Do you think the mini fridge strategy is effective? What changes would you like Tom to make?
I thought I would play around with the reporting tools. I’m not a fan of them, as other visualization software does better, but still it worked to see the results. Here’s to more free drinks! Here are my solutions:
Fun challenge! I loved seeing the usefulness of alteryx in a simple business setting. I wasn’t familiar with that last tool you used, but I was able to get the job done with a formula and select tool! Here is my solution:
Great challenge! To answer your questions, I do think the mini fridge strategy is effective! Clearly his Net Income has gone up since making the change, although not by much. His revenue has increased a bunch, but so have his expenses. Maybe instead of a mini fridge, Tom could possibly introduce a soda fountain. Might be a bit cheaper to operate than the mini fridge, but still give customers the opportunity for unlimited soda. Or, he could put a sign on the fridge that says something like, “One per customer!:)” Either way, I think free soda is driving revenue up, and I love it!
Here is my solution. There are definitely simpler ways of going about this than I did but this worked for me nonetheless. It was interesting to see that Net Income was higher after introducing the mini-fridge. My advice to Tom would be to limit the drinks to one per customer. Customers would still be happy with the free drink and Tom’s expenses would decrease. This was a fun challenge to play around with!
I was able to complete the challenge in fewer steps because I included BD in my sums as “Before Drinks” and AD as “After Drinks” without formatting to have all my data in the same row.
Like others have already mentioned, I think limiting customers to one drink per chicken order would be more reasonable and improve profitability.
Here is my solution. My advice is Tom should give one free drink for each order or each customer. The net income difference between before having a mini fridge and after having a mini fridge is not very big. Considering the cost of the mini fridge and the extra utilities bill, the limited drink offer should be better for the business.
I came to a numerical solution relatively quickly with Alteryx. A quick analysis reveals that profits remained relatively stable with or without the addition of the mini-fridge. However, he was able to sell approximately 1.5 additional pieces of chicken with the addition of the free drinks. If he was able to capitalize on the additional purchases while reducing the cost or quantity of his free drinks (at an average of 2.46 drinks an order), he could further increase his profitability.
Very fun challenge! It was exciting to get to work on an alteryx project again. I realize there are simpler ways to do this, but this was the process that went through my mind. Here is what I came up with:
Tom’s idea to provide free drinks definitely increased business! However, it seems that some customers are taking advantage of his generosity. I noticed that 88 out of 100 orders with free drinks took more than one drink and 5 of those 88 resulted in a net loss rather than net income. Maybe Tom should consider limiting the number of free drinks based on the amount of chicken each customer purchases.
Expenses:
Before - $315.81
After - $456, but $847.14 including cost for drinks
Net Income:
Before - $957
After - $992.25
The mini fridge strategy appears to be effective, yes. His expenses have increased, but so has his net income. I believe that Tom should keep his strategy of giving out free drinks, as it has now become something he is known for. I would say, however, that he should put a limit on it. A free drink per purchase of chicken, for example, or even just one per customer. This way he still keeps his promotion and uniqueness, but decreases his expenses.
SELECT
IIF([Drinks Given] = '', 'No Minifridge', 'Minifridge') AS period
, SUM(CAST([Chicken Sold] AS int)*3.99) AS revenue
, SUM(CAST([Chicken Sold] AS int)*0.99) AS chick_exp
, SUM(IIF([Drinks Given]='',0,CAST([Drinks Given] AS int)*1.59)) AS drink_exp
, SUM(CAST([Chicken Sold] AS int)*3.99)-SUM(CAST([Chicken Sold] AS int)*0.99)-SUM(IIF([Drinks Given]='',0,CAST([Drinks Given] AS int)*1.59)) AS profitability
FROM dbo.Challenge2_Dataset_Little_Chicken
GROUP BY IIF([Drinks Given] = '', 'No Minifridge', 'Minifridge')
@Andrew I liked your idea of using a different technology, so I decided to do this challenge in Python! I admit that Alteryx is one of my weak spots in the tech world. But I think that anything Alteryx can do Python can, too (although maybe a little slower depending on your skill level).
Here’s my solution code:
import pandas as pd
df = pd.read_csv('Challenge2_Dataset_Little_Chicken.csv')
preFridge = df[df['Drinks Given'].isna()]
preFridge = preFridge.fillna(0)
postFridge = df.drop(preFridge.index)
chickExp = .99
chickSales = 3.99
drinkExp = 1.59
def calc_ni(df):
sales = sum(df['Chicken Sold'] * chickSales)
exp = sum(df['Chicken Sold'] * chickExp) + sum(df['Drinks Given'] * drinkExp)
ni = sales - exp
return round(ni,2)
print(f'Before the free drinks, Tom had net income of ${calc_ni(preFridge)}')
print(f'After the free drinks, Tom had net income of ${calc_ni(postFridge)}')
I think keeping the mini-fridge is a good idea, but part of me is still pained by the idea of giving away free drinks. Maybe he should raise his prices on chicken 50 cents or so to recoup some of that investment!
