CHAPTER 2 Goal: Create an Excel spreadsheet to account for business transactions. Scenario: You and two fellow classmates, Teri and Peggy have decided to start a coffee shop on campus called Java Beans. The following transactions represent the transactions that occurred in September, the first month of operation of Java Beans. 1. To incorporate your company, you raised $3,000. You, Teri and Peggy each contributed $1,000 in cash. Java Beans issued 300 common shares in return. 2. You also purchased a cappuccino machine for $4,000. 3. In order to finance the purchase of the cappuccino machine, you took a $4,000 credit line with your bank. 4. You also purchased supplies such as paper cups, coffee, sugar and napkins for $1,500 in cash. 5. During the first week of operation, you are hired by the student association to cater their orientation day event. You are asked to provide coffee and muffins for the event. You provide the services and charge them $750. The sale is made on credit, leaving the student association 2 weeks to pay you. 6. Aside from the catering contract, your sales for the month totaled $4,000 7. At the end of the month, you pay rent expense of $800, utilities of $200 and salaries of $1,500. 8. At the end of the month, the student association pays you the full balance of $750 they owed you for the catering service. 9. With the money received during the month, you decide to reimburse $1,000 of your line of credit balance. Required Prepare a spreadsheet to analyze the transactions that took place in September. Once the spreadsheet is completed, prepare an income statement, a statement of retained earnings and a balance sheet for September. Step‐by‐Step 1. Open a new Excel Worksheet 2. Create a bold heading for your spreadsheet that contains the following: a. Chapter 2 ‐ Excel Application Problem b. Transaction Analysis – Java Beans c. Today’s Date Copyright © 2010 Pearson Education Canada 2‐1
3. Two rows down from your heading, prepare a spreadsheet to account for all the transactions. Use Exhibit 2‐1 from your book as a reference to create your spreadsheet. Note that the name of the columns representing the accounts can be different. 4. Enter each transaction into the spreadsheet by showing how it impacts assets, liabilities and Shareholder’s Equity. Calculate the balance for all accounts at September 30th. Ensure that each transaction balances (Asset = Liabilities + S/H Equity). 5. Once the spreadsheet is completed and balances create 3 sections under the spreadsheet. Section 1 should be labeled Income Statement, Section 2 should be labeled Statement of Retained Earnings and Section 3 should be labeled Balance Sheet. 6. Refer to Exhibit 2‐2 from your book to format each of the financial statement. Enter the appropriate headings for all of the statements. 7. Based on the balances calculated for each account in the spreadsheet, complete all three statements in Section 1, 2 and 3. 8. Format all cells appropriately with dollar signs, save your spreadsheet and save a copy for your files.
Copyright © 2010 Pearson Education Canada 2‐2
