Projects Workbook 2202 - Excel Sign in Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Calibri VAA Gener F Autosum. -Cut Copy *Format Painter board BIU- O A Wrap Text Merge & Center Alignment $. % Insert Delete Format Conditional Formatas Cell Formatting Table Styles Styles Font F Number Ces > B F G H 1 M Information from McCormick Your task is to make an estimate of McCormick & Company's Weighted Average cost of Capital (WACC) to use as the discount rate for evaluating capital projects, such as the one in the next to The CFO plans to borrow a portion of the required $350 million initial investment for this project using 20 year bonds. For most of the past 10 years, the company has used as the discount rate the was based on a study of the company's WACC in early 2010. Interest rates have risen since the last study when the McCormick & Co rate average interest rate was. We wil us today. Also, there has been a change in the weights between debt and equity in the overall capital structure since 2010 The most nificant change was the acquisition of RB Foods. It is the main reason for this study. Here is what our MOBA reported about that acquisition On August 17, 2017, we completed the acquisition of Reckitt Benckiser's Food Division (R8 Foods") from Reckitt Benckiser Group ple. The purchase price was approximately 56.2 billion, net of acquired cash. The acquisition was funded through our issuance of approximately 6,35 million shares of common stock non-voting see note 13 of the financial statements and through new borrowing comprised of senior unsecured notes and pre-payable term loans (see note 6 of the financial statements). The acquired market leading brands of Foods include French's", Frank's Red Hot and Cattlemen's which are a natural strategic fit with our robust global branded flower portfolio. We believe that these additions move us to a leading position in the attractive us condiments category and provide significant international growth opportunities for our consumer and industrial segments The Ro Foods acquisition resulted in acquisitions contributing more than one-third of our sales growth in 2017 and is expected to result in acquisitions contributing more than one-third of our sales growth in 2018 As part of your work, you will be asked for a recommendation for the cost of equity. There are three widely accepted methods used to calculate the cost of equity. They are the Capital Asset Pricing Model (CAPM), the Discounted Cash Flow approach (DCF) and the debt rate plus a risk premium of to recommended by one board member This last is often used by very wealthy investors as check on the other two methods. Your recommendation for the cost of equity will be necessary so you can undertake the calculation estimating the WACC Questions: 1. You have been asked to calculate the cost of equity using the Capital Asset Pricing Model (CAPM). The CFO estimates the Beta as 0.90. Management wants to use the 30 year bond rate as the risk free rate, arguing that investors should make long term investments, that rate is today. The expected return on the stock market as a whole has been estimated to be 7, 10 and 12 by various studies. The CFO asks that you use an expected return of 9 for the average stock. The market risk Premium (RP) will be on minus 3 Instructions Cost of Capital Capital Budgeting Display Settings Projects Workbook 2202 - Excel Sign in Home Insert Page Layout Formulas Data Review View Tell me what you want to do Calibri 11 - General * Cut Copy - Format Painter pboard 2 Wrap Text Merge Center - BIU- $ .% Insert Delete Format Conditional Formatos Cell Formatting Table Styles Styles 1 Font Alignment Number Cells f B F D E Questions: 1. You have been asked to calculate the cost of equity using the Capital Asset Pricing Model (CAPM). The CFO estimates the Beta as 090 Management wants to use the year bond rate as the free rate, arguing that investors should make long term investments; thatrate is today. The expected return on the stock market as a whole has been estimated to be 7, 10% and 12 by various studies. The CFO asks that you use an expected return of 9% for the average stock. The market risk Premium Pul will be minus Calculate the cost of equity (Rs) using the CAPM. The formula is Rs RF + RP Rs is the required return on equity or the cost of Equity is the risk free rute, return in excess of the risk free rate, and B. (Beta) is the stocks relative risk B is also described as the estimate of the amount of risk that an individual stock contributes to a well balance portale is the required stock market 2. The Discounted Cash Flow model is referred to as the Direct Dividend Model in the MBA 620 course materials. The formula reduces to Rs / Pol+ where his is the required return on equity or the Cost of Equity, D, is the expected future dividend, Po is the price of the stock today and is the expected growth in dividends. The CrO notes that the expected tuture dividend is $2.3 and the expected growth rate is 7%. For this calculation, please use the March 17, 2020 closing price of $1.38 70 per share. Calculate the cost of equity (Ra) using the DF approach 3. Cristina Flores is an advisor to a board member who works at a private equity firm. She has told the Cho that sophisticated investors use a quick estimate of the cost of equity. She says that the cost of equity must logically be higher than the company's debt rate. The estimate is that to should be added to the company's long term interest rates, McCormick & Company estimates its current borrowing cost at 4%. Make your own estimate of the relative risk of McCormick & Company. Then, calculate the cost of equity (1) using this own debt plus x to formula 4. Calculate the weighted Average cost of Capital (WACC) for McCormick and Company using the formula WACC = ( WRX-T7+fW, *R) Note that - Rs the cost of equity Rd the cost of debt Ts the tax rate W = Value of debt / (Value of debt plus value of equity) w Value of equity / (Value of debt plus value of equity) Note that the weight of debt plus the weight of equity must total to 100%, as there are only two components in the capital vtructure In order to estimate the weights of debt and equity in the total capital structure, the CFO suggests using the book value of debt and the market value of equity. To determine the book value of debt, use data from the year end November 2019 McCormick 10-K Look on the balance sheet and add the following -- Short term borrowings, Current portion of long term deht, and long term debt. To determine the market value of equity, use the following data: On March 17, 2020 the market value of equity for Market Cap for McCormick (MC)-as found on Yahoo finance was in Use 4 for the cost of debt. Use 275$ as the tax rate an estimate of the combination of federal and state income tax rates. Make your own best estimate of the cost of city based on your work in Questions 1. 2. and 3 Instructions Cost of Capital Capital Budgeting Dupa Settings Project 5 Workbook-2202 - Excel Sign in Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Calibri 2 cut Copy - Format Painter General Wrap Text + Merge Center - BIU- A. $. % Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles board Font Alignment 15 Number Ces Ed B D F 0 M H 4. Calculate the weighted Average Cost of Capital (WACC) for McCormick and Company using the formula WACC (Woo *(1-( WR) Note that -- Rs = the cost of equity Rd the cost of debt T=the tax rate Wo Value of debt/value of debt plus value of equity) Ws -Value of equity / (Value of debt plus value of equity **Note that the weight of debt plus the weight of equity must total to 100%, there are only two components in the capital structure." In order to estimate the weights of debt and equity in the total capital structure, the CFO Sests using the book value of debt and the market value of equity to determine the book value of delt, data from the year end November 2019 McCormick 10 K. Look on the Balance sheet and add the following - Short term borrowings, Current portion of long term debit, and long term de To determine the market value of equity, use the following data: On March 17, 2020 the market value of equity for Market Cap for McCormick (MC)-as found on Yahoo Finance was $18.box Use 4% for the cost of debt. Use 27.5% as the tax rate an estimate of the combination of federal and state income tax rates. Make your own best estimate of the cost of equity based on your work questions 1, 2, and Answer Questions Below Beta Market Premium 1 Risk Free Rate Market Premium Beta Risk Free rate Display Settings Instructions Cost of Capital Capital Budgeting Calibri 2 Wrap Text General Paste Da Copy Format Painter VAA BA ES B IU- III HEH Merge & Center - $ % Clipboard Font Conditional Fc Formatting 51 Alignment Number E50 > f B D E F Answer Questions Below #1 Risk Free Rate 54 55 56 57 58 59 60 61 62 63 Market Premium Beta Beta x Market Premium Risk Free rate CAPM cost of Equity #2 64 D 65 01/Po B DCF Cost of Equity 66 67 68 69 70 71 22 23 14 "5 "6 #13 Bond Rate Premium Cost of Equity 7 8 114 Calculate weights Short-term borrowings Instructions Cost of Capital Capital Budgeting + Clipboard Merge & Center - $ - % 0 Font Conditional Formatting Alignment Number B D F G #4 Calculate weights Short-term borrowings Current portion of long-term debt Long-term debt Total Debt Market Cap Equity Total Capital Weights Debt Interest rate Tax Rate After tax cost of debt Cost of equity Debt term Equity term WACC Instructions Cost of Capital Capital Budgeting Projects Workbook 2202 - Excel Sign in Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Calibri VAA Gener F Autosum. -Cut Copy *Format Painter board BIU- O A Wrap Text Merge & Center Alignment $. % Insert Delete Format Conditional Formatas Cell Formatting Table Styles Styles Font F Number Ces > B F G H 1 M Information from McCormick Your task is to make an estimate of McCormick & Company's Weighted Average cost of Capital (WACC) to use as the discount rate for evaluating capital projects, such as the one in the next to The CFO plans to borrow a portion of the required $350 million initial investment for this project using 20 year bonds. For most of the past 10 years, the company has used as the discount rate the was based on a study of the company's WACC in early 2010. Interest rates have risen since the last study when the McCormick & Co rate average interest rate was. We wil us today. Also, there has been a change in the weights between debt and equity in the overall capital structure since 2010 The most nificant change was the acquisition of RB Foods. It is the main reason for this study. Here is what our MOBA reported about that acquisition On August 17, 2017, we completed the acquisition of Reckitt Benckiser's Food Division (R8 Foods") from Reckitt Benckiser Group ple. The purchase price was approximately 56.2 billion, net of acquired cash. The acquisition was funded through our issuance of approximately 6,35 million shares of common stock non-voting see note 13 of the financial statements and through new borrowing comprised of senior unsecured notes and pre-payable term loans (see note 6 of the financial statements). The acquired market leading brands of Foods include French's", Frank's Red Hot and Cattlemen's which are a natural strategic fit with our robust global branded flower portfolio. We believe that these additions move us to a leading position in the attractive us condiments category and provide significant international growth opportunities for our consumer and industrial segments The Ro Foods acquisition resulted in acquisitions contributing more than one-third of our sales growth in 2017 and is expected to result in acquisitions contributing more than one-third of our sales growth in 2018 As part of your work, you will be asked for a recommendation for the cost of equity. There are three widely accepted methods used to calculate the cost of equity. They are the Capital Asset Pricing Model (CAPM), the Discounted Cash Flow approach (DCF) and the debt rate plus a risk premium of to recommended by one board member This last is often used by very wealthy investors as check on the other two methods. Your recommendation for the cost of equity will be necessary so you can undertake the calculation estimating the WACC Questions: 1. You have been asked to calculate the cost of equity using the Capital Asset Pricing Model (CAPM). The CFO estimates the Beta as 0.90. Management wants to use the 30 year bond rate as the risk free rate, arguing that investors should make long term investments, that rate is today. The expected return on the stock market as a whole has been estimated to be 7, 10 and 12 by various studies. The CFO asks that you use an expected return of 9 for the average stock. The market risk Premium (RP) will be on minus 3 Instructions Cost of Capital Capital Budgeting Display Settings Projects Workbook 2202 - Excel Sign in Home Insert Page Layout Formulas Data Review View Tell me what you want to do Calibri 11 - General * Cut Copy - Format Painter pboard 2 Wrap Text Merge Center - BIU- $ .% Insert Delete Format Conditional Formatos Cell Formatting Table Styles Styles 1 Font Alignment Number Cells f B F D E Questions: 1. You have been asked to calculate the cost of equity using the Capital Asset Pricing Model (CAPM). The CFO estimates the Beta as 090 Management wants to use the year bond rate as the free rate, arguing that investors should make long term investments; thatrate is today. The expected return on the stock market as a whole has been estimated to be 7, 10% and 12 by various studies. The CFO asks that you use an expected return of 9% for the average stock. The market risk Premium Pul will be minus Calculate the cost of equity (Rs) using the CAPM. The formula is Rs RF + RP Rs is the required return on equity or the cost of Equity is the risk free rute, return in excess of the risk free rate, and B. (Beta) is the stocks relative risk B is also described as the estimate of the amount of risk that an individual stock contributes to a well balance portale is the required stock market 2. The Discounted Cash Flow model is referred to as the Direct Dividend Model in the MBA 620 course materials. The formula reduces to Rs / Pol+ where his is the required return on equity or the Cost of Equity, D, is the expected future dividend, Po is the price of the stock today and is the expected growth in dividends. The CrO notes that the expected tuture dividend is $2.3 and the expected growth rate is 7%. For this calculation, please use the March 17, 2020 closing price of $1.38 70 per share. Calculate the cost of equity (Ra) using the DF approach 3. Cristina Flores is an advisor to a board member who works at a private equity firm. She has told the Cho that sophisticated investors use a quick estimate of the cost of equity. She says that the cost of equity must logically be higher than the company's debt rate. The estimate is that to should be added to the company's long term interest rates, McCormick & Company estimates its current borrowing cost at 4%. Make your own estimate of the relative risk of McCormick & Company. Then, calculate the cost of equity (1) using this own debt plus x to formula 4. Calculate the weighted Average cost of Capital (WACC) for McCormick and Company using the formula WACC = ( WRX-T7+fW, *R) Note that - Rs the cost of equity Rd the cost of debt Ts the tax rate W = Value of debt / (Value of debt plus value of equity) w Value of equity / (Value of debt plus value of equity) Note that the weight of debt plus the weight of equity must total to 100%, as there are only two components in the capital vtructure In order to estimate the weights of debt and equity in the total capital structure, the CFO suggests using the book value of debt and the market value of equity. To determine the book value of debt, use data from the year end November 2019 McCormick 10-K Look on the balance sheet and add the following -- Short term borrowings, Current portion of long term deht, and long term debt. To determine the market value of equity, use the following data: On March 17, 2020 the market value of equity for Market Cap for McCormick (MC)-as found on Yahoo finance was in Use 4 for the cost of debt. Use 275$ as the tax rate an estimate of the combination of federal and state income tax rates. Make your own best estimate of the cost of city based on your work in Questions 1. 2. and 3 Instructions Cost of Capital Capital Budgeting Dupa Settings Project 5 Workbook-2202 - Excel Sign in Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Calibri 2 cut Copy - Format Painter General Wrap Text + Merge Center - BIU- A. $. % Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles board Font Alignment 15 Number Ces Ed B D F 0 M H 4. Calculate the weighted Average Cost of Capital (WACC) for McCormick and Company using the formula WACC (Woo *(1-( WR) Note that -- Rs = the cost of equity Rd the cost of debt T=the tax rate Wo Value of debt/value of debt plus value of equity) Ws -Value of equity / (Value of debt plus value of equity **Note that the weight of debt plus the weight of equity must total to 100%, there are only two components in the capital structure." In order to estimate the weights of debt and equity in the total capital structure, the CFO Sests using the book value of debt and the market value of equity to determine the book value of delt, data from the year end November 2019 McCormick 10 K. Look on the Balance sheet and add the following - Short term borrowings, Current portion of long term debit, and long term de To determine the market value of equity, use the following data: On March 17, 2020 the market value of equity for Market Cap for McCormick (MC)-as found on Yahoo Finance was $18.box Use 4% for the cost of debt. Use 27.5% as the tax rate an estimate of the combination of federal and state income tax rates. Make your own best estimate of the cost of equity based on your work questions 1, 2, and Answer Questions Below Beta Market Premium 1 Risk Free Rate Market Premium Beta Risk Free rate Display Settings Instructions Cost of Capital Capital Budgeting Calibri 2 Wrap Text General Paste Da Copy Format Painter VAA BA ES B IU- III HEH Merge & Center - $ % Clipboard Font Conditional Fc Formatting 51 Alignment Number E50 > f B D E F Answer Questions Below #1 Risk Free Rate 54 55 56 57 58 59 60 61 62 63 Market Premium Beta Beta x Market Premium Risk Free rate CAPM cost of Equity #2 64 D 65 01/Po B DCF Cost of Equity 66 67 68 69 70 71 22 23 14 "5 "6 #13 Bond Rate Premium Cost of Equity 7 8 114 Calculate weights Short-term borrowings Instructions Cost of Capital Capital Budgeting + Clipboard Merge & Center - $ - % 0 Font Conditional Formatting Alignment Number B D F G #4 Calculate weights Short-term borrowings Current portion of long-term debt Long-term debt Total Debt Market Cap Equity Total Capital Weights Debt Interest rate Tax Rate After tax cost of debt Cost of equity Debt term Equity term WACC Instructions Cost of Capital Capital Budgeting