MBA:8180 Managerial Finance Risk, Diversification, and Required Returns for Business Divisions T.A. Rietz, 2022 You just took an internship at Tooth-n-Hair, a private company that makes toothbrushes in their Oral Care division and Shampoos and Conditioners in their Beauty Products division. Your management asked your team to provide some risk and return assessments for the divisions and determine what investors will demand for an overall corporate return if they go public. Your team has already estimated the expected returns and standard deviations for each division and the correlation between them as follows: Oral Care Beauty Products % of Corporate Assets: 25% 75% Expected Monthly Return: 1.20% 1.60% Standard Deviation: 4.00% 5.00% Correlation: 0.5 You realize that there is diversification value in running the two divisions and will incorporate that in some additional analysis. You also do a little reading and realize that one of the ways to estimate the return demanded by investors is to figure out the return demanded by investors in similar publicly traded companies. Your team has identified Proctor and Gamble as the publicly traded company with the closest profile to your company. So, you collected 3 years of monthly returns for P&G and the S&P500 from Yahoo Finance (https://finance.yahoo.com/) and one month treasury rates from FRED (https://fred.stlouisfed.org/series/DGS1MO). The data your team you have collected are in the Unit 6 Data Case Template spreadsheet. You proceed with the following analysis: Part 1: Diversification between Oral Care and Beauty Products In this section of the spreadsheet: 1. Calculate the corporate expected return in cell F4. 2. Calculate the corporate standard deviation, taking into account diversification, in cell F5. 3. Minimize the standard deviation using solver to minimize cell F5 by changing cell D3. (Note, cell E3 will automatically change to maintain 100% weight overall.) Part 2: Sensitivity to Division Weights In this section of the spreadsheet, you will make a data table varying the percentage of assets in the oral care division and seeing how it affects the overall corporate expected return and standard deviation. The data table is already set up for you, you just have to fill it in. (If you'd like, you can do this directly with formulas, too.) Next, make a scatter plot chart with the standard deviation on the "X" axis and expected return on the "Y" axis to show your management the benefits of diversification across the two divisions. You should get the bow shape shown in the videos. Part 3: Estimating Cost of Equity In this section of the spreadsheet: 1. Calculate the monthly returns for P&G and the S&P500 from the monthly closing prices in columns E and F. 2 (Recall, r = (FV-PV)/PV.) 2. Find the average monthly returns in row 64 (use the excel average function). 3. Calculate the monthly market risk premium in row 65 (it's the same for both columns). Generally, we estimate the market risk premium by subtracting the risk-free rate from the market return and average over very long periods of time. But, for this example, just use the three years you have. 4. Find the standard deviation in monthly returns for P&G and S&P500 in row 66 (use the excel stdev function). 5. Find the correlation between P&G and S&P500 each with the S&P500 in row 67 (use the excel correl function). 6. Find beta for P&G and S&P500 in row 68 using the formula = , 2 , where the market is the S&P500. 7. Calculate the CAPM required return for P&G and S&P500 in row 69 using the formula ( ) = + . Here, you want to use the most recent risk-free rate, not the average. Why? Because the most recent risk-free rate is the return you can get by investing in the risk-free asset today. 8. You realize your company will probably want annual returns, so annualize the returns a. By finding the APR in row 70. b. By finding the EAR in row 71. Finally, we'll figure out our divisional costs of capital in 74 through 77. Your team has estimated betas for each division. To see what the divisional costs of capital are and whether they are consistent with giving investors their required return, do the following: 1. Find the corporate beta based on the weighted average of the divisional betas in row 75. 2. Find the divisional and corporate required returns based on divisional betas and CAPM in row 76. 3. In row 77, find the corporate required returns as the weighted average of the divisional required returns. Is it he same as the corporate required return on row 76?

 USE BELOW EXCEL TO ANSWER THE ABOVE QUESTIONS.

Part 1: Diversification between Oral Care and Beauty Products
			Oral Care	Beauty Products	Company
% of Corporate Assets:	25%	75%	100%
Expected Monthly Return:	1.20%	1.60%
Standard Deviation:	4.00%	5.00%
Correlation:	0.5
Part 2: Sensitivity to Division Weights	Data Table
			% Invested in	s(R)	E(R)
			Oral Care	0.0000%	0.0000%
			0%
			10%
			20%
			30%
			40%
			50%
			60%
			70%
			80%
			90%
			100%
Part 3: Estimating Cost of Equity
Month	PG	S&P500	R(Treasuries)	R(PG)	R(S&P500)
Apr-19	98.285965	2945.83
May-19	95.661285	2752.06	2.4300%
Jun-19	101.926521	2941.76	2.3500%
Jul-19	109.72554	2980.38	2.1800%
Aug-19	112.485054	2926.46	2.0100%
Sep-19	116.367729	2976.74	2.1000%
Oct-19	116.489349	3037.56	1.9100%
Nov-19	114.926636	3140.98	1.5900%
Dec-19	117.600677	3230.78	1.6200%
Jan-20	117.337044	3225.52	1.4800%
Feb-20	107.246086	2954.22	1.5600%
Mar-20	104.18679	2584.59	1.4500%
Apr-20	111.640877	2912.43	0.0500%
May-20	110.526138	3044.31	0.1000%
Jun-20	114.006302	3100.29	0.1300%
Jul-20	125.018875	3271.12	0.1300%
Aug-20	132.725693	3500.31	0.0900%
Sep-20	133.358948	3363	0.0800%
Oct-20	131.545517	3269.96	0.0800%
Nov-20	133.983643	3621.63	0.0800%
Dec-20	134.244156	3756.07	0.0800%
Jan-21	123.698753	3714.24	0.0800%
Feb-21	119.90229	3811.15	0.0700%
Mar-21	131.452835	3972.89	0.0400%
Apr-21	129.501846	4181.17	0.0100%
May-21	131.727295	4204.11	0.0100%
Jun-21	131.805435	4297.5	0.0100%
Jul-21	138.936401	4395.26	0.0500%
Aug-21	139.967484	4522.68	0.0500%
Sep-21	137.42157	4307.54	0.0300%
Oct-21	140.557281	4605.38	0.0700%
Nov-21	143.001282	4567	0.0600%
Dec-21	161.793808	4766.18	0.1100%
Jan-22	158.697968	4515.55	0.0600%
Feb-22	155.020294	4373.94	0.0300%
Mar-22	151.947525	4530.41	0.0600%
Apr-22	159.654297	4131.93	0.1700%
Statistic	R(Treasuries)	R(PG)	R(S&P500)
Average Return:	0.6225%			<-- Use the Excel "Average" Function here.
Market Risk Premium:				<-- Note: Cells E65 and F65 should be the same number.
Standard Deviation:				<-- Use the Excel "Stdev" Function here.
Correlation with S&P500:				<-- Use the Excel "Correl" Function here.
Beta using Formula:				<-- Use the forumua from the data case here.
Monthly CAPM Required Return:				<-- Use the most recent risk free rate and the market risk premium from row 65 here.
CAPM APR:
CAPM EAR:
Divisional Cost of Capital	Oral Care	Beauty Products	Company
Weights:	25%	75%	100%
Beta:	0.3109	0.46635		<-- Use the weighted average here.
CAPM Required Return:				<-- Use the most recent risk-free rate and the market risk premium from row 65 here.
Corporate Average Req. Return:				<-- Use the weighted average here.
						<-- Note: Cells B76 and B77 should be the same number.