import pandas as pd

Step 1: Load the Excel file

input_file = 'Corporate Fin. Assignment.xlsx' # Replace with your actual file name output_file = 'Financial_Ratios_Calculations.xlsx' # The output file name

Load sheets

income_statement = pd.read_excel(input_file, sheet_name='Income Statement & Balance shee') financial_ratios = pd.read_excel(input_file, sheet_name='Financial Ratio')

Step 2: Merge both sheets on 'Company' and 'Year'

data = income_statement.merge(financial_ratios, on=['Company', 'Year'])

Step 3: Calculate ratios

data['Calculated_Current_Ratio'] = data['Current Assets'] / data['Current Liabilities'] data['Calculated_Quick_Ratio'] = data['Current Assets'] / data['Current Liabilities'] # No inventory data data['Calculated_Gross_Profit_Margin'] = (data['Gross Profit'] / data['Revenue']) * 100 data['Calculated_Net_Profit_Margin'] = (data['Net Income'] / data['Revenue']) * 100 data['Calculated_ROA'] = (data['Net Income'] / data['Total Assets']) * 100 data['Calculated_ROE'] = (data['Net Income'] / data['Shareholder Equity']) * 100 data['Calculated_Debt_to_Equity'] = data['Total Liabilities'] / data['Shareholder Equity'] data['Calculated_Asset_Turnover'] = data['Revenue'] / data['Total Assets'] data['Calculated_Interest_Coverage'] = data['Operating Income'] / data['Interest Expense'] data['Calculated_EPS'] = data['Net Income'] / data['Average Shares Outstanding'] data['Calculated_PE_Ratio'] = data['Market Price per Share'] / data['Calculated_EPS']

Step 4: Save the results to an Excel file

output_columns = [ 'Company', 'Year', 'Current Ratio', 'Calculated_Current_Ratio', 'Quick Ratio', 'Calculated_Quick_Ratio', 'Gross Profit Margin (%)', 'Calculated_Gross_Profit_Margin', 'Net Profit Margin (%)', 'Calculated_Net_Profit_Margin', 'Return on Assets (ROA) (%)', 'Calculated_ROA', 'Return on Equity (ROE) (%)', 'Calculated_ROE', 'Debt-to-Equity Ratio', 'Calculated_Debt_to_Equity', 'Asset Turnover Ratio', 'Calculated_Asset_Turnover', 'Interest Coverage Ratio', 'Calculated_Interest_Coverage', 'Earnings per Share (EPS)', 'Calculated_EPS', 'Price to Earnings (P/E) Ratio', 'Calculated_PE_Ratio' ]

Select relevant columns and save

data[output_columns].to_excel(output_file, index=False)

print(f"Financial Ratios have been saved to {output_file}")