Th assignment should be completed alone. 5 students should treat thair ossignment similiar to the requirements of an in class open book exam. INSTRUCTIONS: Using the 2022 trial balance and additional information below, prepare the projected (2023) financial statements for Walnut Grove. The prior year data (provided) is the srarting point for your projections, and then each of the assumptions fisted below will also be used. Prepare an Excel workbook which contains the following information: Tab 1: 2022. Trial falance (peovided in this documend) Tab 2: 2023 Projected income Statement Tab 3: 2023 Projected Balance Sheet. Tab 4: 2023: Projected Statement of Cash Flows Assumptions: 1. Sales will change as follows: a. Material & Supplies 53 teswill increase 6:5 b. Smali Tocl Sales will increase 3\% c Tool Rental Revenue will continue throughout the 2023 year. An werage of is tools will be rentedeach week, at an average of $75 per week 2. Vendorcompensation willincrease consistently with Material \& Supplies Sales and Small Tooks5ales. 1. Sales will change as follows: a. Material 8 Supplies Sales will increare 6% b. Small Tool 5 ales will increase 3% c. Tool Rental Revenue will continue throughout the 2023 year, An average of 18 tools will be rented each week, at an average of 575 per week. 2. Vendor compensation will increase consistently with Material 8 Supplies sales and Small Tools Sales. 3. Cost of sales for materials and supplies and smali tools will increase proportionately based on their current percentage of sales, respectively. (HINT: You will need to use vertical analysis.) 4. Small tools, induding blades and other items, will be expected, and are expected to total 57,900 in 2023 5. Office supplies and pestage are expected to increase by 114 during 2023. 6. On January 1st, the company will ievest $124,500 in new equipment for its custom cabinet division. This equipment will have a 6-year life and should be depreciated using the straight-line method. This purchase represents the only expected change to groperty. plant, and equipment. The company will finance the equipment purchase with a 6 year note at 4.50% interest. You will need to use an amortization schedule to find the principle and interest payment amounts. The loan is paid menthly. 7. In eelation to 6 above, the ourtom cabinet sales division begins operationsin 2023. The folfawingassumptions must be used to project the impact on the financial statements. (Hint: You may need to add accounts to the thal balance.) a. Walnut Grove anticipates that it will sell 175 cabinets at an average selling price of $3,700 each during 2023 . b. Direct materials per cabinet are $525 per unit. c. The direct labor per cabinet is 4.0 hours, and Walnut Grove pays 529/ hour for this labor: d. Factory overhead is calculated at 50% of direct labor. 8. The building is being depreciated over a 39-year life. 9. Because of the new cabinet division, insurance costs will increase annually by $27,500, effective January 1 . The company prepaid 2 years of this insurance and received a 3% discount for the 2-year prepayment. 10. On March 1, a new cabinet division managerwill be hifed at a cost of $63,000. In addition to the new cabinet division manager, 2 new employees will be hired at an average wage of $23.50 per hour, employees work an average of 40 hours per week. Payroll taxes should be calculated at 18 s of wages. 11. With 20 weeks remaining in the year, 3 additional employees will be hired at a rate of 518,50 perhour, 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 $38,000 in ending inventory. 14. Purchases are made evenly throughout the yearand 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 $148,500. 16. The sales tax rate is 650%. 17. At the end of the year, Walnut Grove has received full payment for 19 custom cabinet orders that will be completedinjanuary 2023. Home Insert Draw Page Layout Formulas Data Review A4 fx Walnut Grove Projected income Statement For the Year Ended December 31, 2023 Home Insert Draw Page Layout Formulas Data Review A4 =fx A 8 c D Walnut Grove Projected Balance Sheet As of December 31,2023 Projected 5tatement of Cash Flows For the Year Ended December 31, 2023 Th assignment should be completed alone. 5 students should treat thair ossignment similiar to the requirements of an in class open book exam. INSTRUCTIONS: Using the 2022 trial balance and additional information below, prepare the projected (2023) financial statements for Walnut Grove. The prior year data (provided) is the srarting point for your projections, and then each of the assumptions fisted below will also be used. Prepare an Excel workbook which contains the following information: Tab 1: 2022. Trial falance (peovided in this documend) Tab 2: 2023 Projected income Statement Tab 3: 2023 Projected Balance Sheet. Tab 4: 2023: Projected Statement of Cash Flows Assumptions: 1. Sales will change as follows: a. Material & Supplies 53 teswill increase 6:5 b. Smali Tocl Sales will increase 3\% c Tool Rental Revenue will continue throughout the 2023 year. An werage of is tools will be rentedeach week, at an average of $75 per week 2. Vendorcompensation willincrease consistently with Material \& Supplies Sales and Small Tooks5ales. 1. Sales will change as follows: a. Material 8 Supplies Sales will increare 6% b. Small Tool 5 ales will increase 3% c. Tool Rental Revenue will continue throughout the 2023 year, An average of 18 tools will be rented each week, at an average of 575 per week. 2. Vendor compensation will increase consistently with Material 8 Supplies sales and Small Tools Sales. 3. Cost of sales for materials and supplies and smali tools will increase proportionately based on their current percentage of sales, respectively. (HINT: You will need to use vertical analysis.) 4. Small tools, induding blades and other items, will be expected, and are expected to total 57,900 in 2023 5. Office supplies and pestage are expected to increase by 114 during 2023. 6. On January 1st, the company will ievest $124,500 in new equipment for its custom cabinet division. This equipment will have a 6-year life and should be depreciated using the straight-line method. This purchase represents the only expected change to groperty. plant, and equipment. The company will finance the equipment purchase with a 6 year note at 4.50% interest. You will need to use an amortization schedule to find the principle and interest payment amounts. The loan is paid menthly. 7. In eelation to 6 above, the ourtom cabinet sales division begins operationsin 2023. The folfawingassumptions must be used to project the impact on the financial statements. (Hint: You may need to add accounts to the thal balance.) a. Walnut Grove anticipates that it will sell 175 cabinets at an average selling price of $3,700 each during 2023 . b. Direct materials per cabinet are $525 per unit. c. The direct labor per cabinet is 4.0 hours, and Walnut Grove pays 529/ hour for this labor: d. Factory overhead is calculated at 50% of direct labor. 8. The building is being depreciated over a 39-year life. 9. Because of the new cabinet division, insurance costs will increase annually by $27,500, effective January 1 . The company prepaid 2 years of this insurance and received a 3% discount for the 2-year prepayment. 10. On March 1, a new cabinet division managerwill be hifed at a cost of $63,000. In addition to the new cabinet division manager, 2 new employees will be hired at an average wage of $23.50 per hour, employees work an average of 40 hours per week. Payroll taxes should be calculated at 18 s of wages. 11. With 20 weeks remaining in the year, 3 additional employees will be hired at a rate of 518,50 perhour, 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 $38,000 in ending inventory. 14. Purchases are made evenly throughout the yearand 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 $148,500. 16. The sales tax rate is 650%. 17. At the end of the year, Walnut Grove has received full payment for 19 custom cabinet orders that will be completedinjanuary 2023. Home Insert Draw Page Layout Formulas Data Review A4 fx Walnut Grove Projected income Statement For the Year Ended December 31, 2023 Home Insert Draw Page Layout Formulas Data Review A4 =fx A 8 c D Walnut Grove Projected Balance Sheet As of December 31,2023 Projected 5tatement of Cash Flows For the Year Ended December 31, 2023