answer this workbook please : https://ccnymailcuny-my.sharepoint.com/:x:/g/personal/malam008_citymail_cuny_edu/EQNuFz9ESMBBi1zuzV0w9uYB2I2aaFXcclMlPPc6JndcLQ?e=12fYP0

18 19 Horizontal Analysis 0 Horizontal analysis evaluates metrics across time. It is also referred to as 'Trend Analysis'. It enables evaluation of a dollar figure over a horizon of several years and always examines results relative to a base figure. Percent Growth Rate or Period-to-Period Growth Rate The Percent Growth Rate or Period-to-Period Growth Rate is the change in a metric over two consecutive periods. There are two ways to calculate percent growth, both generating the same result. (1) growth = value current period / value previous period -1 2) change in value / value previous period = (value current period - value previous period) / value previous period The period being calculated is always specified although the format may vary: month-to-month growth, Q/Q growth or year/year growth. In the yellow cell immediately below enter a formula that calculates the revenue growth if Revenues increase from $100 last year to $103 this year. Compound Annual Growth Rate (CAGR) The 'Compound Annual Growth Rate' (CAGR) is the average growth rate over a number 'n' of consecutive periods. The concept is that the growth rate will 'compound', multiplying itself over time and accumulating like interest. CAGR = (value current period / value n periods ago) ^ (1)-1 The 'N' is Excel's way of expressing exponents and means 'raised to the power' If during five years Revenues increase from 100 to 110 then '=(110/100)^(1/5) - 1' in the yellow cell immidiately below calculates a CAGR of 1.9%. 1.9% In the yellow cell immediately below enter a formula that calculates the CAGR if Revenues increase from 100 to 200 over 10 years. A single period (n=1) in the CAGR formula makes it equivalent to the Percent Growth Rate formula above. EXHIBIT: THE IMPORTANCE OF CAGRS IN FINANCIAL ANALYSIS Financial Analysts use CAGRs to describe the average growth rate of a company historically. - Often, financial analysts characterize companies by whether they grow in the low single digits, high single digits, double digits or 'high growth'; all of these refer to 5+ year CAGRs. CAGRs are very commonly used on Wall Street by analysts and investors to describe companies. Average Annual Growth Rate (AAGR) The 'Average Annual Growth Rate' (AAGR) is a simple average of a series of growth rates. The AAGR is a linear average that does not include the effect of compounding, in direct contrast with the CAGR Before you can calculate the AAGR, you must have a series of Percent Growth Rates for a series of periods. Immediately below, enter the formula to calculate the Percent Growth Rate for each highlighted cell. 2014 2015 2016 2017 2018 2019 Revenues ($MM) 100 95 99 120 115 135 Year-to-Year Growth Rate To calculate the AAGR calculate the linear average of the Percent Growth Rate over the 5 periods. Remember, an average of a sequence of numbers is the sum of the values divided by the number of values in the sequence. The formula '=(857+F57+G57+H57+157)/5' accomplishes this. 0.0% Mathematically this can also be written as '(PGR1 + PGR2 + PGR3 +....+ PGRn) / n' where PGR is Percent Growth Rate and n is equal to the number of PGRs. The AVERAGE function we introduce in the "EXCEL Basic Functions' Sheet simplifies this. For fun, let's see what the 5-year CAGR would be for the same data. 67 68 The financial world generally uses CAGR, but the critical factor for a financial analyst is to understand and document which growth rate is being used. ENTER Release Code A INTRO Income Statement A ACCOUNTS Income Statement ACCOUNTS Non-GAAP FSA Income StatementFor fun, let's see what the 5-year CAGR would be for the same data. The financial world generally uses CAGR, but the critical factor for a financial analyst is to understand and document which growth rate is being used The CAGR includes compounding to incorporate the time value of money. Notice that in the calculation of CAGR we are using 5 periods of growth (although this is defined by 6 years of Revenues in the calculation. Many students incorrectly use the number of data values (years in this case) rather than the number of periods between data values (always one less) when calculating CAGRs. Vertical Analysis Vertical analysis 'normalizes' a value relative to a basic measure of size and is also called 'Common Size Analysis'. The basic measure on the Income Statement is almost always Revenues. In contrast, Vertical Analysis of the Balance Sheet may restate each items as a percentage of Assets, Capital or even Revenues. Vertical analysis permits the analysis of the relative proportions of accounts in a single accounting period, in addition to facilitating comparisons across companies and time periods, detailed in Comparable Analysis. 80 The data in the following EXHIBIT will be used throughout the remainder of the Sheet to help you explore vertical analysis. 81 82 83 EXHIBIT: Company Data For Income Statement Analysis e Bay Exxon Ingersoll Rand J&J PepsiCo UPS 84 Revenues 10,746 279,332 15,668 81,581 64,661 71,861 85 Cost of Revenues 2,382 244,262 10,84 27,091 29,381 58,01 R&D 1,285 1,116 229 10,77 680 SG&A 4,522 10,36 2,903 22,540 24,490 Operating Expenses 6,142 14,231 2,903 34,441 25,170 6,827 Non-Operating Expenses (496) 10,114) 257 2,050 92 1,005 Taxes 190 9,532 281 2,702 (3,370) 1,228 Net Income Attributable to NCI Preferred Dividends Common Dividends Declared 13,798 485 9,493 5,076 3,189 95 Source: Company 10Ks Income Statement Before performing vertical analysis it is important to have a complete Income Statement for each company you are analyzing. As a first step, find the value of each company's Income Statement figures using the following formulas. Gross Profits = Revenues - Cost of Revenues Earnings Before Interest and Taxes (EBIT) = Gross Profit - Operating Expenses Pretax Income = EBIT - Non-Operating Income (Expense Net Income = Pretax Income - Taxes Net Income Attributable to Company = Net Income - Net Income Attributable to Noncontrolling Interests Net Income Attributable to Common = Net Income Attributable to Company - Preferred Dividends Hint: You should be able to write a formula for the first company and copy it across using 'S' as taught in the 'EXCEL Formulas' Sheet in Financial Statement Analysis (Workbook 3) eBay Exxon Ingersoll Rand J& Pepsico UP Gross Profit Earnings Before Interest and Taxes (EBIT) Pretax Income Net Income Net Income Attributable to Company Net Income Attributable to Common 116 1 18 Margins 1 19 Margins are the ratio of various income (profit) measures relative to Revenues. Fill in the EXHIBIT below using the following definitions for margins. A ENTER Release Code INTRO Income Statement ACCOUNTS Income Statement ACCOUNTS Non-GAAP FSA IncoMargins are the ratio of various income (profit) measures relative to Revenues. Fill in the EXHIBIT below using the following definitions for margins. Gross Margin = Gross Profit / Revenues EBIT Margin or Operating Margin = EBIT / Revenues = Operating Income / Revenues Pretax Margin = Pretax Income / Revenues Net Margin = Net Income / Revenues Net Income Attributable to Common Margin = Net Income Attributable to Common / Revenues eBay Exxon Ingersoll Rand J&J PepsiCo UPS Gross Margin EBIT Margin Pretax Margin Net Margin Net Income Attributable to Common Margin What is the company with the strongest Net Margin? Expense Ratios Expense ratios may look at the ratio of various expense measures relative to Revenues. R&D Percent = R&D / Revenues SG&A Percent = SG&A / Revenues Operating Expense Percent = Operating Expenses / Revenues eBay Exxon Ingersoll Rand J& PepsiCo UPS R&D Percent SG&A Percent Operating Expense Percent select the company spending the most to create or improve its products and services. BONUS: Why does UPS have no SG&A? Other expense ratios look at the value of various expense measures relative to the specific measures that drive them. Tax Rate = Taxes / Pretax Income eBay Exxon Ingersoll Rand J&J PepsiCo UPS Tax Rate Select the company with the highest tax rate. Other Ratios Distribution ratios look at capital distributed relative to what is available for distribution. ividend Payout Ratio = Common Dividends Declared / Net Income Attributable to Common Dividend Retention Ratio = 1 - Dividend Payout Ratio eBay Exxon Ingersoll Rand J& PepsiCo UPS 168 Dividend Payout Ratio ENTER Release Code INTRO Income Statement ACCOUNTS Income Statement ACCOUNTS Non-GAAP FS167 eBay Exxon Ingersoll Rand J&J Pepsico UPS 168 Dividend Payout Ratio Dividend Retention Ratio Select the company that does not pay a dividend 76 Below is Required Homework 78 General Income Statement Questions 79 Use the pull down menu in the yellow cell immediately below to select the value which is likely to be the largest. Click on the cell and an arrow will appear on the right of the box. Click on the arrow to view the options and select the correct option. 184 Which of the below are non-cash expenses? 185 86 187 Use the pull down menu in the yellow cell immediately below to select when revenues and matching expenses will be recognized? 188 189 190 Which accounts may include depreciation? 191 192 Cleaning Service You are running a cleaning service. During the winter you also provide snow removal services. Write the Excel formula in the yellow cells below that calculate the appropriate values. Receive $150,000 from customers for cleaning services provided $ 150,000 Receive $50,000 from customers for snow removal. 50,000 Pay $80,000 to workers providing the cleaning services. 80,00 Pay $20,000 cash for supplies 20,00 Pay $5,000 in interest on a loan 5,000 Receive $2000 interest on cash in the bank 2,000 Pay $5,000 cash on a advertisement in the local newspaper Us 5,00 Pay for your office phone bill of $2,000 2,00 Pay for a PC at the beginning of the year for $3,600 (straight line depreciate - 3 years) 3,600 Pay $2,000 for accounting services 2,000 How much are: Revenues Cost of Revenues SG&A (excluding Depreciation) Depreciation 214 Interest Expense (Income) ENTER Release Code Q INTRO Income Statement ACCOUNTS Income Statement ACCOUNTS Non-GAAP Q FSA Income StatementAmerican 253 Airlines Midland Boeing CVS Electronic Arts Ralph Lauren 254 Gross Margin R&D Percent 256 SGA& Percent EBIT (Operating) Margin 258 Tax Rate 259 Net Margin 260 Payout Ratio 261 262 Below is Challenge Homework 263 264 Revenue Growth and Company Life Cycle 265 We have selected five companies and 'indexed' their Revenues so that they are the same value at the starting year 1996. 266 Here we set all five companies Revenues for 1996 to $100. (FYI: To create an Index, divide every year's Revenues by 1996 Revenues and multiply that number by 100). Financial analysts index time-series to a common starting point to compare the trends over time. 268 The graph on the left is the Revenues of these five companies over the past 25 years. 269 The graph on the right is the Revenues of all of these companies excluding Amazon over the past 25 years. Revenues 1996 = 100 Revenues 1996 = 100 1,600,000 400 1,400,000 350 1,200,000 300 1,000,000 250 800,000 200 600,000 150 400,000 100 200,000 50 865T 1999 2000 2001 2002 2004 2005 2006 2007 2008 2010 2 2012 2013 2014 2015 2016 2017 2018 270 AMZN - GE For each of the following descriptions, select the related company: Description Name Hyper-growth Growt Decline Mature cyclical Mature non-cyclical 280 281 Net Margin and EPS Calculations According to its website, IHS Markit is a company that 'combines information, analytics and expertise to provide solutions for business, finance and government'. Below are selected financial results for the period 2015 to 2018. In 2017 IHS merged with Markit resulting in a significant increase in revenues and expenses but also in Shares Outstanding. In the yellow cells in column I, calculate the three-year compound annual growth rate (CAGR) from 2015 to 2018 for each row. 286 In Row 294 calculate the EPS for each year. 87 In Row 295 calculate the Net Margin Attributable to Common for each year. 288 Based on these results answer the questions below. 289 290 2015 2016 2017 2018 CAGR 291 Revenues 1,113 2,735 3,600 1,009 292 t Income Attributable to Commo 153279 280 281 Net Margin and EPS Calculations 282 According to its website, IHS Markit is a company that 'combines information, analytics and expertise to provide solutions for business, finance and government'. 283 Below are selected financial results for the period 2015 to 2018 284 In 2017 IHS merged with Markit resulting in a significant increase in revenues and expenses but also in Shares Outstanding. 285 In the yellow cells in column I, calculate the three-year compound annual growth rate (CAGR) from 2015 to 2018 for each row. 286 In Row 294 calculate the EPS for each year. 287 In Row 295 calculate the Net Margin Attributable to Common for each year. 288 Based on these results answer the questions below. 289 290 2015 2016 2017 2018 CAGR 291 Revenues 1,113 2,735 3,600 4,009 292 Net Income Attributable to Common 153 153 417 542 293 Shares Outstanding 190 316 416 407 294 Earnings per Share (EPS) 295 Net Margin Attributable to Common 296 297 Between 2015 and 2018 did the company's Net Margin increase, decrease or remain flat? 298 299 Between 2015 and 2018, why was company's EPS growth was so much lower than its revenue growth? 300 301 Relative to 2016 (before the merger) was the merger accretive (increased EPS), dilutive (decreased EPS), or neutral. 302 303 304 805 306 307 308 309 31042 The company is selected in the yellow cell B2 of the 'Income Statement (GAAP)' Sheet using the company's 'ticker'. 43 Professionals in the finance community often refer to companies by their tickers. 44 A ticker is the company's stock symbol that is traded on exchanges such as NYSE or NASDAQ. Examples include AAPL for Apple or CMG for Chipotle. 45 If you know the name and not the ticker you can convert it by using the 'Ticker Lookup' Sheet. 46 Changing the ticker on the 'Income Statement (GAAP)' Sheet will change the ticker throughout SFAM's multiple financial statement and analysis Sheets. 47 Look up the Ticker for Halliburton, where Vice President Dick Cheney was formerly CEO 49 Enter it into the 'Income Statement (GAAP)' Sheet to pull up Halliburton's Income Statement 50 Answer each of the following questions. The box will turn red if the answer is wrong. 51 What was Halliburton's 2015 Revenues? 52 53 What was Halliburton's the 2019 EBITDA Margin? 54 55 What was Halliburton's 2016 Non-GAAP Net Income Adjustment? 56 57 58 We Incorporate Non-GAAP Measures 59 Companies may use both GAAP and non-GAAP financial measures when reporting financial results. 60 SFAM provides both a GAAP and a Non-GAAP Income Statement: 61 62 EXHIBIT: USE OF NON-GAAP FIGURES 63 Sheet Explanation 64 Income Statement (GAAP) Income Statement exactly conforms to GAAP (with the exception that SFAM incorporates EBITDA) 65 Income Statement (Non-GAAP) Non-GAAP Income Statement as reported by companies with reconciliation to GAAP. 66 Source: Marianne Wolk & Richard Horwitz 68 As detailed in the prior Sheet 'ACCOUNTS non-GAAP', there is an ongoing controversy and evolving set of regulations regarding the use of non-GAAP financials: 69 the positive value of removing unusual or nonrecurring items 70 the negative potential for these deletions to mislead or manipulate results or inflate executive compensation 71 We believe a good financial analyst must look at both GAAP and non-GAAP presentations. 72 Thus, we consider GAAP and non-GAAP financials as equally valuable inputs to forecasting future performance. 73 The analyst must go through the details of what the adjustments to GAAP are and make an informed decision about which represents the recurring position of the company are best. 74 Unfortunately, this means there is no blanket solution and that everything must be decision on a case by case business. 75 SFAM includes a GAAP Income Statement and a non-GAAP Income Statement. 76 The non-GAAP Income Statement is a separate and complete Income Statement for the company.224 Below is Required Homework 225 226 Above we describe financial analysts as financial detectives. 227 Below, you must examine the financials of the companies listed and, sleuth-like, identify clues to solving this exercise. 228 Use the SFAM 'Income Statement (GAAP) Sheet to select each company listed and examine it's revenue and profitability patterns over the past 10 years. 229 Match each company to the financial profile that best describes the company. 230 Hint: The profile definitions are in Row 117. 231 Hint: Start with the growth trend displayed in the revenues over the past 10 years. 232 233 Financial 234 Company Ticker Profile 235 Edwards Lifesciences EW 236 Chipotle CMG 237 Eaton ETN 238 Facebook FB 239 American Water Works AWK 240 Charter Communications CHAR 241 IBM IBM 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 INTRO Income Statement ACCOUNTS Income Statement ACCOUNTS Non-GAAP A FSA Income Statement ASFAM Income Statement11 Basic functions are those which require one or more parameters or input fields but there is no specific context related to the sequence of entries. 12 Inputs to basic functions can be values, single cells, formulas, ranges of cells, or a list of these separated by commas. 13 The sequence of these can be reorganized and the result of a basic function will not change. 14 15 SUM Function 16 In addition to formulas, Excel supports a large number of functions. 17 We will start off by discussing the SUM function, which adds a series of values, cells, ranges, formulas, or even other functions. 18 For example, in B19 instead of writing the formula '=1+2+3' we could write the formula '=SUM(1,2,3)'. 19 20 #N/A 21 15 22 23 Functions are always embedded in a formula except when they are inputs to other functions. 24 Enter into the yellow cell immediately below '=SUM(' and you will see a window pop up. Click on SUM in the window. 25 3 5 Now enter the addresses of (or click on) the two cells in the grey rectangle separated by commas as inputs to the SUM function. 27 Excel provides a variety of ways for you to enter a function in addition to writing the expression. 28 For the SUM function, a quick way is to select the & on the 'Formula Bar' Q~ Search Workbook + Share Insert E Ex Delete AP. itional Format Cell Sort & atting as Table Styles Format Filter 29 30 If it is not on your formula bar it can be found in 'editing' group of the Home tab and 'function library' of Formulas tab 31 After clicking '2', you can directly enter the list of values, cells, ranges, formula, etc. that you choose to add. 32 Additionally, you can use the 'Formula Builder' to enter a function. 33 You get to the Formula Builder by selecting the fx on the 'Formula Bar'. Home Insert Page Layout Formulas Data Review View Calibri (Body) * 10 . A- A = Paste B U + + . A . HE B17 X +3 ACCOUNTS Income Statement ACCOUNTS Non-GAAP FSA Income Statement SFAM Income Statement EXCEL Basic FunctionsWhile you may not find the formula builder necessary for the SUM function, you will likely find it very useful for more complex functions. Addressing a Range While Using Functions When writing a function, besides addressing a single cell, you can address a 'range' of cells. For example, you can add the values in the range of the four cells in grey by entering '=SUM (G46:H47)' as is in B46. 17 5 2 - w Excel takes all the cells in the 'range' defined by G46 in the upper left hand corner and H47 in the lower right hand corner. Write a formula that sets the yellow cell immediately below equal to the sum of the six cells in the grey range to the right. 5 IN Rather than entering a range address in a formula, a quick way of referencing a range is to select it with the cursor. A range can be selected using the 'Shift' key as follows. In the yellow cell immediately below Select the & in the Formula Bar Select the range by clicking on G56, holding the 'Shift' key down, moving the cursor to 157, and releasing the 'Shift' key. 5 3 12 Alternatively, a range can be selected without even using the 'Shift' key as follows. In the yellow cell immediately below Select the & in the Formula Bar Select the range by clicking on G61, holding the cursor down, and moving it to 163 and then releasing the cursor. 5 0 12 3 Finally, when ranges are large (you will work with ranges of 20,000+ rows later in the course) selecting the range with the cursor can be mechanically difficult. If you click on the upper leftmost cell of a range you can select a range by depressing the shift key and 56 Extend the range down: On a Mac select command+shift+down key On a Windows machine select cntri+shift+down key Extend the range right: On a Mac select command+shift+right key On a Windows machine select cntri+shift+right key Hint : This will only work if there is no column or row gap in the dataset. Other Basic Excel Functions In addition to the SUM Function, Excel supports a variety of other basic functions. ACCOUNTS Income Statement ACCOUNTS Non-GAAP A FSA Income Statement SFAM Income Statement EXCEL Basic Functions A DISRUPTIVE DIG5 Below is Required Homework 06 Basic Functions 08 The AVERAGE function averages the cells that have numeric values. 09 Write a formula in the yellow cell immediately below that sets the value of cell equal to the average of the eight grey cells in the range F110:1111. 10 11 4 5 2 9 text The MAX function selects the maximum value of the cells that have numeric values. Write a formula in the yellow cell immediately below that sets the value of cell equal to the maximum of the eight grey cells in the range F110:1111. The MEDIAN function is the middle value of cells with numeric values. Write a formula in the yellow cell immediately below that sets the value of cell equal to the median of the eight grey cells in the range F110:1111. 18 The MODE function is the most frequent value of the cells that have numeric values. Write a formula in the yellow cell immediately below that sets the value of cell equal to the mode of the eight grey cells in the range F110:1111. The PRODUCT function multiplies the cells that have numeric values. Write a formula in the yellow cell immediately below that sets the value of cell equal to the product of the eight grey cells in the range F110:1111. The COUNT function counts the number of cells that have numeric values. Write a formula in the yellow cell immediately below that sets the value of cell equal to the number of cells with numeric values of the eight grey cells in the range F110:111. 26 The COUNTA function counts the number of cells that are not blank. 28 Write a formula in the yellow cell immediately below that sets the value of cell equal to the number of cells that are not blank of the eight grey cells in the range F110:1111. The COUNTBLANK function counts the number of cells that are blank. .Write a formula in the yellow cell immediately below that sets the value of cell equal to the number of cells that are blank of the eight grey cells in the range F110:111. Functions can be embedded in other functions. Write a formula in the yellow cell immediately below that sets the value of cell equal to the sum of the minimum and the maximum of the cells shaded in grey below. The minimum of the three values shaded in grey is 3. The maximum of the three values shaded in grey is 8. The sum of 3 and 8 is 11. Why don't you try writing a formula with functions embedded in functions. In the yellow cell immediately below write a function using the MIN, SUM, and PRODUCT functions to calculate the minimum of (G142 plus H142) and (G142 times H142). 42 2 43 144 145 146 147 148 49 150 A ACCOUNTS Income Statement ACCOUNTS Non-GAAP A FSA Income Statement SFAM Income Statement EXCEL Basic FunctionsSource field: Net Margin GAAP Field name: Sum of Net Margin GAAP Summarize by Show data as Sum Count Average Max Min Product Count Numbers StdDev Number... Cancel OK 14 Select the 'Number..' box and the 'Format Cells' window that will appear. 15 Select 'Percentage' with zero decimals in the 'Format Cells' window. 16 Click on 'OK' in the bottom right comer of the 'Format Cells' window. 17 Click on 'OK' in the bottom right comer of the 'PivotTable Field' window that will have appeared. Use the results of the PivotTable to answer the following question. Enter the value (not a formula) of the average Net Margin GAAP in 2018? We have already pre-calculated 'Net Margin Non-GAAP' following the same instructions as for "Net Margin GAAP'. Try to answer the below question without using the directions that follow. Create a PivotTable with 'Year' as the 'Rows' and 'Net Margin GAAP' and 'Net Margin Non-GAAP' as the 'Values'. Enter the value (not a formula) of the Net Margin Non-GAAP in 2018 Use PivotChart to graph both Net Margin GAAP and Net Margin Non-GAAP over time 1 Click into the PivotTable and a 'PivotTable Analyze' Tab will appear above 2 Click on 'PivotChart' and a chart window will appear. 3 Click in the chart window and in the 'Design' tab will appear. Click on it. 4 Select 'Change Chart Type'. 5 Select 'Line' and then click on the top left image 2-D Line W 3-D Line The resulting graph should look the same as the graph on the 'Disruptive Digitization ' Sheet (from 2009 onwards). Analyze and Graph the Net Margin GAAP over time for NewCos and OldCos Create a PivotTable with 'Year' as the rows, and 'OldCos-NewCos' as Columns and 'GAAP Net Margin' as Values. 1 If 'PivotTable Fields' window is not open select 'Field List' on the 'PivotTable Analyze' tab. 2 Remove 'Sum of "Net Margin Non-GAAP ' from the 'Values' box. 3 In the 'PivotTable Fields' window drag 'OldCos-NewCos' to the 'Columns' box. If you have left the chart window open the graph will be there, otherwise select 'PivotChart' on the 'PivotTable Analyze' tab A FSA Income Statement A SFAM Income Statement EXCEL Basic Functions A DISRUPTIVE DIGITIZATION DATA ANALYSIS Income Statement3-D Line The resulting graph should look the same as the graph on the 'Disruptive Digitization ' Sheet (from 2009 onwards). Analyze and Graph the Net Margin GAAP over time for NewCos and OldCos Create a PivotTable with 'Year' as the rows, and 'OldCos-NewCos' as Columns and 'GAAP Net Margin' as Values. 1 if 'PivotTable Fields' window is not open select 'Field List' on the 'PivotTable Analyze' tab 2 Remove 'Sum of "Net Margin Non-GAAP ' from the 'Values' box. 3 In the 'PivotTable Fields' window drag 'OldCos-NewCos' to the 'Columns' box. 4 If you have left the chart window open the graph will be there, otherwise select 'PivotChart' on the 'PivotTable Analyze' tab. Enter the value (not a formula) of the 'Sum of Net Margin' for NewCos in 2018? Graph the R&D Percent over time starting in 2010 for NewCos and OldCos We have already pre-calculated 'R&D Pct' dividing 'R&D' by 'Revenues'. 1 If 'PivotTable Fields' window is not open select 'Field List' on the 'PivotTable Analyze' tab. 2 Remove 'Sum of Net Margin GAAP' from the 'Values' box and drag 'R&D Pct' to the 'Values' box. 3 Format 'R&D Pct' as 'Percentage'. 4 To select starting in 2010, drag 'Year' from the Rows box to the 'Filters' box. 5 Select the 'Year' filter in Row 9 of the 'DATA Income Statement' Sheet to unselect 2009. 6 Return 'Year' from the 'Filters' box to the 'Rows' box. 7If you have left the chart window open the graph will be there, otherwise select 'PivotChart' on the 'PivotTable Analyze' tab. Select 'Change Chart Type'. 9 Select 'Line' and then click on the top left image Enter the value (not a formula) of the 'Sum of R&D PCT' for OldCos in 2018? Enter the value (not a formula) of the 'Sum of R&D PCT' for NewCos in 2018? FSA Income Statement SFAM Income Statement EXCEL Basic Functions A DISRUPTIVE DIGITIZATION DATA ANALYSIS Income Statement