Untitled

import pandas as pd

# Months and sales units
months = ["Apr", "May", "Jun", "Jul", "Aug", "Sep", 
          "Oct", "Nov", "Dec", "Jan", "Feb", "Mar"]
sales_units = [1200, 1400, 1500, 1900, 2000, 2000, 2100, 2200, 2000, 1800, 1800, 1900]
sales_price = 300
sales_revenue = [u * sales_price for u in sales_units]

# Production and materials
next_month_sales = sales_units[1:] + [1700]
closing_inventory = [int(0.1 * u) for u in next_month_sales]
opening_inventory = [0] + closing_inventory[:-1]
production_units = [sales_units[i] + closing_inventory[i] - opening_inventory[i] for i in range(12)]
materials_purchases = [u * 85 for u in production_units]

# Labour
labour_hours_per_unit = [6]*2 + [5]*10
labour_hours = [production_units[i] * labour_hours_per_unit[i] for i in range(12)]
labour_cost = [h * 13 for h in labour_hours]

# Overheads
var_prod_ovhd = [h * 15 for h in labour_hours]
var_admin_dist_ovhd = [u * 10 for u in sales_units]
fixed_prod_ovhd = [110000 / 12] * 12
fixed_admin_dist_ovhd = [75000 / 12] * 12

# Depreciation & CapEx
depreciation = [0, 0] + [25000 * 0.2 / 12] * 10
capital_expenditure = [0, 0, 25000] + [0]*9

# Cash Flow: Inflows
cash_sales = [s * 0.15 for s in sales_revenue]
credit_sales = [s * 0.85 for s in sales_revenue]
credit_collections = [0, 0] + credit_sales[:-2]
total_inflows = [cash_sales[i] + credit_collections[i] for i in range(12)]

# Cash Flow: Outflows
materials_payments = [0] + materials_purchases[:-1]
total_outflows = [
    materials_payments[i] + labour_cost[i] + var_prod_ovhd[i] + fixed_prod_ovhd[i] +
    var_admin_dist_ovhd[i] + fixed_admin_dist_ovhd[i] + capital_expenditure[i]
    for i in range(12)
]
net_cash_flow = [total_inflows[i] - total_outflows[i] for i in range(12)]
running_balance = [net_cash_flow[0]]
for i in range(1, 12):
    running_balance.append(running_balance[-1] + net_cash_flow[i])

# Income Statement
income_statement = {
    "Sales Revenue": sum(sales_revenue),
    "Direct Materials": sum(materials_purchases),
    "Direct Labour": sum(labour_cost),
    "Variable Production Overheads": sum(var_prod_ovhd),
    "Variable Admin & Distribution": sum(var_admin_dist_ovhd),
    "Fixed Admin & Distribution": sum(fixed_admin_dist_ovhd),
    "Depreciation": sum(depreciation)
}
income_statement["COGS"] = income_statement["Direct Materials"] + income_statement["Direct Labour"] + income_statement["Variable Production Overheads"]
income_statement["Gross Profit"] = income_statement["Sales Revenue"] - income_statement["COGS"]
income_statement["Operating Expenses"] = income_statement["Variable Admin & Distribution"] + income_statement["Fixed Admin & Distribution"] + income_statement["Depreciation"]
income_statement["Net Profit"] = income_statement["Gross Profit"] - income_statement["Operating Expenses"]
income_statement["Gross Profit %"] = income_statement["Gross Profit"] / income_statement["Sales Revenue"]
income_statement["Net Profit %"] = income_statement["Net Profit"] / income_statement["Sales Revenue"]

# DataFrames
cashflow_df = pd.DataFrame({
    "Month": months,
    "Cash Sales (15%)": cash_sales,
    "Credit Sales Collected": credit_collections,
    "Total Inflows": total_inflows,
    "Materials Paid (Lag)": materials_payments,
    "Labour": labour_cost,
    "Var Prod Ovhd": var_prod_ovhd,
    "Fixed Prod Ovhd": fixed_prod_ovhd,
    "Var Admin/Dist": var_admin_dist_ovhd,
    "Fixed Admin/Dist": fixed_admin_dist_ovhd,
    "CapEx": capital_expenditure,
    "Total Outflows": total_outflows,
    "Net Cash Flow": net_cash_flow,
    "Running Balance": running_balance
})

income_df = pd.DataFrame.from_dict(income_statement, orient='index', columns=["Amount (£)"]).reset_index()
income_df.columns = ["Item", "Amount (£)"]

# Save to Excel
with pd.ExcelWriter("OMG_Ltd_Financials.xlsx", engine="openpyxl") as writer:
    cashflow_df.to_excel(writer, sheet_name="Cash Flow Forecast", index=False)
    income_df.to_excel(writer, sheet_name="Income Statement", index=False)

print("✅ Excel file 'OMG_Ltd_Financials.xlsx' has been created in your working directory.")