Question

I need help filling in the green spaces for both the Income Statement and Balance Sheet. Below is Exhibit 1 (Financial and Operating Data for Cosco).

TIP: use n = 3 for the CAGR calculation.

Please explain using an Excel spreadsheet,

formulas, and a step-by-step analysis/explanation.

Any help would be appreciated!

EXHIBIT 1

544 53 EXHIBIT 1 Selected Financial and Operating Data for Costco Wholesale Corp., Fiscal Years 2000, 2005, 2011, and 2013-2016 ($ in millions, except for per share data) Fiscal Years Ending on Sunday Closest to August 31 Selected Income Statement Data 2016 2015 2014 2013 2011 2005 2000 Net sales $116,073 $113,666 $110,212 $102,870 $87,048 $51,862 $31,621 Membership fees 2.646 2,533 2,428 2 ,286 1.867 1,073 Total revenue 118.719 116,199 112,640 105,156 88,915 52,935 32,164 Operating expenses Merchandise costs 102,901 101,065 98,458 91,948 77,739 46,347 28,322 Selling, general, and administrative 12,068 11,445 10,899 1 0,104 8,682 5,044 2.755 Preopening expenses 78 65 63 51 46 - 42 Provision for impaired assets and store closing costs 7 3,624 Operating income 3,220 2439 3,053 Other income (expense) Interest expense (133) (124) (113) (99) (116) (34) (39 Interest income and other 80 97 54 Income before income taxes 3,619 3,604 3,197 3,051 2.383 1,549 1.052 1,243 1,109 1,195 990 Provision for income taxes 841 486 $ 2,350 $ Net income 2,377 $ 2,058 $ 2,039 $ 1.462 $ 1.063 $ 5,33 $ 5.37 $ 4.65 $ Diluted net income per share 4.63 $ 3.30 $ 2.18 $ 1.35 Dividends per share (not including spe- cial dividend of $5.00 in 2015 and $ 1.70 $ $7.00 in 2013) 1.51 $ 1.33 $ 1.17 $ 0.89 $ 0.43 0.00 Millions of shares used in per share 441.3 442.7 4425 calculations 440. 5 4 43.1 492.0 475.7 Balance Sheet Data $ 3,379 $ 4,801 $5,738 $ 4,644 Cash and cash equivalents $4,009 $ 2.063 3,672 1,037 104 60 109 421

Please use these financial ratio equations:

Liquity Ratios:

Answer 1

Please see the row and colum numbers, cell numbers, to see the formulas

D Ε JA 13 14 Total Revenue Total Revenue (in millions) 16 Total Revenue (in billions) 2013 C15*1000000 105156 105 Tot 2014 =D15*1000000 112641 =D15/1000 15 2015 =E15*1000000 116199 =E15/1000 2016 F15*1000000 118719 F15/1000 17 18 Revenue CARG 2013-2016 ={F14/C14)^(1/3)-1 19 Revenue 1 year G =D14/C14-1 !=E14/014-1 =F14/E14-1 101065 21 22 23 24 Merchandise Costs Gross Progit Gross Margin 91948 13208 =C23/C15 98458 14182 =D23/D15 15134 102901 15818 =F23/F15 =E23/E15 25 Operating Income Operating Margin 3053 i=C26/C15 3220 =D26/D15 3624 =E26/E15 3672 =F26/F15 2039 Net Income NI 1 Year G 2058 =D29/C29-1 2377 =E29/D29-1 2350 =F29/E29-1 Return on Sales =C29/C15 =D29/D15 =E29/E15 =F29/F15 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Total Assets Total Equity 30283 11012 33024 12515 33440 10843 33163 12079 ROA ROE =C29/C34 =C29/C35 =D29/D34 =D29/D35 =E29/E34 =E29/E35 =F29/F34 =F29/F35 EPS 4.63 4.65 5.37 5.33

Balance Sheet Current Assets Inventories 15840 7894 42 43 44 45 46 47 48 49 17588 8456 14412 - --------- 17299 8908 16540 ------ 15218 8969 Current Liab ------------ 13257 ----- 15575 ---- ---- --- Current Ratio Quick Ratio =C44/C46 =(C44-C45)/C46 =D44/D46 ={D44-D45)/D46 =E44/E46 =(E44-E45)/E46 =F44/F46 =(F44-F45)/F46 50 Total Assets Total Equity Total Debt Long Term Debt I=C34 =C35 =C51-C52 4998 =D34 =D35 =D51-D52 5093 =E34 I=E35 =E51-E52 4864 =F34 =F35 =F51-F52 4061 51 52 53 54 55 56 57 58 D/A LTD/A D/E =C53/C51 1-C54/C51 1 =C53/C52 =D53/D51 =D54/D51 =D53/D52 =E53/E51 =E54/E51 =E53/E52 =F53/F51 =F54/F51 =F53/F52 60 PPE 13881 14830 15401 17043 FAT TAT =C15/C60 =C15/C51 =D15/D60 =D15/D51 =E15/E60 =E15/E51 =F15/F60 =F15/F51 65 66 Merchandise Costs Inventory I=C22 I=C45 =D22 =D45 =E22 =E45 =F22 =F45 67 68 Inventory Turnover -C65/C66 =D65/D66 =E65/E66 =F65/F66