^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Please make a spreadsheet out of the instructions above based on the case below, thank you for your help.
(Answer will be scanned, including within chegg answers, thumbs down for plagiarism)
________________________________________________________________________________________________________________________________
Case 4: Repeat questions 1,2 , and 3 , with the following information: CFROI 20%(GD)4%(BI) Acquisitions 10 (GD) 2 (CR) 9 (BI) 2 (HO) 5 (Other) Financial Strategy. No dividends, 60\% debt Capital Allocation, Diversification outside GD destroying shareholder value because of low CFROI. Share Repurchase. Policy of no share buybacks. Directions: See file g41201.14(17)(6)(3)(8)(1)(8)(5)(4)(4)b(1).xlsx from Module 2 (next to last file). Go to line 97, Find the new net income for Genetic Diseases on lines 99-105, for 2006, 2007, and 2008. Replace .25 with .2 in each cell. Find the new net income for BI on lines 99-105, for 2006, 2007, and 2008. Replace .25 with .1 with in each cell. Scroll down to the computation of CFROI. Compute a new CFROI for 2006-2008 for GD and BI. Example, 2006 for GD CFROI=.2999569/7191188 These 2 numbers are cash flow, and total assets from page 74 in the text. Complete the CFROI computation for 2007 , and 2008 for GD and for all 3 years for BI. Answer the 3 questions (in paragraphs) at the top of the excel file for Genzyme. \begin{tabular}{c} a0c \\ c \\ uc0 \\ \hline \end{tabular} 2.6% stake Genzyme Income Statement Net Revenue Total Revenue \begin{tabular}{|r|r|r|} \hline 2006 & 2007 & 2008 \\ \hline 2887409 & 3457778 & 4196907 \\ \hline 3187013 & 3813519 & 4505039 \\ \hline \end{tabular} Operating costs Research \& Development In-process R \& D Operating income (EBIT) Net income Changes in Accounts Growth Change in Revenue (Ending value-Beginning value)/Beginning Value \begin{tabular}{|l|c|c|c|} \hline 20062007 & \multicolumn{2}{|c|}{0.19658093619.6% increase in revenue } \\ \hline 200728 & 0.213758373 & 21.3% increase \\ \hline R D as a percentage of revenue =28% acceptable. & 0.203937355 & 0.193439445 & 0.2904148 \\ \hline Change in Operating Income & 20062007 & 4.4322000544% increase \\ \hline & 20072008 & 0.11070480911% decrease \\ \hline \end{tabular} possible in selling and administrative expenses. Profitability Profit margin Net income/Sales \begin{tabular}{|r|r|} \hline 2006 & 0.005270452 \\ \hline 2007 & 0.138873288 \\ \hline 2008 & 0.100331268 healthy \\ \hline \end{tabular} ROE Net income/Stockholder's equity RO Net income/ Total assets \begin{tabular}{r|r|} \hline 2006 & 0.002335775 \\ \hline 2007 & 0.057754552 \\ \hline 2008 & 0.048560443 \\ \hline \end{tabular} CFROI Cash Flow/ Total Assets \begin{tabular}{|r|r|} \hline 0.123563589 & 2006 \\ \hline 0.110492009 & 2007 \\ \hline 0.087550552 & 2008 \\ \hline \end{tabular} Financial Analysis by Market Segment \begin{tabular}{|l|r|r|r|} \hline Genetic Diseases & 25% & & \\ \hline & & & \\ & 2006 & 2007 & 2008 \\ \hline Net Revenue & 721852.25 & 864444.5 & 1049226.75 \\ \hline Total Revenue & 796753.25 & 953379.75 & 0.25450504 \\ \hline Operating costs & & & \\ \hline Research \& Development & 162487.75 & 184421.25 & 327082.5 \\ \hline In-process R \& D & 1382.25 & 26587.5 & 0 \\ \hline Operating income (EBIT) & 47627.25 & 163466.25 & 145369.75 \\ \hline Net income & 4199.25 & 120048.25 & 105270.25 \\ \hline \end{tabular} Changes in Accounts Growth Change in Revenue (Ending value-Beginning value)/Beginning Value \begin{tabular}{|l|c|c|} \hline & & 1.99668E+1219.6% increase in revenue \\ \hline 20062007 & 0.213758373 & 21.3% increase \\ \hline 200728 & & \\ \hline R D as a percentage of revenue =28% acceptable. & & 4.43220005413% increase \\ \hline Change in Operating Income & 20062007 & 0.11070480911% decrease \\ \hline & 20072008 & \end{tabular} possible in selling and administrative expenses. Profitability Profit margin Net income/Sales \begin{tabular}{|r|r|} \hline 2006 & 0.005270452 \\ \hline 2007 & 0.138873288 \\ \hline 2008 & 0.100331268 healthy \\ \hline \end{tabular} ROE Net income/Stockholder's equity \begin{tabular}{r|r|} \hline 2006 & 0.000741824 \\ \hline 2007 & 0.018153545 \\ \hline 2008 & 0.014408753 \\ \hline \end{tabular} ROI Net income/ Total assets \begin{tabular}{|r|r|} \hline 2006 & 0.000583944 \\ \hline 2007 & 0.014438638 \\ \hline 2008 & 0.012140111 \\ \hline \end{tabular} CFROI Cash Flow/ Total Assets \begin{tabular}{|r|r|} \hline 0.034749787 & 2006 \\ \hline 0.027623002 & 2007 \\ \hline 0.021887638 & 2008 \\ \hline \end{tabular} Exhibit 4.12 Intrinsic Value =$90.00 47+10+8+5+4=74= value of marketed drug portfolio 64.44027047 Computation of Intrinsic Value Exhibit 4.13 Case 4: Repeat questions 1,2 , and 3 , with the following information: CFROI 20%(GD)4%(BI) Acquisitions 10 (GD) 2 (CR) 9 (BI) 2 (HO) 5 (Other) Financial Strategy. No dividends, 60\% debt Capital Allocation, Diversification outside GD destroying shareholder value because of low CFROI. Share Repurchase. Policy of no share buybacks. Directions: See file g41201.14(17)(6)(3)(8)(1)(8)(5)(4)(4)b(1).xlsx from Module 2 (next to last file). Go to line 97, Find the new net income for Genetic Diseases on lines 99-105, for 2006, 2007, and 2008. Replace .25 with .2 in each cell. Find the new net income for BI on lines 99-105, for 2006, 2007, and 2008. Replace .25 with .1 with in each cell. Scroll down to the computation of CFROI. Compute a new CFROI for 2006-2008 for GD and BI. Example, 2006 for GD CFROI=.2999569/7191188 These 2 numbers are cash flow, and total assets from page 74 in the text. Complete the CFROI computation for 2007 , and 2008 for GD and for all 3 years for BI. Answer the 3 questions (in paragraphs) at the top of the excel file for Genzyme. \begin{tabular}{c} a0c \\ c \\ uc0 \\ \hline \end{tabular} 2.6% stake Genzyme Income Statement Net Revenue Total Revenue \begin{tabular}{|r|r|r|} \hline 2006 & 2007 & 2008 \\ \hline 2887409 & 3457778 & 4196907 \\ \hline 3187013 & 3813519 & 4505039 \\ \hline \end{tabular} Operating costs Research \& Development In-process R \& D Operating income (EBIT) Net income Changes in Accounts Growth Change in Revenue (Ending value-Beginning value)/Beginning Value \begin{tabular}{|l|c|c|c|} \hline 20062007 & \multicolumn{2}{|c|}{0.19658093619.6% increase in revenue } \\ \hline 200728 & 0.213758373 & 21.3% increase \\ \hline R D as a percentage of revenue =28% acceptable. & 0.203937355 & 0.193439445 & 0.2904148 \\ \hline Change in Operating Income & 20062007 & 4.4322000544% increase \\ \hline & 20072008 & 0.11070480911% decrease \\ \hline \end{tabular} possible in selling and administrative expenses. Profitability Profit margin Net income/Sales \begin{tabular}{|r|r|} \hline 2006 & 0.005270452 \\ \hline 2007 & 0.138873288 \\ \hline 2008 & 0.100331268 healthy \\ \hline \end{tabular} ROE Net income/Stockholder's equity RO Net income/ Total assets \begin{tabular}{r|r|} \hline 2006 & 0.002335775 \\ \hline 2007 & 0.057754552 \\ \hline 2008 & 0.048560443 \\ \hline \end{tabular} CFROI Cash Flow/ Total Assets \begin{tabular}{|r|r|} \hline 0.123563589 & 2006 \\ \hline 0.110492009 & 2007 \\ \hline 0.087550552 & 2008 \\ \hline \end{tabular} Financial Analysis by Market Segment \begin{tabular}{|l|r|r|r|} \hline Genetic Diseases & 25% & & \\ \hline & & & \\ & 2006 & 2007 & 2008 \\ \hline Net Revenue & 721852.25 & 864444.5 & 1049226.75 \\ \hline Total Revenue & 796753.25 & 953379.75 & 0.25450504 \\ \hline Operating costs & & & \\ \hline Research \& Development & 162487.75 & 184421.25 & 327082.5 \\ \hline In-process R \& D & 1382.25 & 26587.5 & 0 \\ \hline Operating income (EBIT) & 47627.25 & 163466.25 & 145369.75 \\ \hline Net income & 4199.25 & 120048.25 & 105270.25 \\ \hline \end{tabular} Changes in Accounts Growth Change in Revenue (Ending value-Beginning value)/Beginning Value \begin{tabular}{|l|c|c|} \hline & & 1.99668E+1219.6% increase in revenue \\ \hline 20062007 & 0.213758373 & 21.3% increase \\ \hline 200728 & & \\ \hline R D as a percentage of revenue =28% acceptable. & & 4.43220005413% increase \\ \hline Change in Operating Income & 20062007 & 0.11070480911% decrease \\ \hline & 20072008 & \end{tabular} possible in selling and administrative expenses. Profitability Profit margin Net income/Sales \begin{tabular}{|r|r|} \hline 2006 & 0.005270452 \\ \hline 2007 & 0.138873288 \\ \hline 2008 & 0.100331268 healthy \\ \hline \end{tabular} ROE Net income/Stockholder's equity \begin{tabular}{r|r|} \hline 2006 & 0.000741824 \\ \hline 2007 & 0.018153545 \\ \hline 2008 & 0.014408753 \\ \hline \end{tabular} ROI Net income/ Total assets \begin{tabular}{|r|r|} \hline 2006 & 0.000583944 \\ \hline 2007 & 0.014438638 \\ \hline 2008 & 0.012140111 \\ \hline \end{tabular} CFROI Cash Flow/ Total Assets \begin{tabular}{|r|r|} \hline 0.034749787 & 2006 \\ \hline 0.027623002 & 2007 \\ \hline 0.021887638 & 2008 \\ \hline \end{tabular} Exhibit 4.12 Intrinsic Value =$90.00 47+10+8+5+4=74= value of marketed drug portfolio 64.44027047 Computation of Intrinsic Value Exhibit 4.13
