Part 1.

Using Excel, prepare two trend (horizontal) analyses and a common-size (vertical) analysis for the income statements provided.

The companys income statements for six years (2012 through 2017) are presented on the sheet labeled Part 1 Combined Analysis.

Trend Analysis 1:

Prepare the first trend analysis (Trend Analysis 1) using 2012 as the base year and expressing the line items for each subsequent year relative to the base year amount. For example, 2013 revenues are indexed at 109.7 of 2012 revenues calculated as follows:

($20,862 $19,014) * 100 = 109.7

Use the number format and carry out the decimal to the tenths place (one place to the right of the decimal).

Trend Analysis 2:

Prepare the second trend analysis (Trend Analysis 2) showing the year-over-year percentage change in each line item. For example, 2013 revenues increased over 2012 revenues by 9.7% calculated as follows:

($20,862 - $19,014) $19,014 = 9.7%

Use the percentage format and carry out decimals to the tenths place (one place to the right of the decimal).

Common Size Analysis:

Prepare a commons size analysis for each year of income statement presented. When preparing common-size analyses for each years income statement, all line items should be expressed as a percentage of revenues.

When you have completed all three analyses for each years income statement, answer the questions that follow. Support your answers with the results of the three analyses you prepared.

Using the first trend analysis (indexed change versus base year), what is the percentage change in the companys reported revenue that has occurred from 2012 through 2017?

In general, has this company demonstrated consistency in its gross margin percentage performance from 2012 through 2017?

Based on the second trend analysis (year-over-year percentage change) there appears to be significant variability in the year-over-year changes in demand creation expense. Does the common-size analysis provide a different perspective and perhaps greater clarity in explaining changes in demand creation expense?

List the percentage of income before income tax (pre-tax income) the company recognized in income taxes each year (income tax income before income tax). List the percentage of revenues) the company recognized in income taxes each year (income tax revenues). In your opinion, which analysis is more useful in predicting the amount of income tax expense recognized by the company each year?

Based on the information available and the three analyses you prepared how would you characterize the companys net income performance from 2012 through 2017?

Part 2.

Using Excel, prepare common-size (vertical) income statements and balance sheets for the Albany Furniture Company and Burlington Crafters, two firms that operate in the furniture manufacturing industry. Although both companies are in the same industry they have different business models and derive their revenue through different distribution channels.

Albany Furniture Company and Burlington Crafters income statements and balance sheets can be found on the sheet labeled Part 2 Common Size Analysis. When preparing a common-size analysis for the income statement, all line items should be expressed as a percentage of net sales (net revenues). When preparing the common-size analysis for the balance sheet, all line items should be expressed as a percentage of total assets. Use the percentage format and carry out decimals to the tenths place (one place to the right of the decimal).

When you have completed the common-size income statements and balance sheets for both companies answer the following questions.

What is the absolute dollar amount and relative percentage of accounts receivable reported on each companys balance sheets?

What is the ratio of total liabilities to total assets for each company?

How do the companies capital structures compare? (Discuss how the companies finance their assets, for example relative percentages of debt vs. equity).

How do the companies absolute dollar amount of gross profit and relative gross margin percentages compare?

Additional information has become available that one of the companies is a furniture manufacturer that distributes through wholesale channels while the other is a furniture manufacturer that distributes through retail channels. Typically, retailers compare to wholesalers in the furniture manufacturing industry in the following ways:

Retailers generate relatively higher gross profit margins than wholesalers.

Retailers are required to invest relatively more in accounts receivable because of the extended payment terms they offer to consumers. Wholesalers usually carry ordinary trade accounts receivable.

Retailers desire to have stores in desirable locations which require a relatively greater investment in and property, plant and equipment than would be necessary for wholesalers.

Given the foregoing information, which company distributes through retail channels and which distributes through wholesale channels? (Support your answer with evidence generated by your financial statement analysis of the two firms)

Part 3.

Using the information provided in the table below, calculate the Altman Z-score for each of the companies. Use the template in the Excel sheet labeled Part 3 Altman Z-score for your calculations and answer the questions that follow.

($ millions)	Liverpool Company	Everton Incorporated
Working capital	21,854	(9,761)
Retained earnings	85,992	25,212
Earnings before interest and taxes	14,243	25,552
Market value of equity	194,772	188,867
Sales	67,224	126,862
Total assets	121,347	272,093
Total liabilities	56,521	185,77

What is the book value of the equity of each company?

How do you explain the difference between the book value and the market value of a companys equity?

Which of these two companies would you be more likely to grant a loan to? Please support your answer with the results of your Altman Z-score analysis.

Part 4.

Net Operating Profit After Tax

Using the income statement that follows, calculate the net operating profit after tax (NOPAT). Assume a 37% statutory tax rate. Be certain to show your calculations.

Net sales	$50,521
Cost of sales	33,194
Gross profit	17,327
Expenses:
Selling, general and administrative	12,244
Depreciation	1,523
Interest expense, net	1,400
Total expenses	15,167
Pre-tax earnings	2,160
Income tax provision	750
Net income	$1,410

Profitability Ratios

Use the financial information below to calculate Macys profitability ratios for 2017. Show your calculations.

2017 Sales	2017 Net Income	2017 NOPAT	2017 Net Operating Assets	2016 Net Operating Assets	2017 Stockholders Equity	2016 Stockholders Equity
$27,686	$1,335	$1,687	$11,145	$10,864	$6,051	$5,933

Compute the 2017 return on equity (ROE).

Compute the 2017 return on net operating assets (RNOA).

Compute the 2017 net operating profit margin (NOPM).

Compute the 2017 net operating asset turnover (NOAT).

Compute the 2017 nonoperating return.