Hello, I need help with this Excel assignment. It involves the use of what if analysis.

Capital Budgeting - Cott Corpo Suppose that the Cott Corporation (COT) is considering adding a new product line. Currently, Cott sells apple juice and they are considering selling a new fruit drink. The fruit drink will have a selling price of $1.64 per container. The plant has excess capacity in a fully depreciated building to process the fruit drink product line. The new fruit drink will be discontinued in four years. The new equipment is depreciated to zero and falls under the 3-year MACRS depreciation schedule. The new fruit drink requires an increase in inventory of $275,000, an increase in accounts receivable of $135,000, and an increase in accounts payable of $110,000. Projected sales are 4,000,000 containers in the first year, with a 9% growth rate for each subsequent year. Variable costs are 60% of total revenues and fixed costs are $1,250,000 each year. The new equipment costs $2,500,000 and has a salvage value of $500,000. The corporate tax rate for COT is 35%. Step 1: Calculate COT's Weighted Average Cost of Capital (WACC) using the "WACC" tab. Navigate to Yahoo! Finance to begin gathering data. a. Start by entering the ticker 'COT' on the home page on yahoo finance in the Quote Lookup window. b. Enter the current stock price from Yahoo!Finance. For consistency, I have entered the current stock price o c. Look up the estimated growth rate for COT on Yahoo!Finance using the following steps: 1. Enter the ticker symbol for COT under quote lookup in order to go to the COT summary page. 2. Click on Analysts. 3. You will find the 'Next 5 Years (per annum)' growth rate at the bottom of the page under the Grow ***The current analyst estimates for COT's growth rate over the next five years is N/A. Therefore, it is more appropriate to use the industry average growth rate in our analysis at t 4. Next period's expected dividend is under Dividends & Splits on the Statistics tab and is labeled as d. Look up COT's beta and shares outstanding using the following steps: 1. Navigate to Morningstar's website for COT's beta and shares outstanding 2. Enter the ticker symbol in the Quote search window at the top of the page. 3. Beta and shares outstanding are on the bottom right-hand side under Market Data and Short Inter e. The Five-year Treasury Yield will be used as the proxy for the risk-free rate (because the project is expected to 1. Navigate to the US Treasury Department website 2. Click on Data and Charts Center from the Resource Center dropdown menu. 3. Click on the 'Daily Treasury Yield Curve Rates' and record the 5-yr rate for October 13, 2016. f. To determine the market risk premium, we can rely on an annual report written by Pablo Fernandez from the IE Dr. Fernandez is a well-known finance researcher who conducts a global survey each year that simply asks com and professors/academic researchers what market-risk premium and risk-free rate they are using. He compiles a The 2016 report contains average market-risk premium and risk-free rate information for 71 countries. The average market-risk premium for the U.S. is reported in Table 2 of the following report: http://www.valuewalk.com/wp-content/uploads/2016/05/SSRN-id2776636.pdf g. Look up COT's outstanding bond issues in order to calculate the firm's weighted average cost of debt. 1. Navigate to Morningstar's website for COT's outstanding debt information 2. Enter the ticker symbol in the Quote search window at the top of the page. 3. Click on the Bonds link underneath the company name (there is also a Bonds link above the compa 4. Under the Yield to Maturity section is the list of COT's outstanding bond issues that correspond to 5. Fill in the input variable cells and create formulas in the calculation cells in order to calculate the fir *The 8.125% issue was a callable bond that was called and retired in September 2014 h. Calculate the weighted average cost of capital (WACC) in the calculation cells at the bottom of the "WACC" ta i. Answer the following questions: 1. What is the weighted average pre-tax cost of debt? 2. What s the cost of equity using the CAPM approach? 3. What is the cost of equity using the DDM approach? 4. What is the average cost of equity? 5. What is COT's Weighted average cost of capital (WACC)? Step 2: Create a pro-forma income statement for each year of the project, calculate the base ca IRR, and conduct sensitivity analyses using the "Pro-Forma" tab a. Use the input variables to create pro-forma income statements for each year of the project in order to calculate (For row 36, you can either use the EXCEL formula PV() or use the algebraic formula for PV of a lump sum.) b. Calculate the NPV and the IRR of the fruit drink project. 1. What is the Base Case NPV of the fruit drink project? 2. What is the Base Case IRR of the fruit drink project? c. Conduct a senstivity analysis of the project's base case NPV on the projected sales price and sales volume. You will construct a Sensitivity Analysis Table (SAT) that presents an analysis of what is projected to happen to To construct the SAT requires that you use the "What-If Analysis" tool that is located under "Data Tools" on th You will use the "Data Table" option under "What-If Analysis". Part of the asssignment is learning how to use Therefore, if you have not used this previously, you will need to research its use. *** On the Pro-Forma tab, I have provided the template and many notes, hints, and cell commments to (The Sensitivity Analysis begins at row 42) d. Calculate percentage and dollar sensitivities of NPV on both projected sales price and projected sales volume. 1. What is the Dollar (Cent) sensitivity of NPV to a change in Price? 2. What is the Percentage sensitivity of NPV to a change in Price? 3. What is the Dollar sensitivity of NPV to a change in Sales Volume? 4. What is the Percentage sensitivity of NPV to a change in Sales Volume? e. Conduct a sensitivity analysis of the project's base case NPV on the projected fixed and variable costs. You will construct a Sensitivity Analysis Table (SAT) that presents an analysis of what is projected to happen t f. Calculate percentage and dollar sensitivities of NPV on both projected fixed and variable costs. 1. What is the Dollar sensitivity of NPV to a change in Fixed Costs? 2. What is the Percentage sensitivity of NPV to a change in Fixed Costs? 3. What is the Dollar sensitivity of NPV to a change in Variable Costs? 4. What is the Percentage sensitivity of NPV to a change in Variable Costs? g. What can you conclude about the new fruit drink project based upon the sensitivity analyses? Step 3: Create a Base Case NPV profile using the "NPV Profile" tab. a. Instructions for this graphing step are in red on the "NPV Profile" tab. HINTS for all steps: *Unless the value is given above (or pulled straight from external data sources), all highlighted cells re *Do not forget the correct sign conventions. *Double-check all calculations using your calculator eting - Cott Corporation ule. The new of $135,000, ng the "WACC" tab. Lookup window. d the current stock price on COT as the closing price on Thursday, October 13, 2016 he COT summary page. of the page under the Growth Estimates section. five years is N/A. th rate in our analysis at this time.*** tistics tab and is labeled as the 'Forward Annual Dividend Rate'. arket Data and Short Interest se the project is expected to last 4 years) and can be found using the following steps: te for October 13, 2016. Pablo Fernandez from the IESE Business School in Spain. ch year that simply asks companies, analysts, regulators, hey are using. He compiles and reports the data in his annual report. on for 71 countries. verage cost of debt. Bonds link above the company name) nd issues that correspond to those listed on the "WACC" tab s in order to calculate the firm's weighted average cost of debt. September 2014 he bottom of the "WACC" tab. t, calculate the base case NPV & project in order to calculate the PV of each year's cash flows in row 36. mula for PV of a lump sum.) price and sales volume. hat is projected to happen to the base case NPV if price and sales volume turn out to be +/- 20%. ed under "Data Tools" on the "Data" tab. ment is learning how to use this tool for sensitivity analysis. s, and cell commments to help guide you.*** and projected sales volume. d and variable costs. what is projected to happen to the base case NPV if fixed and variable costs turn out to be +/- 12 percentage points. riable costs. itivity analyses? ), all highlighted cells require a calculation done using formulas, or a reference to another cell. Weighted Average Cost of Capital - Cott Corp. (C Input Variable Cells Current Price Estimated Growth Rate Beta Number of Shares Outstanding Next Period's Expected Dividend 5-Year Treasury Yield Tax Rate Expected Market Risk Premium Name Cott Beverages 6.75% Cott Beverages 5.375% Cott Beverages 8.125% Cott Beverages 8.375% $13.67 Calculation Cells 35.0% Maturity 1/1/2020 7/1/2022 9/1/2018 11/15/2017 COT Bond Issues Price Par Value 0 0.00% Total Market Value of Debt: Weighted Average Cost of Capital (WACC) Market Value of Debt Market Value of Equity Total Market Value Weight (%) Debt Weight (%) Equity Cost of Equity - CAPM Approach Cost of Equity - DDM Approach Cost of Equity - Average Pre-Tax Cost of Debt WACC apital - Cott Corp. (COT) Bond Issues Market Value Weight of Issue YTM Weighted Average YTM: Weighted YTM Pro Forma - COT I. Fill in the following data on the proposed capital budgeting project. Economic life of project in years. Price of New Equipment Fixed Costs Salvage value of New Equipment Change in Inventory Change in A/R Change in A/P Unit Price ($) First Year Units (N) Variable Costs (%) Marginal Tax Rate (%) Growth Rate (%) WACC (%) Spreadsheet for determining Cash Flows Timeline: Year II. Net Investment Outlay = Initial CFs Equipment Price Change in NWC III. Cash Flows from Operations Total Revenues Variable Costs Fixed Costs 0 1 Depreciation Earnings Before Taxes Taxes Net Income Net operating CFs IV. Terminal Cash Flows After-Tax Salvage Value Recovery of NWC Total Cash Flows Present Value of CFs V. Calculate: NPV IRR Sensitivity Analyses I. Conduct Sensitivity Analysis of NPV on Price and Sales Volume Unit Price Dollar sensitivity of NPV to a change in Price Percentage sensitivity of NPV to a change in Price Dollar sensitivity of NPV to a change in Sales Volume Percentage sensitivity of NPV to a change in Sales Volume II. Conduct Sensitivity Analysis of NPV on Fixed and Variable Costs Fixed Costs Dollar sensitivity of NPV to a change in Fixed Costs Percentage sensitivity of NPV to a change in Fixed Costs Dollar sensitivity of NPV to a change in Variable Cost (%) Percentage sensitivity of NPV to a change in Variable Costs (%) ma - COT Input Variable Cells Calculation Cells Note: Cells C21 and C22 represent the initial cash flows. Columns D through G are the operating cash flows. Cells G33 and G34 represent terminal cash flows. 2 ty Analyses Sales Volume 3 4 Note: I refer to this as the "Sensitivity Analysis Table" (SAT). Hint: This is the area that you will highlight in order to use the "Data Table" option When this SAT is correct, the NPV in cell E48 will equal the base case NPV from C3 Hint: Divide the entire Dollar Sensitivity formula in cell E52 by 100 so that our ana represents the "Cent" sensitivity. This is more relevant because the juice drink is a cheap product. In other words, the firm would only adjust the selling price by cents Hint: These Percentage Sensitivity formulas must be divided by 100 to present corr Variable Costs When this SAT is correct, the NPV in cell E65 will equal the base case NPV from C3 Hint: This Percentage Sensitivity formula must be divided by 100 to present correct er to use the "Data Table" option under the "What-If Analysis" tool. equal the base case NPV from C38 in the pro-forma above. n cell E52 by 100 so that our analysis actually vant because the juice drink is a relatively y adjust the selling price by cents, not dollars. be divided by 100 to present correctly in the cell. equal the base case NPV from C38 in the pro-forma above. divided by 100 to present correctly in the cell. Creating a NPV Profile - COT Discount Rate: Year CF 0% PV(CF) 2% PV(CF) 0% 2% 0 1 2 3 4 NPV Discount Rate: V Profile - COT 4% PV(CF) 6% PV(CF) 8% PV(CF) *Make sure that the Pro-Forma NPV on the "P Create a NPV profile below by creating a scatte Cells B5 to B9 in this worksheet can link to cell Calculate the present value of cash flows in col NPV in row 11 is simply the sum of rows 5 thro Rows 11 & 12 are used to create the NPV profi 4% 6% 8% that the Pro-Forma NPV on the "Pro-Forma" tab is set to the Base Case V profile below by creating a scatter chart of rows 11 & 12. Use "Scatter with Straight Lines and Markers". B9 in this worksheet can link to cells C35 through G35 in the 'Pro Forma' worksheet. he present value of cash flows in columns C through G using each of the discount rates in row 3. 11 is simply the sum of rows 5 through 9 for each discount rate. 12 are used to create the NPV profile graph below