Homework FINC 3304: Business Finance Work the Web project : Part 2 (due 9/20/20, new due 9/27/20) Instructor: Dr. Nazif Durmaz Work the Web Part-2, Instructions : You will use some of the techniques...

Attached


Homework FINC 3304: Business Finance Work the Web project : Part 2 (due 9/20/20, new due 9/27/20) Instructor: Dr. Nazif Durmaz Work the Web Part-2, Instructions : You will use some of the techniques discussed in Chapter-3 to do a basic financial analysis of the firm you are studying. You will need to use/submit two files for this assignment : one Word file and one Excel file. The XL file will include the data needed for the Work the Web parts. The WW2 report (Word file) will include the following : • Basic company information collected in WW1 • Trend analysis of profitability • Ratio analysis vs competitors • DuPont Analysis Please find the attached XL file (SampleWW2.xlsx). Please refer to this sample file so you understand what you must do for your company. Data collection To start WW2 you will need to obtain historical data that will be used in WW2, WW3 and WW4. Even if you don’t use all the items in this report, make sure you save them in an Excel file that can be used later. For the exercises use one of the freely available Financial Websites ; • CSIMarket https://csimarket.com/ • Macrotrends https://www.macrotrends.net/ • Zacks Investment Research https://www.zacks.com/ • Reuters.com https://www.reuters.com/finance • MSN Money https://www.msn.com/en-us/money • Yahoo ! Finance https://finance.yahoo.com/ or any other financial website you prefer or can locate your company’s data , copy and paste balance sheet and income statements for the last 3 to 5 years. Include BS and IS in the same sheet in Excel. Make sure the columns align. Check how many years are provided in each website and then decide your source. Trend analysis ROA and ROE are two of the most important variables that measure the performance of a company. For example ; you may go to the MSN website and get the 10 year chart of ROA and ROE for your company. You can use the link below and then click on the Management Effectiveness tab http://www.msn.com/en-us/money/stockdetails/ analysis/fi-126.1.XOM.NYS Once you include the information on your report, add your comments describing the trend. Is there a trend that you can see ? Is it improving or deteriorating ? Is there an especially bad year ? If there is, try to explain it. Another performance measure that investors care about is the stock price. Go to your favorite financial website and copy/paste a chart of the stock prices over the last 5 years (or 10 years if available). Compare to the previous chart. Do you see a relationship between profitability and stock prices ? Describe the trend. Is it increasing or decreasing ? Is there an especially bad year ? If there is, try to explain it. Ratio analysis 1 https://csimarket.com/ https://www.macrotrends.net/ https://www.zacks.com/ https://www.reuters.com/finance https://www.msn.com/en-us/money https://finance.yahoo.com/ http://www.msn.com/en-us/money/stockdetails/analysis/fi-126.1.XOM.NYS http://www.msn.com/en-us/money/stockdetails/analysis/fi-126.1.XOM.NYS – Work the Web project : Part 2 2 Visit one of the financial websites provided in Data Collection section that provides financial ratios for your company, industry, and sector. For instance ; you may go to this page http://www.reuters.com/finance/stocks/ financialHighlights?symbol=XOM (input the ticker for your own company) and copy the 7 categories of financial ratios that Reuters reports for Company, Industry and Sector. Start with valuation ratios. Please ignore the data shown in ”Revenue & Earnings per share”. Repeat the process for the two competitors that you identified in WW1. Copy the ratios into a new tab in Excel so the rows for your company and the 2 competitors match. Then rearrange the columns so you can see next to each other the values for your company, your competitor 1, your competitor 2, industry and sector. Once you have them next to each other, you’ll start comparing the numbers trying to find where your company performs better and where your company show worse performance than competitors. As a minimum, you must find one overall weakness and one overall strength as suggested by this comparison. It is not necessary to comment each of the financial ratios, only those that are perceived as strengths or weaknesses. You can report strengths or weaknesses in each financial ratio category but it is not necessary. Du Pont analysis You will copy key data you got in Excel into a new sheet. The specific rows you need are : spacing ◦ Sales ◦ Net Income ◦ Assets ◦ Debt ◦ Equity Based on these five rows of data you will calculate the 5 ratios involved in the Du Pont analysis for each of the years for which you have information ; spacing ◦ Profit Margin ◦ Total assets turnover ◦ Return on Assets ◦ Equity Multiplier ◦ Return on Equity Once you have all the ratios for all the years with data, compare the components of ROA and ROE against industry level and make an overall assessment of the performance of the company. Is the company doing well or not so well ? Need to monitor PM TAT or EM ? Include any recommendations that you believe are needed. http://www.reuters.com/finance/stocks/financialHighlights?symbol=XOM http://www.reuters.com/finance/stocks/financialHighlights?symbol=XOM standardized XOM Exxon Mobil Corp (NYS: XOM) Exchange rate used is that of the Year End reported date Standardized Annual Balance Sheet Report Date12/31/201712/31/201612/31/201512/31/201412/31/201312/31/201212/31/201112/31/201012/31/200912/31/2008 CurrencyUSDUSDUSDUSDUSDUSDUSDUSDUSDUSD Audit StatusNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot Qualified ConsolidatedYesYesYesYesYesYesYesYesYesYes ScaleThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousands Cash & Equivalents3177000365700037050004658000491300099230001306800084530001069300031437000 Short Term Investments-------2000169000570000 Cash & Equivs & ST Investments3177000365700037050004658000491300099230001306800084550001086200032007000 Receivables (ST)25597000213940001987500028009000331520003498700038642000322840002764500024702000 Inventories16992000150800001624500016678000161350001454200015024000129760001155300011646000 Other Current Assets1368000128500027980003565000510800050080006229000526900051750003911000 Total Current Assets47134000414160004262300052910000593080006446000072963000589840005523500072266000 Gross Property Plant & Equip477185000453915000447337000446789000434517000409314000393995000373938000305906000270845000 Accumulated Depreciation224555000209691000195732000194121000190867000182365000179331000174390000166790000149499000 Net Property Plant & Equip252630000244224000251605000252668000243650000226949000214664000199548000139116000121346000 Long Term Investments30048000256590002513500025421000258520002475900024593000260520002299600020984000 Other Assets18879000190150001739500018494000179980001762700018832000179260001597600013456000 Total Assets348691000330314000336758000349493000346808000333795000331052000302510000233323000228052000 Accounts Payable & Accrued Exps31343000264450002777300035638000414980004461400051514000445840003629600033091000 Accounts Payable28032000237920002483600032348000376150004048400044391000378060003037500027225000 Accrued Expenses3311000265300029370003290000388300041300007123000677800059210005866000 Current Debt179300001383000018762000174680001580800036530007711000278700024760002400000 Related Parties (ST Liab)5453000474800046390006589000658700061140005553000545000049790003552000 Other Current Liabilities304500026150002802000493800078310009758000127270009812000831000010057000 Total Current Liabilities57771000476380005397600064633000717240006413900077505000626330005206100049100000 LT Debt & Leases244060002893200019925000116530006891000792800093220001222700071290007025000 Pensions & OPEB21132000206800002264700025802000206460002526700024994000193670001794200020729000 Deferred LT Liabilities26893000340410003681800039230000405300003757000036618000351500002314800019726000 Minority Interests6812000650500059990006665000649200057970006348000584000048230004558000 Other Liabilities23989000251930002658200027111000265220002723100021869000204540001765100013949000 Total Liabilities161003000162989000165947000175094000172805000167932000176656000155671000122754000115087000 Common Share Capital146560001215700011612000107920001007700096530009512000937100055030005314000 Retained Earnings414540000407831000412444000408384000387432000365727000330939000298954000276937000265680000 Accum Other Comprehensive Income-16262000-22239000-23511000-18957000-10725000-121840004168000501100044020001146000 Treasury Stock225246000230424000229734000225820000212781000197333000176932000156608000166410000148098000 For Curr Trans (BS)-9482000-14501000-14170000-5952000-84600024100004168000501100044020001146000 Other Equity948200014501000141700005952000846000-2410000-17459000-14900000-14265000-12223000 Total Equity187688000167325000170811000174399000174003000165863000154396000146839000110569000112965000 Total Liabilities & Equity348691000330314000336758000349493000346808000333795000331052000302510000233323000228052000 Standardized Annual Income Statement Report Date12/31/201712/31/201612/31/201512/31/201412/31/201312/31/201212/31/201112/31/201012/31/200912/31/2008 CurrencyUSDUSDUSDUSDUSDUSDUSDUSDUSDUSD Audit StatusNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot Qualified ConsolidatedYesYesYesYesYesYesYesYesYesYes ScaleThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousands Affiliates Revenue5380000480600076440001332300013927000150100001528900010677000714300011081000 Other Revenue238983000221288000261238000398616000424328000467285000471140000372544000303443000466278000 Total Revenue244363000226094000268882000411939000438255000482295000486429000383221000310586000477359000 Direct Costs162345000157188000188268000296173000315270000336079000340305000262298000211769000321867000 Gross Profit82018000689060008061400011576600012298500014621600014612400012092300098817000155492000 Selling General & Admin41060000367090003876600044884000461070004943500054956000508010004955400057592000 Depreciation & Amortization19893000223080001804800017297000171820001588800015583000147600001191700012379000 Other Operating Expense1790000146700015230001669000197600018400002081000214400020210001451000 Total Indirect Operating Costs62743000604840005833700063850000652650006716300072620000677050006349200071422000 Operating Income1927500084220002227700051916000577200007905300073504000532180003532500084070000 Interest Income-601000-453000-311000-286000-9000-327000-247000-259000-548000-673000 Other Non-Operating Income0000000000 Total Non-Operating Income-601000-453000-311000-286000-9000-327000-247000-259000-548000-673000 Earnings Before Tax1867400079690002196600051630000577110007872600073257000529590003477700083397000 Taxation-1174000-406000541500018015000242630003104500031051000215610001511900036530000 Minority Interests1380005350004010001095000868000280100011460009380003780001647000 Extraordinary Items0000000000 Accounting Changes0000000000 Net Income1971000078400001615000032520000325800004488000041060000304600001928000045220000 Preference Dividends & Similar0000000000 Net Income to Common1971000078400001615000032520000325800004488000041060000304600001928000045220000 Average Shares Basic4256000417700041960004282000441900046280004870000488500048320005149000 EPS Net Basic4.631.883.857.67.379.78.436.243.998.78 EPS Continuing Basic4.631.883.857.67.379.78.436.243.998.78 Average Shares Diluted4256000417700041960004282000441900046280004875000489700048480005203000 EPS Net Diluted4.631.883.857.67.379.78.426.223.988.69 EPS Continuing Diluted4.631.883.857.67.379.78.426.223.988.69 Shares Outstanding4239000414800041560004201000433500045020004734000497900047270004976000 Standardized Annual Retained Earnings Report Date12/31/201712/31/201612/31/201512/31/201412/31/201312/31/201212/31/201112/31/201012/31/200912/31/2008 CurrencyUSDUSDUSDUSDUSDUSDUSDUSDUSDUSD Audit StatusNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot Qualified ConsolidatedYesYesYesYesYesYesYesYesYesYes ScaleThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousands Retained Earnings at Start407831000412444000408384000387432000365727000330939000298899000276937000265680000228518000 Retained Earnings at End414540000407831000412444000408384000387432000365727000330939000298899000276937000265680000 Standardized Annual Cash Flows Report Date12/31/201712/31/201612/31/201512/31/201412/31/201312/31/201212/31/201112/31/201012/31/200912/31/2008 CurrencyUSDUSDUSDUSDUSDUSDUSDUSDUSDUSD Audit StatusNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot Qualified ConsolidatedYesYesYesYesYesYesYesYesYesYes ScaleThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousands Net Income1984800083750001655100033615000334480004768100042206000313980001965800046867000 Adjustments from Inc to Cash116380001531300017072000172560001583600061830001300300013488000995500010936000 Change in Working Capital-649000-1392000-3113000-4932000-4720000627000-10120003845000-11590001432000 Other Operating Cash Flows-771000-214000-166000-82300035000016790001148000-318000-16000490000 Cash Flow from Operations30066000220820003034400045116000449140005617000055345000484130002843800059725000 Purchase of Pty Plant & Equip-15402000-16163000-26490000-32952000-33669000-34271000-30975000-26871000-22491000-19318000 Purchase of Investments-5507000-1417000-607000-1631000-4435000-972000-5340000-1254000-2768000-4608000 Disposal of Investments------16740001550005710001868000 Other Investing Cash Flows51790005177000327300076080003903000964200012476000376600022690006559000 Cash Flow from Investing-15730000-12403000-23824000-26975000-34201000-25601000-22165000-24204000-22419000-15499000 Change in ST Debt-1108000-77730001253000130400011272000-37560001521000-1129000-310000-414000 Change in LT Debt600001206600080020005662000332000848000436000-5081000157000-113000 Change in Equity-747000-971000-4032000-13038000-15900000-20745000-20871000-11928000-18714000-34648000 Payment of Dividends-13185000-12615000-12260000-11816000-11179000-10419000-9326000-8779000-8303000-8433000 Other Financing Cash Flows-150000000-1000204000-16000-7000-113000-419000 Cash Flow from Financing-15130000-9293000-7037000-17888000-15476000-33868000-28256000-26924000-27283000-44027000 Effect of Exchange Rate314000-434000-394000-281000-175000217000-85000-153000520000-2743000 Change in Cash-480000-48000-911000-28000-4938000-30820004839000-2868000-20744000-2544000 Opening Cash36570003705000461600046440009582000126640007825000106930003143700033981000 Closing Cash3177000365700037050004616000464400095820001266400078250001069300031437000 Depn & Amortn (CF)19893000223080001804800017297000171820001588800015583000147600001191700012379000 Net Purch of Pty Plant & Equip-15402000-16163000-26490000-32952000-33669000-34271000-30975000-26871000-22491000-19318000 Standardized Annual CS Balance Sheet Report Date12/31/201712/31/201612/31/201512/31/201412/31/201312/31/201212/31/201112/31/201012/31/200912/31/2008 CurrencyUSDUSDUSDUSDUSDUSDUSDUSDUSDUSD Audit StatusNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot Qualified ConsolidatedYesYesYesYesYesYesYesYesYesYes ScaleThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousands Cash & Equivalents %TA0.9111.1071.11.3331.4172.9733.9472.7944.58313.785 Short Term Investments %TA-------0.0010.0720.25 Cash & Equivs & ST Investments %TA0.9111.1071.11.3331.4172.9733.9472.7954.65514.035 Receivables (ST) %TA7.3416.4775.9028.0149.55910.48211.67210.67211.84810.832 Inventories %TA4.8734.5654.8244.7724.6524.3574.5384.2894.9525.107 Other Current Assets %TA0.3920.3890.8311.021.4731.51.8821.7422.2181.715 Total Current Assets %TA13.51712.53812.65715.13917.10119.31122.0419.49823.67331.688 Gross Property Plant & Equip %TA136.85137.419132.836127.839125.29122.624119.013123.612131.108118.765 Accumulated Depreciation %TA64.39963.48258.12255.54455.03554.63454.1757.64871.48565.555 Net Property Plant & Equip %TA72.45173.93774.71472.29670.25567.99164.84365.96459.62453.21 LT Investments %TA8.6177.7687.4647.2747.4547.4177.4298.6129.8569.201 Other Assets %TA5.4145.7575.1655.2925.195.2815.6895.9266.8475.9 Total Assets as 100%100100100100100100100100100100 Accounts Payable & Accrued Exps %TA8.9898.0068.24710.19711.96613.36615.56114.73815.55614.51 Accounts Payable %TA8.0397.2037.3759.25610.84612.12813.40912.49713.01811.938 Accrued Expenses %TA0.950.8030.8720.9411.121.2372.1522.2412.5382.572 Current Debt %TA5.1424.1875.5714.9984.5581.0942.3290.9211.0611.052 Other Current Liabilities %TA0.8730.7920.8321.4132.2582.9233.8443.2443.5624.41 Total Current Liabilities %TA16.56814.42216.02818.49320.68119.21523.41220.70422.31321.53 LT Debt & Lease %TA6.9998.7595.9173.3341.9872.3752.8164.0423.0553.08 Pensions & OPEB %TA6.066.2616.7257.3835.9537.577.556.4027.699.09 Deferred LT Liabilities %TA7.71310.30610.93311.22511.68711.25511.06111.6199.9218.65 Minority Interests %TA1.9541.9691.7811.9071.8721.7371.9181.9312.0671.999 Other Liabilities %TA6.887.6277.8947.7577.6478.1586.6066.7617.5656.117 Total Liabilities %TA46.17449.34449.27850.09949.82750.3153.36251.4652.61150.465 Common Share Capital %TA4.2033.683.4483.0882.9062.8922.8733.0982.3592.33 Retained Earnings %TA118.885123.468122.475116.85111.714109.56699.96698.825118.693116.5 Accum Other Comprehens Income %TA-4.664-6.733-6.982-5.424-3.092-3.651.2591.6561.8870.503 Other Equity %TA2.7194.394.2081.7030.244-0.722-5.274-4.925-6.114-5.36 Total Equity %TA53.82650.65650.72249.90150.17349.6946.63848.5447.38949.535 Total Liabilities & Equity %TA100100100100100100100100100100 Standardized Annual CS Income Statement Report Date12/31/201712/31/201612/31/201512/31/201412/31/201312/31/201212/31/201112/31/201012/31/200912/31/2008 CurrencyUSDUSDUSDUSDUSDUSDUSDUSDUSDUSD Audit StatusNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot QualifiedNot Qualified ConsolidatedYesYesYesYesYesYesYesYesYesYes ScaleThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousandsThousands Affiliates Revenue %TR2.2022.1262.8433.2343.1783.1123.1432.7862.32.321 Other Revenue %TR97.79897.87497.15796.76696.82296.88896.85797.21497.797.679 Total Revenue (100% TR)100100100100100100100100100100 Direct Costs %TR66.43669.52370.01971.89771.93869.68369.9668.44668.18467.427 Gross Profit %TR33.56430.47729.98128.10328.06230.31730.0431.55431.81632.573 Selling General & Admin %TR16.80316.23614.41710.89610.52110.2511.29813.25615.95512.065 Depreciation & Amortization %TR8.1419.8676.7124.1993.9213.2943.2043.8523.8372.593 Other Operating Expense %TR0.7330.6490.5660.4050.4510.3820.4280.5590.6510.304 Total Indirect Operating Costs %TR25.67626.75221.69615.514.89213.92614.92917.66720.44314.962 Operating Income %TR7.8883.7258.28512.60313.1716.39115.11113.88711.37417.611 Interest Income %TR-0.246-0.2-0.116-0.069-0.002-0.068-0.051-0.068-0.176-0.141 Other Non-Operating Income %TR0000000000 Total Non-Operating Income %TR-0.246-0.2-0.116-0.069-0.002-0.068-0.051-0.068-0.176-0.141 Earnings Before Tax %TR7.6423.5258.16912.53313.16816.32315.0613.81911.19717.47 Taxation %TR-0.48-0.182.0144.3735.5366.4376.3835.6264.8687.653 Minority Interests %TR0.0560.2370.1490.2660.1980.5810.2360.2450.1220.345 Extraordinary Items %TR0000000000 Accounting Changes %TR0000000000 Net Income %TR8.0663.4686.0067.8947.4349.3068.4417