Description
Please correct the bond issuance tab as the caluclations are wrong. Also, please update the analysis accordagly to your new analysis. To help better understand the material better please watch the videos I am going to list below. Watch the Video: WACC, Cost of Equity, and Cost of Debt in a DCFNOTE: The Video, “What Working Capital Means in Valuation and Financial Modeling” is no longer available on YouTube so disregard this video.Watch the Video: Financial modeling in Excel – Working capital – YouTubeRead the Article: Understanding Weighted Average Cost of Capital
Unformatted Attachment Preview
**CHOOSE ONE (1) OF THE COMPANIES LISTED BELOW FOR YOUR COURSE FINA
1. Insert your selected company name in the yellow highlighted cell:
2. Start your copy with the row where your company name appears.
3. Then scroll down to the row of the weighted average cost of capital. Then paste this information into rows 6 through 20 in
Lowe’s Companies Inc. (Stock Ticker: LOW) – (NAICS Code: 444110)
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 20%)
Weighted Average Cost of Capital (Use 5%)
Year
$
$
$
$
$
$
#DIV/0!
–
$
%
%
Johnson & Johnson (Stock Ticker: JNJ) – (NAICS Code: 325412)
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Year
$
–
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 20%)
Weighted Average Cost of Capital (Use 5%)
$
$
$
–
$
$
#DIV/0!
–
$
%
%
Target: (Stock Ticker: TGT) – (NAICS: Code 452210)
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 20%)
Weighted Average Cost of Capital (Use 5%)
Year
$
$
$
$
$
$
#VALUE!
–
$
%
%
Kroger Companies: (Stock Ticker: KR) – (NAICS Code: 445110)
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 20%)
Weighted Average Cost of Capital (Use 5%)
Year
$
$
$
$
#VALUE!
–
$
$
$
%
%
Chevron: (Stock Ticker: CVX) – (NAICS Code: 324110)
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Year
$
$
$
$
–
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 20%)
Weighted Average Cost of Capital (Use 5%)
$
$
#DIV/0!
–
$
%
%
Pepsico, Inc.: (Stock Ticker: PEP) – (NAICS Code: 312111)
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 28%)
Weighted Average Cost of Capital (Use 5%)
2022
$
$
$
$
$
$
$
Southwest Airlines Co.: (Stock Ticker: LUV) (NAICS Code: 481111)
10,811
(5,207.00)
16,018.00
4.60
2.38%
6.42
17,149.00
1387.00
193.39
28%
5%
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 20%)
Weighted Average Cost of Capital (Use 5%)
Year
$
$
$
$
#VALUE!
–
$
$
$
%
%
General Electric Company: (Stock Ticker: GE) – (NAICS Code: 333611)
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Year
$
$
$
$
$
$
#VALUE!
–
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 20%)
Weighted Average Cost of Capital (Use 5%)
$
Notes: North American Industry Classification System Codes:
The North American Industry Calculation System or NAICS is a
classification of business establishments by type of economic activity (process of production).
%
%
R YOUR COURSE FINAL PROJECT**
Then, copy and paste the information for your selected company below as follows:
ion into rows 6 through 20 into Tab 2—Selected Co. for Project
Year
$
$
$
$
Year
$
$
$
$
$
$
#VALUE!
$
$
$
$
#VALUE!
%
%
%
%
Year
$
Year
–
$
–
$
$
$
–
$
$
$
–
$
$
#DIV/0!
$
$
#DIV/0!
–
$
–
–
$
%
%
%
%
Year
$
$
$
$
Year
$
$
$
$
$
$
#VALUE!
$
$
$
$
%
%
#VALUE!
%
%
Year
$
$
$
$
Year
$
$
$
$
$
$
#VALUE!
$
$
$
$
#VALUE!
%
%
%
%
Year
$
$
$
$
Year
–
$
$
$
$
–
#VALUE!
#VALUE!
$
$
$
$
$
$
%
%
2021
$
$
$
$
$
$
$
%
%
2020
11,616 $
(4,625.00) $
16,241.00 $
4.30 $
2.59%
5.49 $
16,043.00 $
1389.00
165.76 $
28%
5%
10,613
(4,240.00)
14,853.00
4.09
2.97%
5.12
13,454.00
1392.00
137.51
28%
5%
Year
$
$
$
$
Year
$
$
$
$
$
$
#VALUE!
$
$
$
$
#VALUE!
%
%
Year
$
$
$
$
$
$
%
%
Year
$
$
$
$
#VALUE!
$
$
#VALUE!
$
$
%
%
%
selected company below as follows:
Information Location
Mergent INTELLECT
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Mergent INTELLECT
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Information Location
Mergent INTELLECT
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Mergent INTELLECT
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Information Location
Mergent INTELLECT
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Formulated Cell (Do Not Change)
Mergent INTELLECT
Mergent INTELLECT
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Information Location
Mergent INTELLECT
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Mergent INTELLECT
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Information Location
Mergent INTELLECT
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Mergent INTELLECT
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Information Location
Mergent INTELLECT
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Mergent INTELLECT
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Information Location
Mergent INTELLECT
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Mergent INTELLECT
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Information Location
Mergent INTELLECT
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Formulated Cell Will Self-Populate (Do Not Change)
Mergent INTELLECT
Mergent INTELLECT
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Yahoo Finance – Stock Market Live, Quotes, Business & Finance News
Enter stock ticker symbol – Click Financials – Click Balance Sheet – Bottom line is Ordinary Shares Outstanding
Enter stock ticker symbol – Search – Historical Data – Chart – 5 Years – Use Calendar Year or Fiscal Year End Date
Enter stock ticker symbol – Click Financials – Click Balance Sheet – Bottom line is Ordinary Shares Outstanding
Enter stock ticker symbol – Search – Historical Data – Chart – 5 Years – Use Calendar Year or Fiscal Year End Date
Enter stock ticker symbol – Click Financials – Click Balance Sheet – Bottom line is Ordinary Shares Outstanding
Enter stock ticker symbol – Search – Historical Data – Chart – 5 Years – Use Calendar Year or Fiscal Year End Date
Enter stock ticker symbol – Click Financials – Click Balance Sheet – Bottom line is Ordinary Shares Outstanding
Enter stock ticker symbol – Search – Historical Data – Chart – 5 Years – Use Calendar Year or Fiscal Year End Date
Enter stock ticker symbol – Click Financials – Click Balance Sheet – Bottom line is Ordinary Shares Outstanding
Enter stock ticker symbol – Search – Historical Data – Chart – 5 Years – Use Calendar Year or Fiscal Year End Date
Enter stock ticker symbol – Click Financials – Click Balance Sheet – Bottom line is Ordinary Shares Outstanding
Enter stock ticker symbol – Search – Historical Data – Chart – 5 Years – Use Calendar Year or Fiscal Year End Date
Enter stock ticker symbol – Click Financials – Click Balance Sheet – Bottom line is Ordinary Shares Outstanding
Enter stock ticker symbol – Search – Historical Data – Chart – 5 Years – Use Calendar Year or Fiscal Year End Date
Enter stock ticker symbol – Click Financials – Click Balance Sheet – Bottom line is Ordinary Shares Outstanding
Enter stock ticker symbol – Search – Historical Data – Chart – 5 Years – Use Calendar Year or Fiscal Year End Date
Pepsico
Using the links to each company’s financial data as your source, fill in all the yellow highlighted cells.
Use most recent three years’ annual calendar or fiscal year-end financial data of your selected company.
Now you are ready to go to work!
Pepsico, Inc.: (Stock Ticker: PEP) – (NAICS Code: 312111)
Dollars and Shares in Millions, Except Per-Share Data
Most Recent Calendar or Fiscal Year End—Then Two Prior Years
Enter Years
Net Cash Provided by Operating Activities
Capital Expenditures
Free Cash Flows
Cash Dividends per Share
Dividend Yield
Diluted Earnings per Common Share
Shareholder’s Equity
Total Common Shares Outstanding
Common Stock Closing Price per Share
Effective Tax Rate (Use 28%)
Weighted Average Cost of Capital (Use 5%)
2022
$
$
$
$
$
$
$
10,811
(5,207.00)
16,018.00
4.60
2.38%
6.42
17,149.00
1387.00
193.39
28%
5%
ghlighted cells.
selected company.
2021
$
$
$
$
$
$
$
11,616 $
(4,625.00) $
16,241.00 $
4.30 $
2.59%
5.49 $
16,043.00 $
1389.00
165.76 $
28%
5%
2020
10,613
(4,240.00)
14,853.00
4.09
2.97%
5.12
13,454.00
1392.00
137.51
28%
5%
Milestone One: Time Value of Money
(please fill in YELLOW cells)
* Dollar amounts are in millions $
Question 1.
Interest Rate
FCF – Years
FCF in dollars*
5%
2022
2021
2020
$ 16,018.00 $ 16,241.00 $ 14,853.00
Pv*
($15,255.24)
Total Pv*
*In millions
($42,816.88)
Question 2. Interest Rate
($14,731.07)
($12,830.58)
5%
FCF – Years
FCF in dollars*
$
Pv*
2022
14,416.20 $
2021
14,616.90 $
2020
13,367.70
($13,729.71) $
(13,257.96) $
(11,547.52)
$ (38,535.20)
Total Pv*
*In millions
Question 3.
Interest Rate
7%
Required Rate of Return for Risk
Associated With Projected Future Three
Year’s Free Cash Flows.
5%
Required Rate of Return for Risk
Associated With Projected Future Three
Year’s Free Cash Flows.
Most Recent Year’s Cash
Flow fom Queston 1.
FCF – Years
FCF in dollars*
2025
2024
2023
$ 17,503.30 $ 16,993.50 $ 16,498.54 $ 16,018.00
Pv*
($15,419.20) $ (14,842.78) $ (13,467.72) $ (15,255.24)
*In millions
Total Pv*
$ (43,729.70)
Instructions and Explanations
Free cash flows (FCF), for this exercise is difference between cash generated from
operating income minus capital expenses at the end of your company’s fiscal or calendar
year. The present value of free cash flows is one method of determining a company’s value
to a potential buyer.
Note: For Milestone One, please use the adjusted free cash flows calculated for your
selected company on Tab 2 (Selected Co. for Project) of this Excel workbook. Use 5% as
the interest rate for these questions.
For the purpose of this exercise, what will happen to the total PV if your selected
company’s free cash flows for each year reported in Question 1 were reduced by 10%?
Recent Year’s Cash
fom Queston 1.
Your selected company is projecting that free cash flows for the next three (3) years
will increase by 3% annually. Using the Most Recent Year’s free cash flows from
Question 1 above, increase each of the future free cash flows by 3% (will self-populate
in the yellow highlighted cells). As there is a chance that these increases may not occur
as projected, a buyer who may be willing to purchase the company before the
increases occur, will account for this risk by using a required rate of return of 7%.
What price would the potential buyer be willing to pay based upon the known free
cash flows and the future projected free cash flows?
Once you have completed these calculations, proceed to write your written analysis.
xplanations
ence between cash generated from
end of your company’s fiscal or calendar
method of determining a company’s value
free cash flows calculated for your
ect) of this Excel workbook. Use 5% as
n to the total PV if your selected
in Question 1 were reduced by 10%?
ash flows for the next three (3) years
Recent Year’s free cash flows from
ree cash flows by 3% (will self-populate
hance that these increases may not occur
rchase the company before the
ing a required rate of return of 7%.
g to pay based upon the known free
ows?
oceed to write your written analysis.
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
Year
Cash Div/Share ($) Dividend Yield
Stockholder’s
Stock Price
Equity (in millions)
2022
2021
2020
$
$
$
$
$
$
4.60 $
4.30 $
4.09 $
4.60
4.30
4.09
17,149.00
16,043.00
13,454.00
$ 193.39
$ 165.76
$ 137.51
Total Shares
Outstanding
(millions of
shares)
1387.00
1389.00
1392.00
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 basis to calculate these questions.
Year
Cash Div/Share ($)
Stockholder’s
Dividend Yield
Stock Price
+1.75
Equity (in millions)
2022
2021
2020
$
$
$
6.35
6.05
5.84
3.28%
3.65%
4.25%
17,149 $
16,043 $
13,454 $
193.39
165.76
137.51
Total Shares
Outstanding
(millions of
shares)
1387
1389
1392
Question 3. The Dividend Yield, Cash Dividend Per Share, Stock Price, and Total Shares Outstanding After the Stock Split
Use the numbers from Question 1 to calculate these questions.
Year
Cash Div/Share ($) Dividend Yield
Stockholder’s
Stock Price
Equity (in millions)
Total Shares
Outstanding
(millions of
shares)
2022
2021
2020
$
$
$
2.300
2.150
2.05
2.38 $
2.59 $
2.97 $
17,149.00
16,043.00
13,454.00
$
$
$
96.70
82.88
68.76
2,774
2,778
2,784
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 these questions.
Year
Cash Div/Share ($)
2022
2021
2020
$
$
$
4.60
4.30
4.09
Stock Price
$
$
$
193.39
165.76
137.51
Return on
Investment
CALCULATE ROI
-12.06% ROI cell is formulated
-14.58% ROI cell is formulated
PART II: BOND ISSUANCE
Newly issued 10-year bond. Calculate the present value in the four scenarios below.
1. The present value of the bond at issuance
Present Value PV
Periods
N
$ 183,081.28
20 Number of semi-annual payments made over 10 years
Interest
Payments
Future Value
I
PMT
FV
$
$
0.025 Annual interest rate at issuance paid semi-annually (Annual Interes
19,244 This bond makes regular semi-annual payments of interest (in doll
300,000 Future value in 10 years—enter as a positive number (Always the
2. The present value of the bond if overall rates in the market increased by 2% annually
Present Value
Periods
Interest
Payments
Future Value
PV
N
I
PMT
FV
$ 150,769.77
20 Number of semi-annual payments made over 10 years
0.035 New annual market interest rate paid semi-annually (New Annual R
$
21,108 This bond makes regular semi-annual payments of interest (in dolla
$ 300,000 Future value in 10 years—enter as a positive number (Always the F
3. The present value of the bond if overall rates in the market decreased by 2% annually
Present Value
Periods
Interest
Payments
Future Value
PV
N
I
PMT
FV
$ 222,741.13
20 Number of semi-annual payments made over 10 years
0.015 New annual market interest rate paid semi-annually (New Annual R
$
17,474 This bond makes regular semi-annual payments of interest (in dolla
$ 300,000 Future value in 10 years—enter as a positive number ( Always the
4. The present value of the bond if overall rates in the market remained the same as at issuance
Present Value
Periods
Interest
Payments
Future Value
PV
N
I
PMT
FV
$ 183,081.28
20 Number of semi-annual payments made over 10 years
0.025 Annual market interest rate remains the same as Question 1, paid se
$
19,244 This bond makes regular semi-annual payments of interest (in dolla
$ 300,000 Future value in 10 years—enter as a positive number (Always the F
Instructions and Explanation
PART I: STOCK VALUATION
d in Question 1
(10 X 2)
ally (Annual Interest Rate / 2)
of interest (in dollars). Annual Interest Payment / 2).
mber (Always the Future or Face Value of the Bond)
(10 X 2)
ally (New Annual Rate divided by 2)
of interest (in dollars) (Dollars Paid Annually divided by 2)
mber (Always the Future or Face Value of the Bond and Never Changes)
(10 X 2)
ally (New Annual Rate divided by 2)
of interest (in dollars) (Dollars Paid Annually divided by 2 and Never Changes)
mber ( Always the Future or Face Value of the Bond and Never Changes)
(10 X 2)
Question 1, paid semi-annually (Annual Rate divided by 2)
of interest (in dollars) (Dollars Paid Annually divided by 2 and Never Changes)
mber (Always the Future or Face Value of the Bond and Never Changes)
Instructions and Explanations
PART I: STOCK VALUATION
Note: Use and enter the fiscal or year-end financial data that you calculated or found for your
selected company and enter the appropriate data into each yellow highlighted cell.
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. This number will be found on
your selected company’s final project Excel workbook 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. This number will be found on
your selected company’s final project Excel workbook spreadsheet.
Return on Investment (ROI) :
Using the formula: ROI = ((Dividend ) + (New Price – Old Price) / Old Price))
PART II: BOND ISSUANCE
Bonds are a long-term debt for corporations. By buying a bond, the bond-purchaser lends
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 payment distributions.
For purposes of this exercise, certain assumptions are being made. Assume that your
selected company issued a new 10-year bond for $300,000 on October 1, 2021, that will
mature on October 1, 2031. The future value of this bond is therefore $300,000. The bond
was issued at the current 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 three scenarios in
Part II: Bond Issuance?
Bonds = Debt………………..Bondholders = Lenders
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 bond will either increase or
decrease.
For the purposes of this exercise, assume that the new market rates occur one (1) day
after the initial bond is issued.
To calculate PV, you can use the Excel formula or the financial calculator provided.
Link is provided below.
https://www.arachnoid.com/finance
Once you have completed these calculations, proceed to write your written analysis.
ver Changes)
Milestone Three: Capital Budgeting Data (fill in YELLOW cells)
WACC
Initial Outlay CF1
$0
Cash Flows (Sales)
$
– Operating Costs (excluding Depreciation)
$
– Depreciation Rate of 20% (5-Years)
$
Operating Income (EBIT)
$
– Income Tax (use 20%)
$
After-Tax EBIT
$
+ Depreciation
$
Cash Flows
$0 $
CF2
–
$
$
$
$
$
$
$
$
CF3
–
Select from dropdown:
NPV
IRR
$0
#NUM!
$
$
$
$
$
$
$
$
CF4
–
$
$
$
$
$
$
$
$
–
Instructions and Explanations
For this milestone, you will assume that your selected company is
considering a potential capital investment for new equipment.
Choose one of the three investment options listed below and solve. You will either ACCEPT or REJECT
CF5
$
$
$
$
$
$
$
$
–
1. Capital Budgeting Example Setup
Initial investment $17,000,000
Straight-line depreciation of 20%
Income tax rate = 20%
WACC: use 5%
Cash Flows (sales revenue based upon this
purchase of new equipment, are projected
to be as follows):
CF1: $4,390,000
CF2: $4,200,000
2. Capital Budgeting Example Setup
Initial investment $65,000,000
Straight-line depreciation of 20%
Income tax rate = 20%
WACC: use 9%
Cash Flows (sales revenue based upon this
purchase of new equipment, are projected
to be as follows):
CF1: $15,000,000
CF2: $17,000,000
CF3: $4,500,000
CF4: $5,000,000
CF5: $4,700,000
Operating Costs
CF1: $700,000
CF2: $700,000
CF3: $225,000
CF4: $350,000
CF5: $400,000
CF3: $18,000,000
CF4: $19,000,000
CF5: $18,000,000
Operating Costs
CF1: $500,000
CF2: $500,000
CF3: $600,000
CF4: $500,000
CF5: $500,000
Initial Investment—always negative. Companies have to invest money (“payout”
funds) in order to gain the future benefit.
WACC—why do we use WACC rate for new projects? If the project’s IRR doesn’t earn a
return equal to or higher than WACC, the corporation should abandon the project and
invest money elsewhere.
Once you have completed these calculations, proceed to write your written analysis.
ther ACCEPT or REJECT the proposal.
3. Capital Budgeting Example Setup
Initial investment $85,000,000
Straight-line depreciation of 20%
Income tax rate = 20%
WACC: use 9%
Cash Flows (sales revenue based upon this
purchase of new equipment, are projected
to be as follows):
CF1: $50,000,000
CF2: $45,000,000
CF3: $35,000,000
CF4: $40,000,000
CF5: $35,000,000
Operating Costs
CF1: $15,000,000
CF2: $12,000,000
CF3: $11,000,000
CF4: $13,000,000
CF5: $13,000,000
alysis.
Milestone Four: Interest Rate Implication (fill in YELLOW cells)
1. Original Scenario From Milestone 1—Time Value of Money Using 8%
Current Market Interest Rate at 8%
Interest Rate
8.00%
Years
Amounts*
$
–
$
–
$
–
Pv*
$
–
$
–
$
–
Total Pv*
*In millions
$
–
2. Change in Interest Rate and Its Implications
Lower Current Market Interest Rate to 4%
Interest Rate
4.00%
Years
Amounts*
$
–
$
–
$
–
Pv*
$
–
$
–
$
–
Total Pv*
$
–
*In millions
3. Change in Interest Rate and Its Implications
3. Increase Current market Interest Rate to 12%
Interest Rate
12.00%
Years
Amounts*
$
–
$
–
$
–
Pv*
$
–
$
–
$
–
Total Pv*
*In millions
$
–
Instructions and Explanations
We will use your selected company’s free cash flows for Milestone Four analysis.
(Note that the initial interest rate and rate changes for this Milestone are different than those used in Milesto
Three scenarios will be analyzed:
1. Current Market Interest Rate at 8%
2. Lower Current Market Interest Rate to 4%
3. Increase Current market Interest Rate to 12%
Once you have completed these calculations, proceed to write your written analysis.
those used in Milestone One.)
ACCEPT
REJECT
PepsiCo: Stock Valuation and Bond Issuance
Christine Sarkissian
Southern New Hampshire University
FIN-550: Corporate Financial Management
Professor Russ Bellemare
October 15, 2023
Stock Valuation
The New Dividend Yield due to a $ 1.75 Increase in Dividend per Share
Significant variations in dividend yields between fiscal years were noted in response to
changes in dividends per share. The initial dividend yield for 2022 was 4.60, representing the
firm’s dividend payout relative to its share price. But the yield fell sharply to 3.28 when the
dividend per share was raised by $1.75. This decline in yield suggests that either investors are
now receiving a lower percentage of their investment in dividends or the dividend increase has
surpassed the increase in share price. After the dividend per share was increased in 2021, the
yield initially increased to 3.65 from 4.30. The yield showed a balance between capital
appreciation and dividend increase even as it declined in value.
Interestingly, in 2020, the yield was 4.09 initially, but it was exceeded by 4.25 due to a
rise in the dividend per share. This would be a favorable reaction to the business’s pledge to give
shareholders their money back. These changes in dividend yields highlight the precarious
balance that must be maintained between paying out earnings to shareholders and keeping share
prices competitive, which affects investor attitude and choice.
The New Dividend Yield Due to a 2-to-1 Stock Split
Upon a 2-to-1 stock split that doubled the outstanding shares, significant shifts in
dividend yields for the fiscal years 2022, 2021, and 2020 were observed, primarily influenced by
changes in the stock price. With the initial dividend yields at 4.60, 4.30, and 4.09 percent for
2022, 2021, and 2020, respectively, the stock split, in essence, halved the stock prices. As a
result, the revised dividend yields for the corresponding fiscal years were 2.38, 2.59, and 2.97
percent. The decline in dividend yields every year following the stock split demonstrates the
inverse link between stock price and dividend yield. Despite the doubling of outstanding shares,
the stock price decline led to a lower dividend yield, indicating that shareholders now receive a
smaller return in the form of dividends than the lower stock price. Given that changes in the
number of outstanding shares can substantially impact how the market perceives a company’s
performance and potential returns, this scenario emphasizes the significance of taking stock
splits and their subsequent impact on dividend yield into account when determining shareholder
value.
Return on Investment Calculations
The financial performance of PepsiCo and investor profitability can be understood by
examining its return on investment (ROI) for the fiscal years 2020 and 2021. The ROI was
determined to be -12.06% in 2021, with a cash dividend per share of $4.30 and a matching stock
price of $165.76. This negative return on investment (ROI) indicates that shareholders lost
12.06% of their investment for every dollar invested. This suggests that the company’s capacity
to provide returns for investors has decreased compared to the initial investment. In the same
way, the ROI was -14.58% in 2020, with a cash dividend per share of $4.09 and a stock price of
$137.51. This further emphasizes a decline in investor profitability, suggesting that PepsiCo
encountered challenges in maintaining shareholder value amidst market fluctuations and
economic uncertainties. The consecutive negative ROIs over these two years signify potential
concerns regarding the company’s financial performance and strategic direction, warranting a
comprehensive analysis of its operational efficiency, market competitiveness, and investment
strategies. Understanding the factors influencing these ROI trends is imperative for PepsiCo to
reassess its dividend policies, growth prospects, and shareholder value propositions to regain
investor confidence and ensure sustainable long-term profitability and growth.
Effects of New Dividends on Shareholder Value
Dividend increased by $ 1.75
Increasing the dividend yield by $1.75 can positively affect shareholder value, aligning
with the company’s goal of maximizing shareholder returns. By raising the dividend yield, the
company signals its commitment to distributing profits and sharing its financial success with
investors. This move can foster investor confidence, attract potential investors seeking stable
returns, and enhance the company’s overall market reputation. According to Torres (2023),
dividends can be crucial in influencing investor perception and contributing to long-term
shareholder loyalty. Furthermore, an increase in dividend yield can signify the company’s robust
financial health and sustainable earnings, which are key indicators of value for shareholders
(Bustani et al., 2021). As a result, this action is likely to reinforce shareholder value rather than
inhibit it, as it demonstrates the company’s dedication to generating consistent returns for its
shareholders, ultimately fostering a positive impact on its stock price and market performance.
Stock Split Effect
A stock split, like the two-to-one split that PepsiCo did, lowers stock prices, which in turn
affects dividend yields. The lower dividend yields of 2.38, 2.59, and 2.97 in 2022, 2021, and
2020, respectively, show that this decision has a direct negative impact on the cash dividends
that shares receive. A lower dividend yield denotes fewer investor returns concerning the
decreased stock price, which lowers shareholder value overall (Arsyad et al., 2021). Since
dividends make up a sizable portion of shareholder returns, the stock split’s fall in dividend yield
may cause investors to lose faith in the company’s capacity to produce sizable profits and returns.
Ergo, this reduction in shareholder value is a direct consequence of the altered financial
dynamics following the stock split, reflecting the impact of such corporate actions on investor
perceptions and returns.
Impact of PepsiCo’s Dividend Strategies
PepsiCo’s dividend policies, as evidenced by the consistent dividend payouts over the
years, seem to prioritize rewarding shareholders rather than solely focusing on growth initiatives.
While beneficial for maintaining investor confidence, this approach could potentially hinder the
company’s strategic growth ambitions. PepsiCo may be limiting its capacity to retain capital for
future investments and expansion opportunities by consistently distributing earnings through
dividends. In an industry marked by dynamic shifts and intense competition, prioritizing
dividend distributions over reinvestment could impede the company’s ability to innovate,
diversify, and capture emerging market segments (Theiri et al., 2023). This conservative
approach to dividend policies, while ensuring stability and shareholder satisfaction in the short
term, might be restraining the company’s potential for long-term growth and market dominance.
The balance between rewarding shareholders and fostering sustainable growth demands a
strategic reconsideration of dividend allocation, emphasizing the importance of retaining
earnings for research, development, and strategic acquisitions to remain competitive in the
rapidly evolving consumer goods industry.
Bond Issuance
Present Value of the Bond at Issuance
When PepsiCo issued the 10-year bond at the prevailing market rate of 5.0% semiannually, the present value of the bond was approximately $183,083. This value represents the
discounted sum of all future cash flows from the bond, considering the fixed interest rate and the
perio
