Aldorado Company is a wholesale aluminum distributor which purchases aluminum in carload lots and sells to several thousand aluminum users. The nature of the aluminum business requires that the company maintain large inventories to take care of customer requirements in the event of strikes or other delays. Assume that Aldorado Company is put for sale at the end of the current year, assumed today. What should be the fair market value $($FMV$)$ for Aldorado?

In examining records from for the past twenty years, the company found consistent relationships among the following accounts as a $$percent of sales$$:

Sales $100\%$

Current Assets $22\%$ of Sales

Net fixed Assets $65\%$ if Sales $<=$ $$110$ million; $70\%$ otherwise

Accounts Payable $10\%$ of Sales

Other Current Liabilities $5\%$ of Sales

Profit Margin $(=$NI$/$Sales$)16\%$ of Sales

Interest Rate $7\%$

Tax Rate $21\%$

Number of shares outstanding $2,000,000$

Market price per share today $$65$

The company$$s sales for the current year were $$90$ million. The company expects to grow by $$9$ million per year over the next $5$ years $($year $1-$year $5).$ The company wants to project its financial statements, financial ratios and financing requirements for each of the next $5$ years, assuming that the projected sales levels are achieved. Assume further that the company pays out $60$ percent of earnings as dividends.

$1.$ Construct proforma balance sheets for the end of each of the next $5$ years $($year $1-$year $5),$ assuming that external capital needs are financed $40$ percent by issuing new long$-$term debt, and $60$ percent by selling new common stock.

$2.$ Using the book values information in your answer to question $(1),$

$-$ Calculate the following ratios: return on equity $($ROE$),$ return on assets $($ROA$),$ return on capital $($ROC$),$ economic value added $($EVA$),$ operating profit margin $($OPM$),$ assets turnover $($TAT$),$ long$-$term debt $($LTD$)$ ratio, long$-$term debt$-$equity $($D$/$E$)$ ratio, total debt $($TD$)$ratio$,$ times interest earned ratio $($TIE$),$ net working capital to total assets, current ratio $($CR$),$ and sustainable growth rate. Note: all ratios formulas are shown in Table $2.$

$-$ Conduct a trend analysis for the calculated ratios over the next $5$ years.

$-$ Comment on each ratio.

$3.$ Using the information in your answer to question $(1),$ calculate net cash flows $($NCF$)$ at the end of each of the next five years $($including terminal value at the end of year $5).$ Assume that all fixed assets are depreciated using the straight line method. Note that additional capital expenditures are equal to changes in fixed assets from one year to another.

Note: NCF $=$ EBIT $$ Taxes $+$ Depreciation $(+$ or $)$ Changes in NWC $(+$ or $)$ Additional Capital Expenditures. This is CFFA.

$7.$ Calculate the change in the acquirer$$s wealth as a result of the acquisition. Assume the acquirer of Aldorado will pay exactly the price as observed in the market at the end of this year. Would you recommend this acquisition?

Hint: use Tables $6$ and $7$ in the attached EXCEL sheet to answer questions $6$ & $7.$