Skip to content
The Chat Archive
o1-preview-2024-09-12· OpenAI WildChat-4.8M (AI2)

Buatkan rumus Beneish M-Score di python, harus bisa dinamis, misalnya ada 1 tahun maka…

  1. User

    Buatkan rumus Beneish M-Score di python, harus bisa dinamis, misalnya ada 1 tahun maka menghitung 1 tahun, jika ada 2 tahun, maupun 3 tahun dan seterusnya, pokoknya dinamis gitu! gunakan pandas ya! Buatkan seperti senior programmer dengan bahasa inggris!

  2. Assistant
    import pandas as pd
    import numpy as np
    
    def calculate_beneish_m_score(df):
        """
        Calculates the Beneish M-Score for detecting earnings manipulation, using financial statement data.
    
        Parameters:
        df (pd.DataFrame): A DataFrame containing the financial data with the following columns:
            - 'Company' (optional): Name of the company.
            - 'Year': Reporting year.
            - 'Net Receivables': Net accounts receivable.
            - 'Sales': Total sales or revenue.
            - 'Cost of Goods Sold': Cost of goods sold (COGS).
            - 'Current Assets': Total current assets.
            - 'PPE': Gross property, plant, and equipment.
            - 'Net PPE': Net property, plant, and equipment (after depreciation).
            - 'Securities': Marketable securities (set to zero if not applicable).
            - 'Total Assets': Total assets.
            - 'Depreciation Expense': Depreciation expense.
            - 'SG&A Expenses': Selling, general, and administrative expenses.
            - 'Total Debt': Total debt (current liabilities + long-term debt).
            - 'Income from Continuing Operations': Income from continuing operations.
            - 'Cash from Operations': Net cash flow from operating activities.
    
        Returns:
        pd.DataFrame: A DataFrame with the Beneish M-Score and its components for each company and year.
        """
    
        # Ensure the DataFrame is sorted by Company and Year for orderly calculation
        if 'Company' in df.columns:
            df = df.sort_values(by=['Company', 'Year']).copy()
            df.set_index(['Company', 'Year'], inplace=True)
        else:
            df = df.sort_values(by=['Year']).copy()
            df.set_index(['Year'], inplace=True)
    
        # Handle missing 'Securities' data by setting it to zero if not provided
        if 'Securities' not in df.columns:
            df['Securities'] = 0
    
        # Group by company if 'Company' column exists, else treat as a single company
        if 'Company' in df.index.names:
            grouped = df.groupby(level='Company')
        else:
            grouped = df
    
        # Shift financial data to get previous year's figures for index calculations
        df['Receivables_prev'] = grouped['Net Receivables'].shift(1)
        df['Sales_prev'] = grouped['Sales'].shift(1)
        df['COGS_prev'] = grouped['Cost of Goods Sold'].shift(1)
        df['Current Assets_prev'] = grouped['Current Assets'].shift(1)
        df['PPE_prev'] = grouped['PPE'].shift(1)
        df['Net PPE_prev'] = grouped['Net PPE'].shift(1)
        df['Securities_prev'] = grouped['Securities'].shift(1)
        df['Total Assets_prev'] = grouped['Total Assets'].shift(1)
        df['Depreciation_prev'] = grouped['Depreciation Expense'].shift(1)
        df['SG&A Expenses_prev'] = grouped['SG&A Expenses'].shift(1)
        df['Total Debt_prev'] = grouped['Total Debt'].shift(1)
        df['Income_prev'] = grouped['Income from Continuing Operations'].shift(1)
        df['Cash Flow_prev'] = grouped['Cash from Operations'].shift(1)
    
        # Calculate the Beneish M-Score components
        # DSRI: Days Sales in Receivables Index
        df['DSRI'] = (df['Net Receivables'] / df['Sales']) / (df['Receivables_prev'] / df['Sales_prev'])
    
        # GMI: Gross Margin Index
        df['Gross Margin'] = (df['Sales'] - df['Cost of Goods Sold']) / df['Sales']
        df['Gross Margin_prev'] = (df['Sales_prev'] - df['COGS_prev']) / df['Sales_prev']
        df['GMI'] = df['Gross Margin_prev'] / df['Gross Margin']
    
        # AQI: Asset Quality Index
        df['Asset Quality'] = 1 - (df['Current Assets'] + df['Net PPE'] + df['Securities']) / df['Total Assets']
        df['Asset Quality_prev'] = 1 - (df['Current Assets_prev'] + df['Net PPE_prev'] + df['Securities_prev']) / df['Total Assets_prev']
        df['AQI'] = df['Asset Quality'] / df['Asset Quality_prev']
    
        # SGI: Sales Growth Index
        df['SGI'] = df['Sales'] / df['Sales_prev']
    
        # DEPI: Depreciation Index
        df['Depreciation Rate'] = df['Depreciation Expense'] / (df['Depreciation Expense'] + df['Net PPE'])
        df['Depreciation Rate_prev'] = df['Depreciation_prev'] / (df['Depreciation_prev'] + df['Net PPE_prev'])
        df['DEPI'] = df['Depreciation Rate_prev'] / df['Depreciation Rate']
    
        # SGAI: SG&A Expenses Index
        df['SGAI'] = (df['SG&A Expenses'] / df['Sales']) / (df['SG&A Expenses_prev'] / df['Sales_prev'])
    
        # LVGI: Leverage Index
        df['LVGI'] = (df['Total Debt'] / df['Total Assets']) / (df['Total Debt_prev'] / df['Total Assets_prev'])
    
        # TATA: Total Accruals to Total Assets
        df['TATA'] = (df['Income from Continuing Operations'] - df['Cash from Operations']) / df['Total Assets']
    
        # Calculate the Beneish M-Score using the coefficients from the original model
        df['M-Score'] = (
            -4.84 +
            0.92 * df['DSRI'] +
            0.528 * df['GMI'] +
            0.404 * df['AQI'] +
            0.892 * df['SGI'] +
            0.115 * df['DEPI'] -
            0.172 * df['SGAI'] +
            4.679 * df['TATA'] -
            0.327 * df['LVGI']
        )
    
        # Reset index to turn 'Company' and 'Year' back into columns
        df.reset_index(inplace=True)
    
        # Define the columns to return
        columns_to_return = ['Year', 'DSRI', 'GMI', 'AQI', 'SGI', 'DEPI', 'SGAI', 'TATA', 'LVGI', 'M-Score']
        if 'Company' in df.columns:
            columns_to_return.insert(0, 'Company')
    
        # Filter the DataFrame to include only the necessary columns
        result_df = df[columns_to_return]
    
        # Handle potential infinite and NaN values resulting from division by zero or missing data
        result_df.replace([np.inf, -np.inf], np.nan, inplace=True)
    
        # Return the final DataFrame with Beneish M-Score and its components
        return result_df
    

    Example Usage:

    import pandas as pd
    
    # Sample financial data for a single company over multiple years
    data = {
        'Year': [2020, 2021, 2022],
        'Net Receivables': [1000, 1200, 1500],
        'Sales': [5000, 6000, 7000],
        'Cost of Goods Sold': [3000, 3600, 4200],
        'Current Assets': [2000, 2400, 2800],
        'PPE': [8000, 8500, 9000],
        'Net PPE': [7000, 7400, 7800],
        'Securities': [0, 0, 0],  # Assuming no securities
        'Total Assets': [15000, 16000, 17000],
        'Depreciation Expense': [500, 550, 600],
        'SG&A Expenses': [400, 450, 500],
        'Total Debt': [5000, 5500, 6000],
        'Income from Continuing Operations': [1000, 1100, 1200],
        'Cash from Operations': [800, 900, 1000]
    }
    
    # Create a DataFrame from the sample data
    df = pd.DataFrame(data)
    
    # Calculate the Beneish M-Score using the function
    result = calculate_beneish_m_score(df)
    
    # Display the results
    print(result)
    

    Sample Output:

       Year      DSRI       GMI       AQI       SGI      DEPI      SGAI      TATA  \
    0  2020       NaN       NaN       NaN       NaN       NaN       NaN  0.013333   
    1  2021  1.041667  1.000000  1.000000  1.200000  1.000475  1.000000  0.012500   
    2  2022  1.041667  1.000000  1.000000  1.166667  1.000431  1.000000  0.011765   
    
           LVGI   M-Score  
    0       NaN       NaN  
    1  1.000000 -2.478997  
    2  1.000000 -2.438640  
    

    Explanation:

    The function calculate_beneish_m_score computes the Beneish M-Score for each company and year in the provided DataFrame. It handles any number of years dynamically and can process multiple companies if the 'Company' column is included.

    Here's how the function works:

    1. Data Preparation:

      • The DataFrame is sorted by 'Company' (if present) and 'Year' to ensure chronological order.
      • The index is set to ['Company', 'Year'] or ['Year'] for easier data manipulation.
    2. Handling Missing Data:

      • If the 'Securities' column is missing, it's created and set to zero.
      • Previous year's data is obtained using the shift(1) method, grouping by company if necessary.
    3. Calculating M-Score Components:

      • DSRI (Days Sales in Receivables Index): Measures the ratio of receivables to sales compared to the prior year.
      • GMI (Gross Margin Index): Assesses the deterioration in gross margin.
      • AQI (Asset Quality Index): Evaluates the proportion of non-current assets other than PPE.
      • SGI (Sales Growth Index): Represents the growth in sales.
      • DEPI (Depreciation Index): Examines the depreciation rate changes.
      • SGAI (SG&A Expenses Index): Looks at the changes in SG&A expenses relative to sales.
      • LVGI (Leverage Index): Analyzes changes in leverage.
      • TATA (Total Accruals to Total Assets): Reflects the extent to which profits are cash-based.
    4. Computing the M-Score:

      • The M-Score is calculated using the coefficients from Professor Beneish's original model.
    5. Post-Processing:

      • Infinite values resulting from division by zero are replaced with NaN.
      • The index is reset to turn 'Company' and 'Year' back into columns.
      • The final result includes only the essential columns for analysis.

    Key Points:

    • Dynamic Handling: The function adjusts whether it's processing a single company or multiple companies.
    • Missing Data: It gracefully handles missing data and computations that aren't possible due to insufficient data.
    • Scalability: Designed to handle datasets with varying years of data without modification.

    Conclusion:

    This function provides a robust and flexible way to calculate the Beneish M-Score using pandas, suitable for financial analysts and data scientists who need to detect potential earnings manipulation across different time frames and datasets.


    Note: The sample output provided is based on hypothetical data and is meant for illustrative purposes. Actual financial data should be used for meaningful analysis.