Question

What formula do you use in excel to convert a consolidated balance sheet into a common size balance sheet.

Formatting . Table Styles . Styles Filter Select Clipboard Font Alignment Cells Editing B38 Consolidated Balance Sheet (In Millions, Except Par Value) 2012 Common Size Balance Sheet 1 December 28, 2013; December 29, 2012; and December 31, 2011 2-1 (in Millions, Except Par Value) 3 Assets 4 Current assets: 5 Cash and cash equivalents 6 Short-term investments 7 Trading assets 8Accounts receivable, net of allowance for doubtful accounts of $38 (S36 in 2011) 2013 2011 $5,674 $8,478 5,065 s5,972 3,999 5,181 $8,441 $5,685$4,591 $4,172 4,734 4,096 2,594 2,117 1,70o 1,589 10 Deferred tax assets Other current assets S 1,6492,512 12 Total current assets 32,084 $31, 358$ 25,872 Property, plant and equipment, net 14 Marketable equity securities 15 Other long-term investments 16 Goodwill $31,428 27,983 23,627 6,221 4,424 S 562 889 $ 10,513 $ 9,710 S 9,254 $ 5,150 6,235 6,267 $5,489 4,148 4,648 $92,358 $ 84,351 71,119 $1,473 $ 493 17 Identified intangible assets, net 8 Other long-term assets 9 Total assets 20 Liabilities and stockholders' equity 21 Current liabilities: Instructions Balance Sheet Income Statement 0 Type here to search 810 PM 1/27/2019 Λ圓

Answer 1

Excel formula for common size balance sheet

A common size balance sheet show the relative percentage of total asset.

the formula for the above excel should be like below.

please enter the formula in cell F5 as =A5/A$19 the answer will be 6% in this cell.

and copy paste the same to all the cell including 2012 and 2011 column as well.

please don't forget to make cells as percentage format