Hello Josh - thank you for you help with the assignment. I am trying to reload the workbook. I denied the other question and am trying to get it to you this way:

Using the MS Excel Financial Analysis Template, complete the following workbook tabsusing Kohl's financial information in completing the assignment. Complete the following two workbook tabs according to the directions set forth in the individual tabs.

• TAB - "Common?size FS" and
• TAB - "Financial Ratios"

You will analyze financial statements submitted by Kohl's for the five-fiscal year period (December 31),2009 -2013. In this assignment, you will:

• - Obtain financial ratios for an industry group using electronic databases and Annual Reports on Form 10-K of public companies.
• - Analyze common-size financial statements of a company and financial ratios of a company and its industry group to identify conditions, trends, and relationships that warrant investigation.

Note: Use care not to delete, hide or reorder workbook tabs (worksheets) i . . . . Week 3 Analysis Adjustments Assignment Grading Rubric The facilitator will grade this assignment, assigning up to 100 points for it, as follows: . . Financial statements analysis adjustments are properly identified from the Annual Report on Form 10-K and the supporting analysis, computations, and presentation is complete, correct, concise, and clear: Type of Analysis Adjustment Maximum Earned Worksheet Tab Label: Operating Lease Commitments 20 18 Oper Lease Adjust 5 and 6 Financial Elements of Pension Costs 10 10 Pension Cost Analysis Adjust Inventory Cost-Flow Assumption 15 14 Invent Cost-Flow Analysis Adj Noncancellable Commitments 5 5 Noncancel Commit Analysis Adj Guaranteed Unconsolidated Debt - - Guar Uncons Debt Analysis Adj Capitalized Interest 5 5 Capitalized Interest Analysis Adj Impairment or Restructuring Charges 20 17 Impair, Restruct Analysis Adj Early Retirement, Restructuring Debt 10 9 Debt Restruct Analysis Adj Gains on Investments or PP&E The maximum points possible for each type 15 of analysis adjustment will depend on the particular facts and Total points 100 circumstances of the company selected by the facilitator for use with the assignments. It is unlikely that every type of analysis adjustment listed here will apply to a particular company. 12 Sales Invest, PP&E Analysis Adj 90 workbook tabs (worksheets) in this workbook. . . . . . . . . . . . . . . . . . . . . INPUT US dollar amounts IN 2011 Amounts in blue font and shading are illustrative only. Input all dollar amounts inAudited MILLIONS, NOT thousands balance Assets Current assets Cash and cash equivalents $ 370 Investment securities AFS 160 AR, gross 650 Less: Allowance for bad debts (30) AR, net 620 Inventories, at LOCOM using . . . ? 975 DIT assets-current 115 Other current assets 125 Analysis Adjustment (See explanations below) (A) (B) - - - 105 2,365 - PP&E, at cost 3,210 800 (E) (F) (G) (H) 105 225 - - - - - Even if your group's company reports amounts in its FS in thousands of dollars, enter all amounts in million dollars in this workbook (e.g., $123,456,000.00 becomes $123.456 in this spreadsheet). Amounts still display in the workbook as "whole millions," which is fine, but some totals may appear not to "add up" (or \"foot\") properly, which is acceptable. So, - reformat cells - the decimal - place. do NOT to display That's would be too distracting, and now widened columns will make many schedules/tables too compressed to fit legibly in the group project report (MS Word template). (720) PP&E, net 2,490 Goodwill, net impairment write-downs 800 - 225 - - - - - 800 105 225 - - - - - 545 Other intangibles, net amort., impairment 65 DIT assets-noncurrent 125 Other noncurrent assets 110 Total assets (D) As illustrated near the end of the A602 Learning Topic 4 Background Paper, this workbook tab presents a common methodology for reflecting the analysis adjustments identified in the BS (and, in the following tab, the IS) of a company so that an analyst may conveniently, accurately, and clearly use adjusted financial statement data as the basis for financial ratio analysis; common size analysis; projected FS; and prospective analysis of residual income and fundamental (intrinsic) equity valuation. As necessary, modify this worksheet tab to accomodate the required analysis adjustments for a particular company. MAKE SURE ALL FORMULAS ARE WORKING AS NEEDED OR INDICATED. Total current assets Less: Accumulated depreciation of PP&E (C) $ 5,700 INPUT US dollar amounts IN 2011 Amounts in blue font and shading are illustrative only. Input all dollar amounts inAudited MILLIONS, NOT thousands balance Analysis Adjustment (See explanations below) (A) Liabilities and stockholders' equity Current liabilities AP $ 880 Accrued liabilities 225 Income taxes payable 110 DIT liabilities-current 85 Capital lease obligation-current portion - Bonds-current portion 100 Notes and loans payable-current 250 Other current liabilities (B) (C) (D) 1,700 Notes and loans payable-noncurrent - Bonds, net discount [or, plus premium] 325 DIT liabilities-noncurrent 125 Other noncurrent liabilities (G) (H) 72 25 72 105 520 Capital lease obligation-noncurrent (F) As illustrated near the end of the A602 Learning Topic 4 Background Paper, this workbook tab presents a common methodology for reflecting the analysis adjustments identified in the BS (and, in the following tab, the IS) of a company so that an analyst may conveniently, accurately, and clearly use adjusted financial statement data as the basis for financial ratio analysis; common size analysis; projected FS; and prospective analysis of residual income 105and fundamental (intrinsic) 5 equity valuation. As necessary, modify this worksheet tab to accomodate the required analysis adjustments for a particular company. MAKE SURE ALL FORMULAS ARE WORKING AS NEEDED OR INDICATED. 50 Total current liabilities (E) 25 200 728 Even if your group's company reports amounts in its FS in thousands of dollars, enter all amounts in million dollars in this workbook (e.g., $123,456,000.00 becomes $123.456 in this spreadsheet). Amounts still display in the workbook as "whole millions," which is fine, but some totals may appear not to "add up" (or \"foot\") properly, which is acceptable. So, 5 reformat cells - the decimal - place. do NOT to display That's would be too distracting, and now widened columns will make many schedules/tables too compressed to fit legibly in the group project report (MS Word template). 30 Total noncurrent liabilities 1,000 728 - 200 - - - - - Total liabilities 2,700 800 105 225 5 - - - - Preferred (or preference) stock 230 Premium on preferred stock 25 Common stock (par or stated value) 50 Additional paid-in capital (APIC) 2,215 Less: Treasury stock (270) Unearned stock-based compensation (65) Retained earnings 1,200 Noncontrolling interest in consolid subsid (120) Accum other comprehensive income (loss) (265) Total stockholders' equity Total liabilities and stockholders' equity CHECK: A - (L + E) (S/B -0-) Explanations for Analysis Adjustments $ (5) 3,000 - - - (5) - - - - 5,700 800 105 225 - - - - - - - - - - - - - - As necessary, modify these analysis adjustment explanations for the particular company analyzed and its particular facts and circumstances. (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB expense (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring charges 2011 INPUT US dollar amounts IN Amounts in blue font and shading are Adjusted illustrative only. Input all dollar amounts in MILLIONS, NOT thousands balance Assets Adjusted balances summarized forend all 5ofFYs the Learning far right foT As are illustrated near the the at A602 ease of use by atab group for itsaratio analysis, etc., in other workshe presents common methodology for reflectin tabs. the BS (and, in the following tab, the IS) of a com Current assets Cash and cash equivalents $ 370 Investment securities AFS 160 AR, gross 650 Less: Allowance for bad debts (30) AR, net 620 Even if your group's company reports amounts Inventories, at LOCOM using . . . ?in its FS in thousands of dollars, enter all amounts in million dollars in this workbook (e.g., $123,456,000.00 becomes $123.456 in DIT assets-current this spreadsheet). Amounts still display in the workbook as "whole millions," which isassets fine, but some totals may appear Other current not to "add up" (or \"foot\") properly, which is acceptable. So, current do NOT Total reformat cellsassets to display the decimal place. That's would be too distracting, and now widened columns will make many schedules/tables too compressed to fit PP&E, at cost legibly in the group project report (MS Word template). Less: Accumulated depreciation of PP&E 1,080 115 125 2,470 4,235 (720) PP&E, net 3,515 Goodwill, net impairment write-downs 545 Other intangibles, net amort., impairment 65 DIT assets-noncurrent 125 Other noncurrent assets 110 Total assets $ 6,830 conveniently, accurately, and clearly use adjusted financial ratio analysis; common size analysis; pr residual income and fundamental (intrinsic) equit worksheet tab to accomodate the required analy company. MAKE SURE ALL FORMULAS ARE W 2011 INPUT US dollar amounts IN Amounts in blue font and shading are Adjusted illustrative only. Input all dollar amounts in MILLIONS, NOT thousands balance Liabilities and stockholders' equity Adjusted balances summarized forend all 5ofFYs the Learning far right foT As are illustrated near the the at A602 ease of use by atab group for itsaratio analysis, etc., in other workshe presents common methodology for reflectin tabs. the BS (and, in the following tab, the IS) of a com Current liabilities AP $ 880 Accrued liabilities 335 Income taxes payable 110 DIT liabilities-current 85 Capital lease obligation-current portion 72 Even if your group's company reports amounts in its FS in Bonds-current portion thousands of dollars, enter all amounts in million dollars in this workbook becomes $123.456 in Notes(e.g., and$123,456,000.00 loans payable-current this spreadsheet). Amounts still display in the workbook as "whole millions," which isliabilities fine, but some totals may appear Other current not to "add up" (or \"foot\") properly, which is acceptable. So, current do NOT Total reformat cellsliabilities to display the decimal place. That's would be too distracting, and now widened columns loans payable-noncurrent will makeNotes many and schedules/tables too compressed to fit legibly in the group project report (MS Word template). 100 275 50 1,907 720 Capital lease obligation-noncurrent 728 Bonds, net discount [or, plus premium] 325 DIT liabilities-noncurrent 125 Other noncurrent liabilities 30 Total noncurrent liabilities 1,928 Total liabilities 3,835 Preferred (or preference) stock 230 Premium on preferred stock 25 Common stock (par or stated value) 50 Additional paid-in capital (APIC) 2,215 Less: Treasury stock (270) Unearned stock-based compensation (65) Retained earnings 1,195 Noncontrolling interest in consolid subsid (120) Accum other comprehensive income (loss) (265) Total stockholders' equity Total liabilities and stockholders' equity CHECK: A - (L + E) (S/B -0-) s necessary, modify these analysis adjustment explanations for the particular company analyzed and its articular facts and circumstances. Explanations for Analysis Adjustments conveniently, accurately, and clearly use adjusted financial ratio analysis; common size analysis; pr residual income and fundamental (intrinsic) equit worksheet tab to accomodate the required analy company. MAKE SURE ALL FORMULAS ARE W 2,995 $ 6,830 As necessary, modify these analysis adjustment explanations particular facts and circumstances. (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB exp (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring c INPUT US dollar amounts IN 2012 Amounts in blue font and shading are illustrative only. Input all dollar amounts inAudited MILLIONS, NOT thousands balance Assets Current assets Analysis Adjustment (See explanations below) (A) (B) (D) (E) (F) (G) (H) Adjusted balances are summarized for all 5near FYs the at the for Learning Topic 4 Background Paper, this workbook As illustrated endfarofright the A602 ease of use by a group for its tab ratiopresents analysis,a etc., in other worksheetfor reflecting the analysis adjustments identified in common methodology tabs. the BS (and, in the following tab, the IS) of a company so that an analyst may Cash and cash equivalents $ 370 Investment securities AFS 160 AR, gross 650 Less: Allowance for bad debts (30) AR, net 620 Inventories, at LOCOM using . . . ? 975 DIT assets-current 115 Other current assets 125 conveniently, accurately, and clearly use adjusted financial statement data as the basis for financial ratio analysis; common size analysis; projected FS; and prospective analysis of residual income and fundamental (intrinsic) equity valuation. As necessary, modify this worksheet tab to accomodate the required analysis adjustments for a particular company. MAKE SURE ALL FORMULAS ARE WORKING AS NEEDED OR INDICATED. - 2,365 - PP&E, at cost 3,210 800 Less: Accumulated depreciation of PP&E - - - - - - - - - - - - - 105 Total current assets 105 225 (720) PP&E, net 2,490 Goodwill, net impairment write-downs 800 - 225 - - - - - 800 105 225 - - - - - 545 Other intangibles, net amort., impairment 65 DIT assets-noncurrent 125 Other noncurrent assets 110 Total assets (C) $ 5,700 INPUT US dollar amounts IN 2012 Amounts in blue font and shading are illustrative only. Input all dollar amounts inAudited MILLIONS, NOT thousands balance Analysis Adjustment (See explanations below) (A) (B) (C) (D) (E) (F) (G) (H) Liabilities and stockholders' equity Adjusted balances are summarized for all 5 FYs at the far right for As illustrated near the end of the A602 Learning Topic 4 Background Paper, this workbook ease of use by a group for its tab ratiopresents analysis,a etc., in other worksheetfor reflecting the analysis adjustments identified in common methodology Current liabilities tabs. the BS (and, in the following tab, the IS) of a company so that an analyst may AP $ 880 Accrued liabilities 225 Income taxes payable 110 DIT liabilities-current 85 Capital lease obligation-current portion - Bonds-current portion 100 Notes and loans payable-current 250 Other current liabilities conveniently, accurately, and clearly use adjusted financial statement data as the basis for financial ratio analysis; common size analysis; projected FS; and prospective analysis of residual income 105and fundamental (intrinsic) 5 equity valuation. As necessary, modify this worksheet tab to accomodate the required analysis adjustments for a particular company. MAKE SURE ALL FORMULAS ARE WORKING AS NEEDED OR INDICATED. 72 25 50 Total current liabilities 1,700 Notes and loans payable-noncurrent 72 105 520 Capital lease obligation-noncurrent - Bonds, net discount [or, plus premium] 325 DIT liabilities-noncurrent 125 Other noncurrent liabilities 25 5 - - - - 200 728 30 Total noncurrent liabilities 1,000 728 - 200 - - - - - Total liabilities 2,700 800 105 225 5 - - - - Preferred (or preference) stock 230 Premium on preferred stock 25 Common stock (par or stated value) 50 Additional paid-in capital (APIC) 2,215 Less: Treasury stock (270) Unearned stock-based compensation (65) Retained earnings 1,200 Noncontrolling interest in consolid subsid (120) Accum other comprehensive income (loss) (265) Total stockholders' equity Total liabilities and stockholders' equity CHECK: A - (L + E) (S/B -0-) Explanations for Analysis Adjustments $ (5) 3,000 - - - (5) - - - - 5,700 800 105 225 - - - - - - - - - - - - - - As necessary, modify these analysis adjustment explanations for the particular company analyzed and its particular facts and circumstances. (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB expense (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring charges 2012 INPUT US dollar amounts IN Amounts in blue font and shading are Adjusted illustrative only. Input all dollar amounts in MILLIONS, NOT thousands balance Assets Current assets Cash and cash equivalents $ 370 Investment securities AFS 160 AR, gross 650 Less: Allowance for bad debts (30) AR, net 620 Inventories, at LOCOM using . . . ? 1,080 DIT assets-current 115 Other current assets 125 Total current assets 2,470 PP&E, at cost 4,235 Less: Accumulated depreciation of PP&E (720) PP&E, net 3,515 Goodwill, net impairment write-downs 545 Other intangibles, net amort., impairment 65 DIT assets-noncurrent 125 Other noncurrent assets 110 Total assets $ 6,830 As illustrated near the end of the A602 Learning T tab presents a common methodology for reflectin the BS (and, in the following tab, the IS) of a com conveniently, accurately, and clearly use adjusted financial ratio analysis; common size analysis; pr residual income and fundamental (intrinsic) equit worksheet tab to accomodate the required analy company. MAKE SURE ALL FORMULAS ARE W 2012 INPUT US dollar amounts IN Amounts in blue font and shading are Adjusted illustrative only. Input all dollar amounts in MILLIONS, NOT thousands balance Liabilities and stockholders' equity Current liabilities AP $ 880 Accrued liabilities 335 Income taxes payable 110 DIT liabilities-current 85 Capital lease obligation-current portion 72 Bonds-current portion 100 Notes and loans payable-current 275 Other current liabilities 50 Total current liabilities 1,907 Notes and loans payable-noncurrent 720 Capital lease obligation-noncurrent 728 Bonds, net discount [or, plus premium] 325 DIT liabilities-noncurrent 125 Other noncurrent liabilities 30 Total noncurrent liabilities 1,928 Total liabilities 3,835 Preferred (or preference) stock 230 Premium on preferred stock 25 Common stock (par or stated value) 50 Additional paid-in capital (APIC) 2,215 Less: Treasury stock (270) Unearned stock-based compensation (65) Retained earnings 1,195 Noncontrolling interest in consolid subsid (120) Accum other comprehensive income (loss) (265) Total stockholders' equity Total liabilities and stockholders' equity CHECK: A - (L + E) (S/B -0-) Explanations for Analysis Adjustments As illustrated near the end of the A602 Learning T tab presents a common methodology for reflectin the BS (and, in the following tab, the IS) of a com conveniently, accurately, and clearly use adjusted financial ratio analysis; common size analysis; pr residual income and fundamental (intrinsic) equit worksheet tab to accomodate the required analy company. MAKE SURE ALL FORMULAS ARE W 2,995 $ 6,830 As necessary, modify these analysis adjustment explanations particular facts and circumstances. (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB exp (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring c INPUT US dollar amounts IN 2013 Amounts in blue font and shading are illustrative only. Input all dollar amounts inAudited MILLIONS, NOT thousands balance Assets Current assets Cash and cash equivalents $ 370 Investment securities AFS 160 AR, gross 650 Less: Allowance for bad debts (30) AR, net 620 Inventories, at LOCOM using . . . ? 975 DIT assets-current 115 Other current assets 125 Analysis Adjustment (See explanations below) (A) (B) - (E) (F) (G) (H) - - - - - - - - - - - - - 105 2,365 - PP&E, at cost 3,210 800 105 225 (720) PP&E, net 2,490 Goodwill, net impairment write-downs 800 - 225 - - - - - 800 105 225 - - - - - 545 Other intangibles, net amort., impairment 65 DIT assets-noncurrent 125 Other noncurrent assets 110 Total assets (D) As illustrated near the end of the A602 Learning Topic 4 Background Paper, this workbook tab presents a common methodology for reflecting the analysis adjustments identified in the BS (and, in the following tab, the IS) of a company so that an analyst may conveniently, accurately, and clearly use adjusted financial statement data as the basis for financial ratio analysis; common size analysis; projected FS; and prospective analysis of residual income and fundamental (intrinsic) equity valuation. As necessary, modify this worksheet tab to accomodate the required analysis adjustments for a particular company. MAKE SURE ALL FORMULAS ARE WORKING AS NEEDED OR INDICATED. Total current assets Less: Accumulated depreciation of PP&E (C) $ 5,700 INPUT US dollar amounts IN 2013 Amounts in blue font and shading are illustrative only. Input all dollar amounts inAudited MILLIONS, NOT thousands balance Analysis Adjustment (See explanations below) (A) Liabilities and stockholders' equity Current liabilities AP $ 880 Accrued liabilities 225 Income taxes payable 110 DIT liabilities-current 85 Capital lease obligation-current portion - Bonds-current portion 100 Notes and loans payable-current 250 Other current liabilities (B) (C) (D) (E) (F) (G) (H) As illustrated near the end of the A602 Learning Topic 4 Background Paper, this workbook tab presents a common methodology for reflecting the analysis adjustments identified in the BS (and, in the following tab, the IS) of a company so that an analyst may conveniently, accurately, and clearly use adjusted financial statement data as the basis for financial ratio analysis; common size analysis; projected FS; and prospective analysis of residual income 105and fundamental (intrinsic) 5 equity valuation. As necessary, modify this worksheet tab to accomodate the required analysis adjustments for a particular company. MAKE SURE ALL FORMULAS ARE WORKING AS NEEDED OR INDICATED. 72 25 50 Total current liabilities 1,700 Notes and loans payable-noncurrent 72 105 520 Capital lease obligation-noncurrent - Bonds, net discount [or, plus premium] 325 DIT liabilities-noncurrent 125 Other noncurrent liabilities 25 5 - - - - 200 728 30 Total noncurrent liabilities 1,000 728 - 200 - - - - - Total liabilities 2,700 800 105 225 5 - - - - Preferred (or preference) stock 230 Premium on preferred stock 25 Common stock (par or stated value) 50 Additional paid-in capital (APIC) 2,215 Less: Treasury stock (270) Unearned stock-based compensation (65) Retained earnings 1,200 Noncontrolling interest in consolid subsid (120) Accum other comprehensive income (loss) (265) Total stockholders' equity Total liabilities and stockholders' equity CHECK: A - (L + E) (S/B -0-) Explanations for Analysis Adjustments $ (5) 3,000 - - - (5) - - - - 5,700 800 105 225 - - - - - - - - - - - - - - As necessary, modify these analysis adjustment explanations for the particular company analyzed and its particular facts and circumstances. (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB expense (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring charges 2013 INPUT US dollar amounts IN Amounts in blue font and shading are Adjusted illustrative only. Input all dollar amounts in MILLIONS, NOT thousands balance Assets Current assets Cash and cash equivalents $ 370 Investment securities AFS 160 AR, gross 650 Less: Allowance for bad debts (30) AR, net 620 Inventories, at LOCOM using . . . ? 1,080 DIT assets-current 115 Other current assets 125 Total current assets 2,470 PP&E, at cost 4,235 Less: Accumulated depreciation of PP&E (720) PP&E, net 3,515 Goodwill, net impairment write-downs 545 Other intangibles, net amort., impairment 65 DIT assets-noncurrent 125 Other noncurrent assets 110 Total assets $ 6,830 As illustrated near the end of the A602 Learning T tab presents a common methodology for reflectin the BS (and, in the following tab, the IS) of a com conveniently, accurately, and clearly use adjusted financial ratio analysis; common size analysis; pr residual income and fundamental (intrinsic) equit worksheet tab to accomodate the required analy company. MAKE SURE ALL FORMULAS ARE W 2013 INPUT US dollar amounts IN Amounts in blue font and shading are Adjusted illustrative only. Input all dollar amounts in MILLIONS, NOT thousands balance Liabilities and stockholders' equity Current liabilities AP $ 880 Accrued liabilities 335 Income taxes payable 110 DIT liabilities-current 85 Capital lease obligation-current portion 72 Bonds-current portion 100 Notes and loans payable-current 275 Other current liabilities 50 Total current liabilities 1,907 Notes and loans payable-noncurrent 720 Capital lease obligation-noncurrent 728 Bonds, net discount [or, plus premium] 325 DIT liabilities-noncurrent 125 Other noncurrent liabilities 30 Total noncurrent liabilities 1,928 Total liabilities 3,835 Preferred (or preference) stock 230 Premium on preferred stock 25 Common stock (par or stated value) 50 Additional paid-in capital (APIC) 2,215 Less: Treasury stock (270) Unearned stock-based compensation (65) Retained earnings 1,195 Noncontrolling interest in consolid subsid (120) Accum other comprehensive income (loss) (265) Total stockholders' equity Total liabilities and stockholders' equity CHECK: A - (L + E) (S/B -0-) Explanations for Analysis Adjustments As illustrated near the end of the A602 Learning T tab presents a common methodology for reflectin the BS (and, in the following tab, the IS) of a com conveniently, accurately, and clearly use adjusted financial ratio analysis; common size analysis; pr residual income and fundamental (intrinsic) equit worksheet tab to accomodate the required analy company. MAKE SURE ALL FORMULAS ARE W 2,995 $ 6,830 As necessary, modify these analysis adjustment explanations particular facts and circumstances. (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB exp (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring c INPUT US dollar amounts IN 2014 Amounts in blue font and shading are illustrative only. Input all dollar amounts inAudited MILLIONS, NOT thousands balance Assets Current assets Cash and cash equivalents $ 370 Investment securities AFS 160 AR, gross 650 Less: Allowance for bad debts (30) AR, net 620 Inventories, at LOCOM using . . . ? 975 DIT assets-current 115 Other current assets 125 Analysis Adjustment (See explanations below) (A) (B) - (E) (F) (G) (H) - - - - - - - - - - - - - 105 2,365 - PP&E, at cost 3,210 800 105 225 (720) PP&E, net 2,490 Goodwill, net impairment write-downs 800 - 225 - - - - - 800 105 225 - - - - - 545 Other intangibles, net amort., impairment 65 DIT assets-noncurrent 125 Other noncurrent assets 110 Total assets (D) As illustrated near the end of the A602 Learning Topic 4 Background Paper, this workbook tab presents a common methodology for reflecting the analysis adjustments identified in the BS (and, in the following tab, the IS) of a company so that an analyst may conveniently, accurately, and clearly use adjusted financial statement data as the basis for financial ratio analysis; common size analysis; projected FS; and prospective analysis of residual income and fundamental (intrinsic) equity valuation. As necessary, modify this worksheet tab to accomodate the required analysis adjustments for a particular company. MAKE SURE ALL FORMULAS ARE WORKING AS NEEDED OR INDICATED. Total current assets Less: Accumulated depreciation of PP&E (C) $ 5,700 INPUT US dollar amounts IN 2014 Amounts in blue font and shading are illustrative only. Input all dollar amounts inAudited MILLIONS, NOT thousands balance Analysis Adjustment (See explanations below) (A) Liabilities and stockholders' equity Current liabilities AP $ 880 Accrued liabilities 225 Income taxes payable 110 DIT liabilities-current 85 Capital lease obligation-current portion - Bonds-current portion 100 Notes and loans payable-current 250 Other current liabilities (B) (C) (D) (E) (F) (G) (H) As illustrated near the end of the A602 Learning Topic 4 Background Paper, this workbook tab presents a common methodology for reflecting the analysis adjustments identified in the BS (and, in the following tab, the IS) of a company so that an analyst may conveniently, accurately, and clearly use adjusted financial statement data as the basis for financial ratio analysis; common size analysis; projected FS; and prospective analysis of residual income 105and fundamental (intrinsic) 5 equity valuation. As necessary, modify this worksheet tab to accomodate the required analysis adjustments for a particular company. MAKE SURE ALL FORMULAS ARE WORKING AS NEEDED OR INDICATED. 72 25 50 Total current liabilities 1,700 Notes and loans payable-noncurrent 72 105 520 Capital lease obligation-noncurrent - Bonds, net discount [or, plus premium] 325 DIT liabilities-noncurrent 125 Other noncurrent liabilities 25 5 - - - - 200 728 30 Total noncurrent liabilities 1,000 728 - 200 - - - - - Total liabilities 2,700 800 105 225 5 - - - - Preferred (or preference) stock 230 Premium on preferred stock 25 Common stock (par or stated value) 50 Additional paid-in capital (APIC) 2,215 Less: Treasury stock (270) Unearned stock-based compensation (65) Retained earnings 1,200 Noncontrolling interest in consolid subsid (120) Accum other comprehensive income (loss) (265) Total stockholders' equity Total liabilities and stockholders' equity CHECK: A - (L + E) (S/B -0-) Explanations for Analysis Adjustments $ (5) 3,000 - - - (5) - - - - 5,700 800 105 225 - - - - - - - - - - - - - - As necessary, modify these analysis adjustment explanations for the particular company analyzed and its particular facts and circumstances. (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB expense (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring charges 2014 INPUT US dollar amounts IN Amounts in blue font and shading are Adjusted illustrative only. Input all dollar amounts in MILLIONS, NOT thousands balance Assets Current assets Cash and cash equivalents $ 370 Investment securities AFS 160 AR, gross 650 Less: Allowance for bad debts (30) AR, net 620 Inventories, at LOCOM using . . . ? 1,080 DIT assets-current 115 Other current assets 125 Total current assets 2,470 PP&E, at cost 4,235 Less: Accumulated depreciation of PP&E (720) PP&E, net 3,515 Goodwill, net impairment write-downs 545 Other intangibles, net amort., impairment 65 DIT assets-noncurrent 125 Other noncurrent assets 110 Total assets $ 6,830 As illustrated near the end of the A602 Learning T tab presents a common methodology for reflectin the BS (and, in the following tab, the IS) of a com conveniently, accurately, and clearly use adjusted financial ratio analysis; common size analysis; pr residual income and fundamental (intrinsic) equit worksheet tab to accomodate the required analy company. MAKE SURE ALL FORMULAS ARE W 2014 INPUT US dollar amounts IN Amounts in blue font and shading are Adjusted illustrative only. Input all dollar amounts in MILLIONS, NOT thousands balance Liabilities and stockholders' equity Current liabilities AP $ 880 Accrued liabilities 335 Income taxes payable 110 DIT liabilities-current 85 Capital lease obligation-current portion 72 Bonds-current portion 100 Notes and loans payable-current 275 Other current liabilities 50 Total current liabilities 1,907 Notes and loans payable-noncurrent 720 Capital lease obligation-noncurrent 728 Bonds, net discount [or, plus premium] 325 DIT liabilities-noncurrent 125 Other noncurrent liabilities 30 Total noncurrent liabilities 1,928 Total liabilities 3,835 Preferred (or preference) stock 230 Premium on preferred stock 25 Common stock (par or stated value) 50 Additional paid-in capital (APIC) 2,215 Less: Treasury stock (270) Unearned stock-based compensation (65) Retained earnings 1,195 Noncontrolling interest in consolid subsid (120) Accum other comprehensive income (loss) (265) Total stockholders' equity Total liabilities and stockholders' equity CHECK: A - (L + E) (S/B -0-) Explanations for Analysis Adjustments As illustrated near the end of the A602 Learning T tab presents a common methodology for reflectin the BS (and, in the following tab, the IS) of a com conveniently, accurately, and clearly use adjusted financial ratio analysis; common size analysis; pr residual income and fundamental (intrinsic) equit worksheet tab to accomodate the required analy company. MAKE SURE ALL FORMULAS ARE W 2,995 $ 6,830 As necessary, modify these analysis adjustment explanations particular facts and circumstances. (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB exp (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring c INPUT US dollar amounts IN 2015 Amounts in blue font and shading are illustrative only. Input all dollar amounts inAudited MILLIONS, NOT thousands balance Assets Current assets Cash and cash equivalents $ 370 Investment securities AFS 160 AR, gross 650 Less: Allowance for bad debts (30) AR, net 620 Inventories, at LOCOM using . . . ? 975 DIT assets-current 115 Other current assets 125 Analysis Adjustment (See explanations below) (A) (B) - (E) (F) (G) (H) - - - - - - - - - - - - - 105 2,365 - PP&E, at cost 3,210 800 105 225 (720) PP&E, net 2,490 Goodwill, net impairment write-downs 800 - 225 - - - - - 800 105 225 - - - - - 545 Other intangibles, net amort., impairment 65 DIT assets-noncurrent 125 Other noncurrent assets 110 Total assets (D) As illustrated near the end of the A602 Learning Topic 4 Background Paper, this workbook tab presents a common methodology for reflecting the analysis adjustments identified in the BS (and, in the following tab, the IS) of a company so that an analyst may conveniently, accurately, and clearly use adjusted financial statement data as the basis for financial ratio analysis; common size analysis; projected FS; and prospective analysis of residual income and fundamental (intrinsic) equity valuation. As necessary, modify this worksheet tab to accomodate the required analysis adjustments for a particular company. MAKE SURE ALL FORMULAS ARE WORKING AS NEEDED OR INDICATED. Total current assets Less: Accumulated depreciation of PP&E (C) $ 5,700 INPUT US dollar amounts IN 2015 Amounts in blue font and shading are illustrative only. Input all dollar amounts inAudited MILLIONS, NOT thousands balance Analysis Adjustment (See explanations below) (A) Liabilities and stockholders' equity Current liabilities AP $ 880 Accrued liabilities 225 Income taxes payable 110 DIT liabilities-current 85 Capital lease obligation-current portion - Bonds-current portion 100 Notes and loans payable-current 250 Other current liabilities (B) (C) (D) (E) (F) (G) (H) As illustrated near the end of the A602 Learning Topic 4 Background Paper, this workbook tab presents a common methodology for reflecting the analysis adjustments identified in the BS (and, in the following tab, the IS) of a company so that an analyst may conveniently, accurately, and clearly use adjusted financial statement data as the basis for financial ratio analysis; common size analysis; projected FS; and prospective analysis of residual income 105and fundamental (intrinsic) 5 equity valuation. As necessary, modify this worksheet tab to accomodate the required analysis adjustments for a particular company. MAKE SURE ALL FORMULAS ARE WORKING AS NEEDED OR INDICATED. 72 25 50 Total current liabilities 1,700 Notes and loans payable-noncurrent 72 105 520 Capital lease obligation-noncurrent - Bonds, net discount [or, plus premium] 325 DIT liabilities-noncurrent 125 Other noncurrent liabilities 25 5 - - - - 200 728 30 Total noncurrent liabilities 1,000 728 - 200 - - - - - Total liabilities 2,700 800 105 225 5 - - - - Preferred (or preference) stock 230 Premium on preferred stock 25 Common stock (par or stated value) 50 Additional paid-in capital (APIC) 2,215 Less: Treasury stock (270) Unearned stock-based compensation (65) Retained earnings 1,200 Noncontrolling interest in consolid subsid (120) Accum other comprehensive income (loss) (265) Total stockholders' equity Total liabilities and stockholders' equity CHECK: A - (L + E) (S/B -0-) Explanations for Analysis Adjustments $ (5) 3,000 - - - (5) - - - - 5,700 800 105 225 - - - - - - - - - - - - - - As necessary, modify these analysis adjustment explanations for the particular company analyzed and its particular facts and circumstances. (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB expense (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring charges 2015 INPUT US dollar amounts IN Amounts in blue font and shading are Adjusted illustrative only. Input all dollar amounts in MILLIONS, NOT thousands balance Assets Current assets Cash and cash equivalents $ 370 Investment securities AFS 160 AR, gross 650 Less: Allowance for bad debts (30) AR, net 620 Inventories, at LOCOM using . . . ? 1,080 DIT assets-current 115 Other current assets 125 Total current assets 2,470 PP&E, at cost 4,235 Less: Accumulated depreciation of PP&E (720) PP&E, net 3,515 Goodwill, net impairment write-downs 545 Other intangibles, net amort., impairment 65 DIT assets-noncurrent 125 Other noncurrent assets 110 Total assets $ 6,830 As illustrated near the end of the A602 Learning T tab presents a common methodology for reflectin the BS (and, in the following tab, the IS) of a com conveniently, accurately, and clearly use adjusted financial ratio analysis; common size analysis; pr residual income and fundamental (intrinsic) equit worksheet tab to accomodate the required analy company. MAKE SURE ALL FORMULAS ARE W 2015 INPUT US dollar amounts IN Amounts in blue font and shading are Adjusted illustrative only. Input all dollar amounts in MILLIONS, NOT thousands balance Liabilities and stockholders' equity Current liabilities AP $ 880 Accrued liabilities 335 Income taxes payable 110 DIT liabilities-current 85 Capital lease obligation-current portion 72 Bonds-current portion 100 Notes and loans payable-current 275 Other current liabilities 50 Total current liabilities 1,907 Notes and loans payable-noncurrent 720 Capital lease obligation-noncurrent 728 Bonds, net discount [or, plus premium] 325 DIT liabilities-noncurrent 125 Other noncurrent liabilities 30 Total noncurrent liabilities 1,928 Total liabilities 3,835 Preferred (or preference) stock 230 Premium on preferred stock 25 Common stock (par or stated value) 50 Additional paid-in capital (APIC) 2,215 Less: Treasury stock (270) Unearned stock-based compensation (65) Retained earnings 1,195 Noncontrolling interest in consolid subsid (120) Accum other comprehensive income (loss) (265) Total stockholders' equity Total liabilities and stockholders' equity CHECK: A - (L + E) (S/B -0-) Explanations for Analysis Adjustments As illustrated near the end of the A602 Learning T tab presents a common methodology for reflectin the BS (and, in the following tab, the IS) of a com conveniently, accurately, and clearly use adjusted financial ratio analysis; common size analysis; pr residual income and fundamental (intrinsic) equit worksheet tab to accomodate the required analy company. MAKE SURE ALL FORMULAS ARE W 2,995 $ 6,830 As necessary, modify these analysis adjustment explanations particular facts and circumstances. (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB exp (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring c INPUT US dollar amounts IN Amounts in blue font and shading are Adjusted balances (used for financial analysis) illustrative only. Input all dollar amounts in MILLIONS, NOT thousands 2011 2012 2013 2014 2015 Assets Current assets Cash and cash equivalents $ 370 $ As illustrated near the end of the A602 Learning Topic 4 Background Paper, t tab presents a common methodology for reflecting the analysis adjustment the BS (and, in the following tab, the IS) of a company so that an analyst ma conveniently, use adjusted 370 $ accurately, 370 $ and clearly 370 $ 370 financial statement data as financial ratio analysis; common size analysis; projected FS; and prospective residual and fundamental equity valuation. As necessary, m 160 income 160 160(intrinsic) 160 worksheet tab to accomodate the required analysis adjustments for a partic 650 650 650WORKING AS NEEDED OR company. MAKE650 SURE ALL FORMULAS ARE Investment securities AFS 160 AR, gross 650 Less: Allowance for bad debts (30) (30) (30) (30) (30) AR, net 620 620 620 620 620 1,080 1,080 1,080 1,080 1,080 DIT assets-current 115 115 115 115 115 Other current assets 125 125 125 125 125 Total current assets 2,470 2,470 2,470 2,470 2,470 PP&E, at cost 4,235 4,235 4,235 4,235 4,235 (720) (720) (720) (720) (720) 3,515 3,515 3,515 3,515 3,515 545 545 545 545 545 65 65 65 65 65 DIT assets-noncurrent 125 125 125 125 125 Other noncurrent assets 110 110 110 110 110 Inventories, at LOCOM using . . . ? Less: Accumulated depreciation of PP&E PP&E, net Goodwill, net impairment write-downs Other intangibles, net amort., impairment Total assets $ 6,830 $ 6,830 $ 6,830 $ 6,830 $ 6,830 INPUT US dollar amounts IN Amounts in blue font and shading are Adjusted balances (used for financial analysis) illustrative only. Input all dollar amounts in MILLIONS, NOT thousands 2011 2012 2013 2014 2015 Liabilities and stockholders' equity Current liabilities AP $ 880 $ As illustrated near the end of the A602 Learning Topic 4 Background Paper, t tab presents a common methodology for reflecting the analysis adjustment the BS (and, in the following tab, the IS) of a company so that an analyst ma conveniently, use adjusted 880 $ accurately, 880 $ and clearly 880 $ 880 financial statement data as financial ratio analysis; common size analysis; projected FS; and prospective residual and fundamental equity valuation. As necessary, m 335 income 335 335(intrinsic) 335 worksheet tab to accomodate the required analysis adjustments for a partic 110 110 110WORKING AS NEEDED OR company. MAKE110 SURE ALL FORMULAS ARE Accrued liabilities 335 Income taxes payable 110 DIT liabilities-current 85 85 85 85 85 Capital lease obligation-current portion 72 72 72 72 72 Bonds-current portion 100 100 100 100 100 Notes and loans payable-current 275 275 275 275 275 50 50 50 50 50 1,907 1,907 1,907 1,907 1,907 Notes and loans payable-noncurrent 720 720 720 720 720 Capital lease obligation-noncurrent 728 728 728 728 728 Bonds, net discount [or, plus premium] 325 325 325 325 325 DIT liabilities-noncurrent 125 125 125 125 125 30 30 30 30 30 Total noncurrent liabilities 1,928 1,928 1,928 1,928 1,928 Total liabilities 3,835 230 3,835 230 3,835 230 3,835 230 3,835 230 Premium on preferred stock 25 25 25 25 25 Common stock (par or stated value) 50 50 50 50 50 2,215 2,215 2,215 2,215 2,215 (270) (270) (270) (270) (270) (65) (65) (65) (65) (65) 1,195 1,195 1,195 1,195 1,195 Noncontrolling interest in consolid subsid (120) (120) (120) (120) (120) Accum other comprehensive income (loss) (265) (265) (265) (265) (265) Total stockholders' equity 2,995 2,995 2,995 2,995 2,995 Total liabilities and stockholders' equity 6,830 6,830 6,830 6,830 6,830 - - - - - Other current liabilities Total current liabilities Other noncurrent liabilities Preferred (or preference) stock Additional paid-in capital (APIC) Less: Treasury stock Unearned stock-based compensation Retained earnings CHECK: A - (L + E) (S/B -0-) Explanations for Analysis Adjustments As necessary, modify these analysis adjustment explanations for the particular company a particular facts and circumstances. (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB expense (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring charges INPUT US dollar amounts IN 2011 Amounts in blue font and shading are Audited illustrative only. Input all dollar amounts in balance MILLIONS, NOT thousands Sales, net returns and allowances $ 8,900 COS (FIFO?LIFO?Avg cost assumption) 5,340 Gross profit 3,560 Operating expenses SG&A expenses (excl D&A and advertising) 900 R&D expense 450 Advertising expense 190 D&A 250 Other operating expenses (income) 270 Analysis Adjustment (See explanations below) (A) (B) (C) (D) (E) (F) (G) (H) As illustrated near the end of the A602 Learning Topic 4 Background Paper, this workbook tab presents a common methodology for reflecting the analysis adjustments identified in the 100 IS (and, in the preceding tab, the BS) of a company so that an analyst may conveniently, accurately, and clearly use adjusted financial statement data as the basis for financial ratio (100) - of residual - income analysis; common size analysis; projected FS; and prospective analysis and fundamental (intrinsic) equity valuation. As necessary, modify this worksheet tab to accomodate the required analysis adjustments for a particular company. MAKE SURE ALL FORMULAS ARE WORKING AS NEEDED OR INDICATED. (120) (20) 80 Total operating expenses 2,060 (40) Operating income 1,500 40 (100) (48) Even if your group's company reports amounts in its FS in thousands of dollars, enter all amounts in million dollars in this workbook (e.g., $123,456,000.00 becomes $123.456 in this spreadsheet). Amounts still display in the workbook as "whole millions," which is fine, but some totals may appear not to "add up" (or \"foot\") properly, which is acceptable. So, do NOT reformat cells to display the decimal place. - now widened (20) will make many That's would be too distracting, and columns schedules/tables too compressed to fit legibly in the group project report (MS Word (100) 20 template). Other income (expense) Interest expense, net of interest income (20) (15) Gain (loss) on spin-off of subsidiary 30 (30) Gain (loss) on debt restruct/early extinguish 20 (20) Gain (loss) on disposal of invest sec's AFS 30 (30) Gain (loss) on disposal of PP&E (40) 40 Impairment loss on PP&E (90) 80 Impairment loss on goodwill (60) 60 Impairment of other intangibles (40) 35 Restructuring charge (80) 50 Total other income (expense) (330) (48) - - - - (20) (15) 185 1,170 (8) - - - (100) - (15) 185 Taxes on income from continuing operations 480 (3) - - - (40) - (6) 44 Income from contin oper incl noncontrol int's 690 (5) - - - (60) - (9) 141 20 - - - - - - - - Income from continuing operations 670 (5) - - - (60) - (9) 141 Gain (loss) on discont'd oper's, net of tax (60) Income before extraordinary items 610 Extraordinary gain (loss), net of tax (30) Disr $ 580 Disr $ 1,170 (8) - - - (100) - (15) 185 (100) (48) - - - - (20) (15) - 1,270 40 - - - (100) 20 - 185 Income from cont. oper's before taxes Less: Income attrib to noncontrol interests Net income Disr For ratios that call for "net income" (e.g., ROCE) use "income from continuing operations (after tax)," instead Disr Before-tax amounts from above: Income from cont. oper's before taxes Interest expense, net Operating profit before income taxes Explanations for Analysis Adjustments $ As necessary, modify these analysis adjustment explanations for the particular company analyzed and its particular facts and circumstances. (A) Capitalize operating lease commitments (see balance sheet for related adjustments) (B) See balance sheet for adjustment to recognize oblig's under noncancellable, fixed-price vendor supply agreements (C) See balance sheet for adjustment to recognize guaranteed debt of unconsolidated affiliated entities (D) See balance sheet for adjustment to recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust FIFO-basis COS for "inflation (holding) profits" (NO balance sheet adjustment needed) (F) Reclassify financial elements of DB pension and OPEB expense (NO balance sheet adjustment needed) (G) Recognize capitalized interest as financial expense (NO balance sheet adjustment needed) (H) Recast or eliminate asset impairment and restructuring charges, nonoperating gains and losses (NO balance sheet adjustment needed) (No tax effect included on analysis adjustments to spin-off gain, goodwill impairment loss, or other intangible asset impairment loss) 2011 INPUT US dollar amounts IN Amounts in blue font and shading are Adjusted illustrative only. Input all dollar amounts in balance MILLIONS, NOT thousands Sales, net returns and allowances $ 8,900 COS (FIFO?LIFO?Avg cost assumption) 5,440 Gross profit 3,460 Operating expenses SG&A expenses (excl D&A and advertising) 760 R&D expense 450 Advertising expense 190 Even if your group's company reports amounts in its FS in thousands of dollars, enter all amounts in million dollars inD&A this workbook (e.g., $123,456,000.00 becomes $123.456 in this spreadsheet). Amounts still display in the workbook as "whole millions," which is fine, but some Other totals may appearexpenses not to "add (income) up" (or \"foot\") properly, operating which is acceptable. So, do NOT reformat cells to display the decimal place. Total operating expenses That's would be too distracting, and now widened columns will make many schedules/tables too compressed to fit legibly in the group project report (MS Word Operating income template). balances summarized fo As illustrated Adjusted near the end of the are A602 Learning To of usemethodology by a group for ratio an tab presents aease common forits reflecting IS (and, in thetabs. preceding tab, the BS) of a company accurately, and clearly use adjusted financial statem analysis; common size analysis; projected FS; and and fundamental (intrinsic) equity valuation. As nec accomodate the required analysis adjustments for a FORMULAS ARE WORKING AS NEEDED OR IND 330 270 2,000 1,460 Other income (expense) Interest expense, net of interest income (183) Gain (loss) on spin-off of subsidiary - Gain (loss) on debt restruct/early extinguish - Gain (loss) on disposal of invest sec's AFS - Gain (loss) on disposal of PP&E - Impairment loss on PP&E (10) Impairment loss on goodwill - Impairment of other intangibles (5) Restructuring charge (30) Total other income (expense) (228) Income from cont. oper's before taxes 1,232 Taxes on income from continuing operations 475 Income from contin oper incl noncontrol int's 757 Less: Income attrib to noncontrol interests Income from continuing operations 20 $ 737 Gain (loss) on discont'd oper's, net of tax Disregard Income before extraordinary items Disregard Extraordinary gain (loss), net of tax Disregard Net income Disregard For ratios that call for "net income" (e.g., ROCE) use "income from continuing operations (after tax)," instead Before-tax amounts from above: Income from cont. oper's before taxes $ Interest expense, net Operating profit before income taxes Explanations for Analysis Adjustments 1,232 (183) $ 1,415 As necessary, modify these analysis adjustment explanations fo particular facts and circumstances. (A) Capitalize operating lease commitments (see balance sheet for related adjustments) (B) See balance sheet for adjustment to recognize oblig's under noncancellable, fixed-price vendor sup (C) See balance sheet for adjustment to recognize guaranteed debt of unconsolidated affiliated entitie (D) See balance sheet for adjustment to recognize undeclared prior-year dividends on cumulative pre (E) Adjust FIFO-basis COS for "inflation (holding) profits" (NO balance sheet adjustment needed) (F) Reclassify financial elements of DB pension and OPEB expense (NO balance sheet adjustment nee (G) Recognize capitalized interest as financial expense (NO balance sheet adjustment needed) estructuring charges, nonoperating gains and (H) losses Recast (NOor balance eliminate sheet asset adjustment impairment needed) and restructuring charges, nonoperating gains and losse (No tax effect included on analysis adjustments to spin-off gain, goodwill impairment loss, or other intan INPUT US dollar amounts IN 2012 Amounts in blue font and shading are Audited illustrative only. Input all dollar amounts in balance MILLIONS, NOT thousands Sales, net returns and allowances $ 8,900 COS (FIFO?LIFO?Avg cost assumption) 5,340 Gross profit 3,560 Operating expenses SG&A expenses (excl D&A and advertising) 900 R&D expense 450 Advertising expense 190 D&A 250 Other operating expenses (income) 270 Analysis Adjustment (See explanations below) (A) (B) (C) (D) (E) (F) (G) (H) Adjusted balances are end summarized for Learning all 5 FYs Topic at the 4far right for Paper, this workbook As illustrated near the of the A602 Background ease of use by a group for its ratio analysis, etc., in the other worksheet tab presents a common methodology for reflecting analysis adjustments identified in the 100 tabs. IS (and, in the preceding tab, the BS) of a company so that an analyst may conveniently, accurately, and clearly use adjusted financial statement data as the basis for financial ratio - common size - analysis;- projected FS; - and prospective (100) - of residual - income analysis; analysis and fundamental (intrinsic) equity valuation. As necessary, modify this worksheet tab to accomodate the required analysis adjustments for a particular company. MAKE SURE ALL FORMULAS ARE WORKING AS NEEDED OR INDICATED. (120) (20) 80 Total operating expenses 2,060 (40) - - - - (20) - - Operating income 1,500 40 - - - (100) 20 - - (100) (48) (20) (15) Other income (expense) Interest expense, net of interest income Gain (loss) on spin-off of subsidiary 60 (60) Gain (loss) on debt restruct/early extinguish 20 (20) Gain (loss) on disposal of invest sec's AFS 30 (30) Gain (loss) on disposal of PP&E (40) 40 Impairment loss on PP&E (90) 80 Impairment loss on goodwill (60) 60 Impairment of other intangibles (40) 35 Restructuring charge (80) 50 Total other income (expense) (300) (48) - - - - (20) (15) 155 1,200 (8) - - - (100) - (15) 155 Taxes on income from continuing operations 480 (3) - - - (40) - (6) 44 Income from contin oper incl noncontrol int's 720 (5) - - - (60) - (9) 111 20 - - - - - - - - Income from continuing operations 700 (5) - - - (60) - (9) 111 Gain (loss) on discont'd oper's, net of tax (60) Income before extraordinary items 640 Extraordinary gain (loss), net of tax (30) Disr $ 610 Disr $ 1,200 (8) - - - (100) - (15) 155 (100) (48) - - - - (20) (15) - 1,300 40 - - - (100) 20 - 155 Income from cont. oper's before taxes Less: Income attrib to noncontrol interests Net income Disr For ratios that call for "net income" (e.g., ROCE) use "income from continuing operations (after tax)," instead Disr Before-tax amounts from above: Income from cont. oper's before taxes Interest expense, net Operating profit before income taxes Explanations for Analysis Adjustments $ As necessary, modify these analysis adjustment explanations for the particular company analyzed and its particular facts and circumstances. (A) Capitalize operating lease commitments (see balance sheet for related adjustments) (B) See balance sheet for adjustment to recognize oblig's under noncancellable, fixed-price vendor supply agreements (C) See balance sheet for adjustment to recognize guaranteed debt of unconsolidated affiliated entities (D) See balance sheet for adjustment to recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust FIFO-basis COS for "inflation (holding) profits" (NO balance sheet adjustment needed) (F) Reclassify financial elements of DB pension and OPEB expense (NO balance sheet adjustment needed) (G) Recognize capitalized interest as financial expense (NO balance sheet adjustment needed) (H) Recast or eliminate asset impairment and restructuring charges, nonoperating gains and losses (NO balance sheet adjustment needed) (No tax effect included on analysis adjustments to spin-off gain, goodwill impairment loss, or other intangible asset impairment loss) 2012 INPUT US dollar amounts IN Amounts in blue font and shading are Adjusted illustrative only. Input all dollar amounts in balance MILLIONS, NOT thousands Sales, net returns and allowances $ 8,900 COS (FIFO?LIFO?Avg cost assumption) 5,440 Gross profit 3,460 Operating expenses SG&A expenses (excl D&A and advertising) 760 R&D expense 450 Advertising expense 190 D&A 330 Other operating expenses (income) 270 Total operating expenses 2,000 Operating income 1,460 As illustrated near the end of the A602 Learning To tab presents a common methodology for reflecting IS (and, in the preceding tab, the BS) of a company accurately, and clearly use adjusted financial statem analysis; common size analysis; projected FS; and and fundamental (intrinsic) equity valuation. As nec accomodate the required analysis adjustments for a FORMULAS ARE WORKING AS NEEDED OR IND Other income (expense) Interest expense, net of interest income (183) Gain (loss) on spin-off of subsidiary - Gain (loss) on debt restruct/early extinguish - Gain (loss) on disposal of invest sec's AFS - Gain (loss) on disposal of PP&E - Impairment loss on PP&E (10) Impairment loss on goodwill - Impairment of other intangibles (5) Restructuring charge (30) Total other income (expense) (228) Income from cont. oper's before taxes 1,232 Taxes on income from continuing operations 475 Income from contin oper incl noncontrol int's 757 Less: Income attrib to noncontrol interests Income from continuing operations 20 $ 737 Gain (loss) on discont'd oper's, net of tax Disregard Income before extraordinary items Disregard Extraordinary gain (loss), net of tax Disregard Net income Disregard For ratios that call for "net income" (e.g., ROCE) use "income from continuing operations (after tax)," instead Before-tax amounts from above: Income from cont. oper's before taxes $ Interest expense, net Operating profit before income taxes Explanations for Analysis Adjustments 1,232 (183) $ 1,415 As necessary, modify these analysis adjustment explanations fo particular facts and circumstances. (A) Capitalize operating lease commitments (see balance sheet for related adjustments) (B) See balance sheet for adjustment to recognize oblig's under noncancellable, fixed-price vendor sup (C) See balance sheet for adjustment to recognize guaranteed debt of unconsolidated affiliated entitie (D) See balance sheet for adjustment to recognize undeclared prior-year dividends on cumulative pre (E) Adjust FIFO-basis COS for "inflation (holding) profits" (NO balance sheet adjustment needed) (F) Reclassify financial elements of DB pension and OPEB expense (NO balance sheet adjustment nee (G) Recognize capitalized interest as financial expense (NO balance sheet adjustment needed) (H) Recast or eliminate asset impairment and restructuring charges, nonoperating gains and losse (No tax effect included on analysis adjustments to spin-off gain, goodwill impairment loss, or other intan INPUT US dollar amounts IN 2013 Amounts in blue font and shading are Audited illustrative only. Input all dollar amounts in balance MILLIONS, NOT thousands Sales, net returns and allowances $ 8,900 COS (FIFO?LIFO?Avg cost assumption) 5,340 Gross profit 3,560 Operating expenses SG&A expenses (excl D&A and advertising) 900 R&D expense 450 Advertising expense 190 D&A 250 Other operating expenses (income) 270 Analysis Adjustment (See explanations below) (A) (B) (C) (D) (E) (F) (G) (H) As illustrated near the end of the A602 Learning Topic 4 Background Paper, this workbook tab presents a common methodology