Assignment #7- WACC & DCF Background: You are an investment banker hired by Spartan Industries (Spartan") to evaluate an offer to purchase the company. Based on previously prepared information, you are now ready to finish the valuation. You are uncertain of Spartan's Cost of Debt (rD), but you are aware of the following: 1.In 2018, a direct competitor issued $97 million in debt with an interest rate of 8.25%; 2.Additionally, Spartan has 125million shares outstanding with a current stock price of $11.23/share Assignment: 1. Using previously derived information, calculate the following for Spartan: a. Free Cash Flow; b. Weighted Average Cost of Capital ("WACC") c. Residual Value (using both the "Perpetuity with Growth" and "PVGO = 0" methods): d. Final Per-Share Valuation (Use "Perpetuity with Growth" and "PVGO = 0" methods). Instructions and Hints: 1. Financial Statements: a. Use the pro-forma information provided in the solution to HW#2. b. Use the following financial periods: 1. Last historical period should be 2018 (which you need to calculate for purposes of showing changes to fixed assets and working capital in 2019). ii.Pro-forma period should be 2019-2023. ii. Residual (terminal) period should be 2024. C. For purposes of calculating net working capital for the period 2019-2023, use the following net working capital/sales ratio: 24/1, or 24%. 2. Use the Short-and Long-Term Debt figures provided in the solution to HW #2. 3. Calculate Net Fixed Assets (i.e., PPE & Other Fixed Assets) using the solution to HW #2. 4. WACC: Use market values and compute cost of equity using CAPM with attached spreadsheet named CAPM 5. Hints: a. First, calculate FCF b. Next, calculate WACC C. Next, calculate PV of FCF, Residual Value and its PV. d. Finally, perform the valuation using the Perpetuity with Growth and the PVGO=0 models. (HINT: As between these two models, the only input changing is Residual Insights: What happens to the stock valuation with the implied Beta of 1.6 that the firm offering to purchase the company used? WIE 37 fx Spartan Industries Adjusted Monthly Closing Prices (past 5yrs) AL S&P 500 Adjusted Monthly Closing Prices (past 5yrs) Det More 11 115 Bet Calculation Covariance of Het HEY IN EN os 20 1/ 10 11/09 /2019 10 We ILAN. TO 100 Aloe 1001 40 10 NE 1.11 IN 30 2 2015 4/1/2012 1 242019 w MO MR NG wa w E SIGUI TE th NE ME AN THE WAT HON TE O / 0 LUN WURDE W WE MO DIN LN w M BE ILI 15 w MESURE w MOSTRU A/U/00 / EMOS ME www WV MULTS WITAL NUT WITH WITH 4/A w w ME WALT TE UN w w WY EM WWW w ww SCT LIN UN LEN w WIE W mort TOWN Now AN NE WIG WW ON th w 100 101 CHAN M6N 21 14 w WW MVND Benchmark Treasury Yield Term Rate mo 1.56% 2yr 1.68% 1.75% 10yr 1.95% 30 2.43% 5yr B D E A 1 2 Spartan Industries Cost of Equity 3 Formula: rE = rf + B (rm-rf) 4 rf 5 B 6 rm-rf (remember, use historical number) 7 rE (CAPM) 8 Use Applicable Rate Derive Using Forumula 9 10 11 12 13 14 15 16 17 18 19 1 20 21 22 23 24 25 26 G K N o SOLUTION Spartan Industries, INC Selected Financial Information 2014-2018) Income Statement 2019 3.409 1,902 362 722 2022 4,919 2.744 523 1,019 2024 5,423 3,026 576 1,156 2018 3.017 1739 327 519 402 14 388 141 247 49" 103 418 2020 3.852 2.149 409 321 473 14 459 161 290 60 238 Sales Cost of Goods Sold RAD Expense SG&A Expense Operating Income (EBIT) Interest Expense Earnings before Tax Income Taxes Net Income Didends to Retained Earnings 2021 4,353 2,429 463 928 534 14 520 182 30 68 270 603 2023 S. 165 2.882 549 1,101 634 14 620 212 303 81 322 665 2025 5,595 3,177 605 1,2144 659 14 685 240 445 89 356 14 404 141 263 53 210 14 589 206 383 22 307 14 651 228 423 85 390 Balance Sheet 2010 30 431 534 32 5077 686 61 1824 2015 31 35 351 137 2024 54 759 976 54 1,814 1.247 2025 57 797 1,025 57 1.936 1,310 Cars Accounts Receivable Inventory Other Current Assets Current Assets Plant Property & Equipment (net) Omer Faxed Assets Total Assets Accounts Payable Accrued Expenses Short Term Debt Current Liabilities Long Term Debt Totatbildes Total Equity Lubies & Net Worth 2013 34 477 614 34 1.150 784 68 2011 307 34 30 376 137 513 1.499 2011 108 2020 39 539 693 39 1,310 886 77 2,273 342 39 35 42) 137 557 1.716 2.273 2021 44 609 784 44 1,480 1,001 87 2568 392 44 35 470 937 607 2023 52 723 930 52 1,756 1,188 103 3047 465 52 2022 49 689 385 49 1.673 1.131 98 2.002 443 49 39 627 137 664 2.230 2002 3,200 488 54 552 137 600 577 137 714 2486 3.200 3.360 513 52 33 604 137 741 2613 3.360 1335 1824 2.568 3.047 20N 10.05 2019 2020 2021 2022 2023 55.79% 55.794 55.799 SS.799755. 10.63% 10,6% 10.63% 10.639 10.63 21.329 21.3292132 21:32 21.32 33.834 33.96% 34.00 34.1934.23% 1.00% 1.00 1.009 1.00 1.00 34.00 34.004 .00934.004. 5. 10.64 21 32 1430 1.00 4009 1.00 340 17.53% 1732 17 24 17.129 12.02 13.06 13.12 13.169 13.2041122 7.71 2.744 7. 7. 2004 169.494 169.494 1694 169.49 10494 12.274 12.212.212.2122 17.00 2.1 Financial Ratios Ratio FO 2018 Averages COGS COGS / Sales 57.649655.799 RSD Expenses RAD Expenses/ Sales 10.8494 10.63 SGSA Expenses SOLA Expenses / Sales 18.209 21.32 Income Tax Income Tax Pad / EBIT 35.07% 34.88 Accrued Expenses Accrued Expenses Sales 1.0396 1.36% Current Assets Current Assets/Sales 35.709 36.76% 7. Return on Equity Net Income/Total Equity 18.4996 17.53% 8. Return on Assets Net Income/Total Assets 13.54 11.35 Profit margin Net Incore/ Sales 3.1995 2.40% 0 Total Asset Turnover Sales/Total Assets 165.411 158.56% 1 Operating Margin EBIT/S. 13.32 12.27 2 Current Ratio Current AssCurrentes 3.060 209.72 1 Debt/Equity Ratio LT Dett/Tots Equity 10.25% 5 6 Castwes 1 ZIAR Sales 14% Se Inventory Sales 104 59 Other C Asses Sales HO AP 99 31 Etiales 19 2 PP / Sales 219 Other Fad Assets 29 4 x Rate SS ded/tiet income 2045 17.00 122 2014 169.49 122 62 Terminal Period Historical Period 2018 Pro-Forma period of Through T.5") 2015 7920 103 2022 2025 PVGOROM werth 2024 PV of FCF (T.1 to T5) PV (RV PV (Operations 3,017 402 5.43 GES 141 261 0 Net Operating Profit (NOP) Sales Orang Income EDIT Income Taxes Met Operating Profe 11 12 Net Fixed Assets Plant Property & Equipment (net) Other Fred Assets 315 et Fixed Assets (NA) 16 ANA Non-Operating sa PVC) Qurrent Debt PV Imouty Number of Sare (is) Pvm Per Share 1.20 100 1,356 125 30 69 747 58 24 202 10 Working Capital 15 Net Working Capital 20 23 Net Operating Prof (NO) 22 O 0 0 0 0 0 0 0 0 0 0 0 0 437 1,356 1,916 0 o 0 0 o -2.274 TV/ #DIV/ PY 26 Ureth flower 16 Par FC (Tato T45) 29 WACC 30 31 Residual Value Calculation 22 FT) ZAWACS, 13 Perpetuty with Growth Mode 34 Revere Growth (2014) PCF 2020 16 WACC 11 PV (T-1) / WACC 1 PVO Model NOR 2020 wa 41 Discount Factor OF HV WACC OVO Periods Desconto1/ #DIV/01 IV/01 59 DIO DIV/01 Yobtv/ DIVO 1 Given Information 2 rD 8.25% 3 Current ST Debt (in millions) $ 35.00 4 Current LT Debt (in millions) $ 137.00 5 Current Stock Price $ 11.23 6 Shares Outstanding (in millions) 125 7 Corporate Tax Rate 35% 8 9 Spartan Industries Cost of Equity 10 Formula: rE = rf + B (rm-rf) 11 rf 1.75% 12 B 1.17 13 rm-rf (remember, use historical nun 8.00% 14 rE (CAPM) 11.13% 15 16 17 Capital Ratios & WACC 18 Formula: = [(E/V) rE] + [[(D/V) K D)] (1-Tc)] 19 Current ST & LT Debt (in millions) 20 MV Equity (in millions) 21 Total Capital (in millions) 22 23 Current Debt Weighting #DIV/0! 24 Current Equity Weighting #DIV/0! 25 26 WACC #DIV/0! 27 28 29 30 31 32 33 34 Assignment #7- WACC & DCF Background: You are an investment banker hired by Spartan Industries (Spartan") to evaluate an offer to purchase the company. Based on previously prepared information, you are now ready to finish the valuation. You are uncertain of Spartan's Cost of Debt (rD), but you are aware of the following: 1.In 2018, a direct competitor issued $97 million in debt with an interest rate of 8.25%; 2.Additionally, Spartan has 125million shares outstanding with a current stock price of $11.23/share Assignment: 1. Using previously derived information, calculate the following for Spartan: a. Free Cash Flow; b. Weighted Average Cost of Capital ("WACC") c. Residual Value (using both the "Perpetuity with Growth" and "PVGO = 0" methods): d. Final Per-Share Valuation (Use "Perpetuity with Growth" and "PVGO = 0" methods). Instructions and Hints: 1. Financial Statements: a. Use the pro-forma information provided in the solution to HW#2. b. Use the following financial periods: 1. Last historical period should be 2018 (which you need to calculate for purposes of showing changes to fixed assets and working capital in 2019). ii.Pro-forma period should be 2019-2023. ii. Residual (terminal) period should be 2024. C. For purposes of calculating net working capital for the period 2019-2023, use the following net working capital/sales ratio: 24/1, or 24%. 2. Use the Short-and Long-Term Debt figures provided in the solution to HW #2. 3. Calculate Net Fixed Assets (i.e., PPE & Other Fixed Assets) using the solution to HW #2. 4. WACC: Use market values and compute cost of equity using CAPM with attached spreadsheet named CAPM 5. Hints: a. First, calculate FCF b. Next, calculate WACC C. Next, calculate PV of FCF, Residual Value and its PV. d. Finally, perform the valuation using the Perpetuity with Growth and the PVGO=0 models. (HINT: As between these two models, the only input changing is Residual Insights: What happens to the stock valuation with the implied Beta of 1.6 that the firm offering to purchase the company used? WIE 37 fx Spartan Industries Adjusted Monthly Closing Prices (past 5yrs) AL S&P 500 Adjusted Monthly Closing Prices (past 5yrs) Det More 11 115 Bet Calculation Covariance of Het HEY IN EN os 20 1/ 10 11/09 /2019 10 We ILAN. TO 100 Aloe 1001 40 10 NE 1.11 IN 30 2 2015 4/1/2012 1 242019 w MO MR NG wa w E SIGUI TE th NE ME AN THE WAT HON TE O / 0 LUN WURDE W WE MO DIN LN w M BE ILI 15 w MESURE w MOSTRU A/U/00 / EMOS ME www WV MULTS WITAL NUT WITH WITH 4/A w w ME WALT TE UN w w WY EM WWW w ww SCT LIN UN LEN w WIE W mort TOWN Now AN NE WIG WW ON th w 100 101 CHAN M6N 21 14 w WW MVND Benchmark Treasury Yield Term Rate mo 1.56% 2yr 1.68% 1.75% 10yr 1.95% 30 2.43% 5yr B D E A 1 2 Spartan Industries Cost of Equity 3 Formula: rE = rf + B (rm-rf) 4 rf 5 B 6 rm-rf (remember, use historical number) 7 rE (CAPM) 8 Use Applicable Rate Derive Using Forumula 9 10 11 12 13 14 15 16 17 18 19 1 20 21 22 23 24 25 26 G K N o SOLUTION Spartan Industries, INC Selected Financial Information 2014-2018) Income Statement 2019 3.409 1,902 362 722 2022 4,919 2.744 523 1,019 2024 5,423 3,026 576 1,156 2018 3.017 1739 327 519 402 14 388 141 247 49" 103 418 2020 3.852 2.149 409 321 473 14 459 161 290 60 238 Sales Cost of Goods Sold RAD Expense SG&A Expense Operating Income (EBIT) Interest Expense Earnings before Tax Income Taxes Net Income Didends to Retained Earnings 2021 4,353 2,429 463 928 534 14 520 182 30 68 270 603 2023 S. 165 2.882 549 1,101 634 14 620 212 303 81 322 665 2025 5,595 3,177 605 1,2144 659 14 685 240 445 89 356 14 404 141 263 53 210 14 589 206 383 22 307 14 651 228 423 85 390 Balance Sheet 2010 30 431 534 32 5077 686 61 1824 2015 31 35 351 137 2024 54 759 976 54 1,814 1.247 2025 57 797 1,025 57 1.936 1,310 Cars Accounts Receivable Inventory Other Current Assets Current Assets Plant Property & Equipment (net) Omer Faxed Assets Total Assets Accounts Payable Accrued Expenses Short Term Debt Current Liabilities Long Term Debt Totatbildes Total Equity Lubies & Net Worth 2013 34 477 614 34 1.150 784 68 2011 307 34 30 376 137 513 1.499 2011 108 2020 39 539 693 39 1,310 886 77 2,273 342 39 35 42) 137 557 1.716 2.273 2021 44 609 784 44 1,480 1,001 87 2568 392 44 35 470 937 607 2023 52 723 930 52 1,756 1,188 103 3047 465 52 2022 49 689 385 49 1.673 1.131 98 2.002 443 49 39 627 137 664 2.230 2002 3,200 488 54 552 137 600 577 137 714 2486 3.200 3.360 513 52 33 604 137 741 2613 3.360 1335 1824 2.568 3.047 20N 10.05 2019 2020 2021 2022 2023 55.79% 55.794 55.799 SS.799755. 10.63% 10,6% 10.63% 10.639 10.63 21.329 21.3292132 21:32 21.32 33.834 33.96% 34.00 34.1934.23% 1.00% 1.00 1.009 1.00 1.00 34.00 34.004 .00934.004. 5. 10.64 21 32 1430 1.00 4009 1.00 340 17.53% 1732 17 24 17.129 12.02 13.06 13.12 13.169 13.2041122 7.71 2.744 7. 7. 2004 169.494 169.494 1694 169.49 10494 12.274 12.212.212.2122 17.00 2.1 Financial Ratios Ratio FO 2018 Averages COGS COGS / Sales 57.649655.799 RSD Expenses RAD Expenses/ Sales 10.8494 10.63 SGSA Expenses SOLA Expenses / Sales 18.209 21.32 Income Tax Income Tax Pad / EBIT 35.07% 34.88 Accrued Expenses Accrued Expenses Sales 1.0396 1.36% Current Assets Current Assets/Sales 35.709 36.76% 7. Return on Equity Net Income/Total Equity 18.4996 17.53% 8. Return on Assets Net Income/Total Assets 13.54 11.35 Profit margin Net Incore/ Sales 3.1995 2.40% 0 Total Asset Turnover Sales/Total Assets 165.411 158.56% 1 Operating Margin EBIT/S. 13.32 12.27 2 Current Ratio Current AssCurrentes 3.060 209.72 1 Debt/Equity Ratio LT Dett/Tots Equity 10.25% 5 6 Castwes 1 ZIAR Sales 14% Se Inventory Sales 104 59 Other C Asses Sales HO AP 99 31 Etiales 19 2 PP / Sales 219 Other Fad Assets 29 4 x Rate SS ded/tiet income 2045 17.00 122 2014 169.49 122 62 Terminal Period Historical Period 2018 Pro-Forma period of Through T.5") 2015 7920 103 2022 2025 PVGOROM werth 2024 PV of FCF (T.1 to T5) PV (RV PV (Operations 3,017 402 5.43 GES 141 261 0 Net Operating Profit (NOP) Sales Orang Income EDIT Income Taxes Met Operating Profe 11 12 Net Fixed Assets Plant Property & Equipment (net) Other Fred Assets 315 et Fixed Assets (NA) 16 ANA Non-Operating sa PVC) Qurrent Debt PV Imouty Number of Sare (is) Pvm Per Share 1.20 100 1,356 125 30 69 747 58 24 202 10 Working Capital 15 Net Working Capital 20 23 Net Operating Prof (NO) 22 O 0 0 0 0 0 0 0 0 0 0 0 0 437 1,356 1,916 0 o 0 0 o -2.274 TV/ #DIV/ PY 26 Ureth flower 16 Par FC (Tato T45) 29 WACC 30 31 Residual Value Calculation 22 FT) ZAWACS, 13 Perpetuty with Growth Mode 34 Revere Growth (2014) PCF 2020 16 WACC 11 PV (T-1) / WACC 1 PVO Model NOR 2020 wa 41 Discount Factor OF HV WACC OVO Periods Desconto1/ #DIV/01 IV/01 59 DIO DIV/01 Yobtv/ DIVO 1 Given Information 2 rD 8.25% 3 Current ST Debt (in millions) $ 35.00 4 Current LT Debt (in millions) $ 137.00 5 Current Stock Price $ 11.23 6 Shares Outstanding (in millions) 125 7 Corporate Tax Rate 35% 8 9 Spartan Industries Cost of Equity 10 Formula: rE = rf + B (rm-rf) 11 rf 1.75% 12 B 1.17 13 rm-rf (remember, use historical nun 8.00% 14 rE (CAPM) 11.13% 15 16 17 Capital Ratios & WACC 18 Formula: = [(E/V) rE] + [[(D/V) K D)] (1-Tc)] 19 Current ST & LT Debt (in millions) 20 MV Equity (in millions) 21 Total Capital (in millions) 22 23 Current Debt Weighting #DIV/0! 24 Current Equity Weighting #DIV/0! 25 26 WACC #DIV/0! 27 28 29 30 31 32 33 34