I would like someone to look over my project. I am unsure about the unearned revenue portion. Also, I think the net income should be the retained earning but I can't balance...Either A. I am off somewhere or B. there are retained earnings from 2014 and we were supposed to deduct by knowing how retained earnings works?

ACCT4111 Project: Accounting Data Analyses Students as Business Advisors: Preparing financial statements and analyzing the implications for a business INTRODUCTION The transition from principles of accounting to intermediate level accounting can be abrupt for students, and instructors can wrongly assume that every student is able to apply the skills that are learned in prior courses when they attempt the intermediate level tasks. This practice set provides a transition from principles of accounting to intermediate level. In addition, this practice serves to alert students to the foundational skills that they would need for future coursework in intermediate accounting. The practice set involves traditional accounting cycle basics (journal entries and adjusting entries) in a setting that is more realistic than typical textbook practice sets. In addition, it also goes beyond the introductory level of principles of accounting by having students study the resultant financial data and give the client advice about the financial status of their small plumbing business. CLIENT BACKGROUND Your professional career has commenced! You just finished a meeting with Jag and Elk, two plumbers who had merged their start-up plumbing businesses two years ago. Jag's residential customers are all on a prepaid service contract basis. For a fixed fee paid in advance, customers get three years of plumbing services (total $648, $18 per month) regardless of the number of service calls, nature of the service, or materials needed. Elk's commercial business is a parts delivery service known for having every part in stock and \"same day delivery\" so that \"your labor crew doesn't stall out for lack of a part\". Both have agreed not to draw any salaries from the business and just split dividends after they have paid off all business debt. They both have simple lifestyles, so they work part-time with another plumbing business in the next town on the weekends to pay their bills until their business is big enough to support both of them. Upon merging their businesses into Jag & Elk Pipes, Jag and Elk obtained a loan and bought a 2014 Nissan truck equipped with plumbing tools and rented a small warehouse in midtown. They pay one part-time worker at the warehouse to manage inventory and customer service. On days when they are on-site for a residential client, they deliver parts to commercial clients either before work, on their lunch hour, or after the residential jobs each day. Neither Jag nor Elk have any financial background but have kept basic records of activity for tax purposes. Their banker requires a set of accrual basis financial statements annually. Given your new found accounting expertise, they have asked you to help, In addition to creating the financial statements for the bank, they have requested that you analyze the profitability and liquidity of their company. THE CLIENT MEETING Jag: \"Here are balances in our accounts at the beginning of the year (Appendix A). We have two sets of records other than the checkbook. We keep a job log of all our trips, one for residential clients and one for commercial clients (Appendix B). We keep track of who has paid us on this log and also record the date and the materials used on each trip. We also have a listing of all our residential contracts since inception (Appendix C).\" Elk: \"We don't write that many checks but I have summarized all the checks written during 2015 and the inventory details for you on this schedule (Appendix D). When we bought the Nissan truck last year, we estimated that it will last about six years and be worth about $5,000 at the end of the sixth year. When we bought the tools for the truck last year, we estimated that they will last about three years and be worth about $500 at the end of the three years. We use the straight line method of depreciation.\" Jag: \"Our note is at 9.5% annual interest and matures on June 1, 2020 but we hope we can pay it off earlier. Our effective tax rate is 15%. We have one thing that we haven't paid for yet: the last two weeks of payroll costs on our part-time worker ($600).\" Elk: \"Oh yeah. Our accountant last year asked us if we thought any of our credit customers might not be able to pay us. We just guessed. We ended up collecting everything from last year. For the current customers, I would guess 2% of the uncollected amount at year end as an estimate for future bad debts.\" REQUIREMENTS Prepare Financial Statements Prepare three financial statements: 1) Income Statement (multiple step), 2) Balance Sheet (add income to retained earnings) and 3) Cash Flow Statement (indirect method). Present them in good form on the separate \"Financial Statement\" worksheet tab in the excel project file. Format your statements to look professional - e.g. even margins, centered on the page, appropriate title on top of page, everything labeled nicely and linked to the appropriate trial balances (link amounts to trial balances, use excel formulae instead of typing in computed results by hand), so that if the client changes any amounts, the financial statements automatically update. Analyze the Business and Communicate Your Findings Review the client's financial results for 2015. Analyze the profitability of the company. Analyze the liquidity of the company by computing the current ratio and the quick ratio. In a separate Microsoft Word file, write the client a short memo (300 word maximum) giving them clear and concise feedback based on the profitability and liquidity analysis that you performed. Exhibits do not count in the word limit. Your memo should support your analysis with observations and explanations that help the client understand your findings. Use specific but short explanations. You may use charts or exhibits to show financial relationships that help illustrate your thoughts. Jag and Elk are busy and not interested in sweet talk or bragging about how hard you have worked. Get right to the pointgive an overview of all important issues, and then explain these issues in the following paragraphs. Avoid off-topic comments and do not mention ideas or balances that do not support your stance on the important issues that you are drawing to the client's attention. Use simple language as much as possible. Your client is a financial novice who is not familiar with technical financial or accounting jargon. Output to be submitted: Please turn in a hard copy of your excel spreadsheet and One-page memo. I may request a softcopy of your excel file if I have any questions. Important: You may work in groups to help one another if you encounter any difficulties. However, the final output must be solely the result of your individual efforts, not a group endeavor. Copying another student's work and/or virtually identical cell contents will receive no credit. Where should you start? It is recommended to identify Service Revenue first. The revenue is recognized when the actual service is performed for commercial customers, but evenly over time for residential customers. Then find all cash transactions. The next step would be to identify Accounts Receivable and Unearned Revenue. Notice that residential customers pay cash in advance and receive service later over time, but commercial customers receive service first and pay cash later. In most of cells you would fill up, Excel formulas or functions should be typed, such as \"=A1+B1-C1\" or \"=sum(A1:A10)\". Additional TIPS to Analyze Appendix Information: Appendix B: - Identify total price of commercial services delivered during the year - Find total amount of cash collection during the year from commercial customers - Compute of Cost of Parts Inventory used for Commercial and Residential Services ("consumed", not necessarily same as cash payment) Appendix C: - Identify Unearned Revenue at the end of each year Appendix D: Financial Statements - Record Realized Revenue during the year - Find total amount of cash collection during the year from residential customers - Make additional proper journal entries for given information - Prepare "formal" financial statements in a new tab: B/S, I/S and C/F - Notice that there is no Investing or Financing Activities for C/F statement Name: Marcia McInnis Cash Accounts Receivable - Commercial Less: Allowance for bad debts - Commercial Parts Inventory Prepaid insurance Plumbing equipment and tools Less: Accumulated depreciation, equip & tools Plumbing truck Less: Accumulated depreciation, trucks Salaries payable Interest Payable Taxes payable Notes payable Unearned revenues Common stock Retained earnings Residential sales CommercIal sales Cost of sales - residential Cost of sales - commercial Salaries and Wage expense Truck operating expenses Depreciation expense Rent and utilities expense Office supplies expense Bad debt expense Insurance expense Interest expense Tax expense Total Balances 1/1/2015 debit credit $ 19,802 $ 1,800 $ 90 $ 16,000 $ 1,100 $ 7,600 $ 1,183 $ 47,000 $ 3,500 $ $ $ $ 2015 transactions debit credit 98,633 $ 103,200 95,331 $ 83,081 $ 281 70,000 $ 49,107 6,000 $ 4,500 2,367 $ $ $ 50,000 $ 13,878 $ 20,000 $ 4,651 $ $ $ 4,000 $ 400 $ 7,000 600 4,750 1,831 $ 8,280 $ 15,552 $ 8,280 $ 95,331 $ 93,302 $ 93,302 $ 6,225 $ 42,882 $ 9,600 $ 4,000 $ 9,367 $ 9,600 $ 200 $ 281 $ 4,500 $ 4,750 $ 1,831 $ 375,879 375,879 $ - Trial Balance 12/31/15 debit credit $ 15,235 $ $ 14,050 $ $ - $ 371 $ 36,893 $ $ 2,600 $ $ 7,600 $ $ - $ 3,550 $ 47,000 $ $ $ 10,500 $ - $ 600 $ - $ 750 $ - $ 1,431 $ $ 50,000 $ $ 21,150 $ $ 20,000 $ - $ 4,651 $ - $ 8,280 $ - $ 95,331 $ 6,225 $ $ 42,882 $ $ 9,600 $ $ 4,000 $ $ 9,367 $ $ 9,600 $ $ 200 $ $ 281 $ $ 4,500 $ $ 4,750 $ $ 1,831 $ 216,613 216,613 $ - Service date 1/1/2015 1/6/2015 1/11/2015 1/12/2015 1/16/2015 1/18/2015 1/22/2015 1/23/2015 1/26/2015 1/28/2015 1/29/2015 2/1/2015 2/7/2015 2/9/2015 2/13/2015 2/17/2015 2/20/2015 2/25/2015 2/28/2015 3/1/2015 3/4/2015 3/7/2015 3/13/2015 3/17/2015 3/23/2015 3/24/2015 3/25/2015 3/29/2015 3/31/2015 4/6/2015 4/10/2015 4/15/2015 4/18/2015 4/19/2015 4/25/2015 4/30/2015 5/1/2015 5/7/2015 5/13/2015 5/15/2015 5/19/2015 5/21/2015 5/27/2015 6/1/2015 6/5/2015 6/6/2015 6/10/2015 6/12/2015 6/14/2015 6/17/2015 6/20/2015 6/21/2015 6/25/2015 6/27/2015 6/28/2015 6/30/2015 7/4/2015 7/10/2015 7/11/2015 7/16/2015 7/21/2015 7/24/2015 7/26/2015 7/31/2015 8/1/2015 8/2/2015 8/5/2015 8/9/2015 8/14/2015 8/19/2015 8/21/2015 8/26/2015 8/28/2015 9/1/2015 9/3/2015 9/8/2015 9/9/2015 9/10/2015 9/14/2015 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Commercial Invoices Billed Cash Collection Date 1,380.00 1/13/2015 1,135.50 1/23/2015 1,108.50 2/1/2015 835.50 2/16/2015 549.00 2/27/2015 1,395.00 2/24/2015 294.00 2/13/2015 714.00 3/10/2015 810.00 3/16/2015 1,156.50 3/18/2015 727.50 2/20/2015 1,434.00 3/15/2015 784.50 3/6/2015 1,170.50 3/10/2015 619.50 2/28/2015 307.50 4/7/2015 1,021.50 4/10/2015 297.00 3/18/2015 1,413.00 4/1/2015 811.50 4/16/2015 972.00 3/23/2015 1,204.50 3/18/2015 489.00 4/11/2015 1,179.00 4/11/2015 357.00 4/3/2015 744.00 5/8/2015 1,197.00 4/25/2015 612.00 5/3/2015 660.00 5/2/2015 1,365.00 5/8/2015 1,339.50 5/8/2015 1,242.00 5/26/2015 1,257.50 5/1/2015 894.00 5/16/2015 936.00 280.50 5/12/2015 991.50 5/18/2015 1,449.00 5/22/2015 606.00 5/24/2015 928.50 6/3/2015 229.50 6/17/2015 1,186.50 6/19/2015 943.50 6/18/2015 832.50 6/11/2015 372.00 6/16/2015 1,008.00 7/10/2015 1,588.00 6/21/2015 309.00 7/4/2015 1,186.50 7/4/2015 1,240.50 7/5/2015 516.00 7/27/2015 646.50 7/3/2015 1,282.50 7/30/2015 963.00 7/15/2015 850.50 7/30/2015 1,123.50 8/19/2015 1,494.00 7/31/2015 178.50 7/25/2015 823.50 7/29/2015 492.00 8/17/2015 1,098.00 8/18/2015 1,459.50 8/9/2015 1,482.00 8/5/2015 354.00 8/28/2015 1,449.00 9/6/2015 289.50 9/8/2015 937.50 9/6/2015 426.00 8/26/2015 867.00 8/31/2015 1,299.00 8/30/2015 190.50 9/11/2015 925.50 9/11/2015 1,299.00 10/9/2015 1,323.00 10/12/2015 1,282.50 9/19/2015 448.50 10/13/2015 661.50 9/19/2015 714.00 10/20/2015 637.50 10/6/2015 cost of parts $ 828.00 $ 340.65 $ 332.55 $ 250.65 $ 329.40 $ 418.50 $ 176.40 $ 428.40 $ 243.00 $ 346.95 $ 436.50 $ 717.00 $ 470.70 $ 582.30 $ 185.85 $ 184.50 $ 408.60 $ 178.20 $ 706.50 $ 486.90 $ 486.00 $ 361.35 $ 244.50 $ 589.50 $ 107.10 $ 372.00 $ 359.10 $ 244.80 $ 396.00 $ 682.50 $ 803.70 $ 496.80 $ 528.75 $ 536.40 $ 374.40 $ 84.15 $ 495.75 $ 434.70 $ 303.00 $ 278.55 $ 68.85 $ 355.95 $ 377.40 $ 499.50 $ 186.00 $ 302.40 $ 744.00 $ 123.60 $ 711.90 $ 372.15 $ 154.80 $ 387.90 $ 513.00 $ 481.50 $ 510.30 $ 449.40 $ 747.00 $ 107.10 $ 411.75 $ 246.00 $ 658.80 $ 875.70 $ 444.60 $ 212.40 $ 869.40 $ 173.70 $ 468.75 $ 170.40 $ 433.50 $ 779.40 $ 114.30 $ 277.65 $ 519.60 $ 793.80 $ 384.75 $ 134.55 $ 330.75 $ 357.00 $ 191.25 Residential Services date cost of parts 1/4/2015 $ 223.00 1/6/2015 $ 1/10/2015 $ 10.00 1/11/2015 $ 37.00 1/12/2015 $ 1/16/2015 $ 117.00 1/19/2015 $ 338.00 1/21/2015 $ 32.00 1/25/2015 $ 1/29/2015 $ 7.00 1/31/2015 $ 298.00 2/1/2015 $ 2/5/2015 $ 9.00 2/7/2015 $ 28.00 2/9/2015 $ 212.00 2/13/2015 $ 2/16/2015 $ 2/18/2015 $ 21.00 2/19/2015 $ 127.00 2/20/2015 $ 8.00 2/24/2015 $ 2/25/2015 $ 2/27/2015 $ 2/28/2015 $ 80.00 3/2/2015 $ 12.00 3/5/2015 $ 150.00 3/6/2015 $ 3/9/2015 $ 93.00 3/11/2015 $ 30.00 3/12/2015 $ 3/15/2015 $ 51.00 3/16/2015 $ 143.00 3/20/2015 $ 3/23/2015 $ 41.00 3/25/2015 $ 44.00 3/28/2015 $ 3/30/2015 $ 11.00 4/3/2015 $ 31.00 4/6/2015 $ 10.00 4/10/2015 $ 4/11/2015 $ 59.00 4/14/2015 $ 61.00 4/16/2015 $ 4/18/2015 $ 4/21/2015 $ 47.00 4/23/2015 $ 78.00 4/25/2015 $ 124.00 4/27/2015 $ 4/28/2015 $ 88.00 4/29/2015 $ 135.00 5/2/2015 $ 5/3/2015 $ 6.00 5/6/2015 $ 25.00 5/8/2015 $ 5/10/2015 $ 39.00 5/12/2015 $ 62.00 5/14/2015 $ 5/15/2015 $ 5/17/2015 $ 39.00 5/20/2015 $ 5/22/2015 $ 24.00 5/24/2015 $ 10.00 5/27/2015 $ 93.00 5/30/2015 $ 6/3/2015 $ 62.00 6/6/2015 $ 6/8/2015 $ 22.00 6/11/2015 $ 55.00 6/14/2015 $ 6/16/2015 $ 146.00 6/19/2015 $ 6/21/2015 $ 77.00 6/24/2015 $ 92.00 6/26/2015 $ 6/29/2015 $ 4.00 7/3/2015 $ 24.00 7/5/2015 $ 7/9/2015 $ 19.00 7/11/2015 $ 228.00 LEGENDS Commercial Invoices: Service Date: The date when the service has been delivered Billed: The price of service billed to customers Cash Collection Date: The date of cash collection, blank if uncollected by the end of year Cost of Parts: Cost of parts inventory used for each service Residentil Services: Date: The date when the service has been delivered Cost of Parts: Cost of parts inventory used for each service Service date 9/15/2015 9/16/2015 9/20/2015 9/25/2015 9/26/2015 9/30/2015 10/1/2015 10/7/2015 10/10/2015 10/15/2015 10/19/2015 10/21/2015 10/24/2015 10/29/2015 11/2/2015 11/7/2015 11/9/2015 11/15/2015 11/16/2015 11/22/2015 11/26/2015 12/1/2015 12/2/2015 12/6/2015 12/12/2015 12/13/2015 12/15/2015 12/20/2015 12/26/2015 12/29/2015 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Commercial Invoices Billed Cash Collection Date 1,444.50 10/21/2015 445.50 10/4/2015 969.00 10/7/2015 352.50 10/28/2015 580.50 10/10/2015 1,410.00 828.00 10/26/2015 358.50 11/4/2015 663.00 11/21/2015 1,050.00 10/27/2015 720.00 11/10/2015 489.00 11/25/2015 1,434.00 471.00 11/30/2015 1,123.50 12/3/2015 996.00 777.00 12/18/2015 201.00 12/26/2016 903.00 12/15/2015 183.00 12/22/2015 708.00 12/17/2015 375.00 12/19/2015 297.00 12/28/2015 1,362.00 264.00 1,177.50 1,326.00 452.50 1,403.00 1,488.50 $ 95,330.50 $ cost of parts $ 866.70 $ 133.65 $ 581.40 $ 211.50 $ 232.20 $ 423.00 $ 331.20 $ 215.10 $ 397.80 $ 420.00 $ 432.00 $ 195.60 $ 430.20 $ 235.50 $ 561.75 $ 298.80 $ 388.50 $ 120.60 $ 361.20 $ 73.20 $ 283.20 $ 225.00 $ 89.10 $ 681.00 $ 132.00 $ 353.25 $ 530.40 $ 141.00 $ 721.80 $ 644.25 83,081.00 $ 42,882.30 Residential Services date cost of parts 7/12/2015 $ 30.00 7/15/2015 $ 33.00 7/17/2015 $ 7/18/2015 $ 7.00 7/22/2015 $ 11.00 7/23/2015 $ 7/24/2015 $ 7/26/2015 $ 2.00 7/30/2015 $ 7/31/2015 $ 93.00 8/2/2015 $ 19.00 8/4/2015 $ 8.00 8/8/2015 $ 17.00 8/9/2015 $ 8/13/2015 $ 10.00 8/16/2015 $ 8/19/2015 $ 93.00 8/20/2015 $ 8/23/2015 $ 8/24/2015 $ 8/25/2015 $ 47.00 8/28/2015 $ 9/1/2015 $ 15.00 9/3/2015 $ 18.00 9/7/2015 $ 42.00 9/11/2015 $ 158.00 9/15/2015 $ 56.00 9/19/2015 $ 12.00 9/21/2015 $ 9/24/2015 $ 3.00 9/28/2015 $ 83.00 10/2/2015 $ 9.00 10/3/2015 $ 10/4/2015 $ 15.00 10/7/2015 $ 33.00 10/11/2015 $ 10/14/2015 $ 62.00 10/16/2015 $ 99.00 10/18/2015 $ 10/21/2015 $ 58.00 10/22/2015 $ 29.00 10/23/2015 $ 54.00 10/27/2015 $ 344.00 10/29/2015 $ 15.00 11/2/2015 $ 11/3/2015 $ 59.00 11/7/2015 $ 2.00 11/11/2015 $ 71.00 11/13/2015 $ 19.00 11/14/2015 $ 1.00 11/17/2015 $ 28.00 11/19/2015 $ 72.00 11/23/2015 $ 56.00 11/26/2015 $ 18.00 11/27/2015 $ 11/28/2015 $ 11/29/2015 $ 33.00 11/30/2015 $ 82.00 12/1/2015 $ 12/5/2015 $ 15.00 12/8/2015 $ 12/9/2015 $ 84.00 12/12/2015 $ 17.00 12/14/2015 $ 12/15/2015 $ 12/19/2015 $ 377.00 12/21/2015 $ 2.00 12/25/2015 $ 18.00 12/27/2015 $ 12/30/2015 $ 14.00 $ 6,225.00 LEGENDS Total commercial services delivered during the year Total amount of cash collection during the year from commercial customers Consumed Cost of Parts used for Commercial and Residential Services $ 95,330.50 $ 83,081.00 $ 49,107.30 three years of plumbing services (total $648, $18 per month) client date paid date effective fee R 101 01/01/14 01/01/14 $ 648.00 R 102 01/18/14 02/01/14 $ 648.00 R 103 02/02/14 02/01/14 $ 648.00 R 104 02/17/14 03/02/14 $ 648.00 R 105 02/28/14 03/02/14 $ 648.00 R 106 03/15/14 03/01/14 $ 648.00 R 107 03/27/14 03/01/14 $ 648.00 R 108 04/15/14 04/01/14 $ 648.00 R 109 04/25/14 04/01/14 $ 648.00 R 110 05/11/14 05/01/14 $ 648.00 R 111 05/21/14 05/01/14 $ 648.00 R 112 05/31/14 05/01/14 $ 648.00 R 113 06/17/14 06/01/14 $ 648.00 R 114 07/01/14 07/01/14 $ 648.00 R 115 07/13/14 08/01/14 $ 648.00 R 116 08/02/14 08/01/14 $ 648.00 R 117 08/18/14 09/01/14 $ 648.00 R 118 08/30/14 09/01/14 $ 648.00 R 119 09/11/14 09/01/14 $ 648.00 R 120 09/29/14 10/01/14 $ 648.00 R 121 10/14/14 10/01/14 $ 648.00 R 122 10/29/14 10/01/14 $ 648.00 R 123 11/15/14 11/01/14 $ 648.00 R 124 11/25/14 12/01/14 $ 648.00 R 125 12/05/14 12/01/14 $ 648.00 R 126 12/22/14 12/01/14 $ 648.00 Was not signed up for service in 2014 R 127 01/01/15 01/01/15 $ 648.00 R 128 01/21/15 01/01/15 $ 648.00 R 129 02/05/15 02/01/15 $ 648.00 R 130 02/23/15 02/01/15 $ 648.00 R 131 03/12/15 03/01/15 $ 648.00 R 132 03/26/15 04/01/15 $ 648.00 R 133 04/11/15 04/01/15 $ 648.00 R 134 04/22/15 05/01/15 $ 648.00 R 135 05/04/15 05/01/15 $ 648.00 R 136 05/23/15 06/01/15 $ 648.00 R 137 06/07/15 06/01/15 $ 648.00 R 138 06/19/15 06/01/15 $ 648.00 R 139 07/06/15 07/01/15 $ 648.00 R 140 07/23/15 08/01/15 $ 648.00 R 141 08/10/15 09/01/15 $ 648.00 R 142 08/24/15 09/01/15 $ 648.00 R 143 09/08/15 09/01/15 $ 648.00 R 144 09/24/15 10/01/15 $ 648.00 R 145 10/06/15 10/01/15 $ 648.00 R 146 10/17/15 11/01/15 $ 648.00 R 147 11/04/15 11/01/15 $ 648.00 R 148 11/15/15 11/01/15 $ 648.00 R 149 12/01/15 12/01/15 $ 648.00 R 150 12/20/15 12/01/15 $ 648.00 Cash Collected Remaining Months 12/31/2014 12/31/2015 12/31/2016 24 12 25 13 25 13 26 14 26 14 26 14 26 14 27 15 27 15 28 16 28 16 28 16 29 17 30 18 31 19 31 19 32 20 32 20 32 20 33 21 33 21 33 21 34 22 35 23 35 23 35 23 24 24 25 25 26 27 27 28 28 29 29 29 30 31 32 32 32 33 33 34 34 34 35 35 15,552.00 unearned revenue collected in 2015 - credit unearned revenue Unearned Revenue at the end of 2014 Unearned Revenue at the end of 2015 Realiaed Revenue 2015 Total amount of cash collection in 2015 from residential customers $ $ $ $ 29,430.00 21,150.00 8,280.00 15,552.00 Unearned Revenue 12/31/2014 12/31/2015 432.00 216.00 450.00 234.00 450.00 234.00 468.00 252.00 468.00 252.00 468.00 252.00 468.00 252.00 486.00 270.00 486.00 270.00 504.00 288.00 504.00 288.00 504.00 288.00 522.00 306.00 540.00 324.00 558.00 342.00 558.00 342.00 576.00 360.00 576.00 360.00 576.00 360.00 594.00 378.00 594.00 378.00 594.00 378.00 612.00 396.00 630.00 414.00 630.00 414.00 630.00 414.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 648.00 29,430.00 432.00 432.00 450.00 450.00 468.00 486.00 486.00 504.00 504.00 522.00 522.00 522.00 540.00 558.00 576.00 576.00 576.00 594.00 594.00 612.00 612.00 612.00 630.00 630.00 21,150.00 Realized Revenue 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 216.00 198.00 198.00 180.00 162.00 162.00 144.00 144.00 126.00 126.00 126.00 108.00 90.00 72.00 72.00 72.00 54.00 54.00 36.00 36.00 36.00 18.00 18.00 8,280.00 realized rev venue for 2015 - Debit unearned revenue Assumptions and business details outlined by client in initial meeting : Useful life of 2014 Nissan Truck (use straight line method) Salvage value of 2014 Nissan Truck Useful life of tools purchased in 2014 for startup (use straight line method) Salvage value of tools $ $ Annual interest rate on note payable due 2020 Effective tax rate 6 years 5,000.00 3 years 500.00 9.50% 15.00% Cash Collections: As specified in Appendices B and C Checkbook details show these payments: Interest payments during 2015 Paid annual insurance policy on truck and warehouse, effective 4/1/15 Paid for gas, oil, repairs on truck Paid for Inventory purchases during year Rent and utilities for the year (all paid in year) Office supplies purchased for cash, consider all used up Estimated tax deposits paid during year Salaries and Wages for part time worker Pending amounts and other data: Last two week of wages and payroll taxes for part time worker (unpaid yet) $ 4,000.00 $ 6,000.00 $ 4,000.00 $ 70,000.00 $ 9,600.00 $ 200.00 $ 400.00 $ 9,000.00 $ 103,200.00 $ Estimated bad debts: all open AR's at year end 9 Months 600.00 2.00% Journal Entries Debit 2,366.67 Depreciation Expense Accumulated Depreciation (Equip & Tools) $ Depreciation Expense Accumulated Depreciation (Truck) $ Interest Expense Interest Payable $ Cash -Commercial Invoices Accounts Receivable - Commercial $ 83,081.00 Cash - Residential Sercices Unearned Revenue $ 15,552.00 Accounts Receivable - Commercial CommercIal Sales $ 95,330.50 Bad Debt expense Allowance for bad debts - Commercial $ Parts Inventory Cash $ 70,000.00 Cost of sales - residential - Expense Cost of sales - commercial - Expense Parts Inventory $ 6,225.00 $ 42,882.30 Prepaid Insurance Cash $ Interest Payable Cash $ Truck operating expenses Cash $ Rent and Utilities Expense Cash $ Office Supplies Expense Cash $ Taxes payable Cash $ Salaries and Wage Expense Cash $ Salaries and Wage Expense Salaries Payable $ Credit $ 2,366.67 $ 7,000.00 $ 4,750.00 7,000.00 4,750.00 $ 83,081.00 $ 15,552.00 $ 95,330.50 280.99 $ 280.99 $ 70,000.00 $ 49,107.30 6,000.00 $ 6,000.00 $ 4,000.00 $ 4,000.00 $ 9,600.00 $ 200.00 $ 400.00 $ 9,000.00 $ 600.00 4,000.00 4,000.00 9,600.00 200.00 400.00 9,000.00 600.00 Cash $ 103,200.00 Insurance Expense Prepaid Insurance $ Unearned revenues Residential sales $ Tax expense Taxes Payable $ 4,500.00 $ 4,500.00 $ 8,280.00 $ 1,830.83 8,280.00 1,830.83 $ 375,879.29 $ 375,879.29 $ - Jag & Elk Pipes Balance Sheet As of December 31, 2015 Cash Accounts Receivable - Commercial Less: Allowance for bad debts - Commercial Inventory Prepaid insurance total current assets Plumbing equipment and tools Less: Accumulated depreciation, equip & tools Plumbing truck Less: Accumulated depreciation, trucks Total assets $ 15,235 $ 14,050 $ (371) $ 36,893 $ 2,600 $ 68,406 $ 7,600 $ (3,550) $ 47,000 $ (10,500) $ 108,957 Interest Payable Salaries payable Taxes payable Unearned revenues total current liabilities Notes payable Common stock Retained earnings Total liabilities and equity $ $ $ $ $ $ $ $ $ Jag & Elk Pipes Income Statement For the Year Ended December 31, 2015 Residential sales Commercial sales Total sales Cost of residential sales Cost of commerical sales Total cost of sales Gross Margin Operating expenses Operating income Interest expense Income before taxes Income tax expense Net income $ 8,280 $ 95,331 $ 103,611 $ 6,225 $ 42,882 $ 49,107 $ 54,503 $ 37,548 $ 16,956 $ 4,750 $ 12,206 $ 1,831 $ 10,375 750 600 1,431 21,150 23,931 50,000 20,000 15,026 ?? 108,957 SUMIF Count # of months Absolute and Relative reference SUM Appendix B: 1. Identify total commercial services delivered during the year 2. Find total amount of cash collection during the year from commercial customers 3. Compute of Cost of Parts used for Commercial and Residential Services ("consumed", not necessarily same as cash payment) Appendix C: 1. Identify Unearned Revenue at the end of each year 2. Record Realiaed Revenue of the year 3. Find total amount of cash collection during the year from residential customers Appendix D: 1. Make additional proper journal entries for given information Financial Statements 1. Prepare "formal" financial statements in a new tab Excel studying resources to: ACCT4111 Project, \"Accounting Data Analyses\" Part 1: Useful information before you start: Microsoft Excel is the most widely used spreadsheet software and it is a very easy to get started. Various Excel functions will be used in this project. However, details of Excel functions are not explained here. Since Excel includes so many useful functions, it might be useful to know how to get help before a beginner starts any project. (1) From Excel: The Help button in Excel is too small and can easily be ignored. Actually the Help button stays in the top right corner of the window. The button looks like a question mark circle. Or you can use the shortcut key of F1 to enable the Help window. surrounded by a (2) Internet search: Many times, searching for solutions using the internet is an easier way to get help. You would be able to find more beginner-friendly explanations with numerous examples, but without any highly technical terminologies. (3) Lynda.com: All GSU students have a free access to Lynda.com which is an online learning company that helps anyone learn software, design, and business skills to achieve their personal and professional goals. To get some instructions on Excel, go to the school's homepage at http://www.gsu.edu and click on \"Student\" \"Technology.\" Then, click on \"Lynda.com Online Training\" in the middle-left side of the page. After logging in using your GSU student ID and password, click on \"Software\" \"Excel\" and \"2013\" on the left. You would find several video lectures and each one is quite lengthy. However, you can partially watch the lectures on the topics that you are interested in. (4) Reference book: you can purchase a reference book. Most books cover same materials, but it is better to review the way of presentation in the book by yourself, and choose your favorite one. Excel Functions potentially useful for the project (i) Inserting (deleting) columns/rows: Simply highlight columns and rows and right click on your mouse. (ii) To view multiple worksheets simultaneously: When you work on one worksheet and need to make a reference from another worksheet, it is convenient to view multiple worksheets at the same time. Click View New Window and then Arrange All (as Tiled, if you want). You can choose the worksheet name that you want to see for the second window. (iii) The Sum function computes the total amount and Average function computes the average of data within the range. (iv) Counting number of days between two dates: You can use DATEDIF(earlier date, later date, \"D\") function. Use \"M\" for number of months and \"Y\" for years. (Caveat: the count does not include the first period. For example, the difference in months between 1/1 and 12/31 is 11 in this function and you need to add 1 to get a desirable answer of 12.) For the date difference, you can simply perform arithmetic computation for the date difference (such as \"Date difference = Later date - Earlier date\"), since Excel understands dates at Julian calendar format such that 01/01/1900 is the day 1 and then counts number of days thereafter. For example, 01/01/2014 is recognized as \"41640\" regardless of its displayed format at Excel, implying that it is the 41640th day since 01/01/1900. v) Year, Month or Date function: For the date of 12/31/2015, Year (Month or Date) function results in 2015 (12 or 31) (vi) Revisited: Counting number of months between two dates: Using the above YEAR and MONTH functions, you can compute number of months between two dates. For example, the number of months between earlier (i.e. 1/1/2014) and later (i.e. 12/31/2015) can be computed as \"=(YEAR(later)YEAR(earlier))*12 + MONTH(later)-MONTH(earlier)+1\" (vii) IF function: IF function is a very useful and frequently used Excel function. IF(a,b,c) results in an output such that if condition a is true, then it gives b, otherwise it gives c. (viii) AND or OR function: When more than one conditions for are used for the above IF function, AND or OR functions are very useful. For example, OR(x,y) is true if at least one of conditions x or y meets. (ix) Relative and Absolute Reference: When a formula is copied and pasted into different location, the address changes. It is called a Relative reference (such as \"=A1\"). If you do not want to change the address, you can use the Absolute reference, by inserting dollar sign ($) before each element of the cell name (such as \"=$A$1\"). You can use the short key of F4. Mixed reference can also be used in some cases (such as \"=$A1\" or \"=A$1\"). The reference after $ sign does not change when it is copied elsewhere. (x) SUMIF function: You may use the SUMIF function to sum the values in a range that meet criteria that you specify. For example, SUMIF(range, criteria, [sum_range]) computes total amount of numbers in [sum_range] only when the information in the [range] meets [criteria]. Similar functions are AVERAGEIF and COUNTIF functions. Part 2: 1. Fill out the Financial Statement The financial statement may be filled automatically by pointing the excel cells to the appropriate cells in the other tabs. Cash will be used as an example. 1. Select the Financial Statement tab. 2. At an appropriate location, e.g. B4, fill in the cell with the formula that points to the appropriate cell on the appropriate tab that contains the information. In this instance, the formula is \"='Appendix A'!H3\". 3. Fill out the rest of the cells in column B by pointing to the appropriate cell on the other tabs. For instance, cell B5, Accounts Receivable - Commercial should point to cell H4 on the Appendix A tab, and cell B7, Inventory, should point to cell H6 on the Appendix A tab. 2. Calculate total 2015 Cash Debit Transactions Step 1: Calculate the total collected commercial invoices for 2015. 4. Select the Appendix B tab 5. At an appropriate location (e.g. D113), sum up all collected commercial invoices using the formula \"=SUMIF(D3:D111, \">0\