what is the quote to do this project for me
Project Instruction (Merck) Your company is: Merck Co. (MCK) Semester Project Instructions: Stock Valuation Please read very carefully. The project consists of i) an individual report and ii) a group oral presentation. The company you’re assigned at the top of this page is your company. The instructions are different for each company, so make sure that you do not share the instructions with another student. The entire project is an individual assignment, and you may not collaborate on the report, except for the Canvas discussion. The estimated number of hours to be spent on this project is at least 25-30 hours in total. So please do not wait until the last minute. I will frequently send out reminders so that you can complete each stage in a timely manner. By the end of the course, you have to prepare a professional report of analysis on this company. You will be analyzing and dissecting this company’s business, finance, operations, and risk in order to come up with a valuation. IMPORTANT: Please save your work as frequently as possible. A sudden crash or shutdown will not be a valid excuse for extension. Requirements: i) Professionalism: To become a “professional” rather than an “amateur,” we can start by emulating the looks of professionals. Make your report look good, look like something done by a professional. In the business world, the looks are just as important as the content. People won’t bother to look at unattractive, messy reports, no matter how excellent the content might be. ii) All reports must be completed in Microsoft Word: 8-10 pages long, single-spaced, Times New Roman, and 12 pts. As for other things, go out of your way and be creative. But don’t overdo it. Sometimes, the simplicity is the virtue. Tables, charts, graphs are all welcome if they serve appropriate purposes and needs. The appropriate use of italics, bold, and underlines is also permitted. You may also use color. iii) You may have a cover page but it won’t count towards the 8-10 page length requirement of the report. iv) Math equations and formulas can be typed with the math applications embedded in Microsoft Word. Different versions of Word support different equation tools. For help, please consult the Google search. v) You’re required to show all your calculations to prove that you did your own work. However, if they make the report look messy, please make the appendix section at the very end of the report where you can show me all your calculation (tables can be imported from Excel and be modified to look nice). Use your judgement to decide whether showing the calculation is critical as part of the report or is more appropriate in the appendix. The appendix does not count towards the designated length of the report. For the appendix section, you may hand-write the formulas (you will have to scan) if you’re not familiar with typing math equations on Word. vi) You can utilize all the resources and tools available outside of class, including the web documents, Excel, Powerpoint, Words, etc. vii) Please submit your report in pdf file (please do not attach the Word document or Excel spreadsheets as your final submission. You may submit spreadsheets and Word for Project Progress only.). Part I (15 Points). Company Description (<=1 page)="" we="" have="" to="" command="" a="" thorough="" understanding="" of="" the="" firm’s="" nature="" of="" business="" in="" order="" to="" come="" up="" with="" the="" right="" valuation.="" first,="" please="" download="" the="" annual="" 10-k="" filings="" for="" the="" company.="" the="" document="" can="" get="" as="" long="" as="" 100="" pages.="" it="" is="" a="" comprehensive="" financial="" report="" that="" includes="" explanations,="" details,="" and="" commentaries="" on="" the="" company’s="" performance="" for="" the="" year="" and="" is="" audited="" by="" an="" independent="" auditor.="" while="" there="" is="" no="" need="" to="" read="" every="" single="" page="" (because="" other="" financial="" sources="" already="" have="" a="" nice="" summary="" of="" many="" aspects="" on="" the="" 10k),="" there="" are="" some="" important="" parts="" worth="" a="" thorough="" read,="" or="" at="" least="" a="" skim,="" to="" successfully="" understand="" the="" company.="" find="" the="" table="" of="" contents="" in="" the="" 10-k,="" and="" quickly="" get="" a="" big="" picture="" of="" how="" the="" 10-k="" is="" structured.="" i="" recommend="" that="" you="" read="" the="" overview="" of="" the="" firm="" businesses,="" which="" is="" provided="" by="" the="" company’s="" management="" itself.="" you’re="" welcome="" to="" supplement="" your="" understanding="" of="" the="" firm="" with="" other="" resources="" out="" there="" besides="" the="" 10-k.="" whatever="" resources="" you="" decide="" to="" use,="" be="" sure="" to="" cite="" your="" sources="" and="" include="" the="" following="" areas,="" at="" the="" least,="" in="" describing="" the="" company:="" i)="" a="" brief="" history,="" the="" management,="" the="" location(s)="" of="" the="" headquarter="" and="" businesses,="" the="" fiscal="" year,="" etc.="" ii)="" business,="" strategy,="" products,="" services,="" etc.="" iii)="" competition,="" risk="" factors.="" part="" ii="" (50="" points).="" financial="" ratios=""><=2 pages) financial ratios are helpful for analyzing the company’s financial condition and performance. we will evaluate the company for the year 2020 and 2019. go to http://finra-markets.morningstar.com/marketdata, search for your company, and download the excel files of income statement, balance sheet, and cashflow statement on the ‘financials’ tab http://finra-markets.morningstar.com/marketdata by using “export” function on the page. when you export, the excel files will be automatically exported as ‘.csv’ files. please re-save your work as .xls, .xlsm, or .xlsx file to fully enable the excel functionalities. on a blank sheet, you can choose a cell, simply type in the equal sign ‘=’, and select the cells that contain the relevant numbers for computation. please consult any basic excel tutorial online for help. i) calculate the liquidity, leverage, turnover, profitability, and market value ratios for the most recent two years (check the fiscal year and month.). in total, you should calculate 20 (ratios) x 2 (years) = 40 ratios. please also provide the formulas and the numbers you use to calculate each ratio in the appendix. ***note*** to calculate eps, p/e, p/s, and me/be, you will need the number of shares outstanding for that year. on the income statements on morningstar, you will see the number of weighted average shares outstanding in two versions, basic and diluted. please use the basic shares. ii) please provide sufficient interpretations of the ratios and explain their change (or no change) from the year before, in 3-5 paragraphs for all the ratios. if there’s anything unusual or notable, please explain. please be cautious about the signs on financial statement figures, especially on the income and cash flow statement. some companies choose to report expense items as negative numbers because they get subtracted from the revenue to obtain the net income. when calculating the ratios, use your judgement to adjust their signs appropriately. iii) in the appendix, please show the formulas (with actual numbers used) for all 20 ratios for just one year. iv) after calculating all the ratios, please decompose the company’s profitability over the two years using the dupont analysis. explain the composition of the profitability for your company. has each component of dupont increased or decreased from the previous year? how has that affected the company’s overall profitability? is there room for improvements? *some financial ratios are already available on lots of sources including the actual 10k filings. for the purpose of learning, do not use those ratios. they may be different from what you calculate. part iii (40 points): cost of equity (2-3 pages) in part iii, we are going to calculate the company’s cost of equity in two different ways, first using the dividend growth model and then the capm method. please review this content from the workbook before starting. first, we will start with the dividend growth model. i) use the year-end price in 2020 (the last price that is available in that year) as the current price (we are going to pretend that we’re at the year-end of 2020. this is because we don’t have 2021 data in full. we’re still in the middle of it.). that is, what was the adjusted closing price of the stock on the last trading day of the year 2020? ii) f ind the company’s annua l d iv idends du r ing the 5 -yea r pe r iod 01/01/2016-12/31/2020. make sure they are sorted in the ascending order of dates. plot the dividends with the dates on the horizontal axis and the dividends on the vertical axis. describe the plot and check whether the dividends have been paid in a constant growth pattern. iii) calculate the dividend growth rate as discussed in class. use the most recent annual dividend (the sum of all quarterly dividends in year 2020) as the current dividend ( and multiply it by 1+g to calculate . iv) calculate the cost of equity using the constant growth model and the dividend growth rate calculated above. alternatively, we can use the capm method to compute the cost of equity. v) download the company’s monthly historical prices during the 5-year period 01/01/2016-12/31/2020 from finance.yahoo.com. make sure they are sorted in the ascending order of dates (oldest to newest). download the s&p500 etf (ticker: spy) monthly historical prices during the same period. using the adjusted closing 1 price for both series, calculate the monthly returns for the company and the s&p500. show a snapshot of your work in this part (5-10 lines, not the entire series) in the appendix. vi) calculate the variance of the market returns, covariance between market and the company. then, calculate the company’s beta. vii) use the capm formula to calculate the cost of equity. assume that the riskless rate of 2% and the average market return of 8%. we can compare the costs of equity obtained using two different approaches and determine what to do with them. d0 d1 spy is an exchange-traded fund (etf) that tracks s&p500 index very closely. it’s a good but imperfect proxy for 1 the market portfolio. viii) in many cases, we use the capm cost of equity. however, we may also take the average of the two values (cost of equities from (iv) and (vii)). what is the averaged cost of equity? part iv pages)="" financial="" ratios="" are="" helpful="" for="" analyzing="" the="" company’s="" financial="" condition="" and="" performance.="" we="" will="" evaluate="" the="" company="" for="" the="" year="" 2020="" and="" 2019.="" go="" to="" http://finra-markets.morningstar.com/marketdata,="" search="" for="" your="" company,="" and="" download="" the="" excel="" files="" of="" income="" statement,="" balance="" sheet,="" and="" cashflow="" statement="" on="" the="" ‘financials’="" tab="" http://finra-markets.morningstar.com/marketdata="" by="" using="" “export”="" function="" on="" the="" page.="" when="" you="" export,="" the="" excel="" files="" will="" be="" automatically="" exported="" as="" ‘.csv’="" files.="" please="" re-save="" your="" work="" as="" .xls,="" .xlsm,="" or="" .xlsx="" file="" to="" fully="" enable="" the="" excel="" functionalities.="" on="" a="" blank="" sheet,="" you="" can="" choose="" a="" cell,="" simply="" type="" in="" the="" equal="" sign="" ‘="’," and="" select="" the="" cells="" that="" contain="" the="" relevant="" numbers="" for="" computation.="" please="" consult="" any="" basic="" excel="" tutorial="" online="" for="" help.="" i)="" calculate="" the="" liquidity,="" leverage,="" turnover,="" profitability,="" and="" market="" value="" ratios="" for="" the="" most="" recent="" two="" years="" (check="" the="" fiscal="" year="" and="" month.).="" in="" total,="" you="" should="" calculate="" 20="" (ratios)="" x="" 2="" (years)="40" ratios.="" please="" also="" provide="" the="" formulas="" and="" the="" numbers="" you="" use="" to="" calculate="" each="" ratio="" in="" the="" appendix.="" ***note***="" to="" calculate="" eps,="" p/e,="" p/s,="" and="" me/be,="" you="" will="" need="" the="" number="" of="" shares="" outstanding="" for="" that="" year.="" on="" the="" income="" statements="" on="" morningstar,="" you="" will="" see="" the="" number="" of="" weighted="" average="" shares="" outstanding="" in="" two="" versions,="" basic="" and="" diluted.="" please="" use="" the="" basic="" shares.="" ii)="" please="" provide="" sufficient="" interpretations="" of="" the="" ratios="" and="" explain="" their="" change="" (or="" no="" change)="" from="" the="" year="" before,="" in="" 3-5="" paragraphs="" for="" all="" the="" ratios.="" if="" there’s="" anything="" unusual="" or="" notable,="" please="" explain.="" please="" be="" cautious="" about="" the="" signs="" on="" financial="" statement="" figures,="" especially="" on="" the="" income="" and="" cash="" flow="" statement.="" some="" companies="" choose="" to="" report="" expense="" items="" as="" negative="" numbers="" because="" they="" get="" subtracted="" from="" the="" revenue="" to="" obtain="" the="" net="" income.="" when="" calculating="" the="" ratios,="" use="" your="" judgement="" to="" adjust="" their="" signs="" appropriately.="" iii)="" in="" the="" appendix,="" please="" show="" the="" formulas="" (with="" actual="" numbers="" used)="" for="" all="" 20="" ratios="" for="" just="" one="" year.="" iv)="" after="" calculating="" all="" the="" ratios,="" please="" decompose="" the="" company’s="" profitability="" over="" the="" two="" years="" using="" the="" dupont="" analysis.="" explain="" the="" composition="" of="" the="" profitability="" for="" your="" company.="" has="" each="" component="" of="" dupont="" increased="" or="" decreased="" from="" the="" previous="" year?="" how="" has="" that="" affected="" the="" company’s="" overall="" profitability?="" is="" there="" room="" for="" improvements?="" *some="" financial="" ratios="" are="" already="" available="" on="" lots="" of="" sources="" including="" the="" actual="" 10k="" filings.="" for="" the="" purpose="" of="" learning,="" do="" not="" use="" those="" ratios.="" they="" may="" be="" different="" from="" what="" you="" calculate.="" part="" iii="" (40="" points):="" cost="" of="" equity="" (2-3="" pages)="" in="" part="" iii,="" we="" are="" going="" to="" calculate="" the="" company’s="" cost="" of="" equity="" in="" two="" different="" ways,="" first="" using="" the="" dividend="" growth="" model="" and="" then="" the="" capm="" method.="" please="" review="" this="" content="" from="" the="" workbook="" before="" starting.="" first,="" we="" will="" start="" with="" the="" dividend="" growth="" model.="" i)="" use="" the="" year-end="" price="" in="" 2020="" (the="" last="" price="" that="" is="" available="" in="" that="" year)="" as="" the="" current="" price="" (we="" are="" going="" to="" pretend="" that="" we’re="" at="" the="" year-end="" of="" 2020.="" this="" is="" because="" we="" don’t="" have="" 2021="" data="" in="" full.="" we’re="" still="" in="" the="" middle="" of="" it.).="" that="" is,="" what="" was="" the="" adjusted="" closing="" price="" of="" the="" stock="" on="" the="" last="" trading="" day="" of="" the="" year="" 2020?="" ii)="" f="" ind="" the="" company’s="" annua="" l="" d="" iv="" idends="" du="" r="" ing="" the="" 5="" -yea="" r="" pe="" r="" iod="" 01/01/2016-12/31/2020.="" make="" sure="" they="" are="" sorted="" in="" the="" ascending="" order="" of="" dates.="" plot="" the="" dividends="" with="" the="" dates="" on="" the="" horizontal="" axis="" and="" the="" dividends="" on="" the="" vertical="" axis.="" describe="" the="" plot="" and="" check="" whether="" the="" dividends="" have="" been="" paid="" in="" a="" constant="" growth="" pattern.="" iii)="" calculate="" the="" dividend="" growth="" rate="" as="" discussed="" in="" class.="" use="" the="" most="" recent="" annual="" dividend="" (the="" sum="" of="" all="" quarterly="" dividends="" in="" year="" 2020)="" as="" the="" current="" dividend="" (="" and="" multiply="" it="" by="" 1+g="" to="" calculate="" .="" iv)="" calculate="" the="" cost="" of="" equity="" using="" the="" constant="" growth="" model="" and="" the="" dividend="" growth="" rate="" calculated="" above.="" alternatively,="" we="" can="" use="" the="" capm="" method="" to="" compute="" the="" cost="" of="" equity.="" v)="" download="" the="" company’s="" monthly="" historical="" prices="" during="" the="" 5-year="" period="" 01/01/2016-12/31/2020="" from="" finance.yahoo.com.="" make="" sure="" they="" are="" sorted="" in="" the="" ascending="" order="" of="" dates="" (oldest="" to="" newest).="" download="" the="" s&p500="" etf="" (ticker:="" spy)="" monthly="" historical="" prices="" during="" the="" same="" period.="" using="" the="" adjusted="" closing="" 1="" price="" for="" both="" series,="" calculate="" the="" monthly="" returns="" for="" the="" company="" and="" the="" s&p500.="" show="" a="" snapshot="" of="" your="" work="" in="" this="" part="" (5-10="" lines,="" not="" the="" entire="" series)="" in="" the="" appendix.="" vi)="" calculate="" the="" variance="" of="" the="" market="" returns,="" covariance="" between="" market="" and="" the="" company.="" then,="" calculate="" the="" company’s="" beta.="" vii)="" use="" the="" capm="" formula="" to="" calculate="" the="" cost="" of="" equity.="" assume="" that="" the="" riskless="" rate="" of="" 2%="" and="" the="" average="" market="" return="" of="" 8%.="" we="" can="" compare="" the="" costs="" of="" equity="" obtained="" using="" two="" different="" approaches="" and="" determine="" what="" to="" do="" with="" them.="" d0="" d1="" spy="" is="" an="" exchange-traded="" fund="" (etf)="" that="" tracks="" s&p500="" index="" very="" closely.="" it’s="" a="" good="" but="" imperfect="" proxy="" for="" 1="" the="" market="" portfolio.="" viii)="" in="" many="" cases,="" we="" use="" the="" capm="" cost="" of="" equity.="" however,="" we="" may="" also="" take="" the="" average="" of="" the="" two="" values="" (cost="" of="" equities="" from="" (iv)="" and="" (vii)).="" what="" is="" the="" averaged="" cost="" of="" equity?="" part="">