United Parcel Service Capital Budgeting Data Please explain the answer in a word document. For this milestone, submit a draft of the Capital Budgeting Data

United Parcel Service Capital Budgeting Data Please explain the answer in a word document. For this milestone, submit a draft of the Capital Budgeting Data section of the final project, along with your supporting explanations. Base your calculations on the data provided in 2017 UPS Annual Report. Be sure to substantiate your claims.Submit your calculations on the designated tab of the Final Project Student Workbook and your supporting explanations as a Microsoft Word document.This milestone will be used in your final project. For additional details, please refer to the Final Project Guidelines and Rubric document and the Milestone Three Guidelines and Rubric document. Milestone One: Time Value of Money (please fill in YELLOW cells)
Interest Rate
FCF – Years
Amounts*
8%
FCF – 2015
Pv*
0.00
Total Pv*
*In millions
0.00
Pv=FVN/(1+I)^N
FCF – 2016
0.00
PV(I,N,0,FV)
FCF – 2017
0.00
Explanations:
FCF (Free Cash Flows) is the net change in cash generated by the operations of
a business during a reporting period, minus cash outlays for working capital,
capital expenditures, and dividends during the same period. This is a strong
indicator of the ability of an entity to remain in business.
Note: For Milestone One, please use the Free Cash Flows from the United
Parcel Service 2017 Annual Report for the years 2015, 2016, and 2017 located
on Page 2 of the Report.
Interest Rate (given) – For purposes of this exercise, use 8% interest rate.
ted by the operations of
or working capital,
d. This is a strong
ws from the United
2016, and 2017 located
e 8% interest rate.
Milestone Two: Stock Valuation and Bond Issuance (fill in the YELLOW cells)
PART I: STOCK VALUATION
Dividend from Financial Statements:
Read the Explanations to the right of the calculation cells for specific information on the data.
Year
Cash
Div/share ($)
Dividend
Yield
Stockholder’s
Equity (in
millions)
Stock Price
2015
2016
2017
#DIV/0!
#DIV/0!
#DIV/0!
1. Stock Valuation – The new dividend yield if the company increased its dividend per share by 1.75
Year
2015
2016
2017
Cash
Div/Share ($)
+1.75
1.75
1.75
1.75
Dividend
Yield
Stockholder’s
Equity (in
millions)
#DIV/0!
#DIV/0!
#DIV/0!
Stock Price
0
0
0
#DIV/0!
#DIV/0!
#DIV/0!
2. The dividend yield if the firm doubled it’s outstanding shares
Year
Cash
Div/Share ($)
Dividend
Yield
2015
2016
2017
0
0
0
#DIV/0!
#DIV/0!
#DIV/0!
Stockholder’s
Equity (in
Stock Price
millions) -doubled
0 #DIV/0!
0 #DIV/0!
0 #DIV/0!
3. The rate of return on equity (i.e., the cost of stock) based on the new dividend yield you calculated above
Year
2015
2016
2017
Cash
Div/Share ($) Stock Price
+1.75
1.75
#DIV/0!
1.75
#DIV/0!
1.75
#DIV/0!
Return on
Investment
CALCULATE ROI
(Dividends + Capital gain)/ Divide
(D1 + (P1-P0)) / PO
PART II: BOND ISSUANCE
Newly issued 10-year bond
Present Value
Periods
Interest
Payments
Future Value
PV
N
I
PMT
FV
Calculate the Present Value in the three scenarios below
Semi-annual payment: 2017-2027 = 10 years *2 = 20 periods
Interest paid semi-annually: 5.00%/2 = 2.5%
This bond make regular semi-annual payments of interest.
Future Value in 20 years – Enter as a positive number
1. The new value of the bond if overall rates in the market increased by 2%
Present Value
Periods
Interest
Payments
Future Value
PV
N
I
PMT
FV
Please adjust interest
2. The new value of the bond if overall rates in the market decreased by 2%
Present Value
Periods
Interest
Payments
Future Value
PV
N
I
PMT
FV
Please adjust interest
CALCULATING PV (see help on the right hand side of the s
3. The value of the bond if overall rates in the market stayed exactly the same
– identical to CURRENT BOND VALUE from Financial Statements
LLOW cells)
ion on the data.
Explanations:
Note:
1. The dividends declared and paid by UPS for 2015, 2016, a
second page of the 2017 UPS Annual Report.
2. The dividend yield for 2015, 2016, and 2017 are found on t
UPS Annual Report.
3. Stockholder’s/Shareholder’s equity for 2015, 2016, and 201
page of the UPS Annual Report.
per share by 1.75
eld you calculated above
ALCULATE ROI
Dividends + Capital gain)/ Divided by the original Price
1 + (P1-P0)) / PO
Dividend Yield – annual cash dividend per share of common sto
of a share of the common stock. (Dividend yield = Annual Divide
Note: Current Stock Price is not part of the Financial Statement
for Dividend Yield
Stockholder’s Equity = Assets – Liabilities. This represents the
Owners are called stockholder because they hold stocks or share
of every corporate manager is to generate shareholder value.
Note: Shareholder’s Equity for 2015, 2016 and 2017 will be f
UPS Annual Report.
Return on Equity – for this part we will modify and use return o
Using the formula: Dividend (+1.75)/+[(new price-old price)/old
ree scenarios below
017-2027 = 10 years *2 = 20 periods
ly: 5.00%/2 = 2.5%
emi-annual payments of interest.
– Enter as a positive number
Bonds are a long-term debt for corporations. By buying a bond, t
the corporation. The borrower promises to pay specified interest
and at the maturity, payback the entire principle. In case of bank
priority over stockholders for any payment distributions.
Bonds = Debt……………Bondholders = Lenders
Stock=Equity…………….Stockholders = OwnersCalculation:
For purposes of this exercise, assume that UPS issues a new t
will mature in 2027. The Future Value of this bond is therefo
issued in December 2017 at a market rate of 5.0% fixed for 1
payments made semi-annually. What is the Present Value of
scenarios in Part II: Bond Issuance. The coupon rate, which
annual PMTs for this bond is 10%.
%+2% = .00%/2 = %
.00%-2% = %/2 = %
PV (Present Value Calculation) – using Excel Formula
Step 1) Select Formulas
Step 2) Click on Financial
Step 3) Select PV – you will see the formula below
Step 4) Enter the following:
Rate – enter as decimal, no % sign. Example: 4% as 0.04 if p
Nper – number of periods where dividends are paid. For exam
Pmt – payment – The semiannual payment of dividends in do
Fv – Future value. Enter as positive. Example 1,000 should b
Type – leave blank
e help on the right hand side of the sheet)
Updated: 10/2018 by RFB
nd paid by UPS for 2015, 2016, and 2017 are found on the
S Annual Report.
15, 2016, and 2017 are found on the second page of the 2017
r’s equity for 2015, 2016, and 2017 are found on the second
port.
dividend per share of common stock divided by the market price
ck. (Dividend yield = Annual Dividend/Current Stock Price)
ot part of the Financial Statements – calculated using the formula
ts – Liabilities. This represents the ownership of a corporations.
because they hold stocks or share of the company. The main goal
to generate shareholder value.
for 2015, 2016 and 2017 will be found on page 2 of the 2017
art we will modify and use return on investment instead.
+1.75)/+[(new price-old price)/old price]
r corporations. By buying a bond, the bond-owner lends money to
promises to pay specified interest rate during the loans lifetime
he entire principle. In case of bankruptcy, bondholders have
any payment distributions.
olders = Lenders
holders = OwnersCalculation:
e, assume that UPS issues a new ten-year bond for 100,000 that
ture Value of this bond is therefore $100,000. The bond was
a market rate of 5.0% fixed for 10 years, with interest
lly. What is the Present Value of this bond using the three
suance. The coupon rate, which is used to calculate the semis 10%.
on) – using Excel Formula
see the formula below
% sign. Example: 4% as 0.04 if paid annually.
If paid semiannually 4/2 = 2% 0.02
here dividends are paid. For example, a 10 year bond pays diviends annually. N = 10.
nual payment of dividends in dollars
positive. Example 1,000 should be 1,000
If semiannualy 10 X 2 = 20 N=2
semiannualy 10 X 2 = 20 N=20
Milestone Three: Capital Budgeting Data (fill in YELLOW cells)
Initial Outlay
CF1
($65,000,000)
Cash Flows (Sales)
– Operating Costs (excluding Depreciation)
– Depreciation Rate of 20%
Operating Income (EBIT)
– Income Tax (Rate 25%)
After-Tax EBIT
+ Depreciation
Cash Flows
($65,000,000)
NPV
IRR
$50,000,000
$25,500,000
(13,000,000)
11,500,000
2,875,000
8,625,000
13,000,000
21,625,000
$15,404,422.60
19%
WACC
CF2
CF3
$45,000,000
$25,500,000
(13,000,000)
6,500,000
1,625,000
4,875,000
13,000,000
17,875,000
Select from drop
down below:
ACCEPT
ACCEPT
$65,500,000
$25,500,000
(13,000,000)
27,000,000
6,750,000
20,250,000
13,000,000
33,250,000
9%
CF4
CF5
$55,000,000
$25,500,000
(13,000,000)
16,500,000
4,125,000
12,375,000
13,000,000
25,375,000
$25,000,000
$25,500,000
(13,000,000)
(13,500,000)
(3,375,000)
(10,125,000)
13,000,000
2,875,000
Capital Budgeting Example Set-up
ACCEPT
Initial investment $65,000,000
REJECT
Straight-line Depreciation of 20%
Income Tax @25%
WACC: use 9% (UPS WACC was about 9.43%)
Cash Flow (which in this case are Sales Revenues) are as follows:
CF1: $50,000,000
CF2: $45,000,000
CF3: $65,500,000
CF4: $55,000,000
CF5: $25,000,000
Operating Costs
CF1: $25,500,000
CF2: $25,500,000
CF3: $25,500,000
CF4: $25,500,000
CF5: $25,500,000
WACC- why do we use WACC rate for new projects? If the project
doesn’t earn more percent than WACC, the corporation should
abandon the project and invest money elsewhere.
Initial Investment – always negative. Corporation has to invest
money (“lose” it till they recover it via sales) in order to gain future
benefit.
Milestone Four: Interest Rate Implication (fill in YELLOW cells)
1. Original Scenario from Milestone 1 – Time Value of Money using 8%
Interest Rate
8.00%
FCF – 2015
FCF – 2016
FCF – 2017
Amounts*
Pv*
0.00
Total Pv*
*In millions
0.00
0.00
0.00
2. Change in interest rate and its implications – Lower Interest Rate (5%)
Interest Rate
FCF – 2015
FCF – 2016
FCF – 2017
Amounts*
Pv*
0.00
Total Pv*
*In millions
0.00
0.00
0.00
3. Change in interest rate and its implications – Higher Interest Rate (15%)
Interest Rate
FCF – 2015
FCF – 2016
FCF – 2017
Amounts*
Pv*
0.00
Total Pv*
*In millions
0.00
0.00
0.00
Explanation:
Use Milestone One and Time Value of Money for Milestone Four an
Two cases will be analyzed:
Lower Interest Rate at 5%
Higher Interest Rate at 15%

Purchase answer to see full
attachment