Calculate stock and bond valuations for PepsiCo. Specifically, the following critical elements must be addressed:Stock ValuationBased on the figures provided from the financial information you prepared for your selected company, calculate each of the following in Part I of the spreadsheet tab: Stock and Bond Valuation:Fill in the yellow highlighted cells with the matching data prepared for your selected company.Identify the new dividend yields if the company increased its dividend per share by $1.75.Identify the dividend yield if the firm doubled its outstanding shares using a stock split. Note that other numbers will be affected by a stock split such as new stock price, actual dividends paid, and number of outstanding shares. Stockholders' equity will not change.Calculate the rate of return on investment based on the data you calculated in Question 1.What effect would each of the calculations you performed have on shareholder value? In other words, suppose the company's goal is to maximize shareholder value. How will each of the situations support or inhibit that goal?To what extent do you feel the company's dividend policies support or hinder its strategies?Bond IssuancePepsiCo. issues a new 10-year bond for $300,000 on October 1, 2023. The bond will mature on October 1, 2033. The future value of this bond is $300,000. The bond was issued at the latest market rate of 5.0% fixed for 10 years, with interest payments paid semiannually. What is the present value of this bond using the four scenarios in Part II of the spreadsheet tab: Stock and Bond Valuation?Calculate the present value of the bond at issuance.Calculate the new present value of the bond if overall rates in the market increase by 2%.Calculate the new present value of the bond if overall rates in the market decrease by 2%.Calculate the present value of the bond if overall rates in the market remain the same as at issuance.How would each of the calculations you performed affect the company's decision to raise capital in this way? Explain for each situation, would bond valuation be a viable option for increasing capital?Assess how bond issuance policies support or hinder company strategies. 234 Pepsico, Inc.: (Stock Ticker: PEP) - (NAICS Code: 312111)5 Dollars and Shares in Millions, Except Per-Share Data6 Most Recent Calendar or Fiscal Year End―Then Two Prior Years7 Enter Years8 Net Cash Provided by Operating Activities9 Capital Expenditures0 Free Cash Flows1 Cash Dividends per Share2 Dividend Yield3 Diluted Earnings per Common Share4 Shareholder's Equity5 Total Common Shares Outstanding6 Common Stock Closing Price per Share7 Effective Tax Rate (Use 20%)8 Weighted Average Cost of Capital (Use 5%)9202220212020$10,811,000 $5,207,000.00 $5,604,000.00 $4.53 $11,616,000 $4,625,000.00 $6,991,000.00 $4.26 $2.64%7.80 $$17,149,000.00 $1377000.00171.73 $2.83%5.86 $16,043,000.00 $1383000.00150.43 $20%20%5%5%Information Location10,613,000 Mergent Intellect4,240,000.00 Mergent Intellect6,373,000.00 Formulated Cell Will Self-Populate (Do Not Change)4.02 Mergent Intellect2.95% Formulated Cell Will Self-Populate (Do Not Change)6.29 Mergent Intellect13,454,000.00 Mergent Intellect1380000.00 Yahoo Finance - Stock Market Live, Quotes, Business & Finance News Enter stock ticker sy136.31 Yahoo Finance - Stock Market Live, Quotes, Business & Finance News Enter stock ticker sy20%5% 235..23+Milestone Two: Stock Valuation and Bond Issuance (Fill in the yellow cells)PART I: STOCK VALUATIONRead the explanations to the right of the calculation cells for specific information on the data.Question 1: Initial Stock ValuationStockholder'sYearCash Div/Share($)DividendYieldEquity (inStockPriceTotal SharesOutstanding(millions ofmillions)shares)0% $$Instructions and ExplanationsPART I: STOCK VALUATIONNote: Use and eater the fiscal or year-end financial data for your selectedcompany and enter the appropriate data into each yellow highlighted cell.$$$0% $0% $$$-000Question 2. Stock Valuation-The New Dividend Yield if the Company Increased Its Dividend Per Share by 1.75Use the numbers from Question 1 as your baris to calculate those questions.YearCash Div/Share($) +1.75DividendYieldStockholder'sEquity (inmillions)StockPriceTotal SharesOutstanding(millions ofshares)#DIV/0!#DIV/0!#DIV/O!0$-0 $0 $-000$$$Question 3. The Dividend Yield, Cash Dividend Per Share, Stock Price, and Total Shares Outstanding After the Stock Split OccursUse the numbers from Question 1 to calculate those questions.Cash Div/ShareYear($)$$$Stockholder'sDividendYieldEquity (inmillions)StockTotal SharesOutstandingPrice (millions ofshares)0.00%0.00%0.00%0$0$00t--00Note: Stock price for your selected company is found in your selectedcompany's spreadsheet.Stockholder's Equity = Assets - Liabilities. This represents the ownership ofa corporations. Owners are called "stockholders" because they hold stocksor shares of the company. The main goal of every corporate manager is togenerate shareholder value. Shareholder Equity is also known asStockholder Equity and can be found for your selected company in the Listof Companies tab in this spreadsheet.Question 4. The Rate of Return on Investment Based on the Cash Div/Share ($) and Stock Price You Used in Question 1Use the numbers from Question 1 to calculate those questions.Return onInvestmentYearCash Div/Share($)Stock Price$-$$-$$$CALCULATE ROJ-#DIV/0!#DIV/0!ROI call is formulatedROI call is formulatedReturn on Investment (ROI):Using the formula: ROI = ((Dividend ) + (New Price - Old Price) / OldPrice))PART II: BOND ISSUANCENewly issued 10-year | Calculate the present value in the four scenarios below.PART II: BOND ISSUANCEBonds are a long-term debt for corporations. By buying a bond, thebond-purchaser leads money to the corporation. The borrowerpromises to pay a specified interest rate during the bond's lifetimeand at maturity, payback the entire future value of the bond. In case ofbankruptcy, bondholders have priority over stockholders for anyFor purposes of this exercise, assume that your selected companyissued a new 10-year bond for $300,000 on October 1, 2023. Theboad will mature on October 1, 2033. The future value of this bond is$300,000. The bond was issued at the latest market rate of 5.0% fixedfor 10 years, with interest payments made semi-annually. What is thepresent value of this bond using the four scenarios in Part II: Bond1. The present value of the bond at issuance$Present Value PVPeriodsN0Interest||PaymentsPMT$2Future Value FY$3.-0 Number of semi-annual payments made over 10 (10 X 2)Annual interest rate at issuance paid semi-annually (Annual Interest Rate / 2)This bond makes regular semi-annual payments of interest (in dollars). Annual Interest Payment/2).Future value in 10 years-enter as a positive number (Always the Future or Face Value of the Bond)Bonds Debt..42. The present value of the bond if overall rates in the market increased by 2% annually..Bondholders = LeadersNOTE: A simple rule to follow: When market rates change, nothing inthe original bond's terms change, except you will enter the new marketinterest rate in place of the interest rate stated at the bond's issuancedate. In other words, the future value remains the same, paymentsremain the same, periods remain the same. When you change theinterest rate to reflect the new market rate, the present value of the

All the formulae required for calculations are already provided in the excel images. We will provide…

Calculate stock and bond valuations for PepsiCo. Specifically, the following critical elements must be addressed: Stock Valuation Based on the figures provided from the financial information you prepared for your selected company, calculate each of the following in Part I of the spreadsheet tab: Stock and Bond Valuation: Fill in the yellow highlighted cells with the matching data prepared for your selected company. Identify the new dividend yields if the company increased its dividend per share by $1.75. Identify the dividend yield if the firm doubled its outstanding shares using a stock split. Note that other numbers will be affected by a stock split such as new stock price, actual dividends paid, and number of outstanding shares. Stockholders' equity will not change. Calculate the rate of return on investment based on the data you calculated in Question 1. What effect would each of the calculations you performed have on shareholder value? In other words, suppose the company's goal is to maximize shareholder value. How will each of the situations support or inhibit that goal? To what extent do you feel the company's dividend policies support or hinder its strategies? Bond Issuance PepsiCo. issues a new 10-year bond for $300,000 on October 1, 2023. The bond will mature on October 1, 2033. The future value of this bond is $300,000. The bond was issued at the latest market rate of 5.0% fixed for 10 years, with interest payments paid semiannually. What is the present value of this bond using the four scenarios in Part II of the spreadsheet tab: Stock and Bond Valuation? Calculate the present value of the bond at issuance. Calculate the new present value of the bond if overall rates in the market increase by 2%. Calculate the new present value of the bond if overall rates in the market decrease by 2%. Calculate the present value of the bond if overall rates in the market remain the same as at issuance. How would each of the calculations you performed affect the company's decision to raise capital in this way? Explain for each situation, would bond valuation be a viable option for increasing capital? Assess how bond issuance policies support or hinder company strategies.