Question
Part 2 Relates to Module 4 Requirements: Download and Use the Excel Workbook Template I am providing for GAP Inc. (January 28, 2023) and TJX
Part 2 Relates to Module 4 Requirements: Download and Use the Excel Workbook Template I am providing for GAP Inc. (January 28, 2023) and TJX Companies Inc. (January 28, 2023) to complete Part 2 Financial Analysis Case. Type you name in Cell A1 on the first worksheet. The last worksheet in the template has the grading rubric for Part 2. Please make sure you have read over Module 4 and Appendix 4C. I have videos I developed as an aid for those that need some Excel help. These videos and other support material are posted in the Financial Analysis Case area on the left panel of our course. 1. Prepare a common-size balance for the current and past year, for both companies. Use cell referencing to calculate the common size percentages and round to two decimal places. Columns have been set up for you to enter your work. Pages 4-40 and 4-41 in the textbook shows an example of a common size balance sheet. Answer the following questions below the common-size balance sheet for each company based on the most recent year. a. What are the companys two largest assets? b. What are the companys two largest liabilities?c. What percentage of total assets is financed by owners?d. What percentage of total assets is financed by creditors? 2. Prepare a common-size income statement for the current and prior two years, for both companies. Use cell referencing to calculate the common size percentages and round to two decimal places. Columns have been set up for you to enter your work. Page 4-40 in the textbook shows an example of a common size income statement. Answer the following questions below the common size income statement for each company. a. Review the vertical analysis and provide the two largest expenses for the current year. Explain the trend over the three years for these two expenses and discuss how these costs affects net earnings.b. Discuss the fluctuations in interest expense. What are reasons that could cause changes in interest expense? You need to provide specific causes for the applicable company that relates specifically to the interest expense. c. Discuss the fluctuations in income tax expense. What are reasons that could cause changes in income tax expense? You need to provide specific causes for the applicable company that relates specifically to the income tax expense. d. Was the company more or less profitable when compared to the prior year? Using the specific income statement ratios or amounts, explain what caused the change in income from the prior year. 3. Compute the ratios for the current and past year for both companies on the Ratio worksheet I have set up in the template. I have provided the total assets and equity for 2021 at the top of this worksheet for both companies, so you can determine the average for Return on Equity and Asset Turnover for the past year. Provide the ratios with two decimal points and percentages should have two decimal points, too. You must use cell references from the applicable worksheets to earn credit on your ratio analysis. You will not earn any credit if you type in the numbers and do not reference the other worksheets that contain the data you need to calculate the applicable ratios. Guidance for computing ratios in #3 above: 1. Current ratio=Current Assets/Current Liabilities 2. Liabilities-to-equity=Total liabilities/Stockholders Equity 3. Times interest earned=Earnings before Interest Expense and taxes/Interest Expense. ****Use Interest expense if given. If the company does not break out interest expense, you will have to use net interest. Also, please note that you should be using operating income if given, since we want net of any other nonoperating income or expenses. 4. Return on Equity=Net(loss) earnings/Average Stockholders Equity. 5. Profit Margin=Net(loss) earnings /Sales 6. Asset Turnover=Sales/Average Total Assets 7. Financial Leverage=Average Total Assets/Average Stockholders Equity 8. Redo your Return on Equity calculation by calculating Profit Margin*Asset Turnover*Financial Leverage. Hint: use your solution for #5, 6, and 7. Your answer should agree with your #4 answer. ***Please note that you can use Net Sales when Sales are part of the formula. ***When calculating averages for stockholders equity or total assets, you should use a simple average which can be calculated by using the Average function (AVG) or calculating= ((current amount plus last years amount)/2). Make sure you are using parenthesis if calculating simple average, so that Excel knows to add the numerator and then divide by 2. ***You must show your calculations by using cell references to earn credit. 4. On the Analysis worksheet, answer the following questions in the text box I have provided using the results of ratio analysis and common-size vertical analyses to explain your answer. You grade is based on you applying financial analysis to the specific results you have determined throughout part 2. I am not looking for generic comments you have found on a website. Make sure you review the ROE Disaggregation of DuPont Analysis in Module 4 and Appendix 4C.
a. Compare the common-size balance sheet statements of each company. Explain and identify two major differences over time and/or between the companies using the common-size balance sheet statement analyses. You need to provide at least two common-size percentages for each company in your comparison comments. Explain why you picked these two items. b. Which company is more profitable? Discuss specifically which accounts impacted profitability. Provide at least two common-size percentages for each company in your comparison comments. Which statement are you using to make your assessment on profitability? Compare the percentage of profit margin ratio over the past 2 years. Have there been changes in this ratio from the past year? c. Explain how the Return on Equity (ROE) has changed over time? Explain how the profit margin, asset turnover, and financial leverage impact ROE. Provide the amount of the ratios for each of the companies in your explanation. Include a discussion of changes in profit margin, asset turnover, and financial leverage. Explain what each ratio is measuring. You need to identify the specific company ratios you computed in #3. d. Explain what liquidity means. Which company is more liquid than the other company is? Why? You must discuss the relevant ratio(s) that support your answer to this question from the work you completed in #3 and provide the specific ratio amount you used to analyze liquidity. e. Explain what solvency means. Which company is more solvent? Why? You must discuss the relevant ratios that support your answer to this question from the work you completed in #3 and provide the specific ratios amount you used to analyze solvency.
***You must use cell references to earn credit. | ||||
TJX Companies Inc. | Dollar amounts in Millions $ | Percentages | ||
Common-Size Balance Sheet | Jan. 28, 2023 | Jan. 29, 2022 | Jan. 28, 2023 | Jan. 29, 2022 |
Current assets: | ||||
Cash and cash equivalents | $ 5,477 | $ 6,227 | ||
Accounts receivable, net | 563 | 518 | ||
Merchandise inventories | 5,819 | 5,962 | ||
Prepaid expenses and other current assets | 478 | 437 | ||
Federal, state and foreign income taxes recoverable | 119 | 115 | ||
Total current assets | 12,456 | 13,259 | ||
Net property at cost | 5,783 | 5,271 | ||
Non-current deferred income taxes, net | 158 | 185 | ||
Operating lease right of use assets | 9,086 | 8,854 | ||
Goodwill | 97 | 97 | ||
Other assets | 769 | 795 | ||
Total assets | $ 28,349 | $ 28,461 | ||
Current liabilities: | ||||
Accounts payable | 3,794 | 4,465 | ||
Accrued expenses and other current liabilities | 4,346 | 4,245 | ||
Current portion of operating lease liabilities | 1,610 | 1,577 | ||
Current portion of long-term debt | 500 | 0 | ||
Federal, state and foreign income taxes payable | 55 | 181 | ||
Total current liabilities | 10,305 | 10,468 | ||
Long-term liabilities: | ||||
Other long-term liabilities | 919 | 1,015 | ||
Non-current deferred income taxes, net | 127 | 44 | ||
Long-term operating lease liabilities | 7,775 | 7,576 | ||
Long-term debt | 2,859 | 3,355 | ||
Commitments and contingencies (See Note N) | ||||
Total long-term liabilities | 11,680 | 11,990 | ||
Shareholders equity | ||||
Common stock, authorized 1,800,000,000 shares, par value $1, issued and outstanding 1,155,437,908 and 1,181,188,731 shares, respectively | 1,155 | 1,181 | ||
Additional paid-in capital | 0 | 0 | ||
Accumulated other comprehensive (loss) income | (606) | (687) | ||
Retained earnings | 5,815 | 5,509 | ||
Total shareholders equity | 6,364 | 6,003 | ||
Total liabilities and shareholders equity | $ 28,349 | $ 28,461 | ||
Answer the following questions based on the most recent year. | ||||
a. What are the companys 2 largest assets? | ||||
b. What are the companys 2 largest liabilities? | ||||
c. What percentage of total assets is financed by owners? | ||||
d. What percentage of total assets is financed by creditors? | ||||
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started