Introduction to Financial Management
- Due date: October 14th, 2017 (Submit your Excel file(s) before 11:59 pm on October 14thvia the Project 1 tab on Blackboard.)
- One single submission is permitted. Please make sure that your work is complete and correct before submitting your Excel file(s) on Blackboard.
- Reference: Gap Inc Financial Statements and Ratios.xlsx
This project is designedfor you to analyze different aspects of a firm’s operations. You will learn how to use internet resources (the Mergent Online and Morningstardatabases), and develop your quantitative and analytical skills.
- Chooseanon-financialpublicly traded company from the Mergent Online database.
A publicly traded company means that it has common equity traded in a stock exchange.Download its balance sheet and income statement for the most recent 5 years to a spreadsheet from Mergent Online. Put these two financial statements in two separate worksheets in an Excel file.
- Create another worksheet in the Excel file for financial ratios.
- Please calculate all of the following financial ratios in the sections of Profitability, Financial Health, and Efficiency Ratios for the most recent five years (2012-2016) in the Excel spreadsheet. The followings are the ratios in each section:
Profitability: Gross margin, operating margin, EBT (Earnings Before Taxes) margin, return on assets, and return on equity.
Financial Health: Current ratio, financial leverage [defined as =total assets/total stockholder’s equity, it is the equity multiplier you learn from the text], and debt/equity (defined as long-term debt / total stockholders’ equity).
Efficiency Ratios: Days sales outstanding, inventory turnover (defined as “Cost of Good Sold / Average Inventory”), fixed asset turnover, and asset turnover (total assets turnover).
- When you work on the calculations, the formula you use in the Excel spreadsheet should reference the data from the Income Statement or Balance Sheet.
Excel has to know the location and the name of the file, the name of the worksheet, and the cell address. Please see my example posted on Blackboard(Gap Inc Financial Statements and Ratios.xls).Please round the number to two decimal places. The profitability ratios should be expressed in percentage. You will be asked to re-work on the project if you do not use reference cells to calculate financial ratios.
- If you are not sure whether your ratios are computed correctly, you can compare the ratios posted in Morningstar database.
Well, they should be close or exactly the same. The small difference may be due to the fact that Morningstar calculates the ratio using the average value of an accounting item over the past 2 years. On Morningstar, click on “Key Ratios” tab and scroll down. You will see the Profitability, Financial Health, and Efficiency Ratios. Click on these tabs, and compare the ratios you compute for the past 5 years with those posted on Morningstar.
- Please draw line or bar charts of the ratios over the past 5 years in Excel. You should have three charts(One chart for Profitability, one chart for financial health, and one chart for efficiency ratios).Well, you want to make them look nice and neat. In fact, you are doing the trend analysis in this part now.
- Please use the Z-score model to predict whether this company will experience financial distress within the next year. The Z-score model was developed by Edward I. Altman (1968).Regular financial ratios have deficiencies or limitations to evaluate a firm’s operating performance. Altman (1968) uses a few modified ratios and a technique called mutliple discriminant analysis to predict whether a firm is likely to experience severe financial distress. His Z-score model for publicly traded companies is defined as:
net working capital = current assets – current liabilities
EBIT is earnings before interest and taxes
The most recent year’s market capitalization can be found via the “Company Financials” tab on Mergent Online. It is on the upper right portion of the webpage called “Market Cap”. Please convert the market capitalization to thousands because the accounting items reported in the income statement and balance sheet are in thousands. Altman (1968) shows that Z-scores are broadly classified into three ranges associated with three scenarios.
Bankruptcy predicted within one year
Financial distress, possible bankruptcy
No financial distress predicted
Please compute the Z-score for the company you choose in an Excel worksheet, and discuss whether this company will experience financial distress in the upcoming year. For this part, you can collaborate with your group members. However, I want to see calculations of the Z-score shown in your Excel file. Again, your calculations of ,…, , and Z-score should reference to the data in the Income Statement and Balance Sheet in the Excel file.
- Please put your name next to the ratios you have calculated and submit your Excel file via the Project 1 tab on Blackboard. The quality of your work counts. Please keep in mind that one single submission on Blackboard is permitted. Do it until you are satisfied with your work.
To access to the Mergent Online database, please go to library’s homepage. Search for it from the Databases and Database List (Alphabetically). Once you access to it, you can type in the ticker symbol or the company name in the lower left box. Then click on “Go.” It shows a bunch of information for the company. Click on “Company Financials.” Use the default dropdown menu for “annual,” “Balance sheet,”and “5 Years/Quarters.” Click on the Excel icon to download the 5-year balance sheet. Then you switch to download the income statement. Change from “Balance sheet” to “Income statement,” and click on “Refresh.” Then click on the Excel icon to download the 5-year income statement. Please organize the balance sheet and income statement in two separate worksheets in an Excel file.Please also create a worksheet named Financial Ratios and start to work on the calculations. I posted an example of financial ratios I calculated for Gap Inc. on Blackboard. The company you pick has to be different from my example!!
You can access to the Morningstar database from library’s databases. On the homepage of Morningstar, you can type in the ticker symbol or the company name in the upper right box and press ENTER from your keyboard. Morningstar will list several companies similar to the keyword you type. If you have no idea of what company you are going to analyze, click on “Stock Favorites”. Morningstar lists out quite a few stocks (hot stocks) equity analysts suggest.
Morningstar shows the company’s information from the “Quote” tab.To view the financial ratios, please click on “Key Ratios” on the top panel. Scroll down and you can see the ratios from “Profitability”, “Financial Health”, and Efficiency Ratios” tabs.
For details, please see E. I. Altman, “Financial Ratios, Discriminant Analysis and the Prediction of Corporate Bankruptcy”, Journal of Finance, September 1968, pp. 589-609.