Hi, I need to do a Free Cash Flow (3 years) for the ADP company. And do ratio analysis for 2 years. I have attached a spreadsheet, feel free to use Doc or excel, and please make necessary changes to the formulas in the sheet, or you can use a fresh spreadsheet. Thank you.
ADP ADP 2,0172,0182,019 NOPAT is EBIT * (1 - Tax Rate)NOPAT is EBIT * (1 - Tax Rate)NOPAT is EBIT * (1 - Tax Rate) EBIT (adjusted EBIT)*14,541EBIT (adjusted EBIT)*14,541EBIT (adjusted EBIT)*14,541 Tax Rate17.0%Tax RateERROR:#DIV/0!Tax RateERROR:#DIV/0!Tax RateTaxes Paid2,374 NOPAT12,071NOPATERROR:#DIV/0!NOPATERROR:#DIV/0!EBT13,976 Incremental Fixed Investments:Incremental Fixed Investments:Incremental Fixed Investments:Just for FUN - Learned a bit more depth from the MD&A section New PPE(16,861)New PPE(16,861)New PPE(16,861) New Acquisitions(2,461)New Acquisitions(2,461)New Acquisitions(2,461)AWSPrime Add Back Depreciation (non cash)21,789Add Back Depreciation (non cash)21,789Add Back Depreciation (non cash)21,789Revenue35,026245,496280,522 Net Incremental Fixed2,467 Net Incremental Fixed2,467 Net Incremental Fixed2,467Costs25,825240,156265,981 EBIT9,2015,34014,541 Operating Margin26.3%2.2%5.2% Incremental Working CapitalIncremental Working CapitalIncremental Working Capital Change in Inventory(3,278)Change in Inventory(3,278)Change in Inventory(3,278) Change in AR(7,681)Change in AR(7,681)Change in AR(7,681) Change in Prepaids- 0Change in Prepaids- 0Change in Prepaids- 0 Change in AP & Accruals6,810Change in AP & Accruals6,810Change in AP & Accruals6,810 Net Incremental WC(4,149) Net Incremental WC(4,149) Net Incremental WC(4,149) Free Cash Flow (FCF)10,389Free Cash Flow (FCF)ERROR:#DIV/0!Free Cash Flow (FCF)ERROR:#DIV/0! *Adjusted EBIT: When there are significant one-time Accounting non-cash charges we can adjust the EBIT to account for it. Essentially what would the EBIT be as if they didn't have these one time charges. What is the TRUE EBIT from operations. ADP 2 For the Company ADP, Complete a full set of ratio analysis using the standard set of ratios from the text. Not all will apply so use your judgement. For 2 years only 2018, 2017. Do some light analysis. Pick one or two metrics that have changed materially (significantly) and make an observation. This is a homework assignment so it's not deep analysis. Directional change (better/worse) is good. A.Profitability ratiosColumn120172018 ** = Recommended 1.Profit margin ** 2.Return on assets (investment) ** and ROIC ** 3.Return on equity B.Page 58 Asset utilization ratios 4.Receivable turnover 5.Average collection period 6.Inventory turnover ** If they are a company that heavily uses inventory 7.Fixed asset turnover** 8.Total asset turnover C.Liquidity ratios 9.Current ratio ** 10Quick ratio D.Debt utilization ratios 11Debt to total assets 12Times interest earned ** 13Fixed charge coverage **