Untitled

import pandas as pd

Sheet 1: Ingredients

ingredients = pd.DataFrame({ 'Ingredient': ['Flour', 'Sugar', 'Milk'], 'Price': [50, 40, 30], 'Gram': [500, 200, 100], 'Price per Gram': ['=B2/C2', '=B3/C3', '=B4/C4'], 'Gram per Serve': [50, 20, 10], 'Cost per Serve': ['=D2E2', '=D3E3', '=D4*E4'] })

Sheet 2: Packaging

packaging = pd.DataFrame({ 'Packaging Item': ['Box', 'Spoon', 'Napkin'], 'Price': [500, 100, 100], 'Quantity Purchased': [100, 50, 200], 'Price per 1': ['=B2/C2', '=B3/C3', '=B4/C4'], 'Qty per Serve': [1, 1, 1], 'Cost per Serve': ['=D2E2', '=D3E3', '=D4*E4'] })

Sheet 3: Equipment/Gas

equipment = pd.DataFrame({ 'Equipment/Utility': ['Gas'], 'Total Cost': [500], 'Unit': ['liter'], 'Qty Used per Serve': [0.5], 'Cost per Serve': ['=B2*D2/Total_Servings'] })

Sheet 4: Transportation

transport = pd.DataFrame({ 'Item': ['Delivery'], 'Total Cost': [200], 'Servings Delivered': [100], 'Cost per Serve': ['=B2/C2'] })

Sheet 5: Summary

summary = pd.DataFrame({ 'Cost Component': ['Ingredients', 'Packaging', 'Equipment/Gas', 'Transportation', 'Grand Total'], 'Cost per Serve': ['=Ingredients!F5', '=Packaging!F5', '=Equipment!E2', '=Transport!D2', '=SUM(B2:B5)'] })

Save to Excel with multiple sheets

with pd.ExcelWriter('/mnt/data/Product_Costing_Calculator_Full.xlsx') as writer: ingredients.to_excel(writer, sheet_name='Ingredients', index=False) packaging.to_excel(writer, sheet_name='Packaging', index=False) equipment.to_excel(writer, sheet_name='Equipment', index=False) transport.to_excel(writer, sheet_name='Transportation', index=False) summary.to_excel(writer, sheet_name='Summary', index=False)