1. DCF and Equity Compensation: (a) Calculate the basic share count by applying a premlum to the voting share class fuse the box provided in cell B56). The company has 5,285,715 Class A shares and 211,394,164 Class B shares. (Class B is inferior voting class share) ( 1.5 points) (b) Under the "Dluted Shares" header in row 53 complete the Treasury with a Twist box. Assume the following: Basic share count from part (a), 1,300,000 exercisable options with an average strike price of $13.63,1,600,000 options outstanding with an average strike price of $15.71. Assume a current stock price of $34.88. The company has 1,600,000 RSUs unvested. Use exercisable options as labeled. (Hint Values in spreadsheet are in thousands) (3.5 points) (c) Under the "Diluted Shares" header in row 56 complete the Treasury Method box. Use the same assumptions as above. Use options outstanding as labeled. (1.5 points) (d) Calculate the Implied Equity Value per share in cell 149 using the Treasury with a Twist method . (1 point) (e) Calculate the Implied Equity Value per share in cell N49 using the Treasury Method. You can do all calculations in this cell. ( 2 points) (f) Using the Option Drag box (starting in cell P36) Calculate the Implied Value of Equity under the option value method if the value per option using the Black Scholes formula is \$24.33. (assume all options vest and use Equlty Value from Exit multiple) (2.5 points) 2. Sensitivity analysis (to be completed below "Sensitivity Analysis" Header in row 137) (a) Use Excel to calculate the implied equity value (under Perpetuity growth terminal value) for the default spreads listed. You may need to ungroup rows to complete. (1 point) (b) Complete the WACCIGrowh rate table. The WACC should start at 4.00% and increase by the WACC step of 1% (place in cell 0143). The growth should start at 0.5% and increase at 0.5% (The growth Step should be in cell O142). Use Excel to calculate the implied equity value (under Perpetuity growth terminal value) for all the different WACC and growth combinations. (2 points) (c) Use Excel to calculate the Implied Equity Value (under Perpetuty growth terminal value) if the Sales Growh rate in 2020 is 3\%, the COGS (as a \% of revenue) is 58% and Marketing, general \& administrative expenses (as \% of revenues) was 24%. Lastly, assume a terminal growth rate of 1.5%. Label this outcome Best Case. (You can leave the resulte in a new sheet and do not need to change the output in anyway). (2 points) 3. Complete Section 2. FOR FULL CREDIT YOU MUST FORMAT ALL CELLS WTH APPROPRIATE DECIMALS, NUMBER FORMAT, COLOR CODING, ETC. YOU MUST ALSO USE EFFCIENT FUNCTONS/FORMULAS THROUGHOUT, ONLY NEED TO FORMAT "TAP_DCF" Shoot. (2 points) 1. DCF and Equity Compensation: (a) Calculate the basic share count by applying a premlum to the voting share class fuse the box provided in cell B56). The company has 5,285,715 Class A shares and 211,394,164 Class B shares. (Class B is inferior voting class share) ( 1.5 points) (b) Under the "Dluted Shares" header in row 53 complete the Treasury with a Twist box. Assume the following: Basic share count from part (a), 1,300,000 exercisable options with an average strike price of $13.63,1,600,000 options outstanding with an average strike price of $15.71. Assume a current stock price of $34.88. The company has 1,600,000 RSUs unvested. Use exercisable options as labeled. (Hint Values in spreadsheet are in thousands) (3.5 points) (c) Under the "Diluted Shares" header in row 56 complete the Treasury Method box. Use the same assumptions as above. Use options outstanding as labeled. (1.5 points) (d) Calculate the Implied Equity Value per share in cell 149 using the Treasury with a Twist method . (1 point) (e) Calculate the Implied Equity Value per share in cell N49 using the Treasury Method. You can do all calculations in this cell. ( 2 points) (f) Using the Option Drag box (starting in cell P36) Calculate the Implied Value of Equity under the option value method if the value per option using the Black Scholes formula is \$24.33. (assume all options vest and use Equlty Value from Exit multiple) (2.5 points) 2. Sensitivity analysis (to be completed below "Sensitivity Analysis" Header in row 137) (a) Use Excel to calculate the implied equity value (under Perpetuity growth terminal value) for the default spreads listed. You may need to ungroup rows to complete. (1 point) (b) Complete the WACCIGrowh rate table. The WACC should start at 4.00% and increase by the WACC step of 1% (place in cell 0143). The growth should start at 0.5% and increase at 0.5% (The growth Step should be in cell O142). Use Excel to calculate the implied equity value (under Perpetuity growth terminal value) for all the different WACC and growth combinations. (2 points) (c) Use Excel to calculate the Implied Equity Value (under Perpetuty growth terminal value) if the Sales Growh rate in 2020 is 3\%, the COGS (as a \% of revenue) is 58% and Marketing, general \& administrative expenses (as \% of revenues) was 24%. Lastly, assume a terminal growth rate of 1.5%. Label this outcome Best Case. (You can leave the resulte in a new sheet and do not need to change the output in anyway). (2 points) 3. Complete Section 2. FOR FULL CREDIT YOU MUST FORMAT ALL CELLS WTH APPROPRIATE DECIMALS, NUMBER FORMAT, COLOR CODING, ETC. YOU MUST ALSO USE EFFCIENT FUNCTONS/FORMULAS THROUGHOUT, ONLY NEED TO FORMAT "TAP_DCF" Shoot. (2 points)