1 wa B F . Accounting Lab #1: Assignment for Chapter 13 2 input cells 3 Piedmont Beverages, Inc. Piedmont Beverages, Inc. 4 Balance Sheets As of December 31 Income Statements for the 5 (000's omitted) 2021 2020 Years Ended 12/31 2021 2020 6 Year 2 Year 1 (000's omitted) Year 2 % of Sales Year 1% of Sales % Change 7 Cash $20,000 $27,000. Net Sales 240,000 100.00% 210,000 100.00% 14.29% 8 Marketable Securities 15,000 20.000 9 Accounts Receivable (net) 54,000 63.000 Cost of Goods Sold 137,000 57.08% 104,000 10 Inventories 38.750 42.150 11 Prepaid Expenses 24,000 18,000 Gross Margin 12 Total Current Assets 13 Property. Plant & Equipment 245.000 215.000 Other Expenses 14 Investments 50,000 56,700 Selling and Administrative 60,000 54,000 15 Long-Term Receivables 5.000 9.000 Other Expenses 15,000 12,500 16 Goodwill and Patents 23,6001 27,500 Total Other Expenses 75,000 66,500 17 Other Assets 2.000 4.000 18 Total Assets Income Before Taxes 19 Income Taxes 7,000 9,875 20 Liabilities and Equity 21 Liabilities Net Income 22 Notes Payable $20,000 $22,500 23 Accounts Payable 7,000 15.000 24 Accrued Expenses 4,500 5,700 25 Income Taxes Payable 4.000 3,500 Ratios for 2021 (Year 2) - use formulas: Payments Due within One Year 4,200 4,400 Current ratio 27 Total Current Liabilities Quick Ratio (Acid-Test) 28 Long-Term Debt 42,500 55,000 A/R turnover 29 Deferred Income Taxes 12,400 17.500 Average # of days to collect A/R 30 Other Liabilities 18.000 15,000 Inventory Turnover 31 Total Liabilities Earnings per share of common stock 32 Stockholders' Equity Price-Eearnings ratio of common stock 33 Preferred Stock* 3.750 3.750 Debt (to assets) ratio 34 Common Stock** 50.000 50.000 Equity (to assets) ratio 35 Additional Paid-in Capital - Common 250,000 250,000 Return on total assets 36 Retained Earnings 61,000 50,000 Return on common shareholder equity 37 Total Stockholders' Equity 38 Total Liab. & Stockholders' Equity 39 Stock information: 40 * 6% Cumulative Pfd. Stk, par value $50 per share; 41 $50 liquidating value: authorized 150,000 shares 42 # issued & outstanding (000's omitted) 75 43 Dividend Percent 6% 44 Par Value $50 45 **$5 par value common stk; 15 million shares authorized; 46 issued and outstanding (000's omitted) 10000 47 Market Price Per Share, 12/31 of Yr 2 $45.00 26 Instructions: 1) On the template provided, use Excel to calculate each subtotal or total (blank underlined and highlighted spaces) on the Balance Sheet and the Income Statement 2) For the Income Statement, perform a vertical analysis USING EXCEL FORMULAS for 2021 and 2020 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 2021 and 2020 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.)
