Prepare an Excel workbook which contains the following information: Tab 1: 2020 Trial Balance (provided in...

  

Transcribed Image Text:

Prepare an Excel workbook which contains the following information: Tab 1: 2020 Trial Balance (provided in this document) Tab 2: 2021 Projected Income Statement Tab 3: 2021 Projected Balance Sheet Tab 4: 2021 Projected Statement of Cash Flows Assumptions: 1. Sales will change as follows: a. Material & Supplies Sales will increase 5.0% b. Small Tool Sales will increase 4.0% c. Tool Rental Revenue will continue throughout the 2021 year. An average of 16 tools will be rented each week. The weekly rental per tool is $55. Assume that the average number of tools given will be rented for all 52 weeks. 2. 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.) 3. Small tools, including blades and other items, is expected to total $7,300 in 2021. 4. Office supplies and postage are expected to increase by 9% during 2021. 5. On January 1st, the company will invest $122,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. b. The company will finance the equipment purchased with a 5-year note at 3.50% interest. You will need to use an amortization schedule to find the principle and interest payment amounts. The loan is paid monthly. WALNUT GROVE TRIAL BALANCE For the Years Ended December 31, 2020 and December 31, 2021 2020 2021 Debit Credit Debit Credit Cash 173,000 Accounts Receivable Inventory Prepaid Expenses Building Computers & Software 26,415 32,160 275,000 10,000 Furniture & Fixtures 25,000 Land 75,000 Machinery & Equipment Accumulated Depreciation Accounts Payable Payroll Tax Payable Sales Tax Payable 8,775 12,311 136 22,135 Unearned Revenue Line of Credit 250,000 Notes Payable Peters, J., Capital Peters, M., Capital Retained Earnings 2,500 2,500 201,301 Custom Cabinet Sales Material & Supplies Sales 288,368 Small Tool Sales 35,972 Tool Rental Revenue 16,201 COGS: Custom Cabinets COGS: Material & Supplies COGS: Small Tools COGS: Wages 92,278 21,735 33,721 Depreciation Expense Insurance Expense Office Supplies Expense 8,950 6,426 1,464 6,069 Payroll Tax Expense Postage Expense Small Tool Expense Interest Expense 347 6,162 7,500 Income Tax Expense 38,972 840,199 840,199 Prepare an Excel workbook which contains the following information: Tab 1: 2020 Trial Balance (provided in this document) Tab 2: 2021 Projected Income Statement Tab 3: 2021 Projected Balance Sheet Tab 4: 2021 Projected Statement of Cash Flows Assumptions: 1. Sales will change as follows: a. Material & Supplies Sales will increase 5.0% b. Small Tool Sales will increase 4.0% c. Tool Rental Revenue will continue throughout the 2021 year. An average of 16 tools will be rented each week. The weekly rental per tool is $55. Assume that the average number of tools given will be rented for all 52 weeks. 2. 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.) 3. Small tools, including blades and other items, is expected to total $7,300 in 2021. 4. Office supplies and postage are expected to increase by 9% during 2021. 5. On January 1st, the company will invest $122,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. b. The company will finance the equipment purchased with a 5-year note at 3.50% interest. You will need to use an amortization schedule to find the principle and interest payment amounts. The loan is paid monthly. WALNUT GROVE TRIAL BALANCE For the Years Ended December 31, 2020 and December 31, 2021 2020 2021 Debit Credit Debit Credit Cash 173,000 Accounts Receivable Inventory Prepaid Expenses Building Computers & Software 26,415 32,160 275,000 10,000 Furniture & Fixtures 25,000 Land 75,000 Machinery & Equipment Accumulated Depreciation Accounts Payable Payroll Tax Payable Sales Tax Payable 8,775 12,311 136 22,135 Unearned Revenue Line of Credit 250,000 Notes Payable Peters, J., Capital Peters, M., Capital Retained Earnings 2,500 2,500 201,301 Custom Cabinet Sales Material & Supplies Sales 288,368 Small Tool Sales 35,972 Tool Rental Revenue 16,201 COGS: Custom Cabinets COGS: Material & Supplies COGS: Small Tools COGS: Wages 92,278 21,735 33,721 Depreciation Expense Insurance Expense Office Supplies Expense 8,950 6,426 1,464 6,069 Payroll Tax Expense Postage Expense Small Tool Expense Interest Expense 347 6,162 7,500 Income Tax Expense 38,972 840,199 840,199

Answer rating: 100% (QA)

Stepbystep explanation 2021 Project income statement Walnut Grove The project statement of Cash Flow For the Year Ended December 31 2021 Custom cabine... View the full answer