Review View Calib Help Copy Paste Format Painter Clipboard 12 VAA BIU AEES 19 Wrap Test Merge & Center - General Font $ - % -1 AZ Alignment Conditional Formula G Formatting Table Styles A B Excel Case #2 - Texas Instruments, Inc. (TXN) 3 Total points available 20 4 The Answer Schedule shows the points per question/answer 6 Points available for each answer are all or nothing must be correct answer AND in correct format Rounding of your answers should take place when you get to the final step, do not round prior to that 8. Please refer to the other worksheets in this Excel file to get the data you need 9 You need to show your work somewhere in this Excel file to get credit when calculations are involved 10 If you just provide the correct answers and do not show your work when calculations are necessary, you will not earn full credit 11 On the answer schedule, you can use cell references OR manually input your answers (as long as you did the actual calculations in Excel) 12 For example: show the growth rate for equity on the worksheet that contains the data you use for the calculation 13 14 Save your file with this name format: Excel Case2 FiestinitialLastname sectionnumber 15 Example: Excel Case 2 Doe 03W 16 17 Submit on Blackboard - do not email directly to Professor Gwinn 18 (due date and time is Sunday, April 18th, 11:59pm) 19 I will only grade your last submission (2 maximum submissions) 20 Late submissions are subject to a 2 point penalty 21 22 23 24 25 Instructions Answer Schedule Finandals Data Bond sched AutoSave File Wage Layout Home X CH scop TIN Excel Caseme section (1) Sr. Data View Help FO Kala 12 AN A HI! 19 Water e- A2 Gen 5-% and GE - A D 1 Excel Case #2 - Texas Instruments, Inc. Answer Schedule Format Points wille Remarks 0:50 Market value of guity (marcat cap) Growth rate for it Marketle of stock per share Annial dividend Bata of stock Risk free rate Market Gate of retur NOGM cost of equity (stock) CAPM cost of culty stock) Average cost of equility 5. Market value of debt billioncurrenty. 2 decimal places percentage 2 decimal places Currency 2 decimal places Cicy, 2 decimal places number, 2 decimal places 1.5096 percentage 2 decimal places 11.EON page2 decimal places percentage 2 decimal places percentage 2 decimal places percentage, 2 decimal places billion currency 2 decimal places 0:50 0.50 0.50 12/30/20 dana provided on Dutatuwe wa LIONS vided the most recentes seronthew se 12/31/20 vided on Dutta wse the most recent year on the Data use 12/31/20 oded on Dutta 125 1.25 1.00 aso 6 Average for late Pre tax cost of debt bonds) After tal cost of debt (bonds) Total market value of equity and debt Weight for equity Weight for debt 2 Weighted average cost of capital percentage 2 decimal places percentage, 2 decimal places percentage 2 decimal places currency 2 decimal places number, 4 de aces number 4 decimal places percentage 2 decimal places billion simple age of the methode bond schedule for 12/11/30-BLUGINS we average for most recent calendar receptable ways to calculate the the slightly different bond schedule for 12/31/20 e band schedule for 12/31/20 usein BILLIONS 1.00 350 135 0.50 1.25 125 Total points 20 med Inst Anwar Schedule France donde A Type search Avec File Home ht Page Layout TINGCe name section () - Saved Formula Data View Help Paste 11 AN X Cut Copy Format Clipboard B1V - SE WA 9 Wrap Test Mengerie $ - %692 Font Condition Formatting 31 Alg D G H 2020-12 2019-12 2018-12 2011-12 2016-12 2015-12 2014-12 5,187 1.074 6.568 1.414 1.955 302 10,219 2,001 4.223 1,207 2.217 140 3,097 4.09 1.270 1.952 1030 8.7.14 3,450 1267 1.70 930 299 8.761 3.215 185 1.693 1.000 7,074 1.541 1.225 1784 850 7,421 A 1 Texas Instruments Inc. (TXN) BALANCE SHEET 2 Fiscal year ends in December. USD in millions 3 Assets 4 Current assets 5 Cash and cash equivalents 6 Receivables 7 Inventories Other current assets 9 Total current assets 10 Non current assets 11 Gross property, plant and equipment 12 Accumulated Deprecation 13. Goodwill 14 other intangible assets 15 Other long-term assets 16 Total non current assets 17 Total assets 19 19 Liabilities 20 Current liabilities at short-term debt 22 Accounts payable and other accruals 2 Other current liabilities Total current ilabilities 25 Non-cament liabilities 26 on torni deb! 22 Deferred taxaties 20 Otherlang termes 29 Totion current liabilities 5,751 12.512 4362 617 164 9,112 19,351 5.40 (2417 4,162 000 986 9.252 18,018 5.425 12,345 2362 717 778 2010 17,132 5:45 12,91 4,352 4.789 (2.125 4,362 1.056 820 3,90 17,642 4323 2411) 4.12 1316 6.266 3.4.1 4,162 2125 23 9,951 17,2 8.574 16,411 509 9.156 16,230 745 2.00 550 1182 650 2,390 500 1.102 521 2.121 500 1018 411 $23 710 2.24 1.000 392 604 2.555 100 10 56 25 2,414 2.258 2.970 50 5,30 70 LO 371 1,102 5.669 54 60 LU 7.774 5,047 Financial 18 otype here to learch 0 . #185M La Auto ON a TIN Excel Case 2 name section (1) - Saved. File Home Insert Search Page Layout Formules Data Peview View Help X Cut b Copy Format Painter Calib Paste 11-AA 0-A General IU 29 Wrap Test Merge & Center - Alignment Clipboard TS $ - % 9 % Font Conditional Format Formatting - Tablety Styles 81 Number G 1 1,436 7,774 10,164 1,607 6,988 9,111 D 1,308 5,669 8,143 E 1.392 5,047 7,305 572 683 3,694 5,958 3,729 6,284 630 4,324 6,982 A 28 Other long-term liabilities 29 Total non-current liabilities 30 Total Habilities 31 32 Stockholders' equity 33 Common stock 34 Additional Paid in Capital 35 Retained earnings 36 Treasury stock 37 Accumulated other comprehensive income 38 Total stockholders equity 39 Total liabilities and stockholders equity 40 1,741 2,333 42,051 (36,578) (360) 9,187 19,351 1,741 2.110 39,898 (34,495) (347 8,907 18,018 1,741 1.950 37,906 (32,130) (473) 8,994 17,137 1,741 1.776 34,662 (27,458) (384) 10,337 17,642 1,741 1.674 33.107 (25,523) (526) 10,473 16,431 1.741 2.629 31.176 (24,068 (532) 9.946 16,230 1.741 1,368 29.653 (21.840) (532) 10,390 17,372 2019-12 2018-12 2017-12 2016-12 2015-12 2014-12 42 Texas Instruments inc. (TXN) INCOME STATEMENT 43 Fiscal year ends in December. USD in millions 2020-12 44 5 Revenue (Sales) 14,461 46 cost of revenue (Cost of goods sold) 5,192 47 Gross profit (Gross margin) 9,269 43 Operating expenses 2,429 19 EBITDA 6,810 Deprecand amort 733 31 Operating Income (EBIT) 6,107 52 terest Expense 90 53 Taxable income 6,017 54 Income tax expense 422 55 Net Income 56 Instructions Answer Schedule Financial Data 14,383 5,219 9,164 2.650 6,514 703 5,806 78 5,728 211 5,017 15,784 5,507 10,277 2.938 7,339 590 6,749 63 6,686 1106 5,580 14,961 5,347 9,614 2.917 6,697 539 6,158 78 6,080 2,399 3,682 13,370 5,130 8,240 2.636 5,604 605 4,999 69 4,930 1,335 2.595 13,000 5,410 7. 2.520 5,040 766 4.224 58 4,216 2.2.30 2.986 13.045 5,618 7,427 2,630 4,797 850 3,90 73 3,874 1,053 5.595 Bond sched te Lo Copy 3 Format Painter Clipboard 25 Wrap Text BILA SEE E Merge & Center Alignment General Font $ - % Conditional Format as Formatting - Table - Sty Number B D E F Texas Instruments, Inc. yahoo finance data 12/31/20 year end) G H Annual Annual Average Dividends Growth Rates Growth Rate $ 164.13 S 150.890 billion 1.27 27.13 6.05 3.72 919.350 million $ 5 Current stock price Market Cap (Value Beta PE Ratio EPS Annual dividend of shares outstanding 1 2 3 current price 4 outstanding shares 15 market value 16 27 18 19 20 27 22 + Quarterly Dividends 1.02 0.90 0.90 0.90 0.90 0.77 0.77 0.77 0.77 0.62 0.62 0.62 0.62 0:50 0.50 0.50 0.50 0.38 Date 10/29/20 07/30/20 05/01/20 01/20/20 10/30/19 07/30/19 05/01/19 01/30/19 10/30/18 07/30/18 05/04/18 01/30/18 10/30/17 07/27/17 04/27/17 01/27/17 11/03/16 07/28/16 04/28/16 01/28/16 10/28/15 07/29/15 04/23/15 01/28/15 10/23/14 s 164-13 919,350,000 S 150,892,915,500 or 150.890 billion 0:38 0.35 0.34 24 25 Instruction A schedule Bond Scheel Paste B Format Painter Number 5 Font Alignment Clipboard A2 WIWIN H F E B D 27.13 $ 6.05 $ 3.72 919.350 million 7 PE Ratio 8 EPS 9. Annual dividend 10 # of shares outstanding 11 12 13 current price 14 outstanding shares 15 market value 16 17 18 $ 164.13 919,250,000 $ 150,892,915,500 or s 150.890 billion 07/30/19 05/01/19 01/30/19 10/30/18 07/30/18 05/04/18 01/30/18 10/30/17 07/27/17 04/27/17 01/27/17 11/03/16 07/28/16 04/28/16 01/28/16 10/28/15 07/29/15 04/28/15 01/28/15 30/29/14 07/29/14 04/28/14 01/29/14 G 0.77 0.77 0.77 0.77 0.62 0.62 0.62 0.62 0.50 0.50 0.50 0.50 0.38 0.38 0.38 0.38 0.34 0.34 0.34 0.34 0.30 0.30 0.30 19 20 21 22 23 24 25 26 27 28 29 31 32 33 34 Tde Name section (1) - Seved Page Layout Formulas Data Review View Help Home Insert X aut Copy Format Painter Clipboard Paste Calibri 14AA3 13 Wrap Test BTW- EN -AA-Merge Center Font Alignment General $ . % 92 Conditional Format Cell Formatting - The Styles Shis Numer D 1 TXN outstanding bonds as of 12/31/18 2 Face amounts are in millions. G H 1 Maturity Current Price Coupon % Market Values (Md) Weight Weighted YTM % 4 Name 5 6 Senior Notes 7 Senior Notes B Senior Notes 9 Senior Notes 10 Senior Notes 11 Senior Notes 12 Senior Notes 13 Senior Notes 14 Senior Notes 15 Senior Notes 16 Totals 17 03/12/21 05/15/22 05/01/23 05/15/24 03/12/25 11/03/27 09/04/29 05/04/30 03/15/39 05/15/48 Face Amount $(Mil) 550.00 500.00 500.00 300.00 750.00 500.00 750.00 750.00 750.00 1,500.00 6,850.00 or 6.850 billion 100 00 10152 103.26 105.76 102 05 10730 101 25 97 50 115 24 120 43 2.750 1.850% 2.2505 2.625 1,375 2.900 2,250% 1.750 3.8756 4.1509 Yield to Maturity % 26694 07344% 8275% 8902% 08754% t6874% 2 0915% 204524 28005 3.0444% 19 20 21 this table may be helpful atter completing columns Pre-tax Cost of Debt 23 Adjustment fer tax rate 75 3 After tax Cost of Debt Instructions Answer Schedule Finander Data Band Sched Review View Calib Help Copy Paste Format Painter Clipboard 12 VAA BIU AEES 19 Wrap Test Merge & Center - General Font $ - % -1 AZ Alignment Conditional Formula G Formatting Table Styles A B Excel Case #2 - Texas Instruments, Inc. (TXN) 3 Total points available 20 4 The Answer Schedule shows the points per question/answer 6 Points available for each answer are all or nothing must be correct answer AND in correct format Rounding of your answers should take place when you get to the final step, do not round prior to that 8. Please refer to the other worksheets in this Excel file to get the data you need 9 You need to show your work somewhere in this Excel file to get credit when calculations are involved 10 If you just provide the correct answers and do not show your work when calculations are necessary, you will not earn full credit 11 On the answer schedule, you can use cell references OR manually input your answers (as long as you did the actual calculations in Excel) 12 For example: show the growth rate for equity on the worksheet that contains the data you use for the calculation 13 14 Save your file with this name format: Excel Case2 FiestinitialLastname sectionnumber 15 Example: Excel Case 2 Doe 03W 16 17 Submit on Blackboard - do not email directly to Professor Gwinn 18 (due date and time is Sunday, April 18th, 11:59pm) 19 I will only grade your last submission (2 maximum submissions) 20 Late submissions are subject to a 2 point penalty 21 22 23 24 25 Instructions Answer Schedule Finandals Data Bond sched AutoSave File Wage Layout Home X CH scop TIN Excel Caseme section (1) Sr. Data View Help FO Kala 12 AN A HI! 19 Water e- A2 Gen 5-% and GE - A D 1 Excel Case #2 - Texas Instruments, Inc. Answer Schedule Format Points wille Remarks 0:50 Market value of guity (marcat cap) Growth rate for it Marketle of stock per share Annial dividend Bata of stock Risk free rate Market Gate of retur NOGM cost of equity (stock) CAPM cost of culty stock) Average cost of equility 5. Market value of debt billioncurrenty. 2 decimal places percentage 2 decimal places Currency 2 decimal places Cicy, 2 decimal places number, 2 decimal places 1.5096 percentage 2 decimal places 11.EON page2 decimal places percentage 2 decimal places percentage 2 decimal places percentage, 2 decimal places billion currency 2 decimal places 0:50 0.50 0.50 12/30/20 dana provided on Dutatuwe wa LIONS vided the most recentes seronthew se 12/31/20 vided on Dutta wse the most recent year on the Data use 12/31/20 oded on Dutta 125 1.25 1.00 aso 6 Average for late Pre tax cost of debt bonds) After tal cost of debt (bonds) Total market value of equity and debt Weight for equity Weight for debt 2 Weighted average cost of capital percentage 2 decimal places percentage, 2 decimal places percentage 2 decimal places currency 2 decimal places number, 4 de aces number 4 decimal places percentage 2 decimal places billion simple age of the methode bond schedule for 12/11/30-BLUGINS we average for most recent calendar receptable ways to calculate the the slightly different bond schedule for 12/31/20 e band schedule for 12/31/20 usein BILLIONS 1.00 350 135 0.50 1.25 125 Total points 20 med Inst Anwar Schedule France donde A Type search Avec File Home ht Page Layout TINGCe name section () - Saved Formula Data View Help Paste 11 AN X Cut Copy Format Clipboard B1V - SE WA 9 Wrap Test Mengerie $ - %692 Font Condition Formatting 31 Alg D G H 2020-12 2019-12 2018-12 2011-12 2016-12 2015-12 2014-12 5,187 1.074 6.568 1.414 1.955 302 10,219 2,001 4.223 1,207 2.217 140 3,097 4.09 1.270 1.952 1030 8.7.14 3,450 1267 1.70 930 299 8.761 3.215 185 1.693 1.000 7,074 1.541 1.225 1784 850 7,421 A 1 Texas Instruments Inc. (TXN) BALANCE SHEET 2 Fiscal year ends in December. USD in millions 3 Assets 4 Current assets 5 Cash and cash equivalents 6 Receivables 7 Inventories Other current assets 9 Total current assets 10 Non current assets 11 Gross property, plant and equipment 12 Accumulated Deprecation 13. Goodwill 14 other intangible assets 15 Other long-term assets 16 Total non current assets 17 Total assets 19 19 Liabilities 20 Current liabilities at short-term debt 22 Accounts payable and other accruals 2 Other current liabilities Total current ilabilities 25 Non-cament liabilities 26 on torni deb! 22 Deferred taxaties 20 Otherlang termes 29 Totion current liabilities 5,751 12.512 4362 617 164 9,112 19,351 5.40 (2417 4,162 000 986 9.252 18,018 5.425 12,345 2362 717 778 2010 17,132 5:45 12,91 4,352 4.789 (2.125 4,362 1.056 820 3,90 17,642 4323 2411) 4.12 1316 6.266 3.4.1 4,162 2125 23 9,951 17,2 8.574 16,411 509 9.156 16,230 745 2.00 550 1182 650 2,390 500 1.102 521 2.121 500 1018 411 $23 710 2.24 1.000 392 604 2.555 100 10 56 25 2,414 2.258 2.970 50 5,30 70 LO 371 1,102 5.669 54 60 LU 7.774 5,047 Financial 18 otype here to learch 0 . #185M La Auto ON a TIN Excel Case 2 name section (1) - Saved. File Home Insert Search Page Layout Formules Data Peview View Help X Cut b Copy Format Painter Calib Paste 11-AA 0-A General IU 29 Wrap Test Merge & Center - Alignment Clipboard TS $ - % 9 % Font Conditional Format Formatting - Tablety Styles 81 Number G 1 1,436 7,774 10,164 1,607 6,988 9,111 D 1,308 5,669 8,143 E 1.392 5,047 7,305 572 683 3,694 5,958 3,729 6,284 630 4,324 6,982 A 28 Other long-term liabilities 29 Total non-current liabilities 30 Total Habilities 31 32 Stockholders' equity 33 Common stock 34 Additional Paid in Capital 35 Retained earnings 36 Treasury stock 37 Accumulated other comprehensive income 38 Total stockholders equity 39 Total liabilities and stockholders equity 40 1,741 2,333 42,051 (36,578) (360) 9,187 19,351 1,741 2.110 39,898 (34,495) (347 8,907 18,018 1,741 1.950 37,906 (32,130) (473) 8,994 17,137 1,741 1.776 34,662 (27,458) (384) 10,337 17,642 1,741 1.674 33.107 (25,523) (526) 10,473 16,431 1.741 2.629 31.176 (24,068 (532) 9.946 16,230 1.741 1,368 29.653 (21.840) (532) 10,390 17,372 2019-12 2018-12 2017-12 2016-12 2015-12 2014-12 42 Texas Instruments inc. (TXN) INCOME STATEMENT 43 Fiscal year ends in December. USD in millions 2020-12 44 5 Revenue (Sales) 14,461 46 cost of revenue (Cost of goods sold) 5,192 47 Gross profit (Gross margin) 9,269 43 Operating expenses 2,429 19 EBITDA 6,810 Deprecand amort 733 31 Operating Income (EBIT) 6,107 52 terest Expense 90 53 Taxable income 6,017 54 Income tax expense 422 55 Net Income 56 Instructions Answer Schedule Financial Data 14,383 5,219 9,164 2.650 6,514 703 5,806 78 5,728 211 5,017 15,784 5,507 10,277 2.938 7,339 590 6,749 63 6,686 1106 5,580 14,961 5,347 9,614 2.917 6,697 539 6,158 78 6,080 2,399 3,682 13,370 5,130 8,240 2.636 5,604 605 4,999 69 4,930 1,335 2.595 13,000 5,410 7. 2.520 5,040 766 4.224 58 4,216 2.2.30 2.986 13.045 5,618 7,427 2,630 4,797 850 3,90 73 3,874 1,053 5.595 Bond sched te Lo Copy 3 Format Painter Clipboard 25 Wrap Text BILA SEE E Merge & Center Alignment General Font $ - % Conditional Format as Formatting - Table - Sty Number B D E F Texas Instruments, Inc. yahoo finance data 12/31/20 year end) G H Annual Annual Average Dividends Growth Rates Growth Rate $ 164.13 S 150.890 billion 1.27 27.13 6.05 3.72 919.350 million $ 5 Current stock price Market Cap (Value Beta PE Ratio EPS Annual dividend of shares outstanding 1 2 3 current price 4 outstanding shares 15 market value 16 27 18 19 20 27 22 + Quarterly Dividends 1.02 0.90 0.90 0.90 0.90 0.77 0.77 0.77 0.77 0.62 0.62 0.62 0.62 0:50 0.50 0.50 0.50 0.38 Date 10/29/20 07/30/20 05/01/20 01/20/20 10/30/19 07/30/19 05/01/19 01/30/19 10/30/18 07/30/18 05/04/18 01/30/18 10/30/17 07/27/17 04/27/17 01/27/17 11/03/16 07/28/16 04/28/16 01/28/16 10/28/15 07/29/15 04/23/15 01/28/15 10/23/14 s 164-13 919,350,000 S 150,892,915,500 or 150.890 billion 0:38 0.35 0.34 24 25 Instruction A schedule Bond Scheel Paste B Format Painter Number 5 Font Alignment Clipboard A2 WIWIN H F E B D 27.13 $ 6.05 $ 3.72 919.350 million 7 PE Ratio 8 EPS 9. Annual dividend 10 # of shares outstanding 11 12 13 current price 14 outstanding shares 15 market value 16 17 18 $ 164.13 919,250,000 $ 150,892,915,500 or s 150.890 billion 07/30/19 05/01/19 01/30/19 10/30/18 07/30/18 05/04/18 01/30/18 10/30/17 07/27/17 04/27/17 01/27/17 11/03/16 07/28/16 04/28/16 01/28/16 10/28/15 07/29/15 04/28/15 01/28/15 30/29/14 07/29/14 04/28/14 01/29/14 G 0.77 0.77 0.77 0.77 0.62 0.62 0.62 0.62 0.50 0.50 0.50 0.50 0.38 0.38 0.38 0.38 0.34 0.34 0.34 0.34 0.30 0.30 0.30 19 20 21 22 23 24 25 26 27 28 29 31 32 33 34 Tde Name section (1) - Seved Page Layout Formulas Data Review View Help Home Insert X aut Copy Format Painter Clipboard Paste Calibri 14AA3 13 Wrap Test BTW- EN -AA-Merge Center Font Alignment General $ . % 92 Conditional Format Cell Formatting - The Styles Shis Numer D 1 TXN outstanding bonds as of 12/31/18 2 Face amounts are in millions. G H 1 Maturity Current Price Coupon % Market Values (Md) Weight Weighted YTM % 4 Name 5 6 Senior Notes 7 Senior Notes B Senior Notes 9 Senior Notes 10 Senior Notes 11 Senior Notes 12 Senior Notes 13 Senior Notes 14 Senior Notes 15 Senior Notes 16 Totals 17 03/12/21 05/15/22 05/01/23 05/15/24 03/12/25 11/03/27 09/04/29 05/04/30 03/15/39 05/15/48 Face Amount $(Mil) 550.00 500.00 500.00 300.00 750.00 500.00 750.00 750.00 750.00 1,500.00 6,850.00 or 6.850 billion 100 00 10152 103.26 105.76 102 05 10730 101 25 97 50 115 24 120 43 2.750 1.850% 2.2505 2.625 1,375 2.900 2,250% 1.750 3.8756 4.1509 Yield to Maturity % 26694 07344% 8275% 8902% 08754% t6874% 2 0915% 204524 28005 3.0444% 19 20 21 this table may be helpful atter completing columns Pre-tax Cost of Debt 23 Adjustment fer tax rate 75 3 After tax Cost of Debt Instructions Answer Schedule Finander Data Band Sched
