Given the following information, what is the maximum price that Neuqun should pay to equity shareholders for Artforever.com? Please show so to solve with equations in excel.
50,000 12 Given Information Neuquen, Inc (Investor) Market Value Debt YTM Market Cap Required Return on Equity Tax Rate Common Stock Price Outstanding shares Book Value for Common Stock $ $2,000,000 6.2% $0.00 0.00% 0.00% Given Information Artforever.com (Private) Market Value Debt Tax Rate Cost of Borrowing Coupon Rate COGS (of sales revenues) Operating Expenses (of revenues) Target debt value Perpetual Growth Rate Cash Flows 2023+ $ $1,475,000 40% 6.2% 7% 42% Given Information ArtToday (Public) Common stock o/s Share Price $ Tax Rate Equity Beta Debt to Equity Ratio Dividend Expectation 40% 1.5 0.75 15% 100,000 20.00 15% 2% 1,036,789.20 Discount Rate (Required Return) - Growth Model R=Divident Yield + Capital Gains Yield R= Dividend/Present Value + Grow Rate Dividend Yield 8% Discount Rate 10% Growth Rate / Capital Gains 2% Price in one year (P) = Dividend Yield/(Rate of return - growth rate) = $1.02/(.10-.02) = $1.02/.08 = $ 12.75 0.75 Stock Gain Dividend Yield 8% Growth Rate / Capital Gain 6% Expected Rate of Return 14% Sales COGS Expenses Depreciation EBIT Interest Operating Income Taxes Net Income 2018 1,000,000.00 405000 150000 15,000 430,000.00 94,400.00 335,600.00 134,240.00 201,360.00 2019 1,250,000.00 495000 187500 30.000 537,500.00 101,400.00 436,100.00 174,440.00 261,660.00 2020 1,875,000.00 737500 281250 50,000 806,250.00 108,600.00 697,650.00 279,060.00 418,590.00 2021 2,100,000.00 810000 315000 72,000 $ 903,000.00 115,900.00 $ 787,100.00 $ 314,840.00 $ 472,260.00 2022 3,750,000.00 1495000 562500 80,000 $ 1,612,500.00 122,400.00 $ 1,490,100.00 $ 596,040.00 $ 894,060.00 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ OCF CapEx & NWC Free Cash Flows 310,760.00 2 5,000 285,760.00 393,060.00 55,000 338,060.00 577,190.00 170,000 407,190.00 $ 660,160.00 80,000 $ 580,160.00 $ 1,096,460.00 80,000 $ 1,016,460.00 $ $ $ Cash Flows 2023+ $ 1,036,789.20 50,000 12 Given Information Neuquen, Inc (Investor) Market Value Debt YTM Market Cap Required Return on Equity Tax Rate Common Stock Price Outstanding shares Book Value for Common Stock $ $2,000,000 6.2% $0.00 0.00% 0.00% Given Information Artforever.com (Private) Market Value Debt Tax Rate Cost of Borrowing Coupon Rate COGS (of sales revenues) Operating Expenses (of revenues) Target debt value Perpetual Growth Rate Cash Flows 2023+ $ $1,475,000 40% 6.2% 7% 42% Given Information ArtToday (Public) Common stock o/s Share Price $ Tax Rate Equity Beta Debt to Equity Ratio Dividend Expectation 40% 1.5 0.75 15% 100,000 20.00 15% 2% 1,036,789.20 Discount Rate (Required Return) - Growth Model R=Divident Yield + Capital Gains Yield R= Dividend/Present Value + Grow Rate Dividend Yield 8% Discount Rate 10% Growth Rate / Capital Gains 2% Price in one year (P) = Dividend Yield/(Rate of return - growth rate) = $1.02/(.10-.02) = $1.02/.08 = $ 12.75 0.75 Stock Gain Dividend Yield 8% Growth Rate / Capital Gain 6% Expected Rate of Return 14% Sales COGS Expenses Depreciation EBIT Interest Operating Income Taxes Net Income 2018 1,000,000.00 405000 150000 15,000 430,000.00 94,400.00 335,600.00 134,240.00 201,360.00 2019 1,250,000.00 495000 187500 30.000 537,500.00 101,400.00 436,100.00 174,440.00 261,660.00 2020 1,875,000.00 737500 281250 50,000 806,250.00 108,600.00 697,650.00 279,060.00 418,590.00 2021 2,100,000.00 810000 315000 72,000 $ 903,000.00 115,900.00 $ 787,100.00 $ 314,840.00 $ 472,260.00 2022 3,750,000.00 1495000 562500 80,000 $ 1,612,500.00 122,400.00 $ 1,490,100.00 $ 596,040.00 $ 894,060.00 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ OCF CapEx & NWC Free Cash Flows 310,760.00 2 5,000 285,760.00 393,060.00 55,000 338,060.00 577,190.00 170,000 407,190.00 $ 660,160.00 80,000 $ 580,160.00 $ 1,096,460.00 80,000 $ 1,016,460.00 $ $ $ Cash Flows 2023+ $ 1,036,789.20
