Buatkan rumus Beneish M-Score di python, harus bisa dinamis, misalnya ada 1 tahun maka…
- 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!
- 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_dfExample 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.438640Explanation:
The function
calculate_beneish_m_scorecomputes 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:
-
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.
-
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.
-
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.
-
Computing the M-Score:
- The M-Score is calculated using the coefficients from Professor Beneish's original model.
-
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.
-