How to make npv profile in excel

Case Instructions
Capital Budgeting - The Coca-Cola Company (KO)
The Coca-Cola Company (KO) is considering selling a new fruit drink. KO has spent $520,000 to develop and test market the new product,
and the new fruit drink will initially be introduced to a limited regional market in order to better gauge demand before taking it national.
This introduction stage will last four years. During this introductory stage, the fruit drink will have a selling price of $2.19 per unit.
The current plant facility in this region has excess capacity in a fully depreciated building to process the fruit drink product line.
The new equipment costs $2,615,000 and is depreciated to zero under the 3-year MACRS depreciation schedule. Projected sales
are 3,670,000 units in the first year, with a 6.75% growth rate for each subsequent year. Variable costs are 67% of total revenues,
and fixed costs are $1,425,000 per year. Currently, similar projects in the portfolio of KO require $0.19 in net working capital
(NWC) to support each dollar of sales in each year. In other words, as revenues change, NWC will adjust to support this
change. The NWC needed for each sales year must be in place at the beginning of that year (i.e., by the end of the previous year).
All changes to NWC will be liquidated and recovered by project-end, and the new equipment is forecasted to have a salvage
value of $415,000 at this time. The corporate tax rate for KO is 21%.
*****Do not delete or add rows, columns, or cells to this template. *****
Step 1: Calculate KO'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 'KO' 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 on KO as the closing price on Friday, April 24, 2020
c. Look up the estimated growth rate, expected dividend, and beta on Yahoo!Finance using the following steps:
1. Enter the ticker symbol for KO under quote lookup in order to go to the KO summary page.
2. Click on the Analysis tab.
3. You will find the 'Next 5 Years (per annum)' growth rate at the bottom of the page under the Growth Estimates section.
4. Beta is on the Statistics tab under Stock Price History.
5. Next period's expected dividend is under Dividends & Splits on the Statistics tab and is labeled as the 'Forward Annual Dividend Rate'.
d. The Five-year Treasury Yield will be used as the proxy for the risk-free rate (because the project is expected to last 4 years) and can be found using the following steps:
1. Navigate to the US Treasury Department website
2. Click on the 'Daily Treasury Yield Curve Rates' from the DATA dropdown menu and record the 5-yr rate for April 24, 2020.
e. To determine the market risk premium, we can rely on an annual report written by Pablo Fernandez from the IESE Business School in Spain.
Dr. Fernandez is a well-known finance researcher who conducts a global survey each year that simply asks companies, analysts, regulators,
and professors/academic researchers what market-risk premium and risk-free rate they are using. He compiles and reports the data in his annual report.
The 2020 report contains average market-risk premium and risk-free rate information for 81 countries.
The average market-risk premium for the U.S. can be found in Table 2 of this report, which can be downloaded for free at the following link:
https://papers.ssrn.com/sol3/papers.cfm?abstract_id=3560869
f. KO currently has bonds outstanding with a 2.75% coupon rate (paid semi-annually) and 9 years left until maturity. They are selling at 104% of par.
g. Calculate the weighted-average cost of capital (WACC) in the calculation cells at the bottom of the "WACC" tab assuming that KO has a target D/E ratio of 3.4.
h. Answer the following questions by linking these answer cells to the appropriate calculation cell on the "WACC" tab:
1. What is the weighted-average pre-tax cost of debt?
2. What is 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 KO's weighted-average cost of capital (WACC)?
Step 2: Create a pro-forma income statement for each year of the project, calculate the base case NPV &
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 the PV of each year's free cash flow in row 33.
(For row 33, 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 and link these answer cells to the appropriate calculation cell on the "Pro-Forma" tab:
1. What is the Base Case NPV of the fruit drink project?
2. What is the Base Case IRR of the fruit drink project?
3. Should KO accept this project?
c. Conduct a scenario analysis of the project's base case NPV and IRR under best/worst case expected units sold growth rates.
Use a 9.5% growth rate for the best case scenario and 1.75% growth rate for the worst case scenario.
1. What is the Best Case NPV of the fruit drink project?
2. What is the Best Case IRR of the fruit drink project?
3. What is the Worst Case NPV of the fruit drink project?
4. What is the Worst Case IRR of the fruit drink project?
*If your table is formulated correctly, you should need to change only the growth rate input variable in order to conduct the scenario analysis.
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 require
a calculation done using formulas, or a reference to another cell.
*Do not forget the correct sign conventions.
*Double-check all calculations using your calculator
*Pay attention to cells with embedded comments (i.e., cells with red triangles in the upper-right corner)
*****Do not delete or add rows, columns, or cells to this template. *****
http://www.treasury.gov/Pages/default.aspx https://finance.yahoo.com/ https://papers.ssrn.com/sol3/papers.cfm?abstract_id=3560869
WACC
Weighted Average Cost of Capital - The Coca-Cola Company (KO)
Input Variable Cells
Current Share Price $45.43
Estimated Growth Rate Calculation Cells
Beta
Next Year's Expected Dividend
5-Year Treasury Yield
Expected Market Risk Premium
Target D/E Ratio
Bond Price
Coupon Rate
Years Until Maturity
Tax Rate 21.0%
Weighted Average Cost of Capital (WACC)
Weight (%) Debt
Weight (%) Equity
Cost of Equity - CAPM Approach
Cost of Equity - DDM Approach
Cost of Equity - Average
Jeffrey Coy: Jeffrey Coy: Here you will take the simple average of the two cost of equity approaches, which you will use in your WACC calculation. If the growth rate is negative, you will simply use the CAPM approach alone.
Pre-Tax Cost of Debt
WACC
Pro-Forma
Pro Forma - KO
I. Fill in the following data on the proposed capital budgeting project.
Economic Life of Project (Yrs.)
Price of New Equipment ($)
Fixed Costs ($) Input Variable Cells
Salvage Value of New Equipment ($)
NWC ($) Calculation Cells
Unit Price ($)
First Year Units (N)
Variable Costs (%)
Marginal Tax Rate (%)
Growth Rate (%)
WACC (%)

Jeffrey Coy: Jeffrey Coy: This cell should reference cell B24 from the WACC tab. Spreadsheet for determining Cash Flows
Timeline: Year 0 1 2 3 4
II. Investment Cash Flows
Net Capital Spending
Book Value
Change in NWC
III. Cash Flows from Operations
Total Revenues
Variable Costs
Fixed Costs
Depreciation
Earnings Before Taxes
Taxes
Net Income
Net Operating CFs
IV. Free Cash Flows
Free Cash Flows
Present Value of FCFs
V. Calculate:
NPV
IRR
NPV Profile
Creating a NPV Profile - KO
Discount Rate: 0% 2% 4% 6% 8% *Make sure that the Pro-Forma NPV on the "Pro-Forma" tab is set to the Base Case
Year CF PV(CF) PV(CF) PV(CF) PV(CF) PV(CF) Create a NPV profile below by creating a scatter chart of rows 11 & 12. Use "Scatter with Straight Lines and Markers".
0 Cells B5 to B9 in this worksheet can link to cells C32 through G32 in the 'Pro Forma' worksheet.
1 Calculate the present value of cash flows in columns C through G using each of the discount rates in row 3.
2 NPV in row 11 is simply the sum of rows 5 through 9 for each discount rate.
3 Rows 11 & 12 are used to create the NPV profile graph below.
4 Do not forget the labels in your NPV profile graph.
NPV
Discount Rate: 0% 2% 4% 6% 8%