Common Size Balance Sheet Preparation
1. Prepare a common size balance sheet for the current and past year using the template I have provided for Bed Bath and Beyond Inc. and Pier 1 Imports, Inc. Express each item on the balance sheet as a percent of total assets using cell referencing. Please note that the first worksheet is for Bed Bath and Beyond Inc. and the 2nd worksheet is for Pier 1 Imports. You can use page 4-40 as a model, but you will only need to show 2 years of percentages. Round the percentages to 2 decimal places like exhibit 4B.2. You will be using the accounts as given in the Excel file Consolidated Balance Sheet that I have provided you. You will need to calculate using cell referencing percentages for 2019 and 2018 in the adjacent columns to the data given on the worksheet using cell referencing.
Answer the following questions below the common-size balance sheet for each company based on the most recent year.
a. What are the company’s two largest assets?
b. What are the company’s 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 past three years. Express each item on the income statement as a percent of net sales using cell referencing. You can use page 4-41 as a model. You will be using the accounts as given in the Excel file Consolidated Income Statement, and you will need to calculate percentages using cell referencing for 2019, 2018, and 2017 by setting up adjacent columns to the data given on the worksheet.
Answer the following questions below the common size income statement for each company.
a. Review the vertical analyses and provide two major differences over the three years and discuss patterns that emerge? You should be focusing on specific accounts and not subtotals or totals.
b. What are the company’s two largest major expenses for the current year?
c. Discuss the fluctuations in interest expense and income taxes. What are the cause of these changes?
d. Was the company more or less profitable when compared to the prior year? 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 and Comments worksheet I have set up in the template. I have provided the total assets and equity for 2017 on this worksheet for both companies. Provide the ratios with two decimal points and percentages should have two decimal points, too.
4. On the Ratio and Comments worksheet answer the following questions in the text box I have provided using the results of ratio analysis in #3 and 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.
a. Compare the two vertical analyses and look for two major differences over time and between the companies using your vertical analyses. Which company is more profitable? Why? Which statement are you using to make your assessment on profitability?
b. Is the profit margin similar for the two companies? Compare the percentage of profit margin over the past 2 years.
c. Explain how the Return on Equity (ROE) has changed over time? Explain how the profit margin, asset turnover, and financial leverage impact ROE. Include a discussion of changes in profit margin, asset turnover, and financial leverage. Do the changes make sense given the economic and competitive factors that affect the industry and the companies?
d. Explain what liquidity means. Which company is more liquid? Why? You must discuss the relevant ratio(s) that support your answer to this question from the work you completed in #3.
e. Explain what solvency means. Which company is more solvent? Why? You must discuss the relevant ratio(s) that support your answer to this question from the work you completed in #3.