answer please
Sales Volume Screwdrivers Drills Saws North 1 West East ON South 12 00 UT 13 We will use the MATCH and INDEX functions to determine how many 'Drills' were sold in the 'East' region. These two functions are often used together to determine the horizontal and vertical offsets (relative positions) of the targeted value and then these offsets are used in the INDEX function to select the value. Write a formula in the green cell immediately below that uses the MATCH function to select the relative position of 'East' in the range C172:C175. Use the MATCH function pop-up window to guide you through the creation of the statement. Since you are searching for an exact match to 'East' the third parameter in the function should be 'FALSE' Write a formula in the green cell immediately that uses the MATCH function to select the relative position of 'Drills' in the range D171:F171. Use the MATCH function pop-up window to guide you through the creation of the statement. Since you are searching for an exact match to 'Drills' the third parameter in the function should be 'FALSE'. INDEX Function The INDEX function returns a value or the reference to a value from within a table or range. Click on the function in the box immediately below to see the expression '=@INDEX($D172:$F175,B180,B184)'. #VALUE! The above expression will work only if you have previously entered the right expressions in B180 and B184. The INDEX function searches a range specified in the fist parameter. The INDEX function searches in the row in the range specified in the second parameter. The INDEX function searches in the column in the range specified in the third parameter. The result of the expression in cell B191 is 8 because the table above shows that the 'East' region sold 8 'Drills'. Write a formula in the green cell immediately below that uses the INDEX function to select the number of 'Screwdrivers' sold in the 'South' region. The INDEX function can also be used to select a row or column from a range. The selected row or column can then be used as input into function that operates on a list of values. Click on the function in the box immediately below to see the expression '=SUM(INDEX(D172:F175,4,0))'. 39 This expression will sum all the values in the fourth row of the range D172:F175. The zero in the third parameter tells Excel to use the entire row. Click on the function in the box immediately below to see the expression '=MAX(INDEX(D172:F175,0,2))'. 13 This expression will take the maximum value all the values in the second column of the range D172:F175. The zero in the second parameter tells Excel to use the entire column. Write a formula in the green cell immediately below that uses the INDEX function to add the cells in the 'East' row of the range D172:F175. OFFSET Function The OFFSET function returns a range that is a specified number of rows and columns from a reference cell or range. The range that the OFFSET function returns can be a single cell or a range of multiple adjacent cells. since the result of the OFFSET function is a range, OFFSET is often embedded in another function which operates over a range (e.g., SUM, AVERAGE, COUNT)Sales Volume Screwdrivers Drills Saws North West East South 12 Click on the function in the box immediately below to see the expression '=SUM(OFFSET(C218,2,1,2,3))'. 42 In contrast to the INDEX function (which selects a single cell, a row, or a column), the OFFSET function selects a range. The OFFSET function selects a range defined relative to the address in the first parameter (the 'reference cell') defined by the subsequent parameters. The first two parameters after the reference cell are the number of rows down relative to the refernce function and the number of columns to the right relative to the reference function. These paramaters define the address of the upper left hand cell of the range. The OFFSET function selects the range relative to the reference cell offset by the number of rows specified in the second parameter. The OFFSET function selects the range relative to the reference cell offset by the number of columns specified in the second parameter. The upper left cell of the range in the formula in B226 is D220 - - 2 rows down and 1 row to the right from C218. The next two parameters after the reference cell are the number of rows down relative to the refernce function and the number of columns to the right relative to the reference function. These paramaters are optional These paramaters define the address of the bottom right hand cell of the range. The OFFSET function selects a range height specified in the third parameter. The OFFSET function selects the range width specified in the fourth parameter. The lower right cell of the range in the formula in B226 is F221 - - 2 rows high and 3 row to the right from D173. In this case the range starts 2 columns below the reference cell and is 3 columns high. If this parameter is omitted the range defaults to 1 row high and 1 row wide. The result of the expression in cell B226 is 81 because it sums the cells in the range D220:F221 (grey shaded range). Each of the parameters in the OFFSET function can be either values or formulas. Write a formula in the green cell immediately below that uses the OFFSET function to COUNT the number of values in the range D172:F175 CHOOSE Function The CHOOSE function selects from a list of values starting in the second parameter based on the position in the list identified by the first parameter. Click on the function in the box immediately below to see the expression '=CHOOSE(5, 7,9,12,23,42,19)'. 42 In the above expression, the 5th position in the list starting with 7 is 42. Write a formula in the green cell immediately below that uses the CHOOSE function to select the third value in the sequence 2,4,6,8,10. Reference Functions in SFAM We have depended on using reference functions to implementing SFAM and developing Data Analysis data. Below is a simplified example of its use in SFAM. The table presents data on three accounts over four years of a fictitious company. Cost of Revenues Revenues Gross Profit 2016 100 60 40 2017 105 61 44 2018 110 67 48Cost of Revenues Revenues Gross Profit 2016 100 60 40 2017 105 61 44 2018 110 62 48 2019 116 64 52 Enter VLOOKUP functions in the green boxes below that pulls in the appropriate values from the table above. 2016 2017 2018 2019 Revenues Enter INDEX and MATCH functions in the green boxes below that pulls in the appropriate values from the table above. 2016 2017 2018 2019 Cost of Revenues Enter OFFSET and MATCH functions in the green boxes below that pulls in the appropriate values from the table above. 2016 2017 2018 2019 Gross ProfitIn 'FORECAST Financial Statements' in a table beginning in Row 36, we described several types of Investment Theses. Even the simplest forecast utilizes a basic investment thesis or viewpoint. Which Investment Thesis type best matches the CSFB analyst's outlook for Microsoft? A Create a New Market B Gain Share of an Existing Market C Disrupt a Market Which of these descriptions best summarizes the analyst's Investment Thesis? A Microsoft is capturing share of the high growth cloud services market with its Azure offering while maintaining decent growth in its legacy Office offerings thanks to online migration, which is likely to continue to drive strong B Microsoft is capturing share of the high growth cloud services market with its Azure offering but margin erosion related to the shift to cloud services offsets these gains to produce cash flows in line with expectations. C Microsoft is capturing share of the high growth cloud services market with its Azure offering but the 0365 product cycle is deteriorating and negative for Microsoft's outlook. Using the Investment Thesis to Craft a Financial Forecast As described in 'Developing a Forecast', financial analysts develop the financial forecast to reflect their Investment Thesis. Analysts cast a 'Judgmental Forecast' expressing a point of view communicated through an explicit quantitative model as described in Row 66 in 'Intro to Forecasting'. Below is the summary of the Credit Suisse financial forecast for Microsoft as presented alongside the Investment Thesis we showed you earlier. Microsoft (MSFT) Price (29 Jan 2020): US$168.04 Target Price: (from 180.00) 190.00 Income Statement 6/18A 6/19A 6/20E 6/21E Revenue (US$ m) 110.360.0 125.843.0 141.477.4 154,292.7 Sales 10.360.0 125.843.0 154,292.7 45.317.8 54.639.9 141.477.4 EBITDA 63.857.8 69.418.4Inputs Revenue Growth: 23% Gross Margins: 66% - OpEx Percent of Revenues *: 30% *HINTS: -There is no 'yellow' box in SFAM to insert the answer for OpEX. - We are reconstructing the environment you will encounter when working as a financial analyst. When financial analysts receive 'guidance' from companies, they are often given a generalization such as 'spending levels will be roughly the same' or'OpEx will be roughly 30%. - Like a working financial analyst, your job here is to adjust the R&D and SG&A figures until the forecast meets the guidance that OpEx is equal to 30%. - There is no right or wrong answer for the components of OpEx, but a working financial analyst would assure the individual line items make 'sense' What does that mean? If the prior two numbers were 10% and 8%, the next figure is unlikely to be 20% unless there is a major change occurring. eaving all other assumptions the same, what is your expectation for EBIT margins for 2020E? What is your EPS and where is it versus the consensus average for GAAP EPS of $5.66 for 2020E? Assignment 3: Bearish Forecast bearish forecast is one that is below the consensus average and shows a deterioration in trends. Microsoft recently issued a press release saying that it would not meet prior guidance for FQ3/March 2020. "On Jan. 29, as part of our second quarter of fiscal year 2020 earnings call, we issued quarterly revenue guidance for our More Personal Computing segment between $10.75 and $11. 15 billion, which included a wider than usual range to reflect uncertainty related to the public health situation in China. Although we see strong Windows demand in line with our expectations, the supply chain is returning to normal operations at a slower pace than anticipated at the time of our Q2 earnings call. As a result, for the third quarter of fiscal year 2020, we do not expect to meet our More Personal Computing segment guidance as Windows OEM and Surface are more negatively impacted than previously anticipated. All other components of our Q3 guidance remain unchanged." ou are 'bearish' and have great concerns that these negative trends will persist. What is the best choice of growth rates to demonstrate your negative outlook? You decide to stick your neck out and make a major call on Microsoft's stock. mplement a very negative forecast below expectations. Here, you expect Gross Margins to decline and OpEx to be lower, as the company cuts back in its spending. se the SFAM 'Income Statement(GAAP)' and insert these forecasts into the yellow forecast boxes in column AH for 2020E. Revenue Growth 10% Gross Margins 62%'ou decide to stick your neck out and make a major call on Microsoft's stock. mplement a very negative forecast below expectations. Here, you expect Gross Margins to decline and OpEx to be lower, as the company cuts back in its spending. Use the SFAM 'Income Statement(GAAP)' and insert these forecasts into the yellow forecast boxes in column AH for 2020E. Revenue Growth 10% Gross Margins 62% OpEx Percent of Revenues 33% Are your EBIT margins higher or lower than in the Bullish forecast above (Row 100)? What is your EPS forecast? Please choose the closest figure for Fully Diluted Earnings per Share in the 'Income Statement (GAAP)' Sheet. Assignment 4: Consensus Forecast Of 34 analysts publishing on Microsoft's shares, the following is the consensus estimate. Low High Mean 2020E Revenues $B 139.4 152.0 142.2 EPS 5.46 6.00 5.66 2021E Revenues $B 153.2 168.7 159.7 EPS 5.52 7.11 6.30 Source: CNN Money, 3/16/20 Use the SFAM IS Model to build a forecast for 2020E and 2021E that meet the consensus. Hints: First, adjust the Revenue growth rate in the SFAM model that sets the Revenues to match the consensus. (Remember the consensus above is in $Billions and the SFAM model is expressed in $Millions.) Second, alter the figures for R&D and SG&A until the EPS matches the consensus forecast Do not touch any of the other variables in the model. What is your EBIT forecast in $Bs for 2020E? (This means you should enter '10' for $10 billion.)What is your EBIT forecast in 33s for 2020E? [This means you should enter '10' for $10 billion.) What revenue growth rate would you expectto see for 2022E? _-_ Save your consensus forecast in the 'Income Statement lGAAP)' Sheet as we will use it in the next case
