Hey i have question with my intermed accounting 4111 excel project and i need help.

Jag Elk Pipes Trial Balance 2009 Name: Neel Patel 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 $ $ $ $ $ 93,302 50,000 13,878 20,000 4,651 $ 93,302 2015 transactions debit credit Trial Balance 12/31/15 debit credit Jag Elk Pipes Job Log 2009 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 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 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 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 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 LEGENDS Commercial Invoices: Service Date: The date when the service has been Billed: The price of service billed to customers Cash Collection Date: The date of cash collection, b Cost of Parts: Cost of parts inventory used for each Residentil Services: Date: The date when the service has been delivere Cost of Parts: Cost of parts inventory used for each Jag Elk Pipes Job Log 2009 Service date 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 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Commercial Invoices Billed Cash Collection Date 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 cost of parts $ 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 Residential Services date cost of parts 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 $ - LEGENDS Jag Elk Pipes Job Log 2009 Service date 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 9/15/2015 9/16/2015 9/20/2015 9/25/2015 9/26/2015 9/30/2015 10/1/2015 10/7/2015 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Commercial Invoices Billed Cash Collection Date 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 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 cost of parts $ 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 $ 866.70 $ 133.65 $ 581.40 $ 211.50 $ 232.20 $ 423.00 $ 331.20 $ 215.10 Residential Services date cost of parts 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 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 LEGENDS Jag Elk Pipes Job Log 2009 Service date 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 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 cost of parts $ 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 Residential Services date cost of parts 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 LEGENDS Jag Elk Pipes Job Log 2009 Service date Commercial Invoices Billed Cash Collection Date cost of parts Residential Services date cost of parts 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 LEGENDS Jag Elk Pipes Job Log 2009 Service date Commercial Invoices Billed Cash Collection Date cost of parts Residential Services date cost of parts 12/21/2015 $ 2.00 12/25/2015 $ 18.00 12/27/2015 $ 12/30/2015 $ 14.00 LEGENDS he date when the service has been delivered e of service billed to customers Date: The date of cash collection, blank if uncollected by the end of year ost of parts inventory used for each service when the service has been delivered ost of parts inventory used for each service Jag Elk Pipes Job Log 2009 Jag Elk Pipes Job Log 2009 Jag Elk Pipes Job Log 2009 Jag Elk Pipes Job Log 2009 Jag Elk Pipes Job Log 2009 Jag Elk Pipes Job Log 2009 Jag Elk Pipes Residential Contracts three years of plumbing services (total $648, $18 per month) client date paid date effective fee R 101 1/1/2014 1/1/2014 $ 648.00 R 102 1/18/2014 2/1/2014 $ 648.00 R 103 2/2/2014 2/1/2014 $ 648.00 R 104 2/17/2014 3/2/2014 $ 648.00 R 105 2/28/2014 3/2/2014 $ 648.00 R 106 3/15/2014 3/1/2014 $ 648.00 R 107 3/27/2014 3/1/2014 $ 648.00 R 108 4/15/2014 4/1/2014 $ 648.00 R 109 4/25/2014 4/1/2014 $ 648.00 R 110 5/11/2014 5/1/2014 $ 648.00 R 111 5/21/2014 5/1/2014 $ 648.00 R 112 5/31/2014 5/1/2014 $ 648.00 R 113 6/17/2014 6/1/2014 $ 648.00 R 114 7/1/2014 7/1/2014 $ 648.00 R 115 7/13/2014 8/1/2014 $ 648.00 R 116 8/2/2014 8/1/2014 $ 648.00 R 117 8/18/2014 9/1/2014 $ 648.00 R 118 8/30/2014 9/1/2014 $ 648.00 R 119 9/11/2014 9/1/2014 $ 648.00 R 120 9/29/2014 10/1/2014 $ 648.00 R 121 10/14/2014 10/1/2014 $ 648.00 R 122 10/29/2014 10/1/2014 $ 648.00 R 123 11/15/2014 11/1/2014 $ 648.00 R 124 11/25/2014 12/1/2014 $ 648.00 R 125 12/5/2014 12/1/2014 $ 648.00 R 126 12/22/2014 12/1/2014 $ 648.00 R 127 1/1/2015 1/1/2015 $ 648.00 R 128 1/21/2015 1/1/2015 $ 648.00 R 129 2/5/2015 2/1/2015 $ 648.00 R 130 2/23/2015 2/1/2015 $ 648.00 R 131 3/12/2015 3/1/2015 $ 648.00 R 132 3/26/2015 4/1/2015 $ 648.00 R 133 4/11/2015 4/1/2015 $ 648.00 R 134 4/22/2015 5/1/2015 $ 648.00 R 135 5/4/2015 5/1/2015 $ 648.00 R 136 5/23/2015 6/1/2015 $ 648.00 R 137 6/7/2015 6/1/2015 $ 648.00 R 138 6/19/2015 6/1/2015 $ 648.00 R 139 7/6/2015 7/1/2015 $ 648.00 R 140 7/23/2015 8/1/2015 $ 648.00 R 141 8/10/2015 9/1/2015 $ 648.00 R 142 8/24/2015 9/1/2015 $ 648.00 Jag Elk Pipes Residential Contracts R 143 R 144 R 145 R 146 R 147 R 148 R 149 R 150 9/8/2015 9/24/2015 10/6/2015 10/17/2015 11/4/2015 11/15/2015 12/1/2015 12/20/2015 9/1/2015 10/1/2015 10/1/2015 11/1/2015 11/1/2015 11/1/2015 12/1/2015 12/1/2015 $ 648.00 $ 648.00 $ 648.00 $ 648.00 $ 648.00 $ 648.00 $ 648.00 $ 648.00 $ 32,400.00 Jag Elk Pipes Client Meeting Notes 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 $ 4,000.00 $ 6,000.00 $ 4,000.00 $ 70,000.00 $ 9,600.00 $ 200.00 $ 400.00 $ 9,000.00 Pending amounts and other data: Last two week of wages and payroll taxes for part time worker (unpaid yet) $ Estimated bad debts: all open AR's at year end 600.00 2.00% 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 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 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 n 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 commercial customers ential Services ("consumed", not necessarily same as cash payment) residential customers