PLease put in excel format
Excel Assignment #3
Credit risk or default remains the primary risk exposure of financial institutions. Typically, the assets held by financial institutions are issued by various entities that have non-zero probabilities of default, for example, sovereigns, municipalities, NGOs, corporations (for profit and not-for-profit), and individuals. While there are several acceptable approaches to estimating default risk, one of the most popular is the Altman Z-Score.
The Altman Z-Score was developed by Edward Altman, a professor at the Leonard N. Stern School of Business of New York University. The purpose of the Z-Score Model is to measure a company’s financial health and to predict the probability that a company will collapse within 2 years. It is proven to be very accurate to forecast bankruptcy in a wide variety of contexts and markets. Past studies report that the model has 72% – 80% reliability of predicting default.
While there are several versions of the Z-score model, the most popular approach is:
Z = 1.2X1 + 1.4X2 + 3.3X3 + 0.6X4 + 1.0X5, where
X1 = Working Capital / Total Assets.
X2 = Retained Earnings / Total Assets.
X3 = Earnings Before Interest and Taxes / Total Assets.
X4 = Market Value of Equity / Book Value of Total Liabilities.
X5 = Sales / Total Assets.
The objective of Excel Assignment #3 is to estimate the predict the probability of default for two companies. This will entail the following tasks:
Obtain the necessary financial data for each company from EDGAR
Develop an Excel-based, Z-Score calculator for each firm.
Provide a summary of your results
