Select Page

Complete the required content in the 652mids2019.docx. Can refer to the example format in the file(student forecasting.docx) . I just want help with Accounting Analysis, Financial analysis (Only excel), and forecasting/valuation part. I dont need introduction, strategy analysis and explaination for financial analysis


Don't use plagiarized sources. Get Your Custom Essay on
Business Analysis Case Merck Support Calculations
Just from $10/Page
Order Essay


Unformatted Attachment Preview

Analysis of Accounting Data
Business Analysis Case (100 Points)
Merck is a leading worldwide pharmaceutical company that delivers health solutions via
prescription medicines, vaccines, biological therapies and animal health products.
Headquartered in New Jersey it is well known for its R&D intensive efforts leading to new
branded drugs in the healthcare treatment for cancer and diabetes. According to IBIS the
Company has a 10% share of the U.S. branded name pharmaceutical manufacturing market.
Perform a complete business analysis (all four parts) and determine if the company’s stock is
fairly priced as of 3/25/19. In your analysis please be sure to discuss all major accounting
issues and indicate amounts that would be adjusted even if they are immaterial and you
determine no adjustment is required. Also, be sure to perform a complete financial analysis
as part of your business analysis. Please start with the industry/strategy analysis followed by
the accounting analysis, financial analysis and forecast/valuation. You will receive a separate
grade for each part. Be advised that you should use the ratios found in the data file in the
financial analysis tab and trend analysis utilizing 5 years of data for profitability ratios and at
least 3 years of data for liquidity and solvency ratios.
Please make sure to support all of your assumptions in the forecast process.
Please read the documents posted in canvas that refer to the major parts of a business
analysis. Class notes and guidance should be read as well. Keep in mind that the
forecast/valuation is essential so please read the example from a prior exam carefully since it
includes using the CAPM to estimate a company’s cost of equity capital. You will need to
insert this value for Merck into the valuation parameters section of the software. You do not
need to change any of the other items in that section and remember your valuation is based
upon the most recent Merck annual financials provided to you in canvas. I will provide you
with suitable inputs for using the CAPM with the data file at the time it is posted by March
24. Please begin with the industry/strategy analysis and follow the sequence noted above. Do
not do the accounting analysis after the financial analysis.
The data file (similar in format to the one for Microsoft posted in canvas and used in class)
will be used for your financial analysis and forecast/valuation to complete your exam.
Instructions for downloading the Microsoft data file earlier are the same for using the Merck
file. Please submit your exam via canvas and attach a section within your forecasting section
that has your assumptions and includes your estimate for the cost of equity and how it was
derived. In the event your forecasted price differs from the market price (it usually does)
provide some sensitivity analysis on what is likely causing the difference. Please submit your
exam to me through canvas assignments. The due date for the exam is 6pm on April 7th.
Your submission should include a word file and your excel data file. Make sure to make a
copy of both of these files for your records. Please contact me via e-mail if you have any
additional questions. Remember you are under the honor code to complete the exam on
your own and to cite any sources used as references when completing any part of the exam.
You are not to distribute any portion of this exam or any of the material posted in our canvas
site now or in the future.
Student Example From A Prior Exam (Student A)
Cost of Equity Capital
I obtained the risk free rate of return (Rf) from the US Department of Treasury 30 year rate as of
7/15/2014 which is 3.37%. This is the interest rate that an investor would expect to receive on a risk
free investment
I used the S&P Beta of .55, as given in the instructions. When the Beta is below 1 it indicates
that the company is less risky than the stock market as a whole. A beta of .55 means that General Mills
is fairly risk free, about 50% less risky than the stock market as a whole.
Finally, I decided to use a risk free premium of 6.5% since this is generally what investors would
expect to receive on a risky asset.
To calculate the cost of equity I used the capital asset pricing model.
Required Rate of Return = Rf+ β (Rp)
= .0337 + (.55)(.065)
= 6.95%
Cost of Equity Capital for General Mills = 6.95% rates/Pages/TextView.aspx?data
Forecasting / Valuation
Below is my sales forecast for the next five years.
In 2011 when General Mills acquired Yoplait, net sales grew 12 percent to $16.7 billion. The
international Yoplait acquisition, which was completed in July 2011, contributed approximately 7 points
of the total net sales growth. This is a very large jump, but this was also an international acquisition
which is why I believe it made such a large spike. Yoplait is also a very well-known brand which already
had a large amount of market penetration.
According to General Mills, “our Number One objective in the new fiscal year is to accelerate our topline
growth…Our fiscal 2015 plans include a strong new-product lineup, compelling news or renovation on
many existing brands, and a full slate of consumer-focused marketing initiatives” (News Release 2012). I
believe that this initiative will raise sales slightly for 2015. I think that the larger growth will occur in
2016, due to the Acquisition of Annie’s, which was announced after the 10K was released. I believe that
the acquisition of Annie’s, which took place after the 2014 10K was released in September 2014, will
result in a sales increase slightly in 2015, but not by much. The acquisition of Annie’s will likely affect
sales, but seeing that this acquisition will not be fully in effect until end of 2014, I do not think it will
have a large effect on sales until after the 2015 10K is released. That is why I have a larger increase from
2015-2016 than from 2014-2015. I think sales will then continue to grow slightly, leveling off in the last
few years. I do not think sales will spike the way that they did when General Mills acquired Yoplait.
Annie’s does not have even close to the market penetration that Yoplait has, it does not have the same
amount of brand recognition, and it is not an international company. If General Mills were to make
other acquisitions or come out with new products in the future, I would expect even larger sales, but
only knowing about the Annie’s acquisition this is what I project.
Below is my COGS forecast.
I have not increased cost of goods sold by much, only slightly. The biggest increase was from 2014 to
2015, where I project that it will increase by .6%. The cost of products that General Mills is using to
produce their goods is rising, partly due to inflation. I think it will also rise due to the organic and
natural foods market that General Mills is beginning to try to satisfy. These products are more
expensive than non-organic products and will increase their cost of goods sold in the short term until
they can get a handle on these expenses and find ways to reduce the cost. Below is a chart of inflation
rates over a 10 year period.
Graph from
As you can see above, the biggest increase was in 2011. The May 27, 2012 cost of goods sold jumped
3.7% for that period, indicating that inflation played a role in the increase. I foresee the cost of goods
sold rising slightly up to 2017. This is when I believe inflation rates will start to level off since we will be
fully out of the recession at this point and back to a somewhat stable economy. From 2018 and after, I
foresee the cost of goods sold slightly decreasing as the price of goods drops slightly, but not by much.
Below is my forecast for SG&A.
According to General Mills “it has begun a formal review of its North American manufacturing and
distribution network with the goals of streamlining operations and identifying potential capacity
reductions. The company also has initiated efforts focused on further reducing overhead costs.
Together, the new cost-reduction initiatives are targeted to generate savings of $40 million pretax in
fiscal 2015, with additional savings expected in fiscal 2016” (News Release 2014). Even with their
initiative, I foresee SG&A expenses rising, but only slightly. I think that it will go up by .1% between 2014
and 2015, increasing more dramatically from 2015 to 2017 due to the future acquisition of Annie’s
which will result in the acquisition of more employees from Annie’s. Once that acquisition is stabilized
and they are better able to take a look at their SG&A to cut costs, I believe these costs will start to drop
For interest expense to average debt, I have raised it .4% from 2014 to 2015. During the acquisition of
Yoplait, the interest expense and debt seemed to rise slightly, so I wanted to account for the possibility
of this with the Annie’s acquisition. I think that the rate will then level off and drop. I think that interest
rates will drop, as they are doing now. As the Federal Reserve prints more money, the interest rates
drop. I think that this will continue for another couple years. This practice cannot continue forever, so
around 2018-2019, I think that the interest rates will being to level off.
I have decided to keep non-operating income between .5% and .7% of sales. There is little to no
movement in this number. I am expecting that this would remain relatively low because the majority of
General Mills’ income comes from their operations and they do not seem to be making any initiative or
effort to change this. Additionally, I have chosen to keep other income between 0% and 1% of net sales
for the forecasting horizon. Based on their history, I believe the 0-1% to be conservative. Once again,
there is no indication that this would increase at all over the forecast period. As far as minority interest/
after tax income, this seems to be on an upward trend. I continued this upward trend slightly over the
course of the forecast, accounting for any potential increase in minority interest.
In 2014, income taxes were higher than they were in 2013. In 2013 the tax rate was 28.3%, which in
2014 it was 32.4%. The reason for this decrease was attributed to restructuring costs of their GMC
subsidiary. The restructuring resulted in a “$63 million decrease to deferred income tax liabilities
related to the tax basis of the investment in GMC and certain distributed assets, with a corresponding
discrete non-cash reduction to income taxes”. Since this decrease was due mainly to restructuring, it is
expected that the income tax expense will go back up to around 32% for future years. My forecast is
shown below.
For 2015, I set income taxes to 32%, which is their relative range. I increased this slightly over the
course of the forecast to account for any possible tax law modifications that could result in slightly
higher tax rates. I do not believe that there will be any large changes to the tax law, which is why I kept
this increase within the range in which it has been in the last few years, peaking at 32.4%.
The result of my forecast yielded a stock price of $57.92. According to Yahoo Finance, the actual
closing price on 7/15/14 was 52.93. Currently, as of 11/5/14, the stock is trading at $53.17. I think that
my stock price is slightly high relative to both the current trading value and the historic trading value.
After looking through General Mills 10K and analyzing their ratios, I feel General Mills is a strong
company and is relatively stable. Like other consumer companies, General Mills has struggled to
manufacture growth. Breakfast cereal is a mature market in all economies and markets (Allison). I do
not foresee major gross profit increases in the near future and as long as they continue to be innovative
in their product growth and participate in acquisitions, I feel they will be fine in the future. Their
products are worldwide and brand names are very strong. I would buy General Mills’ stock and hold it
for the long run.
Sensitivity Analysis
I began my analysis by changing the cost of capital. Instead of using a risk premium of 6.5%, I bumped it
up to 7%, which resulted in a cost of capital of 7.22%. This increase lowered my stock price to 54.75%,
which is very close to the current trading value of $53.17.
My next adjustment was to go back to the financials and change some of the forecasted sales and
expenses. I changed my sales increase for 2016 from 2.5% to 2.0% and 2017 from 2.7% to 2.5%. This
dropped the stock price to $54.47. I then adjusted my SG&A expenses to consistently 20%, lowering the
stock price further to $52.49. Small changes to the sales and expense growth lowered the stock price to
about where it is today. However, I still think that General Mills has the opportunity to reach $57.92 in
the future if their new products and acquisitions achieve the goals I believe they will.
Works Cited
Allison, Kevin. “The Risk In General Mills Deal for Annie’s”. New York Times.
“Current US Inflation Rates: 2004-2014” US Inflation Calculator.
“Debt to Equity”. Investopedia.
“Financial Leverage”. Investopedia.
General Mills, Inc Form 10K. PDF
“GIS Historical Prices”. Yahoo Finance.
Harper, David. “Financial Statements: Pension Plans”. Investopedia.

IBIS World Industry Report: Cereal. PDF
IBIS World Industry Report: Yogurt. PDF
“Kellogg Co”. MorningStar.
Muller, Alan. “A detailed review of every contaminated site in Minnesota is urgently needed….”.

“Net Profit Margin”. Investing Answers.
“News Release”. General Mills.

“News Release 2012”. General Mills.
“News Release 2014”. General Mills.
Oliver, Joseph R. “Accounting and Tax Treatment of R&D: An Update”. The CPA Journal.

Peavler, Rosemary. “What is the long-term debt to total capitalization ratio? How is it calculated?”.

“Pension/OPEB 2014 assumption and disclosure survey”. PwC. PDF
“Return on Assets”. Investopedia.
“Return on Net Assets”. Investopedia.
S&P Industry Survey: Foods & Non-Alcoholic Beverages. PDF
As filed with the Securities and Exchange Commission on February 27, 2019

Annual Report Pursuant to Section 13 or 15(d) of the Securities Exchange Act of 1934
For the Fiscal Year Ended December 31, 2018
Transition Report Pursuant to Section 13 or 15(d) of the Securities Exchange Act of 1934
For the transition period from
Commission File No. 1-6571
Merck & Co., Inc.
2000 Galloping Hill Road
Kenilworth, N. J. 07033
(908) 740-4000
Incorporated in New Jersey
I.R.S. Employer
Identification No. 22-1918501
Securities Registered pursuant to Section 12(b) of the Act:
Title of Each Class
Common Stock ($0.50 par value)
1.125% Notes due 2021
0.500% Notes due 2024
1.875% Notes due 2026
2.500% Notes due 2034
1.375% Notes due 2036
Name of Each Exchange on which Registered
New York Stock Exchange
New York Stock Exchange
New York Stock Exchange
New York Stock Exchange
New York Stock Exchange
New York Stock Exchange
Number of shares of Common Stock ($0.50 par value) outstanding as of January 31, 2019: 2,581,220,308.
Aggregate market value of Common Stock ($0.50 par value) held by non-affiliates on June 30, 2018 based on closing price on June 30,
2018: $161,991,000,000.
Indicate by check mark if the registrant is a well-known seasoned issuer, as defined in Rule 405 of the Securities Act. Yes ☒
No ☐
Indicate by check mark if the registrant is not required to file reports pursuant to Section 13 or Section 15(d) of the Act. Yes ☐
No ☒
Indicate by check mark whether the registrant (1) has filed all reports required to be filed by Section 13 or 15(d) of the Securities Exchange
Act of 1934 during the preceding 12 months (or for such shorter period that the registrant was required to file such reports), and (2) has been subject
to such filing requirements for the past 90 days. Yes ☒
No ☐
Indicate by check mark whether the registrant has submitted electronically every Interactive Data File required to be submitted pursuant
to Rule 405 of Regulation S-T (§ 232.405 of this chapter) during the preceding 12 months (or for such shorter period that the registrant was required
to submit such files). Yes ☒
No ☐
Indicate by check mark if disclosure of delinquent filers pursuant to Item 405 of Regulation S-K (§ 229.405) is not contained herein, and
will not be contained, to the best of registrant’s knowledge, in definitive proxy or information statements incorporated by reference in Part III of
this Form 10-K or any amendment to this Form 10-K. ☐
Indicate by check mark whether the registrant is a large accelerated filer, an accelerated filer, a non-accelerated filer, a smaller reporting
company, or an emerging growth company. See the definitions of “large accelerated filer,” “accelerated filer,” “smaller reporting company,” and
“emerging growth company” in Rule 12b-2 of the Exchange Act. (Check One):
Large accelerated filer
Non-accelerated filer

Accelerated filer
Smaller reporting company
Emerging growth company

If an emerging growth company, indicate by check mark if the registrant has elected not to use the extended transition period for complying
with any new or revised financial accounting standards provided pursuant to Section 13(a) of the Exchange Act. ☐
Indicate by check mark whether the registrant is a shell company (as defined in Rule 12b-2 of the Exchange Act). Yes ☐
Documents Incorporated by Reference:
Proxy Statement for the Annual Meeting of Shareholders to be held May 28, 2019, to be filed with the
Securities and Exchange Commission within 120 days after the close of the fiscal year covered by this report
No ☒
Part of Form 10-K
Part III
Table of Contents
Table of Contents
Part I
Item 1.
Item 1A.
Item 1B.
Item 2.
Item 3.
Item 4.
Item 5.
Item 6.
Item 7.
Item 7A.
Item 8.
Item 9.
Item 9A.
Item 9B.
Item 10.
Item 11.
Item 12.
Item 13.
Item 14.
Item 15.
Item 16.
Risk Factors
Cautionary Factors that May Affect Future Results
Unresolved Staff Comments
Legal Proceedings
Mine Safety Disclosures
Executive Officers of the Registrant
Part II
Market for Registrant’s Common Equity, Related Stockholder Matters and Issuer Purchases of
Equity Securities
Selected Financial Data
Management’s Discussion and Analysis of Financial Condition and Results of Operations
Quantitative and Qualitative Discl …
Purchase answer to see full

Order your essay today and save 10% with the discount code ESSAYHSELP