Project: LBO Analysis of Distressed Meat Processor

#----------- Step 1: Import Financial Statements 

import pandas as pd

# ---------- Income Statement ----------
income_data = {
    "Item": [
        "Turnover", 
        "Cost of sales",
        "Gross profit",
        "Administrative expenses",
        "Other operating income",
        "Operating (loss)/profit",
        "Interest payable and similar expenses",
        "(Loss)/profit before tax",
        "Tax on (loss)/profit",
        "(Loss)/profit after tax",
        "Retained earnings at the beginning of the year",
        "(Loss)/profit for the year",
        "Retained earnings at the end of the year",
    ],
    "2021 (€)": [
        26160618,
        -23126547,
        3034071,
        -3214597,
        0,
        -180526,
        -77569,
        -258095,
        43332,
        -214763,
        404462,
        -214762,  
        189700
    ],
    "2020 (€)": [
        21422124,
        -18668580,
        2753544,
        -2547693,
        56451,
        262302,
        -69436,
        192866,
        -36575,
        156291,
        248170,
        156291,
        404461
    ]
}
df_income = pd.DataFrame(income_data)

# ---------- Balance Sheet ----------
balance_data = {
    "Item": [
        "Tangible assets",
        "Stocks",
        "Debtors: amounts falling due within one year",
        "Cash at bank and in hand",
        "Total current assets",
        "Creditors: due within one year",
        "Net current liabilities",
        "Total assets less current liabilities",
        "Creditors: due after more than one year",
        "Net assets",
        "Called up share capital",
        "Profit and loss account",
        "Shareholders' funds"
    ],
    "2021 (€)": [
        1343463,
        282943,
        2945120,
        425344,
        3653407,
        -3991422,
        -338015,
        1005448,
        -815650,
        189798,
        100,
        189698,
        189798,
    ],
    "2020 (€)": [
        899616,
        600760,
        2672529,
        64346,
        3337635,
        -3732300,
        -394665,
        504951,
        -100390,
        404561,
        100,
        404461,
        404561,
    ]
}
df_balance = pd.DataFrame(balance_data)

# ---------- Loan Breakdown ----------
loan_data = {
    "Item": [
        "Bank loans (due within 1 year)",
        "Invoice discounting (due within 1 year)",
        "Total current borrowings",
        "Bank loans (due in 2-5 years)",
        "Total noncurrent borrowings"
    ],
    "2021 (€)": [
        117811,
        1818403,
        1936214,
        428336,
        428336
    ],
    "2020 (€)": [
        19740,
        1515556,
        1535296,
        100390,
        100390
    ]
}
df_loans = pd.DataFrame(loan_data)

# ---------- Quick Display ----------
print("Income Statement")
print(df_income, "\n")
print("Balance Sheet")
print(df_balance, "\n")
print("Loan Breakdown")
print(df_loans)

#----------- Step 2: Project Future Cash Flows ---------------

# Assumptions
revenue_growth_rate = 0.05
cost_growth_rate = 0.05
admin_exp_growth_rate = 0.03
tax_rate = 0.20

# Function to project income statement
def project_income_statement(df_income, years=5):
    projections = []
    last_year = df_income.columns[-1]
    
    for year in range(1, years + 1):
        new_year = str(int(last_year.split()[0]) + year) + " (€)"
        turnover = df_income[last_year][0] * (1 + revenue_growth_rate) ** year
        cost_of_sales = df_income[last_year][1] * (1 + cost_growth_rate) ** year
        gross_profit = turnover + cost_of_sales
        admin_expenses = df_income[last_year][3] * (1 + admin_exp_growth_rate) ** year
        operating_profit = gross_profit + admin_expenses
        interest_expense = df_income[last_year][6]
        profit_before_tax = operating_profit + interest_expense
        tax = profit_before_tax * tax_rate
        profit_after_tax = profit_before_tax - tax
        
        projections.append({
            "Item": new_year,
            "Turnover": turnover,
            "Cost of sales": cost_of_sales,
            "Gross profit": gross_profit,
            "Administrative expenses": admin_expenses,
            "Operating (loss)/profit": operating_profit,
            "Interest payable and similar expenses": interest_expense,
            "(Loss)/profit before tax": profit_before_tax,
            "Tax on (loss)/profit": tax,
            "(Loss)/profit after tax": profit_after_tax
        })
    
    return pd.DataFrame(projections)

# Project the income statement for the next 5 years
df_income_projections = project_income_statement(df_income)
print("Projected Income Statement")
print(df_income_projections)

#----------- Step 3: Determine Debt Equity Structure ---------------

# Assumptions for LBO
purchase_price = 10000000
debt_ratio = 0.70
equity_ratio = 0.30
interest_rate = 0.05
debt_repayment_period = 5

# Initial debt and equity
initial_debt = purchase_price * debt_ratio
initial_equity = purchase_price * equity_ratio

# Annual interest expense and principal repayment
annual_interest_expense = initial_debt * interest_rate
annual_principal_repayment = initial_debt / debt_repayment_period

# Display the initial debt and equity structure
print(f"Initial Debt: €{initial_debt}")
print(f"Initial Equity: €{initial_equity}")
print(f"Annual Interest Expense: €{annual_interest_expense}")
print(f"Annual Principal Repayment: €{annual_principal_repayment}")

#----------- Step 4: Calculate Returns ---------------

# Assumption for exit multiple
exit_multiple = 6

# Calculate projected EBITDA and future value
df_income_projections["EBITDA"] = df_income_projections["Operating (loss)/profit"]

# Future value at exit (using the last year's EBITDA)
future_value = df_income_projections["EBITDA"].iloc[-1] * exit_multiple

# Calculate the equity value at exit
equity_value_at_exit = future_value - (initial_debt - annual_principal_repayment * debt_repayment_period)

# Calculate the return on equity
return_on_equity = (equity_value_at_exit - initial_equity) / initial_equity

# Display the results
print(f"Future Value at Exit: €{future_value}")
print(f"Equity Value at Exit: €{equity_value_at_exit}")
print(f"Return on Equity: {return_on_equity * 100:.2f}%")