The file is a Balance Sheet for The Ford Motor Company You need to format the file for the CEO to read, using the following guidelines: 1. All the data needs to remain within the rows that it exists in now. 2. Your 2010 numbers need to end up in column D, and your 2009 figures in column G. 3. Replace the figures in rows 20, 28, 38, 40 and 41 with formulas to insure that if the data is changed then the figures will calculate the correct totals 4. Use what you learned from the practice file to format the Assessment file. Use critical thinking skills to create a professional document 5. Answer the assessment questions based on the file you create. When asked for a figure, enter exactly what is in the cells on your file Question 1 1 pts What do you have in cell G39 of your file? December 31, 2009 $ 20,894 21,387 MPANY AND SUBSIDIARIES (TED BALANCE SHEET (in millions) December 31, 2010 ASSETS Cash and cash equivalen ts $ 14,805 Marketab le securities (Note 6) 20,765 Finance receivabl es, net (Note 7) 70,070 Other receivabl es, net 7,388 Net investme nt in operating leases (Note 8) 11,675 Inventori es (Note 10) 5,917 Equity in net assets of affiliated companie s (Note 11) 2,569 75,892 7,194 17,270 5,041 2,367 2,569 2,367 23,179 22,637 2,003 3,479 Equity in net assets of affiliated companie s (Note 11) Net property (Note 14) Deferred income taxes Net intangible assets (Note 16) Assets of held-for- sale operation (Note 24) Other assets Total assets 102 165 7,618 6,214 8,096 164,687 192,040 16,362 14,301 LIABILIT IES Payables s Accrued liabilities and deferred revenue (Note 17) Debt (Note 19) Deferred income taxes Liabilitie s of held- for-sale 43,844 46,144 103,988 131,635 1.135 2,421 Liabilitie s of held- for-sale operation s (Note 24) Total liabiliti es 5,321 165,329 199,822 EQUITY Capital stock (Note 25) Commo n Stock par value 30.01 per share (3,707 million shares issued of 6 billion authoriz ed) 37 33 Class B Stock, par value $0.01 per share (71 million shares issued of 530 million authoriz ed) 55 Class B Stock, par value S0.01 per share (71 million shares issued of 530 million authoriz ed) Capital in excess of par value of stock 20,803 16,786 (14,313 ) (10,864) (163) (177) Accumul ated other comprehe nsive income/ 055) Treasury stock Retained earnings/ Accumul ated deficit) Total equity/ deficit) attribut able to Ford Motor Compa ny (7,038 ) (13,599 ) (673) (7.820) (14,313 ) (10,864) comprehe nsive income/ oss) Treasury stock Retained eamings/ Accumul ated deficit) (163) (177) (7,038 (13,599 Total equity/ deficit) attribut able to Ford Motor Compa Equity/(D eficit) attributab le to noncontr olling interests (673) (7.820) 31 38 (642) (7.782 Total equity/ deficit) Total liabiliti es and equitys 164,687 192,040 The file is a Balance Sheet for The Ford Motor Company You need to format the file for the CEO to read, using the following guidelines: 1. All the data needs to remain within the rows that it exists in now. 2. Your 2010 numbers need to end up in column D, and your 2009 figures in column G. 3. Replace the figures in rows 20, 28, 38, 40 and 41 with formulas to insure that if the data is changed then the figures will calculate the correct totals 4. Use what you learned from the practice file to format the Assessment file. Use critical thinking skills to create a professional document 5. Answer the assessment questions based on the file you create. When asked for a figure, enter exactly what is in the cells on your file Question 1 1 pts What do you have in cell G39 of your file? December 31, 2009 $ 20,894 21,387 MPANY AND SUBSIDIARIES (TED BALANCE SHEET (in millions) December 31, 2010 ASSETS Cash and cash equivalen ts $ 14,805 Marketab le securities (Note 6) 20,765 Finance receivabl es, net (Note 7) 70,070 Other receivabl es, net 7,388 Net investme nt in operating leases (Note 8) 11,675 Inventori es (Note 10) 5,917 Equity in net assets of affiliated companie s (Note 11) 2,569 75,892 7,194 17,270 5,041 2,367 2,569 2,367 23,179 22,637 2,003 3,479 Equity in net assets of affiliated companie s (Note 11) Net property (Note 14) Deferred income taxes Net intangible assets (Note 16) Assets of held-for- sale operation (Note 24) Other assets Total assets 102 165 7,618 6,214 8,096 164,687 192,040 16,362 14,301 LIABILIT IES Payables s Accrued liabilities and deferred revenue (Note 17) Debt (Note 19) Deferred income taxes Liabilitie s of held- for-sale 43,844 46,144 103,988 131,635 1.135 2,421 Liabilitie s of held- for-sale operation s (Note 24) Total liabiliti es 5,321 165,329 199,822 EQUITY Capital stock (Note 25) Commo n Stock par value 30.01 per share (3,707 million shares issued of 6 billion authoriz ed) 37 33 Class B Stock, par value $0.01 per share (71 million shares issued of 530 million authoriz ed) 55 Class B Stock, par value S0.01 per share (71 million shares issued of 530 million authoriz ed) Capital in excess of par value of stock 20,803 16,786 (14,313 ) (10,864) (163) (177) Accumul ated other comprehe nsive income/ 055) Treasury stock Retained earnings/ Accumul ated deficit) Total equity/ deficit) attribut able to Ford Motor Compa ny (7,038 ) (13,599 ) (673) (7.820) (14,313 ) (10,864) comprehe nsive income/ oss) Treasury stock Retained eamings/ Accumul ated deficit) (163) (177) (7,038 (13,599 Total equity/ deficit) attribut able to Ford Motor Compa Equity/(D eficit) attributab le to noncontr olling interests (673) (7.820) 31 38 (642) (7.782 Total equity/ deficit) Total liabiliti es and equitys 164,687 192,040