Build a spreadsheet to calculate the Stock Price or required return for the scenarios described below. Use the numbers in each scenario to test your model.

1. Using the Constant Growth Model, build a model that will calculate the stock price for a firm, given r, g, and the dividend at time zero. Use D₀ = 2, r = 10% and g = 3% to test your model.

2. Using the Constant Growth Model, build a model that will calculate the stock price for a firm, given r, g, and the dividend at time one. Use D1 = 3.56, r = 12% and g = 2% to test your model.

3. Using the Constant Growth Model, build a model that will calculate the stock price for a firm at some time t years into the future, given r, g, and the dividend at time zero. Calculate the price at time 5 assuming D0 = 2, r = 10% and g = 3% to test your model.

4. Using the Constant Growth Model, build a model that will calculate the required return for a firm, given P0, g, and the dividend at time zero. Use D0 = 2, P0 = 30 and g = 4% to test your model.

5. Using the Constant Growth Model, build a model that will calculate the required return for a firm, given P0, g, and the dividend at time one. Use D1 = 3, P0 = 30 and g = 4% to test your model.

6. Build a model that will calculate the stock price based on PE multiples given the PE and EPS. Use an EPS = 5 and a PE = 20 to test your model.

7. Build a model that will calculate the stock price based on P/Sales multiples given the number of shares outstanding, sales and P/Sales. To test your model, consider a firm with 130,000 shares outstanding, $2.1 million in Sales and a P/Sales multiple of 4.3 times sales.

Answer rating: 100% (QA)

You can create a spreadsheet in Microsoft Excel to calculate the stock price and required return for the scenarios described using the Constant Growth ... View the full answer