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, reports
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 companys
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 companys 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
Why Choose Us
Top quality papers
We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.
Professional academic writers
We have hired a team of professional writers experienced in academic and business writing. Most of them are native speakers and PhD holders able to take care of any assignment you need help with.
Free revisions
If you feel that we missed something, send the order for a free revision. You will have 10 days to send the order for revision after you receive the final paper. You can either do it on your own after signing in to your personal account or by contacting our support.
On-time delivery
All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.
Original & confidential
We use several checkers to make sure that all papers you receive are plagiarism-free. Our editors carefully go through all in-text citations. We also promise full confidentiality in all our services.
24/7 Customer Support
Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
Our Services
No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.
Essays
You are welcome to choose your academic level and the type of your paper. Our academic experts will gladly help you with essays, case studies, research papers and other assignments.
Admissions
Admission help & business writing
You can be positive that we will be here 24/7 to help you get accepted to the Master’s program at the TOP-universities or help you get a well-paid position.
Reviews
Editing your paper
Our academic writers and editors will help you submit a well-structured and organized paper just on time. We will ensure that your final paper is of the highest quality and absolutely free of mistakes.
Reviews
Revising your paper
Our academic writers and editors will help you with unlimited number of revisions in case you need any customization of your academic papers