Please go through and solve within excel and show you solved! Really appreciate it!

WINE Time. Case Wine Time, Inc., (WT) is a leading producer of wine in the United States. The firm was founded in 1950 by Robert Montoya, an Air Force veteran who had spent several years in France both before and after World War II. This experience convinced him that California could produce wines that were as good as or better than the best France had to offer. Originally, Robert Montoya sold his wine to wholesalers for distribution under their own brand names. Then in the early 1950s, when wine sales were expanding rapidly, he joined with his brother Marshall and several other producers to form Wine Time, Inc., which then began an aggressive promotion campaign. Today, its wines are sold throughout the world. The table wine market has matured and Wine Time' wine cooler sales have been steadily decreasing. Consequently, to increase winery sales, management is currently considering a potential new product: a premium varietal red wine using the cabernet sauvignon grape. The new wine is designed to appeal to middle-to-upper-income professionals. The new product, Suav Mauv, would be positioned between the traditional table wines and super premium table wines. In market research samplings at the company's Napa Valley headquarters, it was judged superior to various competing products. Sarah Sharpe, the financial vice president, must estimate the company's cost of capital, and then present her findings to the company's executive committee. As members of Sarah's financial staff, your job is to help her prepare these finding for her report to the executive committee. Part 1: Cost of Capital Analysis Sarah Sharpe has given you the following information to help you estimate Wine Time (WT), Inc.'s weighted average cost of capital for 2020 (Dec). (1) The bond quote on WT's long-term, semi-annual bond as reported in the financial press is as follows: Bonds WT Annual Coupon Rate Maturity Date 5.2% Dec 2045 Price% of par) 112.0 (2) Quotes on WT's common and preferred stock (pf) were as follows: Stock Div Div Yld% PE Vol(100s) Close Price Net Chg WT 2.10 3.0 456 80 + 14 WTpf 3.30 78 - 1/8 7.5 7.3 54 (3) The 10-year Treasury bond rate is 1%. The company's investment bank recommends using this long-term treasury rate as the risk free rate for capital budgeting purposes. (4) WT's federal-plus-state tax rate is 25 percent. (5) The firm's last dividend (Do) was $2.10, and recent dividends have been growing at a rate of about 14%. Some analysts expect the recent growth rate to continue, while others expect it to go to zero as new competition enters the market, but the majority anticipate a growth rate of about 9% for the indefinite future. The company has 2 million shares outstanding. (6) A prominent investment banking firm recently estimated that the market risk premium is 8 percent. WT's beta, as measured by several analysts who follow the stock, is 1.3. (7) Any new preferred stock would be sold at the current Close Price of $54 per share and would incur flotation costs of $2.50 per share. (8) The market value optimal target capital structure is based on the current market prices (from items (1) and (2)) times the following par value of bonds and shares of common and preferred stock respectively. Wine Time has bonds outstanding with a par value of $50 million Wine Time has 300,000 shares of preferred stock outstanding. Wine Time has 1.5 million shares of common stock outstanding. Sharpe then answered the following set of questions to complete her assigned task and has asked you to complete an independent verification of her results. Part I Questions: 1. What is your estimate of WT's after-tax cost of debt? 2. What is the estimate of the cost of preferred stock? 3. What is WT's estimated cost of retained earnings, using the CAPM approach? 4. What is the discounted cash flow (DCF) model estimate of WT's cost of retained earnings? 5. What is your choice of estimate for WT's cost of retained earnings? 6. What are WT's market value-based financing weights? 7. What is WT's weighted average cost of capital? Part II: Cash Flow and Capital Budgeting Analysis Production facilities for the new wine would be set up in an unused section of Wine Time' main plant. New machinery with an estimated total cost of $5,000,000 would be purchased. Furthermore, Wine Time' inventories (the new product requires aging in oak barrels made in France) would have to be increased by $600,000. This cash flow is assumed to occur at the time of the initial investment. The machinery has a remaining economic life of 5 years, and the company has obtained a special tax ruling that allows it to depreciate the equipment under the MACRS 5-year class life. MACRS depreciation percentages can be found in Appendix 12A of your textbook. The machinery is expected to have a salvage value of $450,000 after 5 years of use. The section of the plant in which production would occur had not been used for several years and, consequently, had suffered some deterioration. Last year, as part of a routine facilities improvement program, $500,000 was spent to rehabilitate that section of the main plant. Earnie Jones, the chief accountant, believes that this outlay, which has already been paid and expensed for tax purposes, should be charged to the wine project. His contention is that if the rehabilitation had not taken place, the firm would have had to spend the $500,000 to make the plant suitable for the wine project Wine Time's management expects to sell 70,000 bottles of the new wine in year 1 followed by annual sales of 120,000 for each of the following 4 years, at a wholesale price of $41 per bottle, but $25 per bottle would be needed to cover cash operating costs. In examining the sales figures, Sharpe noted a short memo from Wine Time's sales manager which expressed concern that the wine project would cut into the firm's sales of other wines-this type of effect is called cannibalization. Specifically, the sales manager estimated that existing wine sales would fall by 5 percent if the new wine were introduced. Sharpe then talked to both the sales and production managers and concluded that the new project would probably lower the firm's existing wine sales by $200,000 per year, but, at the same time, it would also reduce production costs by $125,000 per year, all on a pre-tax basis. Wine Time's federal-plus-state tax rate is 25 percent, and its overall cost of capital is the weighted average cost of capital from #7 from Part I of this case. Now assume that you are Sharpes assistant and she has asked you to analyze this project, and then to present your findings in a "tutorial" manner to Wine Time's executive committee. As financial vice president, Sharpe wants to educate some of the other executives, especially the marketing and sales managers, in the theory of capital budgeting so that these executives will have a better understanding of capital budgeting decisions. Therefore, Sharpe wants you to ask and then answer a series of questions as set forth next. Keep in mind that you will be questioned closely during your presentation, so you should understand every step of the analysis, including any assumptions and weaknesses that may be lurking in the background and that someone might spring on you in the meeting. Part II Questions 1. Should the $500,000 that was spent to rehabilitate the plant be included in the analysis? Explain. 2. Suppose another winemaker had expressed an interest in leasing the wine production site for $200,000 a year. If this were true in fact it was not), how would that information be incorporated into the analysis? 3. What is Wine Time's Year 0 total initial investment outlay on this project? 4. Estimate the project's operating cash flows for years 1 through 5. 5. What is the expected non-operating (terminal) cash flow when the project is terminated at the end of Year 52 6. Using your WACC from Part I, what is the project's NPV and IRR? Should the project be undertaken? 7. Now, assume WT has enough operating income to write off (deduct) or expense the $5,000,000 project cost at the beginning of the project (time period 0) under current tax laws. This would lead to an up-front tax deduction and no depreciation on an annual basis during the life of the project. Redo your cash flow estimates from questions 4, 5, and 6 under the full immediate expensing of the project cost allowed under the new tax laws. What is the project's NPV and IRR under the new tax laws? Should the project be accepted? WINE Time. Case Wine Time, Inc., (WT) is a leading producer of wine in the United States. The firm was founded in 1950 by Robert Montoya, an Air Force veteran who had spent several years in France both before and after World War II. This experience convinced him that California could produce wines that were as good as or better than the best France had to offer. Originally, Robert Montoya sold his wine to wholesalers for distribution under their own brand names. Then in the early 1950s, when wine sales were expanding rapidly, he joined with his brother Marshall and several other producers to form Wine Time, Inc., which then began an aggressive promotion campaign. Today, its wines are sold throughout the world. The table wine market has matured and Wine Time' wine cooler sales have been steadily decreasing. Consequently, to increase winery sales, management is currently considering a potential new product: a premium varietal red wine using the cabernet sauvignon grape. The new wine is designed to appeal to middle-to-upper-income professionals. The new product, Suav Mauv, would be positioned between the traditional table wines and super premium table wines. In market research samplings at the company's Napa Valley headquarters, it was judged superior to various competing products. Sarah Sharpe, the financial vice president, must estimate the company's cost of capital, and then present her findings to the company's executive committee. As members of Sarah's financial staff, your job is to help her prepare these finding for her report to the executive committee. Part 1: Cost of Capital Analysis Sarah Sharpe has given you the following information to help you estimate Wine Time (WT), Inc.'s weighted average cost of capital for 2020 (Dec). (1) The bond quote on WT's long-term, semi-annual bond as reported in the financial press is as follows: Bonds WT Annual Coupon Rate Maturity Date 5.2% Dec 2045 Price% of par) 112.0 (2) Quotes on WT's common and preferred stock (pf) were as follows: Stock Div Div Yld% PE Vol(100s) Close Price Net Chg WT 2.10 3.0 456 80 + 14 WTpf 3.30 78 - 1/8 7.5 7.3 54 (3) The 10-year Treasury bond rate is 1%. The company's investment bank recommends using this long-term treasury rate as the risk free rate for capital budgeting purposes. (4) WT's federal-plus-state tax rate is 25 percent. (5) The firm's last dividend (Do) was $2.10, and recent dividends have been growing at a rate of about 14%. Some analysts expect the recent growth rate to continue, while others expect it to go to zero as new competition enters the market, but the majority anticipate a growth rate of about 9% for the indefinite future. The company has 2 million shares outstanding. (6) A prominent investment banking firm recently estimated that the market risk premium is 8 percent. WT's beta, as measured by several analysts who follow the stock, is 1.3. (7) Any new preferred stock would be sold at the current Close Price of $54 per share and would incur flotation costs of $2.50 per share. (8) The market value optimal target capital structure is based on the current market prices (from items (1) and (2)) times the following par value of bonds and shares of common and preferred stock respectively. Wine Time has bonds outstanding with a par value of $50 million Wine Time has 300,000 shares of preferred stock outstanding. Wine Time has 1.5 million shares of common stock outstanding. Sharpe then answered the following set of questions to complete her assigned task and has asked you to complete an independent verification of her results. Part I Questions: 1. What is your estimate of WT's after-tax cost of debt? 2. What is the estimate of the cost of preferred stock? 3. What is WT's estimated cost of retained earnings, using the CAPM approach? 4. What is the discounted cash flow (DCF) model estimate of WT's cost of retained earnings? 5. What is your choice of estimate for WT's cost of retained earnings? 6. What are WT's market value-based financing weights? 7. What is WT's weighted average cost of capital? Part II: Cash Flow and Capital Budgeting Analysis Production facilities for the new wine would be set up in an unused section of Wine Time' main plant. New machinery with an estimated total cost of $5,000,000 would be purchased. Furthermore, Wine Time' inventories (the new product requires aging in oak barrels made in France) would have to be increased by $600,000. This cash flow is assumed to occur at the time of the initial investment. The machinery has a remaining economic life of 5 years, and the company has obtained a special tax ruling that allows it to depreciate the equipment under the MACRS 5-year class life. MACRS depreciation percentages can be found in Appendix 12A of your textbook. The machinery is expected to have a salvage value of $450,000 after 5 years of use. The section of the plant in which production would occur had not been used for several years and, consequently, had suffered some deterioration. Last year, as part of a routine facilities improvement program, $500,000 was spent to rehabilitate that section of the main plant. Earnie Jones, the chief accountant, believes that this outlay, which has already been paid and expensed for tax purposes, should be charged to the wine project. His contention is that if the rehabilitation had not taken place, the firm would have had to spend the $500,000 to make the plant suitable for the wine project Wine Time's management expects to sell 70,000 bottles of the new wine in year 1 followed by annual sales of 120,000 for each of the following 4 years, at a wholesale price of $41 per bottle, but $25 per bottle would be needed to cover cash operating costs. In examining the sales figures, Sharpe noted a short memo from Wine Time's sales manager which expressed concern that the wine project would cut into the firm's sales of other wines-this type of effect is called cannibalization. Specifically, the sales manager estimated that existing wine sales would fall by 5 percent if the new wine were introduced. Sharpe then talked to both the sales and production managers and concluded that the new project would probably lower the firm's existing wine sales by $200,000 per year, but, at the same time, it would also reduce production costs by $125,000 per year, all on a pre-tax basis. Wine Time's federal-plus-state tax rate is 25 percent, and its overall cost of capital is the weighted average cost of capital from #7 from Part I of this case. Now assume that you are Sharpes assistant and she has asked you to analyze this project, and then to present your findings in a "tutorial" manner to Wine Time's executive committee. As financial vice president, Sharpe wants to educate some of the other executives, especially the marketing and sales managers, in the theory of capital budgeting so that these executives will have a better understanding of capital budgeting decisions. Therefore, Sharpe wants you to ask and then answer a series of questions as set forth next. Keep in mind that you will be questioned closely during your presentation, so you should understand every step of the analysis, including any assumptions and weaknesses that may be lurking in the background and that someone might spring on you in the meeting. Part II Questions 1. Should the $500,000 that was spent to rehabilitate the plant be included in the analysis? Explain. 2. Suppose another winemaker had expressed an interest in leasing the wine production site for $200,000 a year. If this were true in fact it was not), how would that information be incorporated into the analysis? 3. What is Wine Time's Year 0 total initial investment outlay on this project? 4. Estimate the project's operating cash flows for years 1 through 5. 5. What is the expected non-operating (terminal) cash flow when the project is terminated at the end of Year 52 6. Using your WACC from Part I, what is the project's NPV and IRR? Should the project be undertaken? 7. Now, assume WT has enough operating income to write off (deduct) or expense the $5,000,000 project cost at the beginning of the project (time period 0) under current tax laws. This would lead to an up-front tax deduction and no depreciation on an annual basis during the life of the project. Redo your cash flow estimates from questions 4, 5, and 6 under the full immediate expensing of the project cost allowed under the new tax laws. What is the project's NPV and IRR under the new tax laws? Should the project be accepted