Untitled
unknown
plain_text
11 days ago
4.2 kB
3
Indexable
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.")
Editor is loading...
Leave a Comment