pfa

[Junaid Iqbal]

from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from datetime import datetime
import pandas as pd

# Create a new Excel workbook with sheets for City P&L, Forecast Assumptions, and Valid Line Items
wb = Workbook()

# -------------------------
# Sheet 1: Forecast_Assumptions
# -------------------------
assumptions = pd.DataFrame({
    "Item": [
        "Monthly Trip Growth Rate", "Avg Fare - UberX", "Avg Fare - Black", "Avg Fare - SUV",
        "Cost per Trip - UberX", "Cost per Trip - Black", "Cost per Trip - SUV",
        "UberX Mix", "Black Mix", "SUV Mix", "Platform Fee %"
    ],
    "Value": [
        0.02, 10.0, 20.0, 25.0,
        6.0, 12.0, 18.0,
        0.60, 0.25, 0.15, 0.20
    ]
})

ws_assumptions = wb.active
ws_assumptions.title = "Forecast_Assumptions"
for r in dataframe_to_rows(assumptions, index=False, header=True):
    ws_assumptions.append(r)

# -------------------------
# Sheet 2: Valid_Line_Items
# -------------------------
valid_items = [
    "Total Gross Bookings", "Total Net Revenue", "Driver Incentives", "Promotions",
    "Cost of Revenue", "Gross Profit", "Operating Expenses", "EBITDA"
]
ws_valid = wb.create_sheet("Valid_Line_Items")
for i, item in enumerate(valid_items, start=1):
    ws_valid.cell(row=i, column=1, value=item)

# -------------------------
# Sheet 3: City_PnL_Template
# -------------------------
months = pd.date_range(start="2022-01-01", end="2024-12-01", freq='MS').strftime('%b-%Y')
line_items = valid_items
city_pnl_template = pd.DataFrame(columns=["Line Item"] + list(months))
city_pnl_template["Line Item"] = line_items

ws_pnl = wb.create_sheet("City_PnL_Template")
for r in dataframe_to_rows(city_pnl_template, index=False, header=True):
    ws_pnl.append(r)

# Save the workbook
output_path = "/mnt/data/Uber_City_PnL_Forecast_Template.xlsx"
wb.save(output_path)

output_path