I have posted the instructions and the lab template. please answer it fully. Thank you!

Lab #1 - Chapter 13 Assignment Instructions: 1) On the template provided, use Excel to calculate each subtotal or total (blank underlined and highlighted space) on the Balance Sheet and the Income Statement 2) For the Income Statement, perform a vertical analysis USING EXCEL FORMULAS for 20X9 and 20X8 in the cells labeled "% of Sales." Round to two decimal places. (To copy a formula, you may want to use absolute cell references. To "lock" or make a cell reference an absolute cell reference, you insert a $ before and after the letter of the cell. For example, $G$4 tells Excel to always go to cell G4.) 3) Use Excel Formulas to perform a horizontal analysis of the Income Statement for 20X9 and 20X8 in the column labeled "% change. Again, round to two decimal places. 4) Use Excel Formulas to calculate the ratios indicated. Remember to create as efficient a spreadsheet as possible, using cell references instead of dollar amounts whenever possible. (For ratios that should be expressed as percentages, format the cell as a percentage.) Paste 685 Format as Table Cell Styles P54 H I J K Accounting Lab #1: Assignment for Chapter 13 Input cells 6 Piedmont Beverages, Inc. 7 Balance Sheets As of December 8 (000's omitted) 8 (000's omitted 20x9 Piedmont Beverages, Inc. Income Statements for the Years Ended 12/31 (000's omitted) 2 0x8 Year 1 Year 2 20x9 Year 2 20x8 Year 1 % of Sales % of Sales % Change Net Sales 100% 2095 $3,000 5,000 47.000 50.000 2,000 $2,000 4,000 44,000 60.000 1,000 180,000_ 147,000 100% 82% 150,000_ 120,000 Cost of Goods Sold Gross Margin Cash Marketable Securities Accounts Receivable (net) Inventories Prepaid Expenses Total Current Assets Property, Plant, & Equipment Investments Long-Term Receivables Goodwill and Patents Other Assets Total Assets 100,000 1.000 3,000 2,000 2,000 105,000 1.000] 2,000 4,000 3,000 Other Expenses Selling And Administrative Other Expenses Total Other Expenses 20.000 2,000 18,000 2.000 20,000 22.000 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Income Before Taxes Income Taxos 5.000 4,000 Liabilities and Equity Liabilities Notes Payable Accounts Payable Accrued Expenses Income Taxes Payable Payments Due within One Year Total Current Liabilities Long-Term Debt Deferred Income Taxes Other Liabilities ACC 214 CH. 13 Template $3,000 12.000 9.000 1,000 3,000 Net Income $5,000 16.000 11.000 1,000 Ratios for 20X9. use formulas: 2,000 Current ratio Quick Ratio (Acid-Test) 60,000 A/R turnover 2 7,000 Average # of days to collect AR 4,000 inventory Turnover 50,000 30.000 5,000 P54 Piedmont Beverages, Inc. Balance Sheets As of December 31 (000's omitted) 8 20X9 Year 2 20X8 Year 1 Piedmo Income Years E (000's $3,000 Net Sale $2,000 4,000 44,000 60,000 1,000 47,000 50,000 2,000 Cost of Gross! 11 12 13 14 15 16 17 18 19 20 21 22 Cash Marketable Securities Accounts Receivable (net) Inventories Prepaid Expenses Total Current Assets Property. Plant, & Equipment Investments Long-Term Receivables Goodwill and Patents Other Assets Total Assets 100,000 1,000 3,000 2,000 2,000 105,000 1,000 2,000 4,000 3,000 Other E Selling Other E Total 0 / Her Assets / Income Income Net Ince $5,000 $3,000 12,000 9.000 1,000 3,000 16,000 11.000 1,000 2,000 Liabilities and Equity 25 Liabilities 26 Notes Payable Accounts Payable 28 Accrued Expenses Income Taxes Payable 30 Payments Due within One Year Total Current Liabilities 32 Long-Term Debt Deferred Income Taxes 34 Other Liabilities 35 Total Liabilities 36 Stockholders' Equity 37 Preferred Stock* 38 Common Stock ACC 214 CH. 13 Template 33 Defer 50,000 30,000 5,000 60,000 27,000 4,000 Ratios Current Quick F A/R turr Averag Invento Earning Price-E Debt (to Equity 20,000 5,000 20,000 5,000 SX Arial BI U V LD D 10 AM a. A Paste P54 - X 1,000 3,000 50,000 30,000 5,000 29 Income Taxes Payable 30 Payments Due within One Year 31 Total Current Liabilities 32 Long-Term Debt 33 Deferred Income Taxes 34 Other Liabilities Total Liabilities 36 Stockholders' Equity 37 Preferred Stock 38 Common Stock** 39 Additional Paid-in Capital - Common 40 Retained Eamings 41 Total Stockholders' Equity C D 1,000||Ratios fo 2.000 Current rat Quick Rati 60,000 AR turnov 27.000 Average # 4,000 Inventory Earnings Price-Eear 20,000 Debt (to as 5,000 Equity (to 35,000 Return on 40,000 Return on 20,000 5,000 35,000 42,000 43 Total Liab. & Stockholders' Equity 44 Hed) 5% 45 Stock Information: 46.5% Cumulative Preferred Stock, par value $100 per share 47 $100 liquidating value; authorized 250,000 shares 48 # issued & outstanding (000's omitted) 200 49 Dividend Percent 50 Par Value $100 51 52 **$1 par value common stock: 10 million shares authorized 53 issued and outstanding (000's omitted) 5000 54 Market Price Per Share, 12/31 of Yr 2 $16.00 55 ab #1: Assignment for Chapter 13 input cells Piedmont Beverages, Inc. Income Statements for the Years Ended 12/31 (000's omitted) 20x9 Year 2 % of Sales 20x8 Year 1 % of Sales % Change Net Sales 180,000 100% 150,000 100% 20% Cost of Goods Sold 147,000 82% 120,000 Gross Margin Other Expenses Selling And Administrative Other Expenses Total Other Expenses 20,000 2,000 22,000 18,000 2,000 20,000 Income Before Taxes Income Taxes 5,000 4,000 Net Income 100000 000 Ratios for 20X9 - use formulas: Current ratio Quick Ratio (Acid-Test) A/R turnover Average of days to collect AIR Inventory Turnover TUTUS marym 08888 Other Expenses Selling And Administrative Other Expenses Total Other Expenses 20,000 2,000 22,000 18,000 2,000 20,000 Income Before Taxes Income Taxes 5,000 4,000 Net Income IUU Ratios for 20X9 - use formulas: Current ratio Quick Ratio (Acid-Test) A/R turnover Average # of days to collect AIR Inventory Turnover Earnings per share of common stock Price-Eearnings ratio of common stock Debt (to assets) ratio Equity (to assets) ratio Return on total assets Return on common shareholder equity Lab #1 - Chapter 13 Assignment Instructions: 1) On the template provided, use Excel to calculate each subtotal or total (blank underlined and highlighted space) on the Balance Sheet and the Income Statement 2) For the Income Statement, perform a vertical analysis USING EXCEL FORMULAS for 20X9 and 20X8 in the cells labeled "% of Sales." Round to two decimal places. (To copy a formula, you may want to use absolute cell references. To "lock" or make a cell reference an absolute cell reference, you insert a $ before and after the letter of the cell. For example, $G$4 tells Excel to always go to cell G4.) 3) Use Excel Formulas to perform a horizontal analysis of the Income Statement for 20X9 and 20X8 in the column labeled "% change. Again, round to two decimal places. 4) Use Excel Formulas to calculate the ratios indicated. Remember to create as efficient a spreadsheet as possible, using cell references instead of dollar amounts whenever possible. (For ratios that should be expressed as percentages, format the cell as a percentage.) Paste 685 Format as Table Cell Styles P54 H I J K Accounting Lab #1: Assignment for Chapter 13 Input cells 6 Piedmont Beverages, Inc. 7 Balance Sheets As of December 8 (000's omitted) 8 (000's omitted 20x9 Piedmont Beverages, Inc. Income Statements for the Years Ended 12/31 (000's omitted) 2 0x8 Year 1 Year 2 20x9 Year 2 20x8 Year 1 % of Sales % of Sales % Change Net Sales 100% 2095 $3,000 5,000 47.000 50.000 2,000 $2,000 4,000 44,000 60.000 1,000 180,000_ 147,000 100% 82% 150,000_ 120,000 Cost of Goods Sold Gross Margin Cash Marketable Securities Accounts Receivable (net) Inventories Prepaid Expenses Total Current Assets Property, Plant, & Equipment Investments Long-Term Receivables Goodwill and Patents Other Assets Total Assets 100,000 1.000 3,000 2,000 2,000 105,000 1.000] 2,000 4,000 3,000 Other Expenses Selling And Administrative Other Expenses Total Other Expenses 20.000 2,000 18,000 2.000 20,000 22.000 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Income Before Taxes Income Taxos 5.000 4,000 Liabilities and Equity Liabilities Notes Payable Accounts Payable Accrued Expenses Income Taxes Payable Payments Due within One Year Total Current Liabilities Long-Term Debt Deferred Income Taxes Other Liabilities ACC 214 CH. 13 Template $3,000 12.000 9.000 1,000 3,000 Net Income $5,000 16.000 11.000 1,000 Ratios for 20X9. use formulas: 2,000 Current ratio Quick Ratio (Acid-Test) 60,000 A/R turnover 2 7,000 Average # of days to collect AR 4,000 inventory Turnover 50,000 30.000 5,000 P54 Piedmont Beverages, Inc. Balance Sheets As of December 31 (000's omitted) 8 20X9 Year 2 20X8 Year 1 Piedmo Income Years E (000's $3,000 Net Sale $2,000 4,000 44,000 60,000 1,000 47,000 50,000 2,000 Cost of Gross! 11 12 13 14 15 16 17 18 19 20 21 22 Cash Marketable Securities Accounts Receivable (net) Inventories Prepaid Expenses Total Current Assets Property. Plant, & Equipment Investments Long-Term Receivables Goodwill and Patents Other Assets Total Assets 100,000 1,000 3,000 2,000 2,000 105,000 1,000 2,000 4,000 3,000 Other E Selling Other E Total 0 / Her Assets / Income Income Net Ince $5,000 $3,000 12,000 9.000 1,000 3,000 16,000 11.000 1,000 2,000 Liabilities and Equity 25 Liabilities 26 Notes Payable Accounts Payable 28 Accrued Expenses Income Taxes Payable 30 Payments Due within One Year Total Current Liabilities 32 Long-Term Debt Deferred Income Taxes 34 Other Liabilities 35 Total Liabilities 36 Stockholders' Equity 37 Preferred Stock* 38 Common Stock ACC 214 CH. 13 Template 33 Defer 50,000 30,000 5,000 60,000 27,000 4,000 Ratios Current Quick F A/R turr Averag Invento Earning Price-E Debt (to Equity 20,000 5,000 20,000 5,000 SX Arial BI U V LD D 10 AM a. A Paste P54 - X 1,000 3,000 50,000 30,000 5,000 29 Income Taxes Payable 30 Payments Due within One Year 31 Total Current Liabilities 32 Long-Term Debt 33 Deferred Income Taxes 34 Other Liabilities Total Liabilities 36 Stockholders' Equity 37 Preferred Stock 38 Common Stock** 39 Additional Paid-in Capital - Common 40 Retained Eamings 41 Total Stockholders' Equity C D 1,000||Ratios fo 2.000 Current rat Quick Rati 60,000 AR turnov 27.000 Average # 4,000 Inventory Earnings Price-Eear 20,000 Debt (to as 5,000 Equity (to 35,000 Return on 40,000 Return on 20,000 5,000 35,000 42,000 43 Total Liab. & Stockholders' Equity 44 Hed) 5% 45 Stock Information: 46.5% Cumulative Preferred Stock, par value $100 per share 47 $100 liquidating value; authorized 250,000 shares 48 # issued & outstanding (000's omitted) 200 49 Dividend Percent 50 Par Value $100 51 52 **$1 par value common stock: 10 million shares authorized 53 issued and outstanding (000's omitted) 5000 54 Market Price Per Share, 12/31 of Yr 2 $16.00 55 ab #1: Assignment for Chapter 13 input cells Piedmont Beverages, Inc. Income Statements for the Years Ended 12/31 (000's omitted) 20x9 Year 2 % of Sales 20x8 Year 1 % of Sales % Change Net Sales 180,000 100% 150,000 100% 20% Cost of Goods Sold 147,000 82% 120,000 Gross Margin Other Expenses Selling And Administrative Other Expenses Total Other Expenses 20,000 2,000 22,000 18,000 2,000 20,000 Income Before Taxes Income Taxes 5,000 4,000 Net Income 100000 000 Ratios for 20X9 - use formulas: Current ratio Quick Ratio (Acid-Test) A/R turnover Average of days to collect AIR Inventory Turnover TUTUS marym 08888 Other Expenses Selling And Administrative Other Expenses Total Other Expenses 20,000 2,000 22,000 18,000 2,000 20,000 Income Before Taxes Income Taxes 5,000 4,000 Net Income IUU Ratios for 20X9 - use formulas: Current ratio Quick Ratio (Acid-Test) A/R turnover Average # of days to collect AIR Inventory Turnover Earnings per share of common stock Price-Eearnings ratio of common stock Debt (to assets) ratio Equity (to assets) ratio Return on total assets Return on common shareholder equity