import streamlit as st import pandas as pd import numpy as np st.set_page_config( page_title="Executive BI — PNL Dashboards", page_icon="📊", layout="wide", initial_sidebar_state="collapsed", ) CUSTOM_CSS = """ """ st.markdown(CUSTOM_CSS, unsafe_allow_html=True) @st.cache_data def load_data(): """Load and clean the Kitchen PNL CSV data.""" df = pd.read_csv("Kittchen PNL Data.csv", skiprows=1) df.columns = df.columns.str.strip() df["DATE"] = pd.to_datetime(df["MONTH"], format="%b-%Y") df["MONTH_DISPLAY"] = df["DATE"].dt.strftime("%b %Y") num_cols = [ "ORDER COUNT", "CART SALES", "DISCOUNT", "NET REVENUE", "IDEAL FOOD COST", "GROSS MARGIN", "KITCHEN EBITDA", "VARIANCE", ] for c in num_cols: df[c] = pd.to_numeric(df[c], errors="coerce").fillna(0) df["GM%"] = np.where(df["NET REVENUE"] != 0, df["GROSS MARGIN"] / df["NET REVENUE"] * 100, 0) df["CM"] = df["GROSS MARGIN"] - df["VARIANCE"] df["CM%"] = np.where(df["NET REVENUE"] != 0, df["CM"] / df["NET REVENUE"] * 100, 0) df["EBITDA%"] = np.where(df["NET REVENUE"] != 0, df["KITCHEN EBITDA"] / df["NET REVENUE"] * 100, 0) df["VARIANCE%"] = np.where(df["CART SALES"] != 0, df["VARIANCE"] / df["CART SALES"] * 100, 0) df["VARIANCE_BUCKET"] = pd.cut( df["VARIANCE%"], bins=[-np.inf, 2, 3, 5, np.inf], labels=["(a) Var < 2%", "(b) Var 2% to 3%", "(c) Var 3% to 5%", "(d) Var > 5%"], ) df["REVENUE_CATEGORY"] = pd.cut( df["NET REVENUE"], bins=[-np.inf, 1500000, 2500000, 3500000, 4500000, np.inf], labels=[ "(a) Below INR 15 lacs", "(b) INR 15 to 25 lacs", "(c) INR 25 to 35 lacs", "(d) INR 35 to 45 lacs", "(e) Above INR 45 lacs", ], ) str_cols = ["CITY", "STORE", "STATUS", "ZONE MAPPING", "REVENUE COHORT", "CM COHORT", "EBITDA CATEGORY", "EBITDA COHORT"] for c in str_cols: df[c] = df[c].astype(str).str.strip() return df def fmt_inr(v): """Format a number as ₹ lakhs.""" if abs(v) >= 100000: return f"₹{v / 100000:.1f}L" return f"₹{v:,.0f}" def fmt_pct(v): return f"{v:.1f}%" st.markdown( '", unsafe_allow_html=True, ) df = load_data() tab1, tab2 = st.tabs(["Kitchen Level PNL", "VARIANCE Level PNL"]) with tab1: st.markdown("## 1. Kitchen Level PNL –") k1, k2, k3, k4 = st.columns(4) total_rev = df["NET REVENUE"].sum() avg_gm = df["GM%"].mean() avg_cm = df["CM%"].mean() avg_ebitda = df["EBITDA%"].mean() with k1: st.markdown( f'

{fmt_inr(total_rev)}

' f'

Total Net Revenue

', unsafe_allow_html=True, ) with k2: st.markdown( f'

{avg_gm:.1f}%

' f'

Avg Gross Margin %

', unsafe_allow_html=True, ) with k3: st.markdown( f'

{avg_cm:.1f}%

' f'

Avg Contribution Margin %

', unsafe_allow_html=True, ) with k4: delta_cls = "delta-pos" if avg_ebitda >= 0 else "delta-neg" st.markdown( f'

{avg_ebitda:.1f}%

' f'

Avg EBITDA %

', unsafe_allow_html=True, ) st.markdown('

', unsafe_allow_html=True) s1, s2, s3 = st.columns(3) ebitda_min = int(df["KITCHEN EBITDA"].min()) ebitda_max = int(df["KITCHEN EBITDA"].max()) cm_pct_min = 0 cm_pct_max = 100 rev_min = 0 rev_max = int(df["NET REVENUE"].max()) with s1: ebitda_range = st.slider( "Select EBITDA Range (in ₹)", min_value=ebitda_min, max_value=ebitda_max, value=(ebitda_min, ebitda_max), format="₹%d", ) with s2: cm_range = st.slider( "Select Contribution Margin (CM) Range (%)", min_value=cm_pct_min, max_value=cm_pct_max, value=(0, 100), format="%d%%", ) with s3: rev_range = st.slider( "Select Revenue Range (in ₹)", min_value=rev_min, max_value=rev_max, value=(rev_min, rev_max), format="₹%d", ) st.markdown('

', unsafe_allow_html=True) fc1, fc2, fc3 = st.columns(3) with fc1: search_term = st.text_input("Search stores", "", placeholder="Type store name...") with fc2: zones = ["All"] + sorted(df["ZONE MAPPING"].unique().tolist()) sel_zone = st.selectbox("Zone", zones) with fc3: ebitda_cats = ["All"] + sorted(df["EBITDA CATEGORY"].unique().tolist()) sel_ebitda_cat = st.selectbox("EBITDA Category", ebitda_cats) fc4, fc5, fc6, fc7 = st.columns([1.2, 1, 1, 1]) with fc4: st.markdown('

KITCHEN SNAPSHOT

', unsafe_allow_html=True) with fc5: stores_list = ["All"] + sorted(df["STORE"].unique().tolist()) sel_store = st.selectbox("Store", stores_list) with fc6: months_sorted = df.sort_values("DATE")["MONTH_DISPLAY"].unique().tolist() sel_months = st.multiselect("Month", months_sorted, default=months_sorted[:3]) with fc7: rev_cohorts = ["All"] + sorted(df["REVENUE COHORT"].unique().tolist()) sel_rev_cohort = st.selectbox("Revenue Category", rev_cohorts) st.markdown("

", unsafe_allow_html=True) filtered = df.copy() filtered = filtered[ (filtered["KITCHEN EBITDA"] >= ebitda_range[0]) & (filtered["KITCHEN EBITDA"] <= ebitda_range[1]) ] filtered = filtered[ (filtered["CM%"] >= cm_range[0]) & (filtered["CM%"] <= cm_range[1]) ] filtered = filtered[ (filtered["NET REVENUE"] >= rev_range[0]) & (filtered["NET REVENUE"] <= rev_range[1]) ] if sel_zone != "All": filtered = filtered[filtered["ZONE MAPPING"] == sel_zone] if sel_ebitda_cat != "All": filtered = filtered[filtered["EBITDA CATEGORY"] == sel_ebitda_cat] if sel_store != "All": filtered = filtered[filtered["STORE"] == sel_store] if sel_rev_cohort != "All": filtered = filtered[filtered["REVENUE COHORT"] == sel_rev_cohort] if search_term: filtered = filtered[ filtered["STORE"].str.contains(search_term, case=False, na=False) ] if sel_months: filtered = filtered[filtered["MONTH_DISPLAY"].isin(sel_months)] st.markdown('

', unsafe_allow_html=True) if filtered.empty: st.info("No data matches the current filters. Adjust your selections.") else: display_cols = { "Net Revenue": "NET REVENUE", "GM %": "GM%", "CM %": "CM%", "EBITDA": "KITCHEN EBITDA", "EBITDA %": "EBITDA%", } pivot_data = filtered[ ["STORE", "DATE", "MONTH_DISPLAY"] + list(display_cols.values()) ].copy() month_order = ( pivot_data.drop_duplicates("MONTH_DISPLAY") .sort_values("DATE", ascending=False)["MONTH_DISPLAY"] .tolist() ) pivot = pivot_data.pivot_table( index="STORE", columns="MONTH_DISPLAY", values=list(display_cols.values()), aggfunc="mean", ) pivot.columns = pivot.columns.swaplevel(0, 1) ordered_cols = [] for month in month_order: for label, col in display_cols.items(): if (month, col) in pivot.columns: ordered_cols.append((month, col)) pivot = pivot[ordered_cols] col_rename = {v: k for k, v in display_cols.items()} new_cols = pd.MultiIndex.from_tuples( [(m, col_rename.get(c, c)) for m, c in pivot.columns] ) pivot.columns = new_cols pivot = pivot.reset_index() for col in pivot.columns: if isinstance(col, tuple): _, metric = col if metric in ("GM %", "CM %", "EBITDA %"): pivot[col] = pivot[col].apply( lambda x: f"{x:.1f}%" if pd.notna(x) else "—" ) elif metric in ("Net Revenue", "EBITDA"): pivot[col] = pivot[col].apply( lambda x: f"₹{x:,.0f}" if pd.notna(x) else "—" ) st.dataframe( pivot, use_container_width=True, hide_index=True, height=450, ) st.markdown('

', unsafe_allow_html=True) if not filtered.empty: csv_export = filtered.to_csv(index=False).encode("utf-8") st.download_button( "⬇ Download Filtered Data", csv_export, "kitchen_pnl_filtered.csv", "text/csv", ) with st.expander("📊 Key Data Insights"): n_stores = df["STORE"].nunique() n_cities = df["CITY"].nunique() neg_ebitda = df[df["KITCHEN EBITDA"] < 0]["STORE"].nunique() avg_var = df["VARIANCE%"].mean() st.markdown(f""" - **{n_stores}** unique kitchen stores across **{n_cities}** cities - **{neg_ebitda}** stores have at least one month with negative EBITDA - Average food wastage (variance) rate: **{avg_var:.2f}%** of cart sales - Revenue ranges from **{fmt_inr(df['NET REVENUE'].min())}** to **{fmt_inr(df['NET REVENUE'].max())}** - Most stores fall in the *INR 20-30 lacs* revenue cohort - EBITDA-positive stores outnumber EBITDA-negative in most months """) with tab2: st.markdown( '

VARIANCE BY REVENUE CATEGORY

', unsafe_allow_html=True, ) st.markdown( '

The tables below summarise the average variance % ' "on cart of the kitchens under revenue categories and the count of kitchens " "under each revenue category to further drill down on the variance category.

", unsafe_allow_html=True, ) main_col, filter_col = st.columns([4, 1]) with filter_col: st.markdown('

', unsafe_allow_html=True) st.markdown('

Variance Category

', unsafe_allow_html=True) var_buckets = [ "(a) Var < 2%", "(b) Var 2% to 3%", "(c) Var 3% to 5%", "(d) Var > 5%", ] selected_var = [] for bucket in var_buckets: if st.checkbox(bucket, value=True, key=f"var_{bucket}"): selected_var.append(bucket) st.markdown("

", unsafe_allow_html=True) var_filtered = df.copy() if selected_var: var_filtered = var_filtered[ var_filtered["VARIANCE_BUCKET"].isin(selected_var) ] all_months = ( var_filtered.drop_duplicates("MONTH_DISPLAY") .sort_values("DATE", ascending=False)["MONTH_DISPLAY"] .tolist() ) rev_cat_order = [ "(a) Below INR 15 lacs", "(b) INR 15 to 25 lacs", "(c) INR 25 to 35 lacs", "(d) INR 35 to 45 lacs", "(e) Above INR 45 lacs", ] with main_col: st.markdown('

', unsafe_allow_html=True) if var_filtered.empty: st.info("No data for the selected variance categories.") else: t1 = var_filtered.pivot_table( index="REVENUE_CATEGORY", columns="MONTH_DISPLAY", values="VARIANCE%", aggfunc="mean", observed=False, ) t1 = t1.reindex(rev_cat_order) month_cols = [m for m in all_months if m in t1.columns] t1 = t1[month_cols] grand_avg = var_filtered.pivot_table( columns="MONTH_DISPLAY", values="VARIANCE%", aggfunc="mean" ) grand_row = pd.DataFrame( grand_avg.values, columns=grand_avg.columns, index=["Grand Total"] ) grand_row = grand_row[month_cols] t1_display = pd.concat([t1, grand_row]) t1_display = t1_display.map( lambda x: f"{x:.1f}%" if pd.notna(x) else "—" ) t1_display.index.name = "Revenue Category" st.markdown("**Visual 1 — Average Variance % by Revenue Category**") st.dataframe(t1_display, use_container_width=True, height=320) st.markdown("") st.markdown('

', unsafe_allow_html=True) if var_filtered.empty: st.info("No data for the selected variance categories.") else: t2 = var_filtered.pivot_table( index="REVENUE_CATEGORY", columns="MONTH_DISPLAY", values="STORE", aggfunc="nunique", observed=False, ) t2 = t2.reindex(rev_cat_order) t2 = t2[month_cols] grand_count = var_filtered.pivot_table( columns="MONTH_DISPLAY", values="STORE", aggfunc="nunique" ) grand_row2 = pd.DataFrame( grand_count.values, columns=grand_count.columns, index=["Grand Total"] ) grand_row2 = grand_row2[month_cols] t2_display = pd.concat([t2, grand_row2]) t2_display = t2_display.map( lambda x: f"{int(x)}" if pd.notna(x) else "0" ) t2_display.index.name = "Revenue Category" st.markdown("**Visual 2 — Kitchen Store Count by Revenue Category**") st.dataframe(t2_display, use_container_width=True, height=320) st.markdown('

', unsafe_allow_html=True) with st.expander("📊 Variance Insights"): low_var = df[df["VARIANCE%"] < 2]["STORE"].nunique() high_var = df[df["VARIANCE%"] > 5]["STORE"].nunique() st.markdown(f""" - **{low_var}** stores consistently maintain variance below 2% - **{high_var}** stores have at least one month with variance > 5% - Lower-revenue kitchens tend to have higher variance % (higher food wastage relative to sales) - Variance is seasonal — winter months show lower wastage rates on average """)