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.

Transcribed Image Text:2 3 4 Pepsico, Inc.: (Stock Ticker: PEP) - (NAICS Code: 312111) 5 Dollars and Shares in Millions, Except Per-Share Data 6 Most Recent Calendar or Fiscal Year End―Then Two Prior Years 7 Enter Years 8 Net Cash Provided by Operating Activities 9 Capital Expenditures 0 Free Cash Flows 1 Cash Dividends per Share 2 Dividend Yield 3 Diluted Earnings per Common Share 4 Shareholder's Equity 5 Total Common Shares Outstanding 6 Common Stock Closing Price per Share 7 Effective Tax Rate (Use 20%) 8 Weighted Average Cost of Capital (Use 5%) 9 2022 2021 2020 $ 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.00 171.73 $ 2.83% 5.86 $ 16,043,000.00 $ 1383000.00 150.43 $ 20% 20% 5% 5% Information Location 10,613,000 Mergent Intellect 4,240,000.00 Mergent Intellect 6,373,000.00 Formulated Cell Will Self-Populate (Do Not Change) 4.02 Mergent Intellect 2.95% Formulated Cell Will Self-Populate (Do Not Change) 6.29 Mergent Intellect 13,454,000.00 Mergent Intellect 1380000.00 Yahoo Finance - Stock Market Live, Quotes, Business & Finance News Enter stock ticker sy 136.31 Yahoo Finance - Stock Market Live, Quotes, Business & Finance News Enter stock ticker sy 20% 5%
Transcribed Image Text:2 3 5. . 2 3 + Milestone Two: Stock Valuation and Bond Issuance (Fill in the yellow cells) PART I: STOCK VALUATION Read the explanations to the right of the calculation cells for specific information on the data. Question 1: Initial Stock Valuation Stockholder's Year Cash Div/Share ($) Dividend Yield Equity (in Stock Price Total Shares Outstanding (millions of millions) shares) 0% $ $ Instructions and Explanations PART I: STOCK VALUATION Note: Use and eater the fiscal or year-end financial data for your selected company and enter the appropriate data into each yellow highlighted cell. $ $ $ 0% $ 0% $ $ $ - 0 0 0 Question 2. Stock Valuation-The New Dividend Yield if the Company Increased Its Dividend Per Share by 1.75 Use the numbers from Question 1 as your baris to calculate those questions. Year Cash Div/Share ($) +1.75 Dividend Yield Stockholder's Equity (in millions) Stock Price Total Shares Outstanding (millions of shares) #DIV/0! #DIV/0! #DIV/O! 0$ - 0 $ 0 $ - 0 0 0 $ $ $ Question 3. The Dividend Yield, Cash Dividend Per Share, Stock Price, and Total Shares Outstanding After the Stock Split Occurs Use the numbers from Question 1 to calculate those questions. Cash Div/Share Year ($) $ $ $ Stockholder's Dividend Yield Equity (in millions) Stock Total Shares Outstanding Price (millions of shares) 0.00% 0.00% 0.00% 0$ 0$ 0 0t - - 0 0 Note: Stock price for your selected company is found in your selected company's spreadsheet. Stockholder's Equity = Assets - Liabilities. This represents the ownership of a corporations. Owners are called "stockholders" because they hold stocks or shares of the company. The main goal of every corporate manager is to generate shareholder value. Shareholder Equity is also known as Stockholder Equity and can be found for your selected company in the List of 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 1 Use the numbers from Question 1 to calculate those questions. Return on Investment Year Cash Div/Share ($) Stock Price $ - $ $ - $ $ $ CALCULATE ROJ - #DIV/0! #DIV/0! ROI call is formulated ROI call is formulated Return on Investment (ROI): Using the formula: ROI = ((Dividend ) + (New Price - Old Price) / Old Price)) PART II: BOND ISSUANCE Newly issued 10-year | Calculate the present value in the four scenarios below. PART II: BOND ISSUANCE Bonds are a long-term debt for corporations. By buying a bond, the bond-purchaser leads money to the corporation. The borrower promises to pay a specified interest rate during the bond's lifetime and at maturity, payback the entire future value of the bond. In case of bankruptcy, bondholders have priority over stockholders for any For purposes of this exercise, assume that your selected company issued a new 10-year bond for $300,000 on October 1, 2023. The boad 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 made semi-annually. What is the present value of this bond using the four scenarios in Part II: Bond 1. The present value of the bond at issuance $ Present Value PV Periods N 0 Interest || Payments PMT $ 2 Future 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 = Leaders NOTE: A simple rule to follow: When market rates change, nothing in the original bond's terms change, except you will enter the new market interest rate in place of the interest rate stated at the bond's issuance date. In other words, the future value remains the same, payments remain the same, periods remain the same. When you change the interest rate to reflect the new market rate, the present value of the

Question

Calculate stock and bond valuations for PepsiCo.

Specifically, the following critical elements must be addressed:

Stock Valuation
1. 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:
  1. Fill in the yellow highlighted cells with the matching data prepared for your selected company.
  2. Identify the new dividend yields if the company increased its dividend per share by $1.75.
  3. 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.
  4. Calculate the rate of return on investment based on the data you calculated in Question 1.
2. 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?
3. To what extent do you feel the company's dividend policies support or hinder its strategies?
Bond Issuance
1. 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?
  1. Calculate the present value of the bond at issuance.
  2. Calculate the new present value of the bond if overall rates in the market increase by 2%.
  3. Calculate the new present value of the bond if overall rates in the market decrease by 2%.
  4. Calculate the present value of the bond if overall rates in the market remain the same as at issuance.
2. 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?
3. Assess how bond issuance policies support or hinder company strategies.

2
3
4 Pepsico, Inc.: (Stock Ticker: PEP) - (NAICS Code: 312111)
5 Dollars and Shares in Millions, Except Per-Share Data
6 Most Recent Calendar or Fiscal Year End―Then Two Prior Years
7 Enter Years
8 Net Cash Provided by Operating Activities
9 Capital Expenditures
0 Free Cash Flows
1 Cash Dividends per Share
2 Dividend Yield
3 Diluted Earnings per Common Share
4 Shareholder's Equity
5 Total Common Shares Outstanding
6 Common Stock Closing Price per Share
7 Effective Tax Rate (Use 20%)
8 Weighted Average Cost of Capital (Use 5%)
9
2022
2021
2020
$
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.00
171.73 $
2.83%
5.86 $
16,043,000.00 $
1383000.00
150.43 $
20%
20%
5%
5%
Information Location
10,613,000 Mergent Intellect
4,240,000.00 Mergent Intellect
6,373,000.00 Formulated Cell Will Self-Populate (Do Not Change)
4.02 Mergent Intellect
2.95% Formulated Cell Will Self-Populate (Do Not Change)
6.29 Mergent Intellect
13,454,000.00 Mergent Intellect
1380000.00 Yahoo Finance - Stock Market Live, Quotes, Business & Finance News Enter stock ticker sy
136.31 Yahoo Finance - Stock Market Live, Quotes, Business & Finance News Enter stock ticker sy
20%
5%

2
3
5.
.
2
3
+
Milestone Two: Stock Valuation and Bond Issuance (Fill in the yellow cells)
PART I: STOCK VALUATION
Read the explanations to the right of the calculation cells for specific information on the data.
Question 1: Initial Stock Valuation
Stockholder's
Year
Cash Div/Share
($)
Dividend
Yield
Equity (in
Stock
Price
Total Shares
Outstanding
(millions of
millions)
shares)
0% $
$
Instructions and Explanations
PART I: STOCK VALUATION
Note: Use and eater the fiscal or year-end financial data for your selected
company and enter the appropriate data into each yellow highlighted cell.
$
$
$
0% $
0% $
$
$
-
0
0
0
Question 2. Stock Valuation-The New Dividend Yield if the Company Increased Its Dividend Per Share by 1.75
Use the numbers from Question 1 as your baris to calculate those questions.
Year
Cash Div/Share
($) +1.75
Dividend
Yield
Stockholder's
Equity (in
millions)
Stock
Price
Total Shares
Outstanding
(millions of
shares)
#DIV/0!
#DIV/0!
#DIV/O!
0$
-
0 $
0 $
-
0
0
0
$
$
$
Question 3. The Dividend Yield, Cash Dividend Per Share, Stock Price, and Total Shares Outstanding After the Stock Split Occurs
Use the numbers from Question 1 to calculate those questions.
Cash Div/Share
Year
($)
$
$
$
Stockholder's
Dividend
Yield
Equity (in
millions)
Stock
Total Shares
Outstanding
Price (millions of
shares)
0.00%
0.00%
0.00%
0$
0$
0
0t
-
-
0
0
Note: Stock price for your selected company is found in your selected
company's spreadsheet.
Stockholder's Equity = Assets - Liabilities. This represents the ownership of
a corporations. Owners are called "stockholders" because they hold stocks
or shares of the company. The main goal of every corporate manager is to
generate shareholder value. Shareholder Equity is also known as
Stockholder Equity and can be found for your selected company in the List
of 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 1
Use the numbers from Question 1 to calculate those questions.
Return on
Investment
Year
Cash Div/Share
($)
Stock Price
$
-
$
$
-
$
$
$
CALCULATE ROJ
-
#DIV/0!
#DIV/0!
ROI call is formulated
ROI call is formulated
Return on Investment (ROI):
Using the formula: ROI = ((Dividend ) + (New Price - Old Price) / Old
Price))
PART II: BOND ISSUANCE
Newly issued 10-year | Calculate the present value in the four scenarios below.
PART II: BOND ISSUANCE
Bonds are a long-term debt for corporations. By buying a bond, the
bond-purchaser leads money to the corporation. The borrower
promises to pay a specified interest rate during the bond's lifetime
and at maturity, payback the entire future value of the bond. In case of
bankruptcy, bondholders have priority over stockholders for any
For purposes of this exercise, assume that your selected company
issued a new 10-year bond for $300,000 on October 1, 2023. The
boad 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 made semi-annually. What is the
present value of this bond using the four scenarios in Part II: Bond
1. The present value of the bond at issuance
$
Present Value PV
Periods
N
0
Interest
||
Payments
PMT
$
2
Future 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 = Leaders
NOTE: A simple rule to follow: When market rates change, nothing in
the original bond's terms change, except you will enter the new market
interest rate in place of the interest rate stated at the bond's issuance
date. In other words, the future value remains the same, payments
remain the same, periods remain the same. When you change the
interest rate to reflect the new market rate, the present value of the

Accepted Answer

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