Assingments % - Ratio Analysis of [Company Name]: Performance Evaluation Using Mergent Database and Excel

Using the company that you chose in this week’s Discussion, develop a ratio analysis using the Mergent Database and Excel.
Create an Excel worksheet that lists all downloaded (+/- 20) financial ratios, the calculations, and the purpose of each ratio. (This is general, and not specific to your company’s performance.)
Include the following headings in your worksheet.
ave your worksheet.
If you have not already done so, follow the instructions in the Ratios section on page 4 of the Mergent Online Database Instructions.docx Open this document with ReadSpeaker docReader Download Mergent Online Database Instructions.docxOpen this document with ReadSpeaker docReaderto download the Ratios worksheet for your company.
For the ratio analysis, use the worksheet you created and the Ratios worksheet that you saved from Mergent.
How did your company perform over time in regard to the following ratios? (This is separate from above, and specific to your company’s performance.)
Current Ratio
Inventory Turnover – if your company does not have inventory, use Receivables Turnover or Accounts Payable Turnover
Total Debt to Equity
Return on Equity (ROE)
Book Value Per Share
Document your analysis on the Mergent Ratio Worksheet

Struggling with where to start this assignment? Follow this guide to tackle your assignment easily!

In this assignment, you’ll be conducting a ratio analysis for your chosen company using data from the Mergent Database and Excel. Here’s a clear, step-by-step guide to help you organize your work and complete the ratio analysis effectively.

Step 1: Download the Necessary Financial Data

Access the Mergent Database: Begin by logging into the Mergent database using the instructions provided in the Mergent Online Database Instructions.docx file.
Download the Ratios Worksheet: Follow the step-by-step instructions in the guide to download the financial data specific to your company, including the ratio worksheet.
Gather Financial Statements: Download your company’s balance sheet, income statement, and other relevant financial data for the most recent years (at least two years).

Step 2: Set Up Your Excel Worksheet

Create a new Excel worksheet to organize your financial ratio analysis. Use the following headings to structure your sheet:

Financial Ratios: List the names of the ratios you’ll be calculating.
Formulas/Calculations: Provide the formulas to calculate each ratio.
Purpose: Briefly describe what each ratio measures and why it’s important for financial analysis.
Year 1: Input the values for the first year from the downloaded financial data.
Year 2: Input the values for the second year.
Year 3 (if available): Input the values for the third year (if available).

Step 3: List and Calculate Financial Ratios

Below is a general list of approximately 20 financial ratios that you can include in your Excel sheet. Each ratio will have a corresponding formula and purpose. You’ll need to calculate these ratios using the downloaded data for your company.

Key Ratios to Include (and their formulas):

Current Ratio
Formula: Current Assets ÷ Current Liabilities
Purpose: Measures the company’s ability to cover short-term liabilities with short-term assets. A ratio > 1 indicates good short-term financial health.
Quick Ratio (Acid Test)
Formula: (Current Assets – Inventory) ÷ Current Liabilities
Purpose: A more stringent measure of liquidity, as it excludes inventory, which may not be easily liquidated.
Inventory Turnover
Formula: Cost of Goods Sold ÷ Average Inventory
Purpose: Indicates how quickly the company is selling and replacing inventory. A higher turnover means efficient inventory management.
Receivables Turnover (Use if your company doesn’t have inventory)
Formula: Net Credit Sales ÷ Average Accounts Receivable
Purpose: Measures how often the company collects its average accounts receivable during a period. A higher number indicates effective credit management.
Accounts Payable Turnover (Alternative if Receivables is not applicable)
Formula: Net Credit Purchases ÷ Average Accounts Payable
Purpose: Shows how quickly the company pays its suppliers. A lower ratio could indicate slower payments, while a higher ratio shows faster payment.
Total Debt to Equity
Formula: Total Debt ÷ Total Equity
Purpose: Measures the company’s financial leverage, showing the ratio of debt to equity financing. A high ratio may suggest higher financial risk.
Debt to Total Assets
Formula: Total Debt ÷ Total Assets
Purpose: Indicates what portion of the company’s assets is financed by debt.
Gross Profit Margin
Formula: (Sales – Cost of Goods Sold) ÷ Sales
Purpose: Measures the percentage of revenue that exceeds the cost of goods sold, showing the efficiency of production.
Operating Profit Margin
Formula: Operating Income ÷ Sales
Purpose: Measures the percentage of profit a company makes from its operations before accounting for taxes and interest.
Net Profit Margin
Formula: Net Income ÷ Sales
Purpose: Indicates the overall profitability of the company, showing how much profit is made for every dollar of sales.
Return on Assets (ROA)
Formula: Net Income ÷ Average Total Assets
Purpose: Measures how efficiently the company uses its assets to generate profit.
Return on Equity (ROE)
Formula: Net Income ÷ Average Shareholders’ Equity
Purpose: Shows the return on shareholders’ investments, helping assess profitability in relation to equity.
Return on Investment (ROI)
Formula: (Net Profit ÷ Cost of Investment) × 100
Purpose: Measures the gain or loss generated relative to the investment’s cost.
Earnings Before Interest and Taxes (EBIT) Margin
Formula: EBIT ÷ Sales
Purpose: Measures the operating profitability of a company before interest and taxes are subtracted.
Interest Coverage Ratio
Formula: EBIT ÷ Interest Expense
Purpose: Measures the company’s ability to meet its interest obligations from its operating income.
Cash Conversion Cycle
Formula: Days Inventory Outstanding + Days Sales Outstanding – Days Payable Outstanding
Purpose: Measures the time it takes for the company to convert its inventory into cash.
Dividend Yield
Formula: Dividends per Share ÷ Market Price per Share
Purpose: Shows the return on investment through dividends for shareholders.
Price-to-Earnings (P/E) Ratio
Formula: Market Price per Share ÷ Earnings per Share
Purpose: Measures the price investors are willing to pay for each dollar of earnings, indicating market expectations for growth.
Price-to-Book (P/B) Ratio
Formula: Market Price per Share ÷ Book Value per Share
Purpose: Compares the market value to the book value, showing how much investors are willing to pay for each dollar of net assets.
Dividend Payout Ratio
Formula: Dividends ÷ Net Income
Purpose: Shows the proportion of earnings that are paid out as dividends, indicating how much profit is retained for reinvestment.

Step 4: Analyze Your Company’s Performance

Now, using the ratios from the Mergent database and your Excel worksheet, evaluate your company’s performance over time:

Current Ratio: Determine if your company has sufficient short-term assets to cover its liabilities.
Inventory Turnover or Receivables Turnover: Examine how well the company is managing its inventory or receivables (depending on what data is available).
Total Debt to Equity: Evaluate the company’s financial risk based on its debt level in comparison to equity.

For each of these ratios, note any significant trends or shifts between years. Are there any improvements or declines in performance? Use the data from Mergent to compare year-over-year changes.

Step 5: Prepare Your Written Analysis

In your written analysis, make sure to:

Summarize the key ratios for your company, focusing on the current ratio, inventory/receivables turnover, and debt-to-equity ratio.
Analyze the trends in these ratios over time. Are there any concerning fluctuations, or is the company improving its performance in any areas?
Interpret the results in the context of the company’s overall financial health. How do these ratios relate to industry standards or competitor performance?

Step 6: Review and Submit

Once you’ve completed your ratio analysis and written summary, review your work to ensure it meets all assignment requirements:

Ensure that your Excel worksheet is properly formatted and that each ratio has its calculation and purpose clearly stated.
Proofread your written analysis for clarity and accuracy.
Submit the Excel worksheet along with your written analysis.

By following these steps, you’ll be able to complete a comprehensive ratio analysis of your company, which will help you evaluate its financial health and performance effectively. Good luck!