Required: Make in Excel the following items based on the company narrative which begins on page 5: 1. Journal 2. Ledger 3. Income statement 4. Balance sheet 5. Statement of cash flows ou are required to turn in You can talk to each other about the assignment, but each student must make their own project. In other words, you will get a zero if you don't do your own work. The electronic copy will have a stamp on it with the author's information, therefore I will know if two of you try a upload same file. You will receive a zero on the project if you do not make your own Excel fi ul Prepare journal entries to record the transactions and other events described or implied in the Cayman Corporation Narrative (narrative starts on pg. 5). The more you separate your 1. entries, the easier it will be when you prepare your financial statements, especially the statement of cash flows. You do not have to worry about exact dates or the exact order of your entries; all of the transactions are related to 2020. See the example journal on page 3 of this handout. (60 points) Post all journal entries to the ledger (T-accounts). Set up the ledger under the accounting equation. In other words, put all of your assets in once column, liabilities in another column, and equity in a column. See the example ledger on page 3 of this handout. (10 points) 2. Prepare the income statement for 2020. Show gross margin, net income before income tax expense, and net income. Separate your expenses on the income statement. There are no gains and losses in this problem. See the textbook example on page 764. (10 points) 3. Page 1 of 6 Note: Calculate make the statement of retained earnings), Beginning retained 2020 is the first year of operations. Think earnings, which you will need to know for the final exam. because ending retained earnings for the balance sheet (you don't have to actually what increases and decreases retained Prepare the balance sheet as of December 31, 2020. Make sure to list current assets before long-term assets and current liabilities before long-term liabilities. See the textbook example on page 763. You will not have a comparative (multi-year) balance sheet. (10 points) 4. ent as Prepare a statement of cash flows for 2020. You may use either the direct method or the indirect method for presenting cash flows from operating activities. For this project, the direct method could be easily prepared straight from the cash t-account. (10 points) if you do both the direct and indirect methods, you can earn 5 bonus points s. Tips: .Don't get overwhelmed by looking at the problem as a whole. Take one journal entry at a time and don't try to shortcut anything. Start early. This project will likely take you up to 10 hours. Keep in mind that doing well on this project will help make the final exam significantly easier for you, at least in my opinion. For this project, I think it would be easier to combine all of your expenses into one t account rather than separate them. If you combine the expenses, just label each expense to the side (example: Int Exp; Depr Exp; Rent Exp). . .Label the transactions in your cash account to the side with a brief explanation (ex: Collect AR: Paid Inv; Pald Wages; Inc Tax) You can ask me questions, but I expect you to take initiative to try and find the answer in the book and notes before asking me. I expect you to struggle some with this assignment. .For Excel: Right click->format cells to format numbers and borders. All numbers should No decimal places are needed on the financial be rounded to the nearest dollar. statements. Learn how to use print preview in Excel. Please do not print a financial statement that is split between two different pages Page 2 of 6 Don't try and fit everything on the same tab in Excel. You might want to use up to s separate tabs for this project When printing in Excel, you can print the entire workbook withous having to print each Entire Workbook. . tab separately, File print - settings, change from "Print Active Sheets t to Print . used the "Merge and Center"teature above the t-accounts to center the account ones. .Use spell check in Excel Build formulas into your financial statements. For example, net income should be a formula set up where Excel automatically recalculates the amount if you change your revenue amount or one of your expense amounts. Microsoft Office (which includes Excel) is available to you for free as a student. http://www.mclennan.edu/tech-support/software . Sample journal made in Excel: 250,000 Buildi 250,000 Merge and Center Cash 100,000 2.Cash 100,000 Revenue Sample ledger (t-accounts) made in Excel: EXPENSES CASH ssue CS 100,000 100,000 Paid AP Rent Exp 10,000 10,000 Wages Exp 10,000 30,000 Depr Exp Cash Rev 100,000 100,000 You will need approximately 10-12 lines (rows) for the cash and expense accounts Right click, format cells, border is one way to add lines Page 3 of 6 Cayman Corporation was forme d on January 1, 2020 when Cayman issued 10,00 $o.50 par common stock for $250,000 cash On the date of formation, Cayman paid $70,000 cash for 4,000 units of inventory and $5,000 cash for office supplies. gning a 5-year,5% notch ue on December 31, 2020. Each Also, on January 1, Cayman purchased equipment for $100,000 by si payable. Interest accrues annually and the first payme annual loan payment will consist of $20,000 principal plus the accrued interest. Keep in mind you don't necessarily have to have all of your transactions in chronological order At this point, I suggest either recording the loan payment (principal + interest expense) least write yourself a reminder to record the loan pay or at ment as one of the last transactions. Don't forget that the principal and interest belong in separate areas on the statement of cash flows. You might want to record the principal payment as a separate journal entry from interest. Cayman uses the straight-line method to depreciate office equipment assuming a 10-year life and no residual value. Later in the year, Cayman purchased an additional 4,000 units of inventory on account at a cost of $75,000. Before year end, Cayman Corporation paid 75% of this amount. Sales for 2020 consisted of 5,500 units sold on account for $75 each. (When recording sales revenue, don't forget to also record cost of goods sold.) Cayman uses the FIFO method to account for inventory and cost of goods sold. Before year end, Cayman collected 80% of the accounts receivable. Cayman expects it will not collect 1% of the receivables that are still outstanding as of December 31, 2020. Cayman uses the allowance method to account for potential uncollectible accounts. No specific accounts were actually written off in 2020. Cayman employs three people. The total payroll for 2020 was $50,000, of which Cayman still owes $3,000 at year end. Don't worry about payroll taxes for this problem In November, Cayman purchased land for $80,000 cash During 2020, Cayman paid building rent of $30,000 and used $4,500 worth of office supplies In December one of the initial investors sold stock back to Cayman Corporation. Cayman paid $20,000 for the treasury stock. Page 5 of6 Caymn Corporation declared and paid a $10,000 cash dividend to stockholders. You can use the dividends accounts or you can take the dividends directly out of retained earnings. If you take the dividends directly out of retained earnings, then you don't need a closing entry for dividends. The income tax rate for 2020 is 20%. Assume the income tax for the year has all been paid. To calculat e income tax, multiply net income before tax by the tax rate. Don't forget to record an actual journal entry for the income tax expense. Just to be absolutely clear: Calculate income before income tax expense Calculate income tax expense Income before income tax-tax net income Prepare the journal entry for income tax expense 1. 2. 3. 4. Prepare closing journal entries for the temporary accounts Check figures: Cash: Total Assets: Retained Earnings: Operating CF $193,940 $492,990 $161,240 $73,940 Page 6 o Required: Make in Excel the following items based on the company narrative which begins on page 5: 1. Journal 2. Ledger 3. Income statement 4. Balance sheet 5. Statement of cash flows ou are required to turn in You can talk to each other about the assignment, but each student must make their own project. In other words, you will get a zero if you don't do your own work. The electronic copy will have a stamp on it with the author's information, therefore I will know if two of you try a upload same file. You will receive a zero on the project if you do not make your own Excel fi ul Prepare journal entries to record the transactions and other events described or implied in the Cayman Corporation Narrative (narrative starts on pg. 5). The more you separate your 1. entries, the easier it will be when you prepare your financial statements, especially the statement of cash flows. You do not have to worry about exact dates or the exact order of your entries; all of the transactions are related to 2020. See the example journal on page 3 of this handout. (60 points) Post all journal entries to the ledger (T-accounts). Set up the ledger under the accounting equation. In other words, put all of your assets in once column, liabilities in another column, and equity in a column. See the example ledger on page 3 of this handout. (10 points) 2. Prepare the income statement for 2020. Show gross margin, net income before income tax expense, and net income. Separate your expenses on the income statement. There are no gains and losses in this problem. See the textbook example on page 764. (10 points) 3. Page 1 of 6 Note: Calculate make the statement of retained earnings), Beginning retained 2020 is the first year of operations. Think earnings, which you will need to know for the final exam. because ending retained earnings for the balance sheet (you don't have to actually what increases and decreases retained Prepare the balance sheet as of December 31, 2020. Make sure to list current assets before long-term assets and current liabilities before long-term liabilities. See the textbook example on page 763. You will not have a comparative (multi-year) balance sheet. (10 points) 4. ent as Prepare a statement of cash flows for 2020. You may use either the direct method or the indirect method for presenting cash flows from operating activities. For this project, the direct method could be easily prepared straight from the cash t-account. (10 points) if you do both the direct and indirect methods, you can earn 5 bonus points s. Tips: .Don't get overwhelmed by looking at the problem as a whole. Take one journal entry at a time and don't try to shortcut anything. Start early. This project will likely take you up to 10 hours. Keep in mind that doing well on this project will help make the final exam significantly easier for you, at least in my opinion. For this project, I think it would be easier to combine all of your expenses into one t account rather than separate them. If you combine the expenses, just label each expense to the side (example: Int Exp; Depr Exp; Rent Exp). . .Label the transactions in your cash account to the side with a brief explanation (ex: Collect AR: Paid Inv; Pald Wages; Inc Tax) You can ask me questions, but I expect you to take initiative to try and find the answer in the book and notes before asking me. I expect you to struggle some with this assignment. .For Excel: Right click->format cells to format numbers and borders. All numbers should No decimal places are needed on the financial be rounded to the nearest dollar. statements. Learn how to use print preview in Excel. Please do not print a financial statement that is split between two different pages Page 2 of 6 Don't try and fit everything on the same tab in Excel. You might want to use up to s separate tabs for this project When printing in Excel, you can print the entire workbook withous having to print each Entire Workbook. . tab separately, File print - settings, change from "Print Active Sheets t to Print . used the "Merge and Center"teature above the t-accounts to center the account ones. .Use spell check in Excel Build formulas into your financial statements. For example, net income should be a formula set up where Excel automatically recalculates the amount if you change your revenue amount or one of your expense amounts. Microsoft Office (which includes Excel) is available to you for free as a student. http://www.mclennan.edu/tech-support/software . Sample journal made in Excel: 250,000 Buildi 250,000 Merge and Center Cash 100,000 2.Cash 100,000 Revenue Sample ledger (t-accounts) made in Excel: EXPENSES CASH ssue CS 100,000 100,000 Paid AP Rent Exp 10,000 10,000 Wages Exp 10,000 30,000 Depr Exp Cash Rev 100,000 100,000 You will need approximately 10-12 lines (rows) for the cash and expense accounts Right click, format cells, border is one way to add lines Page 3 of 6 Cayman Corporation was forme d on January 1, 2020 when Cayman issued 10,00 $o.50 par common stock for $250,000 cash On the date of formation, Cayman paid $70,000 cash for 4,000 units of inventory and $5,000 cash for office supplies. gning a 5-year,5% notch ue on December 31, 2020. Each Also, on January 1, Cayman purchased equipment for $100,000 by si payable. Interest accrues annually and the first payme annual loan payment will consist of $20,000 principal plus the accrued interest. Keep in mind you don't necessarily have to have all of your transactions in chronological order At this point, I suggest either recording the loan payment (principal + interest expense) least write yourself a reminder to record the loan pay or at ment as one of the last transactions. Don't forget that the principal and interest belong in separate areas on the statement of cash flows. You might want to record the principal payment as a separate journal entry from interest. Cayman uses the straight-line method to depreciate office equipment assuming a 10-year life and no residual value. Later in the year, Cayman purchased an additional 4,000 units of inventory on account at a cost of $75,000. Before year end, Cayman Corporation paid 75% of this amount. Sales for 2020 consisted of 5,500 units sold on account for $75 each. (When recording sales revenue, don't forget to also record cost of goods sold.) Cayman uses the FIFO method to account for inventory and cost of goods sold. Before year end, Cayman collected 80% of the accounts receivable. Cayman expects it will not collect 1% of the receivables that are still outstanding as of December 31, 2020. Cayman uses the allowance method to account for potential uncollectible accounts. No specific accounts were actually written off in 2020. Cayman employs three people. The total payroll for 2020 was $50,000, of which Cayman still owes $3,000 at year end. Don't worry about payroll taxes for this problem In November, Cayman purchased land for $80,000 cash During 2020, Cayman paid building rent of $30,000 and used $4,500 worth of office supplies In December one of the initial investors sold stock back to Cayman Corporation. Cayman paid $20,000 for the treasury stock. Page 5 of6 Caymn Corporation declared and paid a $10,000 cash dividend to stockholders. You can use the dividends accounts or you can take the dividends directly out of retained earnings. If you take the dividends directly out of retained earnings, then you don't need a closing entry for dividends. The income tax rate for 2020 is 20%. Assume the income tax for the year has all been paid. To calculat e income tax, multiply net income before tax by the tax rate. Don't forget to record an actual journal entry for the income tax expense. Just to be absolutely clear: Calculate income before income tax expense Calculate income tax expense Income before income tax-tax net income Prepare the journal entry for income tax expense 1. 2. 3. 4. Prepare closing journal entries for the temporary accounts Check figures: Cash: Total Assets: Retained Earnings: Operating CF $193,940 $492,990 $161,240 $73,940 Page 6 o