Excel cell-formulas

Compute the following ratios for the latest year for all three companies in a new worksheet (called Ratios) in the same spreadsheet, using cell-formulas. If you do not use Excel cell-formulas, I will give you no credit:

1. Return on Assets
2. Return on Equity
3. Current Ratio
4. Long-term Debt to Equity
5. Total Asset Turnover
6. Profit Margin

For these ratios, use the formula in the book; do not use the formula used by Mergent, which uses Total Assets and Total Equity numbers, averaged over the year.

Answer the following questions (keep in mind that you’re answering as financial analysts, not simply as somebody who knows the mathematical relationship between numbers in a formula):

1. What is the relationship between your answers for Profit Margin, Total Asset Turnover and Return on Assets? Explain. Do not just give me the definitions of the three ratios, explain how they are related conceptually and mathematically, making reference to your calculations.
2. Explain, as best as you can, why the Return on Assets for the three companies differ, using the Dupont Model. Note that the Dupont model is not simply a mathematical equation. Use the latest year’s numbers to compare.
3. Plot the ROE for the three companies over the last five years, on the same graph. How would you describe the relative evolution over time of the ROE for the three companies. (The emphasis is on the word relative, that is relative to each other; in other words, don’t give me three different unrelated explanations.)
4. Can you explain the relative evolution of the companies’ ROE? I am not looking for a detailed explanation, just a reasonable argument that is plausible, given what you know of the companies, their place in the industry and the economic environment.

Make sure you look at the Retail Apparel Industries case before you attempt this case!