Using the 2020 trial balance and additional information below, prepare the projected (2021) 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: 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 4.5% 

b. Small Tool Sales will increase 3.0% 

c. Tool Rental Revenue will continue throughout the 2021 year. 

An average of 10 tools will be rented each week. The weekly rental per tool is $45. 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 $6,436 in 2021. 

5. Office supplies and postage are expected to increase by 10% during 2021.

 6. On January 1st, the company will invest $115,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 4.50% 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 2021. 

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 100cabinets at an average selling price of $2,500.

b. Direct materials per cabinet are $700per unit. 

c. The direct labor per cabinet is 10hours, and Walnut Grove pays $40.00/hour for this labor. 

d. Factory overhead is calculated at 45% of direct labor.8.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 $24,500, effective January 1. The company prepaid 2years of this insurance and received a 3% discount for the 2-year prepayment.

10.On January1, a new cabinet division manager will be hired at a cost of $62,000. In additional to the new cabinet division manager, 3new employees will be hired at an average wage of $21.50per hour, employees work an average of 40hours per week. Payroll taxes and benefits should be calculated at 21% of wages.

11. With 20weeks remaining in the year, 3additional employees will be hired at a rate of $18.50per hour, based on an average of 32hours per week.

12. The income tax rate is 25%.13.At the end of the year, Walnut Grove will have $35,000in 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 $75,550.

16.The sales tax rate is 6.5%.

17. At the end of the year, Walnut Grove has received full payment for 13custom cabinet orders that will be completed in January 2022.

Answer rating: 100% (QA)

2021 Project income statement 2021 2021 Custom cabinet sales Material supplies 288368 5 3027864 sale... View the full answer