FIN 630 Manchester Metropolitan University Weighted Average cost of Capital Excel Sheet The question will be based on CINTAS Corporation.Please read projec

FIN 630 Manchester Metropolitan University Weighted Average cost of Capital Excel Sheet The question will be based on CINTAS Corporation.Please read project’s description in the attached Word document.Excel file submission is enough as long as you provide your sources, assumptions made, if any, and brief discussion of results.You might find screenshots attached useful. Also please check WACC Assignment FAQ. The purpose of this project is for you to have some practice working with financial concepts in
the real world. This will involve integrating some material from throughout the course. The
project will also involve the development of your own approach to doing the work. The project
does not provide a step-by-step procedure for you to follow.
Your task is to determine the WACC for a given firm using what you know about WACC as well
as data you can find through research. Your deliverable is to be a brief report in which you state
your determination of WACC, describe and justify how you determined the number, and provide
relevant information as to the sources of your data. Instead of submitting two files, report’s
elements can be embedded in Excel file.
With the help of your professor, you have selected a company for which to research and find the
WACC (this is the same firm that you have chosen for Module 1 and 2 assignments). Your
research is to be independent from any information you may find at thatswacc.com or similar
sites although you might want to use such sites to provide a reasonableness check on the WACC
you calculate.
Assumptions
As you recall, the formula for WACC is
WACC =
E
D
rE +
rD (1 ? TC )
E+D
E+D
The formula for the required return on a given equity investment is
ri= rf + ?i * (RMkt-rf)
rMkt-rf is the Market Risk Premium. For this project, you may check Damodaran’s website to
estimate risk premium based on historical data (as a difference between S&P 500 and Bond
returns) or use implied risk premium. You may also use other sources to estimate the Market
Risk Premium. Usually it is assumed to be in the range between 3% and 8%.
rf is the risk free rate. The YTM on 10 year US Treasury securities is a good approximation.
You may assume a corporate tax rate of 21%.
One good source for financial data for companies as well as data about their equity is
http://finance.yahoo.com . By looking around this site, you should be able to find the market
capitalization (E) as well as the ? for any publicly traded company.
There are not many places left where data about corporate bonds is still available. One of them is
http://finra-markets.morningstar.com/BondCenter . To find data for a particular company’s
bonds, find the Quick Search feature, then be sure to specify corporate bonds and type in the
name of the issuing company. This should give you a list of all of the company’s outstanding
bond issues. Clicking on the symbol for a given bond issue will lead you to the current amount
outstanding and the yield to maturity. You are interested in both. The total of all bonds
outstanding is D in the above formula.
If you like, you can use the YTM on a bond issue that is not callable as the pre-tax cost of debt
for the company.
Deliverable
Submit the Excel File with your calculations.
Write a two or three page report that contains the following elements:
–
Your calculated WACC.
Sources for your data.
A discussion of how much confidence you have in your answer. What were the limiting
assumptions that you made, if any.
Instead of a separate Word file, report elements can be embedded in Excel file.
FS to FCF Template
Based on historical financial data, find FCF for the company of your choice.
1.
In week 1 you were asked to choose a publicly traded company and submit its name for instructor’s approval. Assuming
2.
Download annual financial data for the last 5 years from Edgar Website (forms 10-K). Attention: if your company became public less
than 5 years ago, you will also have to download data from its IPO filing prospectus (form 424B4). Template below is approxim ate, you
may modify it (change, delete, or add rows), to suit your company
3.
Using approach similar to the one described in this module’s Excel file you will have to calculate the FCF for the company of your
choice
Solution Legend
Value given in problem
Formula/Calculation/Analysis required
Qualitative analysis or Short answer required
If you have difficulty estimating company’s tax rate you may assume statutory tax rate of 35% for years before 2018.
For submission, Excel file is enough
Financial Data
USD in millions except per share data.
Assets
Cash and cash equivalents
Short-term investments
Receivables
Inventories
Prepaid expenses
Other current assets
Total current assets
Non-current assets
Property, plant and equipment
Gross property, plant and equipment
Accumulated Depreciation
Net property, plant and equipment
Equity and other investments
Goodwill
Intangible assets
Other long-term assets
Total non-current assets
Total assets
Liabilities
Current liabilities
Short-term debt
Accounts payable
Deferred income taxes
Taxes payable
Accrued liabilities
Other current liabilities
Total current liabilities
Non-current liabilities
Long-term debt
Deferred taxes liabilities
Accrued liabilities
Total non-current liabilities
Total liabilities
Stockholders’ equity
Common stock
Additional paid-in capital
Retained earnings
Treasury stock
Accumulated other comprehensive income
Total stockholders’ equity
Total liabilities and stockholders’ equity
Income Statement (USD in millions except per share data)
Revenue
Cost of revenue
Gross profit
Operating expenses
Sales, General and administrative
Depreciation and Armotization
Restructuring, merger and acquisition
Other operating expenses
Total operating expenses
Operating income
Interest Expense
Other income (expense)
Income before taxes
Provision for income taxes
Other income
Net income from continuing operations
Net income from discontinuing ops
Net income
Preferred dividend
Net income available to common shareholders
Earnings per share
Basic
Diluted
Weighted average shares outstanding
Basic
Diluted
Year 1
Year 2
Year 3
$417
$16
$496
$226
$24
$556
$1,736
$139
$70
$563
$249
$26
$542
$1,590
$169
$22
$736
$278
$30
$719
$1,954
$2,194
($1,323)
$871
$330
$1,196
$44
$16
$2,457
$4,192
$2,414
($1,419)
$994
$125
$1,292
$76
$28
$2,514
$4,104
$2,840
($1,516)
$1,324
$165
$2,782
$588
$30
$4,890
$6,844
Year 4
Year 5
$139
$97
$805
$280
$32
$722
$1,978
$910
$335
$103
$792
$2,236
$3,046
($1,664)
$1,383
$176
$2,847
$575
$3,192
($1,762)
$1,431
$192
$2,842
$735
$4,980
$6,958
$5,200
$7,437
$250
$115
$363
$177
$215
$312
$226
$6
$268
$178
$816
$8
$312
$271
$1,131
$8
$299
$253
$776
$8
$302
$279
$1,128
$1,300
$227
$112
$1,639
$2,260
$1,050
$259
$137
$1,446
$2,262
$2,771
$469
$170
$3,410
$4,541
$2,535
$353
$278
$3,166
$3,942
$2,538
$438
$331
$3,306
$4,434
$329
$157
$4,228
($2,773)
($8)
$1,932
$4,192
$410
$205
$4,806
($3,553)
($25)
$1,843
$4,104
$485
$224
$5,171
($3,574)
($3)
$2,303
$6,844
$618
$245
$5,838
($3,701)
$16
$3,017
$6,958
$840
$228
$6,691
($4,718)
($39)
$3,003
$7,437
$110
$112
$6
$235
$158
$621
Year 1
$4,477
$2,556
$1,921
Year 2
$4,905
$2,776
$2,130
$1,225
$155
$1,348
$165
$1,225
$696
$65
$27
$658
$245
($6)
$408
$23
$431
$1,348
$782
$65
$1
$718
$261
Year 3
$5,323
$2,943
$2,380
Year 4
$6,477
$3,568
$2,909
Year 5
$6,892
$3,764
$3,129
$1,527
$197
$79
($79)
$1,527
$853
$87
($79)
$687
$230
$1,917
$279
$42
($42)
$1,917
$992
$110
($41)
$841
$57
$1,981
$360
$14
($14)
$1,981
$1,148
$102
$56
$1,102
$220
$457
$23
$481
$784
$59
$843
$431
$457
$237
$694
$7
$686
$481
$843
$883
$2
$885
$10
$875
$4
$4
$6
$6
$4
$4
$8
$8
$8
$8
$116
$118
$108
$110
$105
$108
$107
$110
$106
$109
Year 1
$1,319
$621
$698
Year 2
$1,451
$566
$885
Year 3
$1,785
$768
$1,017
Year 4
$1,839
$776
$1,063
Year 5
$2,139
$816
$1,323
$187
$132
$46
$260
Find Changes in NWC
Current Assets, other than Cash and Marketable Securities
Current Liabilities, excluding Debt
Net Working Capital (CA-CL)
Change in NWC (NWC Investment)
Find CapEx
Net Property, Plant & Equipment
Depreciation and amortization
Capex
You can read more using the following link
Year 1
$871
$155
Year 2
Year 3
Year 4
Year 5
$994
$1,324
$1,383
$1,431
$165
$197
$279
$360
$133
$362
$141
$129
Howard Keen, Clearing Up Confusion over calculation of FCF.
Estimate FCF
Tax Rate
35%
21%
Year
Year 1
EBIT
EBIT(1-T) = NOPAT
Plus: Depreciation Expense
Less: CAPEX
Less: Working Capital Investment
Firm Free Cash Flow ( USD in millions)
Year 2
$782
$508
$165
$133
$187
$353
Year 3
$853
$554
$197
$362
$132
$257
Year 4
$992
$645
$279
$141
$46
$737
Year 5
$1,148
$907
$360
$129
$260
$878
The free cash flow of Cintas between the years 2016 and 2019 were as shown above. the company’s free cash flows were positive over the 4 year period. Additionally, the trend analysis of the free cash flows
shows that, overall, the company’s free cash flows increased between 2016 and 2019. the increase is as a result of the increase in the company’s earnings before interest and taxes and the overall decrease in
CAPEX.
Page 1

Purchase answer to see full
attachment