Preparing a Classified Balance Sheet using Excel's SUM Function and Cell Referencing Lantana Company has opened its company and has recorded its first month of transactions in T-accounts. The Controller has asked you to prepare the company's classified balance sheet at the month end based on those transactions. Use the information included in the Excel Simulation and the Excel functions described below to complete the task. Cell Reference: Allows you to refer to data from another cell in the worksheet. From the Excel Simulation below, if in a blank cell, "=B14" was entered, the formula would output the result from cell B14, or 75,000 in this example. Basic Math functions: Allows you to use the basic math symbols to perform mathematical functions. You can use the following keys: + (plus sign to add), - (minus sign to subtract), * (asterisk sign to multiply), and / (forward slash to divide). From the Excel Simulation below, if in a blank cell"=C14+C15" was entered the formula would add the values from those cells and output the result, or 15,000 in this example. If using the other math symbols the result would output an appropriate answer for its function. SUM function: Allows you to refer to multiple cells and adds all the values. You can add individual cell references or ranges to utilize this function. From the Excel Simulation below, if in a blank cell SUM(C14,C15,C16)" was entered the formula would output the result of adding those three separate cells, or 25,000 in this example. Similarly, if in a blank cell"=SUM(C14:C16)" was entered the formula would output the same result of adding those cells, except they are expressed as a range in the formula, and the result would be 25,000 in this example. XI H Preparing a classified balance sheet - Excel PAGE LAYOUT FORMULAS DATA REVIEW FILE HOME INSERT VIEW Sign In Calibri - 11 Paste Cells Font B | A % BIU Alignment Number Conditional Format as Cell Editing Formatting Table Styles Clipboard Styles A1 fx The following transactions occurred for Lantana Company during its DEF G H 1 JK L 1 The following transactions occurred for Lantana Company during its first month of 2 and have been recorded in the T-accounts bel 3 4 a. Issued 7,500 shares of stock with a par value $0.10 to three owners (2,500 shares each) for $75,000 in 5 b. Purchased a short-term investment for $10,000 cash. c. Purchased 2 acres of land for $20,000, paid $5,000 in cash and signed a 2 year note foi 7 d. Bought $900 of supplies on accou 8 e. Sold 1/2 acre of land for $5,000. Accepted a note to receive payment in one yea 9 f. Purchased $10,000 of equipment in ca 10 g. Paid $500 on account for supplies purchased in transac ounts bel 6 11 Short-Term Investments 10,000 12 13 Cash 14 a. 75,000 10,000 b. b. 15 5,000 C. 16 10,000 f. 17 500 g. 18 bal. 49.500 bal. 19 20 Notes Receivable 5,000 d. 22 23 21 Sheet1 10,000 Supplies 21 e. 9001 CAA - READY Attempt(s) + 100% Hint Preparing a classified balance sheet - Excel ? E r Y 1 FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In > Calibri 11 - A A % Paste BIU Alignment Number Cells Editing Conditional Format as Cell Formatting Table Styles Styles Clipboard Font A4 : fax a. Issued 7,500 shares of stock with a par value $0.10 to three owners L M 32 9. A B c DEF G H IJK 20 Notes Receivable Supplies 21 e. 5,000 d. 900 22 23 24 bal. 5.000 bal. 900 25 26 Land Equipment 27 c. 20,000 5,000 e. f. 10,000 28 29 bal. 15,000 bal. 10,000 30 31 Accounts Payable Note Payable 500 900 d. 15,000 33 34 400 bal. 15.000 bal. 35 36 Common Stock Additional Paid-in Capital 37 750 a. 74,250 38 39 750 bal. 74 250 bal. 40 41 Required: 42 Using the T-account BALANCES and the information in the transactions, 43 prepare a classified balance sheet for Lantana Company. 44 Note: If nothing belongs in a cell, leave it blank. Do not include blank cells in calcula 15. Alaa liat lanta to lo olababataal Sheet1 c. a. - . READY E + 70% Attempt(s) Hint Preparing a classified balance sheet - Excel ? r Y 1 FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In > Calibri 11 - A A % Paste BIU Alignment Number Cells Editing Conditional Format as Cell Formatting Table Styles Styles Font Clipboard A4 : fax a. Issued 7,500 shares of stock with a par value $0.10 to three owners A B G H L M 49 DEF 1 JK 41 Required: 42 Using the T-account BALANCES and the information in the transactions, 43 prepare a classified balance sheet for Lantana Company. 44 Note: If nothing belongs in a cell, leave it blank. Do not include blank cells in calcula 45 Also, list long-term assets in alphabetical 46 47 LANTANA COMPANY 48 Balance Sheet At December 31, 2016 50 Assets Liabilities and Stockholders' Equity 51 Current Assets Current Liabilities 52 53 54 55 56 57 Total Current Assets Total Current Liabilities 58 59 Long-term Assets Long-term Liabilities 60 61 62 63 Total Long-term Assets Total Long-term Liabilities 64 Total Liabilities 65 66 Stockholders' Equity Sheet1 - READY E + 70% Attempt(s) Hint Preparing a classified balance sheet - Excel ? r Y 1 FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In Calibri 11 - A A % Paste BIU- Cells Clipboard Font A4 : Alignment Number Conditional Format as Cell Formatting Table Styles Editing Styles for a. Issued 7,500 shares of stock with a par value $0.10 to three owners DEF H IJK At December 31, 2016 Assets Liabilities and Stockholders' Equity Current Liabilities B G L M Current Assets Total Current Assets Total Current Liabilities Long-term Assets Long-term Liabilities 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 Total Long-term Assets Total Long-term Liabilities Total Liabilities Stockholders' Equity 0 Retained Earnings Total Stockholders' Equity Total Liabilities and Stockholders' Equity Total Assets - Sheet1 READY E + 70% Attempt(s) Hint