foodie

[arashraf27]

import pandas as pd

1. Prepare Menu Data [cite: 1, 3]

menu_data = {
"Category": ["Chicken"]*7 + ["Beef"]*5 + ["Dessert"]*3,
"Item": [
"Chicken Korma", "Chicken Boneless Karhai", "Shinwari / Charsi Karhai",
"Chicken White Karhai", "Murgh Kashmiri / Achar Gosht", "Butter Chicken", "Chicken Haleem",
"Beef Paya", "Beef Haleem", "Beef Korma", "Aloo / Karela / Matar Qeema", "Aloo/Lauki/Bhindi/Arvi Gosht",
"Kheer", "Custard", "Fruit Trifle Custard"
],
"Hefty Box": ["$15.99", "$19.99", "$17.99", "$18.99", "$16.99", "$16.99", "$17.99", "$19.99", "$18.99", "$17.99", "$15.99", "$17.99", "-", "-", "-"],
"Small Tray": ["$59.99", "$79.99", "$69.99", "$68.99", "$64.99", "$64.99", "$69.99", "$74.99", "$69.99", "$65.99", "$59.99", "$65.99", "$54.99", "$54.99", "$54.99"],
"Medium Tray": ["$89.99", "$104.99", "$99.99", "$98.99", "$94.99", "$94.99", "$94.90", "$109.99", "$99.99", "$95.99", "$74.99", "$95.99", "$74.99", "$74.99", "$74.99"],
"Large Tray": ["$134.99", "$154.99", "$139.99", "$198.99", "$134.99", "$134.99", "$139.99", "$144.99", "$139.99", "$135.99", "$124.99", "$135.99", "$119.99", "$119.99", "$119.99"]
}
df_menu = pd.DataFrame(menu_data)

2. Prepare Base Recipe Costs (Sheet1 Data) [cite: 2]

recipe_ingredients = [
{"Ingredient": "Chicken", "Unit": "lb", "Cost_per_lb": 1.29},
{"Ingredient": "Yogurt", "Unit": "lb", "Cost_per_lb": 0.94},
{"Ingredient": "Onions", "Unit": "lb", "Cost_per_lb": 0.53},
{"Ingredient": "Shan Masala", "Unit": "lb", "Cost_per_lb": 1.41},
{"Ingredient": "Oil", "Unit": "lb", "Cost_per_lb": 7.09},
{"Ingredient": "Gas/Utilities", "Unit": "flat", "Cost_per_lb": 25.00},
{"Ingredient": "Hefty Packaging", "Unit": "pc", "Cost_per_lb": 2.00}
]
df_calc_base = pd.DataFrame(recipe_ingredients)

3. Dynamic Calculator Sheet

We want this sheet to be a template.

For demonstration, we'll fill one example row based on previous logic.

scaling = {"Hefty": 0.25, "Small": 1.0, "Medium": 1.5, "Large": 2.5}
calc_rows = []
for ing in recipe_ingredients:
base_qty = 10 # dummy base quantity for Small Tray
row = {
"Ingredient": ing["Ingredient"],
"Unit": ing["Unit"],
"Cost/Unit ($)": ing["Cost_per_lb"],
"Small Qty (lb)": base_qty,
"Small Cost ($)": base_qty * ing["Cost_per_lb"],
"Hefty Qty": base_qty * scaling["Hefty"],
"Hefty Cost ($)": base_qty * scaling["Hefty"] * ing["Cost_per_lb"],
"Medium Qty": base_qty * scaling["Medium"],
"Medium Cost ($)": base_qty * scaling["Medium"] * ing["Cost_per_lb"],
"Large Qty": base_qty * scaling["Large"],
"Large Cost ($)": base_qty * scaling["Large"] * ing["Cost_per_lb"]
}
calc_rows.append(row)
df_calc_template = pd.DataFrame(calc_rows)

4. Inventory Sheet

inventory_items = ["Chicken", "Beef", "Onions", "Yogurt", "Oil", "Rice", "Spices", "Trays"]
inv_data = {
"Item": inventory_items,
"Unit": ["lb"]*7 + ["pc"],
"Opening Stock": [0.0]*8,
"Purchases": [0.0]*8,
"Closing Stock": [0.0]*8,
"Usage": [0.0]*8
}
df_inventory = pd.DataFrame(inv_data)

Save to Excel

with pd.ExcelWriter('Cloud_Kitchen_Management_Calculator.xlsx') as writer:
df_menu.to_excel(writer, sheet_name='Menu Price List', index=False)
df_calc_template.to_excel(writer, sheet_name='Recipe Scaling Calculator', index=False)
df_inventory.to_excel(writer, sheet_name='Monthly Inventory', index=False)

print("Excel file created successfully.")