Aldorado Aldorado Company is a wholesale whominum distributer which has alusi in carload lots and wills to several thousandumises. 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 you, and today. What should be the fair market value (FMV) for Aldorado? In camining records from for the past twenty years, the company found sonotondationships month. ang contosalon des 10046 Current Asset 22% of Sales Net fixed Assets 65% if Sales SS110 million, 70% otherwise Accounts Payable 10% of Sales Other Current Ladies 5 of Sales Profit Margin (-NI Sales) 16% of Sales Interest Rate 746 Tax Rate 219 Number of shares outstanding 2,000,000 Market price per share today $65 The company's sales for the current year were 590 million. The company expects to grow by S9 million per your 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 years, assuming to the projected sales levels are achieved. Assume further that the compuy pays out 60 percent of earnings as dividends QUESTIONS (50 Paints Construct proforma balance sheets for the end of each of the next years or your suming that external capital and are financed 40 percent by issuing new long-term debt and 60 percent by selling new common stock Hint: use Tables 1A, 18 and 1 In the attached EXCEL sheet to solve question and prepare necessary data for other questions 2. (15 Points) Using the book values information in your answer to question (I). - Calculate the following radiosteturn 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 (DE) ratio, total debt (ID)ratio, times interest corned ratio (TIE). net working capital so 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 Hint: use Table in the attached EXCEL sheet. 3.(15) Using the information in your answer to question (), calculate net cash flow (NCF) at the end of each of the next five years (including terminal value at the end of your 5). Assume that all fixed assets are depreciated using the stright line method. Note that additional capital expenditures are equal to changes in fissed assets from one year to another Note: NCF-EBIT - Taxes + Depreciation (+ or --> Changes in NWC + or -) Additional Capital Expenditures. This is CFFA. Hint: use Table in the attached EXCEL sheet (15 Point) Using the DCF method, what is the NPV for And Here are some hints - Use the NCF calculated in question (3) as your net cash flows for each of the next 5 years - Assume that the risk free rate is 5.6%, required market rate of return is 11.6%, and Aldorado (sets) beta is 14 Assume that RADR (I... WACC) will remain constant for the next five years. - Use Gordon model (V-CF1/-) to calculate the terminal value of Aldorado at the end of this year, ie, today For that, assume a permanent constant growth rate in NCF of 4% starting year I. - Calculate Aldorado NPV. Assume initial investment equals to book value of total assets minus current liabilities at the end of the current year 5. (5 Points) What is the IRR for Aldorado? Is this IRR acceptable? Hint use Tables 4 and 5 in the attached EXCEL sheet to answer questions 4 & 5. 6.5 Point Calculate the new price per share as a result of your NPV calculations in gestion (Point) cacuite the change in the acquirer's wealth as a result of the acquisition. Assume the equirer ol 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 18 (15 Pointochene se waghod average cost of capital for to the next years. As comment on changes in WACC during the same period. Is there an optimal (minimal) WACC7 Hints: WACC - Wa.Kd(1-Te) + We Kel. Where Wd - Long Term Debu(Total Equity + LTD"(1-Te) We - Total Equity/Total Equity + LTD*(1-Te) After Tax LTD-LTD"(1-TC) After Tax D/E Ratio - After Tax LTD/Total Equity Assume Beta of Debt (B) - 0.07 and it remains constant throughout the next five years regardless of the change in leverage Unlevered Cost of Equity (KeU) - 1.41 and it remains constant throughout the next five years La cred Cost of Equity (Kel.) - 1.70 in year 1, and it will change with the change in debt Equity Beta (fel) eu (BU-BY-(1-Te)D/E) Use CAPM to calculate cost of equity (Kel.) 9.(10 Points) What is the optimal capital structure mix for Aldorado? Toward this end: - Calculate the value of the levered firm Aldorado and share price for each of the next 5 years. Comment on changes in firm value and share price during the same period. Is there an optimal (maximal) value? Compare the above results to the changes in WACC as calculated in question 8. Can you reconcile the discrepancy? Hint: the firm value is the NPV of the firm as calculated in question 4. 10. (5 Points) Calculate the new number of outstanding shares and new book value per share for each of the next 5 years (year 1-year 5). Hint 1: the increase in outstanding shares increase in common stock account divided by last year share price. Hint 2: use Tables 8-10 in the attached EXCEL sheet to answer questions 8,9 & 10. NOTE: You will be expected to turn in printed outputs similar in format to what are presented on next pages. Tables are numbered after above questions. All calculations should use spreadsheet formulas. An EXCEL sheet is attached to ease your work. Aldorado Spreadsheet TABIA. Alorado Company PRO FORMA Balance Sheet For the Net Years Thesand of Dallars) Year 1 Yak2 Year 3E Year 4E Year Sales Current Assets Net Fixed Ass Toul Asset YASH Year o 90,000 19,800 31.500 78100 9.000 4.500 13.500 Accounts Payable Other Current Liabilities Total Current sites LTDebt Common Stock Retinod Farnings Total Equity 16,000 28,800 20.000 78.100 1,120 $ Interest S1120 (796 borrowing rate) Depreciation is 20% of Fixed Assets Table 18. Aldorado Company PRO FORMA Income Statement For the Next 5 Years (Thousand of Dollars) Year Year 1E Year 28 90,000.00 NA NA NA NA NA NA Year 3 Year 45 Year 5 Year Sales Cost of Goods Sold Selling & Administrative Expenses Depreciation Famings Before Interest & Taxes NA NA NA NA NA NA NA NA S Interest $1120 (1.0.27borrowing rate) Tares (2196) Net Income (PM-16%) NA NA NA . NA NA NA Dividends (60% Payout Ratio) New Additional Retained Earnings (40% Retention Ratio) Table 1C. Allorado Company Additional Calculates For the Next Years Thousand of Dollars) Year 18 Year YG BE Year Year NA NA NA NA Year Change in Current AEG Change in Net Fixed Assets Change in Total Asset Change in Current Liabilities New Toul Financing Needs (TEN) New Additional Retained Earnings New Extemal Financing Neede EFN) New Audio Extemal Debt New Additional External Equity i... Common Stocks) 1533 3131313 NA NA NA Year JE Year 4 Year SE Financial Ratio Excformance Measures Market Value Added Marke-o-Book Ratio Table 2. Aldorado Company Financial Ratio Analysis Ratio Formula Year 1E Year 28 market value of equity-book value of equity market value of equity book value of equity net income /equity Profitability Measures Return on Equity (ROE) Return on Assets (ROA) Return on Capital (ROC) (net income interesITA - (not income interest) (long term debt equity) net income (cost of equity RADR equity) EVA (not incontro alles TA start of you Operatin Profit Marin Emdes MESES Asset TV Let Me Long Term Debat LaTerm Daboy Radio Total Debt Ratio Times Interested Laski Mies Nat Working Capital TA Current Ratio Grethe Measure Sustainable Growth long term debelong term dit Lang term de BENOITA FRIT -NWCTTA CA/CL - (1-dividend payout ratio) "ROE Tab Aldorado CPEDY Projected NCP Cakulaties ( hr Your Year E Year Yaar 90,000 NA NA NA NA Year Sales EHIT Taxes Depreciation (2013) Additional (Change) Net Working Capital Additional (Change) Capital Expendituros NCFCCCFFA) Year SE 3533 NA NA Tables 4 & 5. AMorado Company NPV & IRR Cakulations (Thousand of Dollars) Calculation of RADR (LG, WACA Terminal Value NA Initial Investment NA NCF. Year 1 NA NA NA NCTA Yar 2 INCHAY 3 INCHAYear NCHY Toul NCF NPV (thousand of dollars) IRR Table 6AMorado Company Share PriCalculation New Price per Share Calculation Change in the Acquirer's Wealth Tables 8,9 & 10. Aklorado Company - Capital Structure Calculations 0.07 0.07 0.07 0.07 0,07 0.07 Cost of Debt Afler Tax Beta of Debt Long Term Debt (LT) After Tax LTD Equity After Tax LTD Ratio (Wd) Afler Tax Equity Ratio (We) Afer Tax Debt Equity Ratio Beta of Unlevered Equity fleu 1.41 1.41 1.41 1.41 1.41 Aldorado Aldorado Company is a wholesale whominum distributer which has alusi in carload lots and wills to several thousandumises. 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 you, and today. What should be the fair market value (FMV) for Aldorado? In camining records from for the past twenty years, the company found sonotondationships month. ang contosalon des 10046 Current Asset 22% of Sales Net fixed Assets 65% if Sales SS110 million, 70% otherwise Accounts Payable 10% of Sales Other Current Ladies 5 of Sales Profit Margin (-NI Sales) 16% of Sales Interest Rate 746 Tax Rate 219 Number of shares outstanding 2,000,000 Market price per share today $65 The company's sales for the current year were 590 million. The company expects to grow by S9 million per your 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 years, assuming to the projected sales levels are achieved. Assume further that the compuy pays out 60 percent of earnings as dividends QUESTIONS (50 Paints Construct proforma balance sheets for the end of each of the next years or your suming that external capital and are financed 40 percent by issuing new long-term debt and 60 percent by selling new common stock Hint: use Tables 1A, 18 and 1 In the attached EXCEL sheet to solve question and prepare necessary data for other questions 2. (15 Points) Using the book values information in your answer to question (I). - Calculate the following radiosteturn 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 (DE) ratio, total debt (ID)ratio, times interest corned ratio (TIE). net working capital so 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 Hint: use Table in the attached EXCEL sheet. 3.(15) Using the information in your answer to question (), calculate net cash flow (NCF) at the end of each of the next five years (including terminal value at the end of your 5). Assume that all fixed assets are depreciated using the stright line method. Note that additional capital expenditures are equal to changes in fissed assets from one year to another Note: NCF-EBIT - Taxes + Depreciation (+ or --> Changes in NWC + or -) Additional Capital Expenditures. This is CFFA. Hint: use Table in the attached EXCEL sheet (15 Point) Using the DCF method, what is the NPV for And Here are some hints - Use the NCF calculated in question (3) as your net cash flows for each of the next 5 years - Assume that the risk free rate is 5.6%, required market rate of return is 11.6%, and Aldorado (sets) beta is 14 Assume that RADR (I... WACC) will remain constant for the next five years. - Use Gordon model (V-CF1/-) to calculate the terminal value of Aldorado at the end of this year, ie, today For that, assume a permanent constant growth rate in NCF of 4% starting year I. - Calculate Aldorado NPV. Assume initial investment equals to book value of total assets minus current liabilities at the end of the current year 5. (5 Points) What is the IRR for Aldorado? Is this IRR acceptable? Hint use Tables 4 and 5 in the attached EXCEL sheet to answer questions 4 & 5. 6.5 Point Calculate the new price per share as a result of your NPV calculations in gestion (Point) cacuite the change in the acquirer's wealth as a result of the acquisition. Assume the equirer ol 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 18 (15 Pointochene se waghod average cost of capital for to the next years. As comment on changes in WACC during the same period. Is there an optimal (minimal) WACC7 Hints: WACC - Wa.Kd(1-Te) + We Kel. Where Wd - Long Term Debu(Total Equity + LTD"(1-Te) We - Total Equity/Total Equity + LTD*(1-Te) After Tax LTD-LTD"(1-TC) After Tax D/E Ratio - After Tax LTD/Total Equity Assume Beta of Debt (B) - 0.07 and it remains constant throughout the next five years regardless of the change in leverage Unlevered Cost of Equity (KeU) - 1.41 and it remains constant throughout the next five years La cred Cost of Equity (Kel.) - 1.70 in year 1, and it will change with the change in debt Equity Beta (fel) eu (BU-BY-(1-Te)D/E) Use CAPM to calculate cost of equity (Kel.) 9.(10 Points) What is the optimal capital structure mix for Aldorado? Toward this end: - Calculate the value of the levered firm Aldorado and share price for each of the next 5 years. Comment on changes in firm value and share price during the same period. Is there an optimal (maximal) value? Compare the above results to the changes in WACC as calculated in question 8. Can you reconcile the discrepancy? Hint: the firm value is the NPV of the firm as calculated in question 4. 10. (5 Points) Calculate the new number of outstanding shares and new book value per share for each of the next 5 years (year 1-year 5). Hint 1: the increase in outstanding shares increase in common stock account divided by last year share price. Hint 2: use Tables 8-10 in the attached EXCEL sheet to answer questions 8,9 & 10. NOTE: You will be expected to turn in printed outputs similar in format to what are presented on next pages. Tables are numbered after above questions. All calculations should use spreadsheet formulas. An EXCEL sheet is attached to ease your work. Aldorado Spreadsheet TABIA. Alorado Company PRO FORMA Balance Sheet For the Net Years Thesand of Dallars) Year 1 Yak2 Year 3E Year 4E Year Sales Current Assets Net Fixed Ass Toul Asset YASH Year o 90,000 19,800 31.500 78100 9.000 4.500 13.500 Accounts Payable Other Current Liabilities Total Current sites LTDebt Common Stock Retinod Farnings Total Equity 16,000 28,800 20.000 78.100 1,120 $ Interest S1120 (796 borrowing rate) Depreciation is 20% of Fixed Assets Table 18. Aldorado Company PRO FORMA Income Statement For the Next 5 Years (Thousand of Dollars) Year Year 1E Year 28 90,000.00 NA NA NA NA NA NA Year 3 Year 45 Year 5 Year Sales Cost of Goods Sold Selling & Administrative Expenses Depreciation Famings Before Interest & Taxes NA NA NA NA NA NA NA NA S Interest $1120 (1.0.27borrowing rate) Tares (2196) Net Income (PM-16%) NA NA NA . NA NA NA Dividends (60% Payout Ratio) New Additional Retained Earnings (40% Retention Ratio) Table 1C. Allorado Company Additional Calculates For the Next Years Thousand of Dollars) Year 18 Year YG BE Year Year NA NA NA NA Year Change in Current AEG Change in Net Fixed Assets Change in Total Asset Change in Current Liabilities New Toul Financing Needs (TEN) New Additional Retained Earnings New Extemal Financing Neede EFN) New Audio Extemal Debt New Additional External Equity i... Common Stocks) 1533 3131313 NA NA NA Year JE Year 4 Year SE Financial Ratio Excformance Measures Market Value Added Marke-o-Book Ratio Table 2. Aldorado Company Financial Ratio Analysis Ratio Formula Year 1E Year 28 market value of equity-book value of equity market value of equity book value of equity net income /equity Profitability Measures Return on Equity (ROE) Return on Assets (ROA) Return on Capital (ROC) (net income interesITA - (not income interest) (long term debt equity) net income (cost of equity RADR equity) EVA (not incontro alles TA start of you Operatin Profit Marin Emdes MESES Asset TV Let Me Long Term Debat LaTerm Daboy Radio Total Debt Ratio Times Interested Laski Mies Nat Working Capital TA Current Ratio Grethe Measure Sustainable Growth long term debelong term dit Lang term de BENOITA FRIT -NWCTTA CA/CL - (1-dividend payout ratio) "ROE Tab Aldorado CPEDY Projected NCP Cakulaties ( hr Your Year E Year Yaar 90,000 NA NA NA NA Year Sales EHIT Taxes Depreciation (2013) Additional (Change) Net Working Capital Additional (Change) Capital Expendituros NCFCCCFFA) Year SE 3533 NA NA Tables 4 & 5. AMorado Company NPV & IRR Cakulations (Thousand of Dollars) Calculation of RADR (LG, WACA Terminal Value NA Initial Investment NA NCF. Year 1 NA NA NA NCTA Yar 2 INCHAY 3 INCHAYear NCHY Toul NCF NPV (thousand of dollars) IRR Table 6AMorado Company Share PriCalculation New Price per Share Calculation Change in the Acquirer's Wealth Tables 8,9 & 10. Aklorado Company - Capital Structure Calculations 0.07 0.07 0.07 0.07 0,07 0.07 Cost of Debt Afler Tax Beta of Debt Long Term Debt (LT) After Tax LTD Equity After Tax LTD Ratio (Wd) Afler Tax Equity Ratio (We) Afer Tax Debt Equity Ratio Beta of Unlevered Equity fleu 1.41 1.41 1.41 1.41 1.41