Ru Individual Learning Project 3 > ACCT 212 3 wk Project 3 Instruct X G formula for vertical analysis - GoX + c File C:/Users/fathi/Downloads/ACCT%2021296208%20wk_Project%203%20Instructions%20Fall%202020%20(1).pdf INSTRUCTIONS: Using the 2019 trial balance and additional information below, prepare the projected (2020) financial statements for Walnut Grove. The prior year data (provided) is the starting point for your projections, and then each of the assumptions listed below will also be used. Prepare an Excel Workbook which contains the following information: Tab 1: 2019 Trial Balance (provided in this document) Tab 2: 2020 Projected Income Statement Tab 3:2020 Projected Balance Sheet Tab 4:2020 Projected Statement of Cash Flows Assumptions: 1. Sales will change as follows: a. Material & Supplies Sales will increase 7.5% b. Small Tool Sales will increase 8.0% c. Tool Rental Revenue will continue throughout the 2020 year. An average of 20 tools will be rented each week. The weekly rental per tool is $70. Assume that the average number of tools given will be rented for all 52 weeks. 2. Vendor Compensation will increase consistently with the sum increase of Material & Supplies Sales and Small Tools Sales 3. Cost of sales for materials and supplies and small tools will increase proportionately based on their current percentage of sales, respectively. (HINT: You will need to use vertical analysis.) 4. Small tools, including blades and other items, is expected to total $7,500 in 2020. 5. Office supplies and postage are expected to increase by 20% during 2020 6. On January 1st, the company will invest $140,000 in new equipment for its custom cabinet division a. This equipment will have a 5-year life and should be depreciated using the straight-line method. This purchase represents the only expected change to property, plant, and equipment. (2A it 2 ENG 12:29 PM Lu Individual Learning Project 3 x ACCT 212 8 wk_Project 3 Instruct X G formula for vertical analysis - Go X | + c File C:/Users/fathi/Downloads/ACCT-20212%209%20wk_Project%203%20instructions%20Fall%202020%20(1).pdf b. The company will finance the equipment purchased with a 5 year note at 3.25% interest. You will need to use an amortization schedule to find the principle and interest payment amounts. The loan is paid monthly 7. In relation to #6 above, the custom cabinet sales division begins operations in 2020. The following assumptions must be used to project the impact on the financial statements (Hint: You may need to add accounts to the trial balance. a. Walnut Grove anticipates that it will sell 120 cabinets at an average selling price of $2,635. b. Direct materials per cabinet are $925 per unit. c. The direct labor per cabinet is 6 hours, and Walnut Grove pays $35.00/hour for this labor d. Factory overhead is calculated at 60% of direct labor. B. The building is being depreciated over a 39-year life, the computers and software are being depreciated over a 3-year life, and the furniture and fixtures are being depreciated over a 5-year life, all using straight-line depreciation. 9. Because of the new cabinet division, annual insurance costs will increase to $30,000, effective January 1. The company prepaid 2 years of this insurance and received a 4% discount for the 2-year prepayment. 10. On January 1, a new cabinet division manager will be hired at a cost of $60,000. In additional to the new cabinet division manager, 3 new employees will be hired at an average wage of $20.00 per hour, employees work an average of 40 hours per week. Payroll taxes should be calculated at 22% of wages. 11. With 22 weeks remaining in the year, 3 additional employees will be hired at a rate of $17.00 per hour, based on an average of 35 hours per week 12. The income tax rate is 21% 13. At the end of the year, Walnut Grove will have $62,000 in ending inventory. 14. In relation #13, purchases are made evenly throughout the year and are paid in full in the month following purchase. 15. Sales are collected in full the month following the sale. During the month of December, invoiced sales totaled $156,750. 16. The sales tax rate is 6.3%. 17. At the end of the year, Walnut Grove has received full payment for 15 custom cabinet orders that will be completed in January 2021. OBI it 2 ENG 12:29 PM J K L M N A B C E F G H 1 WALNUT GROVE 2 TRIAL BALANCE 3 For the Years Ended December 31, 2019 and December 31, 2020 4 2019 2020 5 Debit Credit Debit Credit 6 Cash 185,500 7 Accounts Receivable 125,600 8 Inventory 55,000 9 Prepaid Expenses 10 Building 275,000 11 Computers & Software 10,000 12 Furniture & Fixtures 25,000 13 Land 75,000 14 Machinery & Equipment 15 Accumulated Depreciation 15,385 16 Accounts Payable 48,500 17 Payroll Tax Payable 1,050 18 Sales Tax Payable 7,913 19 Unearned Revenue 20 Line of Credit 300,000 21 Notes Payable 22 Peters, J., Capital 2,500 23 Peters, M., Capital 2,500 24 Retained Earnings 258,429 25 Custom Cabinet Sales 26 Material & Supplies Sales 282,714 303,918 27 Small Tool Sales 34,932 37,727 28 Tool Rental Revenue 12,648 85,448 29 Vendor Compensation Revenue 629 23,999 30 COGS: Custom Cabinets 31 COGS: Material & Supplies 90,468 32 COGS: Small Tools 21,309 33 COGS: Wages 33,060 34 Depreciation Expense 8,775 35 Insurance Expense 6,300 36 Office Supplies Expense 1,435 37 Payroll Tax Expense 5,950 38 Postage Expense 340 39 Small Tool Expense 6,041 40 Interest Expense 11,900 41 Income Tax Expense 30,522 42 967,200 967,200 43 44 45 46 47 48 49 50 51 Trial Balance_Walnut Grove Proj Income Statement Proj Balance Sheet Proj Cash Flows Ready Ru Individual Learning Project 3 > ACCT 212 3 wk Project 3 Instruct X G formula for vertical analysis - GoX + c File C:/Users/fathi/Downloads/ACCT%2021296208%20wk_Project%203%20Instructions%20Fall%202020%20(1).pdf INSTRUCTIONS: Using the 2019 trial balance and additional information below, prepare the projected (2020) financial statements for Walnut Grove. The prior year data (provided) is the starting point for your projections, and then each of the assumptions listed below will also be used. Prepare an Excel Workbook which contains the following information: Tab 1: 2019 Trial Balance (provided in this document) Tab 2: 2020 Projected Income Statement Tab 3:2020 Projected Balance Sheet Tab 4:2020 Projected Statement of Cash Flows Assumptions: 1. Sales will change as follows: a. Material & Supplies Sales will increase 7.5% b. Small Tool Sales will increase 8.0% c. Tool Rental Revenue will continue throughout the 2020 year. An average of 20 tools will be rented each week. The weekly rental per tool is $70. Assume that the average number of tools given will be rented for all 52 weeks. 2. Vendor Compensation will increase consistently with the sum increase of Material & Supplies Sales and Small Tools Sales 3. Cost of sales for materials and supplies and small tools will increase proportionately based on their current percentage of sales, respectively. (HINT: You will need to use vertical analysis.) 4. Small tools, including blades and other items, is expected to total $7,500 in 2020. 5. Office supplies and postage are expected to increase by 20% during 2020 6. On January 1st, the company will invest $140,000 in new equipment for its custom cabinet division a. This equipment will have a 5-year life and should be depreciated using the straight-line method. This purchase represents the only expected change to property, plant, and equipment. (2A it 2 ENG 12:29 PM Lu Individual Learning Project 3 x ACCT 212 8 wk_Project 3 Instruct X G formula for vertical analysis - Go X | + c File C:/Users/fathi/Downloads/ACCT-20212%209%20wk_Project%203%20instructions%20Fall%202020%20(1).pdf b. The company will finance the equipment purchased with a 5 year note at 3.25% interest. You will need to use an amortization schedule to find the principle and interest payment amounts. The loan is paid monthly 7. In relation to #6 above, the custom cabinet sales division begins operations in 2020. The following assumptions must be used to project the impact on the financial statements (Hint: You may need to add accounts to the trial balance. a. Walnut Grove anticipates that it will sell 120 cabinets at an average selling price of $2,635. b. Direct materials per cabinet are $925 per unit. c. The direct labor per cabinet is 6 hours, and Walnut Grove pays $35.00/hour for this labor d. Factory overhead is calculated at 60% of direct labor. B. The building is being depreciated over a 39-year life, the computers and software are being depreciated over a 3-year life, and the furniture and fixtures are being depreciated over a 5-year life, all using straight-line depreciation. 9. Because of the new cabinet division, annual insurance costs will increase to $30,000, effective January 1. The company prepaid 2 years of this insurance and received a 4% discount for the 2-year prepayment. 10. On January 1, a new cabinet division manager will be hired at a cost of $60,000. In additional to the new cabinet division manager, 3 new employees will be hired at an average wage of $20.00 per hour, employees work an average of 40 hours per week. Payroll taxes should be calculated at 22% of wages. 11. With 22 weeks remaining in the year, 3 additional employees will be hired at a rate of $17.00 per hour, based on an average of 35 hours per week 12. The income tax rate is 21% 13. At the end of the year, Walnut Grove will have $62,000 in ending inventory. 14. In relation #13, purchases are made evenly throughout the year and are paid in full in the month following purchase. 15. Sales are collected in full the month following the sale. During the month of December, invoiced sales totaled $156,750. 16. The sales tax rate is 6.3%. 17. At the end of the year, Walnut Grove has received full payment for 15 custom cabinet orders that will be completed in January 2021. OBI it 2 ENG 12:29 PM J K L M N A B C E F G H 1 WALNUT GROVE 2 TRIAL BALANCE 3 For the Years Ended December 31, 2019 and December 31, 2020 4 2019 2020 5 Debit Credit Debit Credit 6 Cash 185,500 7 Accounts Receivable 125,600 8 Inventory 55,000 9 Prepaid Expenses 10 Building 275,000 11 Computers & Software 10,000 12 Furniture & Fixtures 25,000 13 Land 75,000 14 Machinery & Equipment 15 Accumulated Depreciation 15,385 16 Accounts Payable 48,500 17 Payroll Tax Payable 1,050 18 Sales Tax Payable 7,913 19 Unearned Revenue 20 Line of Credit 300,000 21 Notes Payable 22 Peters, J., Capital 2,500 23 Peters, M., Capital 2,500 24 Retained Earnings 258,429 25 Custom Cabinet Sales 26 Material & Supplies Sales 282,714 303,918 27 Small Tool Sales 34,932 37,727 28 Tool Rental Revenue 12,648 85,448 29 Vendor Compensation Revenue 629 23,999 30 COGS: Custom Cabinets 31 COGS: Material & Supplies 90,468 32 COGS: Small Tools 21,309 33 COGS: Wages 33,060 34 Depreciation Expense 8,775 35 Insurance Expense 6,300 36 Office Supplies Expense 1,435 37 Payroll Tax Expense 5,950 38 Postage Expense 340 39 Small Tool Expense 6,041 40 Interest Expense 11,900 41 Income Tax Expense 30,522 42 967,200 967,200 43 44 45 46 47 48 49 50 51 Trial Balance_Walnut Grove Proj Income Statement Proj Balance Sheet Proj Cash Flows Ready