This workbook contains some new data for you to practice what you learned in module 3....

 

 

 

Transcribed Image Text:

This workbook contains some new data for you to practice what you learned in module 3. Copy Sheet This sheet contains data that you will copy. Paste Values Paste the data from the copy sheet using the Paste Values command. Adjust the column widths to fully display the data. Paste Link Paste the data from the copy sheet using the Paste Link command. Clean up the worksheet to remove any formulas that reference blank cells. Adjust the column widths to fully display the data. Charts Create a 2D Clustered bar chart of the individual Assets for 2010 and 2011. Create a 3D Clustered column chart of the individual Liabilities for 2010 and 2011. Add chart titles (Assets and Liabilities, respectively) and include legends for 2010 and 2011. Place the charts next to the data at the top of the worksheet. Pivot Data Insert a pivot table and column pivot chart showing the total sales by category name. Change the pivot table headers to Category and Total Sales. Format the Total Sales as Currency. Make your pivot chart title "Total Sales by Category" and have no legend. Apple Data Create a xy spatter chart from the data on the Apple sheet. Add a trend line and calculate Beta and Slope for the data. BALANCE SHEETS (In millions) 2010 2011 Assets Current assets: Cash and cash equivalents Short-term investments (including securities loaned of $1,181 and $62) Total cash, cash equivalents, and short-term investments Accounts receivable, net of allowance for doubtful accounts of $333 and $375 5,505 9,610 31,283 43,162 36,788 52,772 13,014 14,987 Inventories 740 1,372 Deferred income taxes 2,184 2,950 2,467 Other 3,320 Total current assets 55,676 74,918 Property and equipment, net of accumulated depreciation of $9,829 and $8,629 Equity and other investments 7,630 8,162 7,754 12,394 10,865 Goodwill 12,581 Intangible assets, net 1,158 744 Other long-term assets 1,501 1,434 Total assets 86,113 108,704 Liabilities and stockholders' equity Current liabilities: Accounts payable 4,025 4,197 Short-term debt 1,000 Accrued compensation Income taxes 3,283 3,575 1,074 580 Short-term unearned revenue 13,652 15,722 Securities lending payable 182 1,208 Other 3,492 28,774 2,931 Total current liabilities 26,147 Long-term debt Long-term unearned revenue 4,939 11,921 1,178 1,398 Deferred income taxes 229 1,456 Other long-term liabilties 7,445 8,072 Total liabilities 39,938 51,621 Commitments and contingencies Stockholders' equity: Common stock and paid-in capital – shares authorized 24,000; outstanding 8,376 and 8,668 Retained deficit, including accumulated other comprehensive income of $1,863 and $1,055 Total stockholders' equity Total liabilities and stockholders' equity 62,856 63,415 -16,681 -6,332 57,083 108,704 46,175 86,113 BALANCE SHEETS (In millions) 2010 2011 Assets Current assets: Cash and cash equivalents 5,505 9,610 Short-term investments (including securities loaned of $1,181 and $62) Total cash, cash equivalents, and short-term investments 43,162 52,772 31,283 36,788 Accounts receivable, net of allowance for doubtful accounts of $333 and $375 13,014 14,987 Inventories 740 1,372 Deferred income taxes 2,184 2,467 Other 2,950 55,676 3,320 74,918 Total current assets Property and equipment, net of accumulated depreciation of $9,829 and $8,629 7,630 8,162 Equity and other investments 7,754 10,865 Goodwill 12,394 12,581 Intangible assets, net Other long-term assets 1,158 744 1,501 1,434 Total assets 86,113 108,704 Liabilities and stockholders' equity Current liabiities: Accounts payable 4,025 4,197 Short-term debt 1.000 Accrued compensation 3,283 3,575 Income taxes 1,074 13,652 580 Short-term unearned revenue 15,722 Securities lending payable Other 182 1,208 2,931 3,492 Total current liabilities 26,147 28,774 Long-term debt Long-term uneaned revenue 4,939 11,921 1,178 1,398 Deferred income taxes 229 1,456 Other long-term liabilities 7,445 8,072 51,621 Total liabilities 39,938 Commitments and contingencies Stockholders' equity: Common stock and paid-in capital – shares authorized 24,000; outstanding 8,376 and 8,668 Retained deficit, including accumulated other comprehensive income of $1,863 and $1,055 Total stockholders' equity 62,856 63,415 -16,681 -6,332 46,175 57,083 Total liabilities and stockholders' equity 86,113 108,704 CategoryID CategoryName ProductName Alice Mutton ProductSales 6 Meat/Poultry 2 Condiments 8 Seafood 4 Dairy Products Camembert Pierrot 14,123.85 Aniseed Syrup 1,544.00 9,796.33 22,413.48 Boston Crab Meat 8 Seafood Carnarvon Tigers 14,525.00 1 Beverages 1 Beverages 1 Beverages 2 Condiments 2 Condiments 3 Confections 1 Beverages 8 Seafood 5 Grains/Cereals Filo Mix 4 Dairy Products Fløtemysost 4 Dairy Products Geitost 2 Condiments 5 Grains/Cereals Gnocchi di nonna Alice Chai 5,070.60 Chang 5,817.80 Chartreuse verte 2,295.90 Chef Anton's Cajun Seasoning 5,214.88 917.62 Chef Anton's Gumbo Mix Chocolade 1,282.01 Côte de Blaye Escargots de Bourgogne 51,569.58 2,913.68 2,058.00 10,433.94 906.00 1,474.82 30,088.40 7,188.49 Genen Shouyu 4 Dairy Products Gorgonzola Telino 2 Condiments 8 Seafood 1 Beverages 2,500.00 Grandma's Boysenberry Spread Gravad lax 629.20 Guaraná Fantástica 1,571.62 4 Dairy Products Gudbrandsdalsost 14,041.80 Gula Malacca Gumbär Gummibärchen 2 Condiments 6,543.43 3 Confections 5 Grains/Cereals Gustaf's Knäckebröd 8 Seafood 8 Seafood 1 Beverages 11,225.65 4,023.60 8,720.30 Ikura Inlagd Sill Ipoh Coffee Jack's New England Clam Chowder 6,397.11 10,034.90 8 Seafood 4,322.72 8 Seafood 1 Beverages 1 Beverages 7 Produce 2 Condiments 2 Condiments 7 Produce 4 Dairy Products Mascarpone Fabioli 3 Confections 6 Meat/Poultry 4 Dairy Products Mozzarella di Giovanni 8 Seafood Konbu 941.34 Lakkalikööri Laughing Lumberjack Lager Longlife Tofu 7,883.10 868.00 1,186.00 Louisiana Fiery Hot Pepper Sauce Louisiana Hot Spiced Okra Manjimup Dried Apples 8,117.88 2,108.00 16,024.02 2,668.80 Maxilaku 3,060.60 Mishi Kobe Niku 6,935.50 12,427.38 Nord-Ost Matjeshering 5,573.62 8 Seafood Nord-Ost Matjeshering Northwoods Cranberry Sauce NuNuCa Nuß-Nougat-Creme 5,573.62 3,300.00 2 Condiments 3 Confections 1,058.40 Original Frankfurter grüne Soße Outback Lager 4,711.98 5,663.40 2 Condiments 1 Beverages 6 Meat/Poultry Pâté chinois 9,114.48 7,180.15 13,832.13 5,310.69 3 Confections Pavlova Perth Pasties 6 Meat/Poultry 4 Dairy Products 4 Dairy Products 4 Dairy Products 5 Grains/Cereals Ravioli Angelo Queso Cabrales Queso Manchego La Pastora 8,335.30 Raclette Courdavault 32,769.55 2,156.70 1 Beverages Rhönbräu Klosterbier 3,670.24 8 Seafood Röd Kaviar 3,517.50 Røgede sild Rössle Sauerkraut Sasquatch Ale Schoggi Schokolade Scottish Longbreads 5 Grains/Cereals Singaporean Hokkien Fried Mee Sir Rodney's Marmalade Sir Rodney's Scones 8 Seafood 3,203.40 7 Produce 1 Beverages 13,691.48 2,079.00 9,218.00 3 Confections 3 Confections 3,909.00 4,996.60 3 Confections 9,711.90 3 Confections 4,941.00 2 Condiments Sirop d'érable 8,521.50 8 Seafood Spegesild 3,655.32 1 Beverages Steeleye Stout 5,955.30 3 Confections Tarte au sucre 20,811.32 3 Confections 6 Meat/Poultry 7 Produce 6 Meat/Poultry 5 Grains/Cereals Tunnbröd 7 Produce 3 Confections Teatime Chocolate Biscuits 2,339.99 Thüringer Rostbratwurst 30,038.43 6,820.38 Tofu Tourtière 3,120.36 2,576.70 Uncle Bob's Organic Dried Pears 8,251.80 Valkoinen suklaa 2,368.44 2 Condiments Vegie-spread 6,087.69 5 Grains/Cereals Wimmers gute Semmelknödel 3 Confections 7,923.47 Zaanse koeken 2,645.75 What is the cell contents (not the value, what shows in the formula bar) of cell A2 on the Paste Link tab? Be specific, include all punctuation, if applicable. This workbook contains some new data for you to practice what you learned in module 3. Copy Sheet This sheet contains data that you will copy. Paste Values Paste the data from the copy sheet using the Paste Values command. Adjust the column widths to fully display the data. Paste Link Paste the data from the copy sheet using the Paste Link command. Clean up the worksheet to remove any formulas that reference blank cells. Adjust the column widths to fully display the data. Charts Create a 2D Clustered bar chart of the individual Assets for 2010 and 2011. Create a 3D Clustered column chart of the individual Liabilities for 2010 and 2011. Add chart titles (Assets and Liabilities, respectively) and include legends for 2010 and 2011. Place the charts next to the data at the top of the worksheet. Pivot Data Insert a pivot table and column pivot chart showing the total sales by category name. Change the pivot table headers to Category and Total Sales. Format the Total Sales as Currency. Make your pivot chart title "Total Sales by Category" and have no legend. Apple Data Create a xy spatter chart from the data on the Apple sheet. Add a trend line and calculate Beta and Slope for the data. BALANCE SHEETS (In millions) 2010 2011 Assets Current assets: Cash and cash equivalents Short-term investments (including securities loaned of $1,181 and $62) Total cash, cash equivalents, and short-term investments Accounts receivable, net of allowance for doubtful accounts of $333 and $375 5,505 9,610 31,283 43,162 36,788 52,772 13,014 14,987 Inventories 740 1,372 Deferred income taxes 2,184 2,950 2,467 Other 3,320 Total current assets 55,676 74,918 Property and equipment, net of accumulated depreciation of $9,829 and $8,629 Equity and other investments 7,630 8,162 7,754 12,394 10,865 Goodwill 12,581 Intangible assets, net 1,158 744 Other long-term assets 1,501 1,434 Total assets 86,113 108,704 Liabilities and stockholders' equity Current liabilities: Accounts payable 4,025 4,197 Short-term debt 1,000 Accrued compensation Income taxes 3,283 3,575 1,074 580 Short-term unearned revenue 13,652 15,722 Securities lending payable 182 1,208 Other 3,492 28,774 2,931 Total current liabilities 26,147 Long-term debt Long-term unearned revenue 4,939 11,921 1,178 1,398 Deferred income taxes 229 1,456 Other long-term liabilties 7,445 8,072 Total liabilities 39,938 51,621 Commitments and contingencies Stockholders' equity: Common stock and paid-in capital – shares authorized 24,000; outstanding 8,376 and 8,668 Retained deficit, including accumulated other comprehensive income of $1,863 and $1,055 Total stockholders' equity Total liabilities and stockholders' equity 62,856 63,415 -16,681 -6,332 57,083 108,704 46,175 86,113 BALANCE SHEETS (In millions) 2010 2011 Assets Current assets: Cash and cash equivalents 5,505 9,610 Short-term investments (including securities loaned of $1,181 and $62) Total cash, cash equivalents, and short-term investments 43,162 52,772 31,283 36,788 Accounts receivable, net of allowance for doubtful accounts of $333 and $375 13,014 14,987 Inventories 740 1,372 Deferred income taxes 2,184 2,467 Other 2,950 55,676 3,320 74,918 Total current assets Property and equipment, net of accumulated depreciation of $9,829 and $8,629 7,630 8,162 Equity and other investments 7,754 10,865 Goodwill 12,394 12,581 Intangible assets, net Other long-term assets 1,158 744 1,501 1,434 Total assets 86,113 108,704 Liabilities and stockholders' equity Current liabiities: Accounts payable 4,025 4,197 Short-term debt 1.000 Accrued compensation 3,283 3,575 Income taxes 1,074 13,652 580 Short-term unearned revenue 15,722 Securities lending payable Other 182 1,208 2,931 3,492 Total current liabilities 26,147 28,774 Long-term debt Long-term uneaned revenue 4,939 11,921 1,178 1,398 Deferred income taxes 229 1,456 Other long-term liabilities 7,445 8,072 51,621 Total liabilities 39,938 Commitments and contingencies Stockholders' equity: Common stock and paid-in capital – shares authorized 24,000; outstanding 8,376 and 8,668 Retained deficit, including accumulated other comprehensive income of $1,863 and $1,055 Total stockholders' equity 62,856 63,415 -16,681 -6,332 46,175 57,083 Total liabilities and stockholders' equity 86,113 108,704 CategoryID CategoryName ProductName Alice Mutton ProductSales 6 Meat/Poultry 2 Condiments 8 Seafood 4 Dairy Products Camembert Pierrot 14,123.85 Aniseed Syrup 1,544.00 9,796.33 22,413.48 Boston Crab Meat 8 Seafood Carnarvon Tigers 14,525.00 1 Beverages 1 Beverages 1 Beverages 2 Condiments 2 Condiments 3 Confections 1 Beverages 8 Seafood 5 Grains/Cereals Filo Mix 4 Dairy Products Fløtemysost 4 Dairy Products Geitost 2 Condiments 5 Grains/Cereals Gnocchi di nonna Alice Chai 5,070.60 Chang 5,817.80 Chartreuse verte 2,295.90 Chef Anton's Cajun Seasoning 5,214.88 917.62 Chef Anton's Gumbo Mix Chocolade 1,282.01 Côte de Blaye Escargots de Bourgogne 51,569.58 2,913.68 2,058.00 10,433.94 906.00 1,474.82 30,088.40 7,188.49 Genen Shouyu 4 Dairy Products Gorgonzola Telino 2 Condiments 8 Seafood 1 Beverages 2,500.00 Grandma's Boysenberry Spread Gravad lax 629.20 Guaraná Fantástica 1,571.62 4 Dairy Products Gudbrandsdalsost 14,041.80 Gula Malacca Gumbär Gummibärchen 2 Condiments 6,543.43 3 Confections 5 Grains/Cereals Gustaf's Knäckebröd 8 Seafood 8 Seafood 1 Beverages 11,225.65 4,023.60 8,720.30 Ikura Inlagd Sill Ipoh Coffee Jack's New England Clam Chowder 6,397.11 10,034.90 8 Seafood 4,322.72 8 Seafood 1 Beverages 1 Beverages 7 Produce 2 Condiments 2 Condiments 7 Produce 4 Dairy Products Mascarpone Fabioli 3 Confections 6 Meat/Poultry 4 Dairy Products Mozzarella di Giovanni 8 Seafood Konbu 941.34 Lakkalikööri Laughing Lumberjack Lager Longlife Tofu 7,883.10 868.00 1,186.00 Louisiana Fiery Hot Pepper Sauce Louisiana Hot Spiced Okra Manjimup Dried Apples 8,117.88 2,108.00 16,024.02 2,668.80 Maxilaku 3,060.60 Mishi Kobe Niku 6,935.50 12,427.38 Nord-Ost Matjeshering 5,573.62 8 Seafood Nord-Ost Matjeshering Northwoods Cranberry Sauce NuNuCa Nuß-Nougat-Creme 5,573.62 3,300.00 2 Condiments 3 Confections 1,058.40 Original Frankfurter grüne Soße Outback Lager 4,711.98 5,663.40 2 Condiments 1 Beverages 6 Meat/Poultry Pâté chinois 9,114.48 7,180.15 13,832.13 5,310.69 3 Confections Pavlova Perth Pasties 6 Meat/Poultry 4 Dairy Products 4 Dairy Products 4 Dairy Products 5 Grains/Cereals Ravioli Angelo Queso Cabrales Queso Manchego La Pastora 8,335.30 Raclette Courdavault 32,769.55 2,156.70 1 Beverages Rhönbräu Klosterbier 3,670.24 8 Seafood Röd Kaviar 3,517.50 Røgede sild Rössle Sauerkraut Sasquatch Ale Schoggi Schokolade Scottish Longbreads 5 Grains/Cereals Singaporean Hokkien Fried Mee Sir Rodney's Marmalade Sir Rodney's Scones 8 Seafood 3,203.40 7 Produce 1 Beverages 13,691.48 2,079.00 9,218.00 3 Confections 3 Confections 3,909.00 4,996.60 3 Confections 9,711.90 3 Confections 4,941.00 2 Condiments Sirop d'érable 8,521.50 8 Seafood Spegesild 3,655.32 1 Beverages Steeleye Stout 5,955.30 3 Confections Tarte au sucre 20,811.32 3 Confections 6 Meat/Poultry 7 Produce 6 Meat/Poultry 5 Grains/Cereals Tunnbröd 7 Produce 3 Confections Teatime Chocolate Biscuits 2,339.99 Thüringer Rostbratwurst 30,038.43 6,820.38 Tofu Tourtière 3,120.36 2,576.70 Uncle Bob's Organic Dried Pears 8,251.80 Valkoinen suklaa 2,368.44 2 Condiments Vegie-spread 6,087.69 5 Grains/Cereals Wimmers gute Semmelknödel 3 Confections 7,923.47 Zaanse koeken 2,645.75 What is the cell contents (not the value, what shows in the formula bar) of cell A2 on the Paste Link tab? Be specific, include all punctuation, if applicable.

Answer rating: 100% (QA)

File Home Insert Draw Page Layout Formulas Data Review View Help 6 Viewing v O Comm... View the full answer