FinalS24_template_proctor copy

xlsx

School

North Lake College *

*We aren’t endorsed by this school

Course

4520

Subject

Finance

Date

Apr 3, 2024

Type

xlsx

Pages

15

Uploaded by Inara_K

Report
Question Total Points Your Score Problem 1 25 Problem 2a 10 Problem 2b 3 Problem 2c 3 Problem 2d 8 Problem 3 8 Problem 4a 8 Problem 4b 5 Problem 5 6 Problem 6 6 Total 82 0 Your Name: I agree to not communicate with others. I will not use email, the Internet, text, or other communication devices. I will not share this exam with other students.
Question 1 (25 points) Income Statement Actual Projected 2023 2024 2025 Sales 1,490 1,646 Cost of Goods Sold 817 799 Selling, General, and Administrative 221 313 Operating Income Before Depreciation 452 535 Depreciation and Amortization 81 90 EBIT 371 445 Interest Expense 3 3 Pretax Income 368 441 Income Taxes 77 93 Net Income (Loss) 291 349 Cash Dividends 46 51 Retained 245 298 INCOME STATEMENT ASSUMPTIONS 2024 2025 Sales growth 10.5% 10.0% COGS/Sales 48.5% 47.5% SG&A/Sales 19.0% 18.0% Depreciation % (Use average fixed assets) 8.3% 8.3% Interest Rate (Use average debt) 6.3% 6.3% Tax rate 21.0% 21.0% Dividend growth 10.0% 10.0% Balance Sheet Actual Projected 2023 2024 2025 In this problem, you will create a fully integrated Income Statement, Balance Sheet, and Statement of Cash Flows. Assumptions are provided below the income statement and below the balance sheet. Complete all cells filled yellow.
ASSETS Cash and Short-Term Investments 76 ### Receivables 120 155 Inventories - Total 81 80 Other Current Assets 19 21 Total Current Assets 296 ### Property, Plant, and Equipment, Gross 975 1,205 Accumulated Depreciation & Amortization 209 299 Property, Plant, and Equipment, Net 766 906 Intangibles 19 19 TOTAL ASSETS 1,081 ### LIABILITIES Accounts Payable 102 91 Debt 57 52 Other Liabilities 11 12 Deferred Taxes 12 19 EQUITY Common Stock-Par 98 98 Additional Paid-In Capital 124 ### Retained Earnings 795 1,093 Treasury Stock - Total Dollar Amount 118 118 Total stockholders equity 899 ### TOTAL LIABILITIES AND EQUITY 1,081 ### BALANCE SHEET ASSUMPTIONS 2024 2025 Minimum cash balance (% of sales) 6.5% 6.5% Accounts Receivable Days (Use 360 days) 34 33 Inventory Days (Use 360 days) 36 35 Other Current Assets Grow at sales growth rate Fixed Assets/Sales 55.0% 54.0% Intangibles Grow at 2% Grow at 2% Accounts Payable Days 41 41
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Other Liabilities Grow at sales growth rate Deferred Taxes 20% of income taxes are deferred each period 20% 20% Debt The firm plans to pay down $5 in debt each period $5 $5 Equity Statement of Cash Flows Projected 2024 2025 Cash flow from operating activities Net Income 349 Depreciation 90 Changes in Working Capital: Accounts Receivable -35 Inventory 1 Other Current Assets -2 Accounts Payable -11 Change in Deferred Tax Liabilities 7 Change in Other Liabilities 1 Cash Flows from Operations 399 Cash flow from investing activities Purchases of Fixed Assets--Capital Expenditures -230 Purchases of Intangible Assets 0 Build a repurchases schedule (see below in row 108) to close the model. Fill in rows 110-112 to compute total cash available for repurchases. If the cash available in 113 is negative, the firm will issue stock (increase additional paid-in-capital in row 55) rather than repurchase shares. Hold common stock par and paid in cpaital in rows 54 and 55 constant.
Net cash used in investing activities -230 Cash flow from financing activities Change in Debt -5 Common Stock and Paid-In-Capital ### Treasury Stock Repurchases 0 Cash Dividends Paid -51 Net cash from financing activities ### Net increase in cash and cash equivalents ### Repurchases Schedule Calculation of Repurchases 2024 2025 Cash at Beginning of Period 76 Cash Flows Before Repurchases ### Minimum Cash Balance 107.01925 Total cash available for repurchases ###
Question 2 (19 points) The income statement and balance sheet are provided below. Complete the 3 questions below the Use a 20% tax rate. Points Income Statement Actual 2023 2024 2025 Sales 2,425 2,619 2,802 Cost of Goods Sold 1,091 1,178 1,261 Selling, General, and Administrative 461 498 532 Operating Income Before Depreciation 873 943 1,009 Depreciation and Amortization 105 120 135 EBIT 768 822 874 Interest Expense 5 6 6 Pretax Income 763 816 868 Income Taxes 153 163 174 Net Income (Loss) 610 653 694 Cash Dividends 76 84 92 Retained 534 569 602 Balance Sheet Actual 2023 2024 2025 ASSETS Cash and Short-Term Investments 145 157 168 Receivables 229 240 257 Inventories - Total 106 118 126 Other Current Assets 29 32 34 Total Current Assets 510 547 585 Property, Plant, and Equipment, Gross 1,753 1,954 2,188 Accumulated Depreciation & Amortization 420 540 675 Property, Plant, and Equipment, Net 1,334 1,414 1,513 Intangibles 27 27 27 TOTAL ASSETS 1,871 1,988 2,125 LIABILITIES Accounts Payable 121 134 144 Other Current Liabilities 9 11 12 Debt 95 101 107 Other Long-Term Liabilities 15 17 18 Deferred Taxes 34 36 38 EQUITY Common Stock 176 176 176 Capital Surplus 146 146 146 Retained Earnings 1,726 2,295 2,897 Treasury Stock - Total Dollar Amount 451 926 1,411 Total stockholders equity 1,597 1,690 1,807
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
TOTAL LIABILITIES AND EQUITY 1,871 1,988 2,125 Question 2a (10 points) Compute Free Cash Flow to the Firm for 2022 to 2028. Use a 22% tax rate. Add rows if necessary 2024 2025 Question 2b (3 points) Compute Free Cash Flow to Equity (FCFE) for 2024 to 2028. Use a 22% tax rate. Add rows if nec 2024 2025 Question 2c (3 points) Comparable firms are trading at 6.8x EBITDA. Use the multiple approach to determine the termi Question 2d (8 points) 2024 2025 Use the WACC valuation methodology to find the 1) enterprise value and, 2) equity value as of 2 "excess" and can be used to pay down debt. Carefully show your work in the rows below. Use mid the terminal value using the perpetuity method in cell H92. Use a WACC of 10% and long
financial statements. Projected 2026 2027 2028 2,970 3,119 3,243 1,337 1,403 1,460 564 593 616 1,069 1,123 1,168 150 166 182 919 957 986 7 7 7 913 950 978 183 190 196 730 760 783 102 112 123 628 648 660 Projected 2026 2027 2028 178 187 195 272 286 297 134 140 146 36 38 39 620 651 677 2,428 2,675 2,924 825 990 1,172 1,604 1,684 1,751 27 27 27 2,251 2,362 2,456 152 160 166 14 15 17 113 119 125 19 20 21 40 42 43 176 176 176 146 146 146 3,526 4,174 4,833 1,933 2,488 3,070 1,914 2,007 2,085
2,251 2,362 2,456 y. 2026 2027 2028 cessary. 2026 2027 2028 inal value in 2028. Show your work. 2026 2027 2028 2023. Assume that 2023 cash is d-year discounting. Recompute g-term growth of 1.5%.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Question 3 (8 points) Pre-Event Period Event Period Regression results for target (pre-event period) Intercept 0.00085 -2 0.04% 0.01% -0.61% Slope 0.68 -1 1.20% -1.32% -0.41% Standard Error 0.049 0 7.10% -1.02% -2.41% Acquirer market capitalization (10 days before event) 4,800,000 1 0.81% -0.20% -1.30% Target market capitalization (10 days before event) 2,620,000 2 0.12% 0.50% -0.10% Target Cumulative Abnormal Return (CAR) On January 1, 2020 (day=0) Blerg Corp. announced an acquisition of Barm Corp. For this question, we will conduct an event study to regression results for the target and the market capitalizations have already been provided to you in Rows 6-10. The event period returns fo announcement to two days following the announcement are provided in Columns E-F. The acquirer abnormal returns and cumulative abnorm cumulative abnormal return for the target and total dollar synergy over the [-2,2] event window. Fill out the cells highlighted yellow. Feel fre # Days before event Target Return Return on S&P 500 Acquirer Abnormal Return (AR)
Question 4A (8 Points): DIRECTIONS Points Offer calculation Stock Price $10.75 Deal Premium 22% Deal Stock Price # fully diluted shares outstanding (millions) 110 Purchase price of equity (millions) Management Participation Amount Management Rollover Cash to Shareholders SOURCES (in Millions) Amount Excess Cash Senior secured note (bank debt) $870.00 Subordinated note (unsecured debt) $520.00 Sponsor Equity Management Rollover Total Sources USES (in Millions) Amount Equity Purchase Price Debt Retired Purchase of Pref. Stock Transaction Costs Management Rollover Total Uses A group of managers is going to offer to purchase the company they manage with the help debt (par value of $375 million) and preferred stock (par value of $125 million) at its par v million in subordinated debt. The sponsor also intends to finance the transaction by investi equity). The company currently has a cash balance of $30 million and requires a minimum yellow.
p of a financial sponsor. In addition to redeeming the existing stock, mana value. The sponsor intends to finance this transaction by issuing various d ing its own equity, and having management invest $140 million in equity m cash balance of $26 million. The financial sponsor will put in enough e
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
agement and its financial sponsor will also redeem all outstanding debt securities, including $870 million in senior secured debt, $520 y from rollover equity. Transaction costs are 1.7% (based on equity to fund the remainder. Fill in all blank cells highlighted
Question 4B (5 points) Points Mike Myers Inc. went private through a leveraged buyout. The financial sponsor, Goldmember Fund, made an equity investment of $93 on the transaction date 6/08/2018. The LBO increased debt to $86. Goldmember Fund exited the transaction on 12/07/2022. By the exit date, debt had been paid down to $51. Between the deal and the exit date, the value of the assets did not change (e.g., cash flows were not increased due to operating improvements and the firm was valued at the same cash flow multiple). What is the IRR to equity holders (Goldmember Fund)? Carefully show your work for partial credit.
Question 5 (6 points) Consider the large-sample event study results we discussed in class. What does the literature say about whether mergers & value for acquirer shareholders? For target shareholders? Do mergers create combined synergies? Does the target or acquire potential explanations for this result? Answer in NO MORE THAN 6 SENTENCES.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help