– Please see exercise outline that shows instructions to completeDescriptionSolution downloadThe…

– Please see exercise outline that shows instructions to completeDescriptionSolution downloadThe QuestionPlease see exercise outline that shows instructions to complete MS excel ratio analysis document.? All data in blue has been updated.? Need help creating formulas for cells in red.?Ratio Analysis1) Assume that you are a financial analyst updating this ratio analysis. Start by getting the latest data onIBM. Try ?http://finance.yahoo.com?>?Search Yahoo? for ?IBM?>Income Statements & Balance Sheet.Update tabs ?income? and ?balance? in your file with the recent data and the previous year?s data. THISDATA IS ALREADY POSTED TO THE SPREADSHEET.Note: As of 29 Feb 2016, the latest financial statements available via finance.yahoo.com on IBM is asof 31 Dec 2015. This is the data that you should use for this exercise. You are comparing 2015 to 2014.2) Also regard IBM’s current market data from Yahoo’s finance page. Take the price-per-share (pps)directly from Yahoo (the big bold unlabeled number on the Yahoo page) and enter it into your homeworkfile. Overwrite the date that you downloaded this data into the appropriate cells (see the old dates?).For the “Market to Book” ratio, take “Mkt Cap” from Yahoo, enter the value into your spreadsheet anddivide it by ?shareholders equity? from the balance sheet to get Mkt/Book. Also, get the E.P.S. data froma current page. For your ?PE ratio?, create a formula using the current pps and EPS data.3) As you update the various ratios, use Excel formulas with cell references, rather than typing in anactual value. For example use ?=B7/F5? rather than ?=B7/12?. For the calculated cells, you MUST use cellreferences in the formulas (and not actual values). The other advantage to using cell references in yourformulas instead of actual values is that your spreadsheet will be more functional ? if you change theinputs, the ratios will update automatically. There are very rare exceptions to this ?use cell references,not values? rule when the model actually contains a constant instead of a variable.Hint1: When working in the ?RATIO? tab, for example in cell B3, and you want to write a formula usingdata in the ?BALANCE? tab, the format is ?=balance!B8/balance!F6?Hint2: One method of checking your formula is to write the formula using the OLD sample data. Note ifthe results that you get are the same as the sample. If not, the error is in your formula. This suggestionis especially relevant for ratios for which there are alternative methods of calculation and for accountsthat go by several different names.Hint3: You might want to try building formulas in the income statement and balance sheet itself. Thisstep is not required, but may save you some points as it will sometimes pick up data entry errors thatotherwise go undetected.3) Notice column E in the ratio tab. These are brief comments regarding the change in the ratios andwhether they are going in a favorable direction. To say that a value is getting bigger or smaller is notsaying whether the firm is getting better or worse in the particular area that the ratio measures. Changethe comments to reflect the changes from 2014 to 2015. Your comments will be the real ?analysis?.

"Is this question part of your assignment? We can help"

ORDER NOW