s T U posle points A B C D E F G H H 1 j K L M N O Q R 1 Accounting and the Business Environment 2 Using Excel to Prepare Transaction Analysis 3 4 Echo Lake Corporation started operations on November 1, 2018. Nine transactions occurred during November. Financial statements are prepared at the end ofthe month. 5 Use the blue shaded areas on the ENTER-ANSWERS tab for inputs. 6 ALWAYS use cell references and formulas where appropriate to receive full credit. Copy/pasting values will NOT earn full points. 7 8 9 Requirements Possible Points 10 1 Use Excel to prepare a transaction analysis of the nine transactions. Use the blue shaded areas for inputs. 28 a. For each transaction, record the amount either an increase or decrease) under the correct account. Enter only non-zero amounts. If an account is not affected by the transaction, leave the amount blank. Be sure to use a minus sign (-) if the amount is a decrease. Enter dividends and expenses as negative amounts. Do not use 11 an equal sign (=) when entering a numeric value. 12 b b. The row totals will be calculated automatically. C. The accounting equation (Assets - Liabilities + Equity) should remain in balance after each transaction. The accounting equation is calculated automatically to the 13 right of the transaction table. 2 Prepare the income statement (11 pts), statement of retained earnings (10 pts), balance sheet (20 pts), and statement of cash flows (22 pts) for the company. Each financial statement appears on a separate worksheet tab. Fill in the blue shaded areas using a formula that references the account balances at the end of the month in 63 14 the ENTERANSWERS1 tab. 15 16 17 Excel Skills 18 1 Enter numbers into cells. Enter text into cells. 19 2 Use Excel to prepare the income statement, statement of retained earnings, balance sheet and statement of cash flows. 20 21 22 Excel Hints 23 Expenses are entered as negative numbers in the transactional analysis table because they reduce total equityl, but entered as positive numbers in the income statement (they will be subtracted from revenues). 24 Add a negative sign in front of each formula for expense on the income statement to reverse the sign. 25 Cell F29 below contains the formula with a negative sign (=-C29). 26 27 Rent Expense Income Statement 28 1,500 Rent Expense 1,500 Q R AE Total Equity L+SE 0 TRUE 0 TRUE E G K L M N 0 1 Use Excel to prepare a transaction analysis of the nine transactions. Use the blue shaded areas for inputs. 2 a. For each transaction, record the amount (either an increase or decrease) under the correct account. Enter only non-zero amounts. If an account is not affected by the transaction, leave the amount blank, 3 Be sure to use a minus sign (-) if the amount is a decrease. Enter dividends and expenses as negative amounts. 4 Do not not use an equal sign (=) when entering a numeric value. 5 b. The row totals will be calculated automatically 6 c The accounting equation (Assets = Liabilities + Equity) should remain in balance after each transaction. The accounting equation is calculated automatically to the right of the transaction table. 7 8 9 9 Description Assets Llabilities Equity Contributed 10 Capital Retained Earnings Accounts Office Accounts Service Rent Salaries Total 11 Cash Receivable Supplies Land Payable Common Stock Dividends Revenue Expense Expense Total Assets Liabilities Received $50,000 cash from Luigi 12 1 Stefanni and issued stock. 13 Balance 0 D 0 0 0 0 ol 0 0 0 0 0 0 14 2 Purchased land for $23,000 cash. 15 Balance 0 0 0 0 0 0 0 0 0 0 0 0 0 Purchased $300 of office supplies on 16 3 account 17 Balance ol ol 0 0 ol 01 ol 0 0 0 Received $7,500 cash for services 18 4 performed. 19 Balance 0 0 0 0 0 0 0 ol 0 0 0 0 0 Performed services on account, 20 5 $2,000 . 21 Balance 0 0 0 0 0 0 0 0 0 0 0 0 Paid in cash: $1,500 for rent & $1,100 22 6 for employees salaries 23 Balance D 0 0 0 01 0 0 0 0 0 0 0 0 24 7 Paid $250 to creditor on account. 25 Balance 0 0 0 0 0 0 0 0 0 0 0 0 Collected $1,600 from customers on 26 8 account. 27 Balance 0 0 0 0 0 0 0 01 0 0 0 0 28 9 Paid $4,000 cash dividend. 29 Balance 0 0 0 0 0 01 01 0 0 ol 01 0 0 0 30 31 0 TRUE 0 TRUE 0 TRUE 0 0 TRUE 0 0 TRUE 0 TRUJE 0 TRUE A B D E F. G H | J K L 1 Prepare the income statement. Fill in the blue shaded areas using a formula that references the account balances at the end of the month in the ENTERANSWERS1 tab. (Always use cell references and formulas where appropriate to receive full credit.) 2 3 4 5 Income Statement 6 7 8 Revenues: 9 10 11 Expenses: 12 13 Total Expenses 14 15 $ 16 17 18 19 20 HINTS Cell | Hint: D12:D13 Expenses should be entered as positive numbers in the income statement. Add a negative sign in front of each formula for expense on the income statement to reverse the sign. 21 22 23 24 25 M A B D E F G . 1 J L Prepare the statement of retained earnings. Fill in the blue shaded areas using a formula that references the account balances at the end of the month in the ENTERANSWERS1 1 and the ENTERANSWERS2 tabs. 2 (Always use cell references and formulas where appropriate to receive full credit.) 3 4 Nm 6 00 5 Statement of Retained Earnings 7 $ 0 9 10 11.12 11 12 $ 13 14 15 16 HINTS 17 Cell | Hint: 18 E9 and E11 | Be sure that the amount is entered as a negative number if it is an outflow. 19 20 21 22 23 24 25 A B D F. G H 1 J K L 1 Prepare the balance sheet. Fill in the blue shaded areas using a formula that references the account balances at the end of the month in the ENTERANSWERS1 and the ENTERANSWERS3 tabs. (Always use cell references and formulas where appropriate to receive full credit.) 2 3 4 Balance Sheet 5 6 7 8 Assets 9 10 11 12 13 Total Assets $ 14 Liabilities 15 16 17 18 Stockholder's Equity 19 20 21 Total Stockholders' Equity Total Liabilities and Stockholders' Equity 22 $ 23 24 25 26 27 28 29 30 21 A B D E F G H K L M Prepare the statement of cash flows. Fill in the blue shaded areas using a formula that references the changes in the account balances due to the corresponding transaction in the 1 ENTERANSWERS1 tab. 2 Do not use an equal sign (=) when entering a numeric value. Enter value in all answer boxes, even if answer is 0. 3 (Always use cell references and formulas where appropriate to receive full credit.) 4 5 6 Statement of Cash Flows 7 8 9 Cash Flows from operating activities: 10 Receipts: 11 12 Payments: 13 14 15 Total Payments 16 Net cash provided by operating activities 17 18 Cash flows from investing activities: 19 20 Net cash used by investing activities 21 22 Cash flows from financing activities: 23 24 25 Net cash provided by financing activities 26 27 28 29 $ 30 31 32 33 HINTS 34 Cell Hint: 35 E13:E24 Be sure that the amount is entered as a negative number if it is an outflow. 36