WALNUT GROVE
TRIAL BALANCE
For the Years Ended December 31, 2019 and December 31, 2020
		2019		2020
		Debit	Credit	Debit	Credit
Cash		185,500
Accounts Receivable		125,600
Inventory		55,000
Prepaid Expenses		-
Building		275,000
Computers & Software		10,000
Furniture & Fixtures		25,000
Land		75,000
Machinery & Equipment		-
Accumulated Depreciation			15,385
Accounts Payable			48,500
Payroll Tax Payable			1,050
Sales Tax Payable			7,913
Unearned Revenue			-
Line of Credit			300,000
Notes Payable			-
Peters, J., Capital			2,500
Peters, M., Capital			2,500
Retained Earnings			258,429
Custom Cabinet Sales			-
Material & Supplies Sales			282,714
Small Tool Sales			34,932
Tool Rental Revenue			12,648
Vendor Compensation Revenue			629
COGS: Custom Cabinets		-
COGS: Material & Supplies		90,468
COGS: Small Tools		21,309
COGS: Wages		33,060
Depreciation Expense		8,775
Insurance Expense		6,300
Office Supplies Expense		1,435
Payroll Tax Expense		5,950
Postage Expense		340
Small Tool Expense		6,041
Interest Expense		11,900
Income Tax Expense		30,522
		967,200	967,200

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:

   1. Material & Supplies Sales will increase 8.5%

   2. Small Tool Sales will increase 10%

   3. 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 $60. 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 $8,000 in 2020.

5. Office supplies and postage are expected to increase by 35% during 2020.

6. On January 1st, the company will invest $135,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.65% 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.)

1. Walnut Grove anticipates that it will sell 110 cabinets at an average selling price of $2,700.

2. Direct materials per cabinet are $850 per unit.

3. The direct labor per cabinet is 5 hours, and Walnut Grove pays $30.00/hour for

   this labor.

4. Factory overhead is calculated at 65% of direct labor.

8. The building is being depreciated over a 39-year life.

9. Because of the new cabinet division, yearly insurance costs will increase by $29,500,

   effective January 1. The company prepaid 2 years of this insurance and received a 5%

   discount for the 2-year prepayment.

10. On January 1, a new cabinet division manager will be hired at a cost of $55,500. In

    additional to the new cabinet division manager, 3 new employees will be hired at an average wage of $18.50 per hour, employees work an average of 40 hours per week. Payroll taxes should be calculated at 20% of wages.

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

12. The income tax rate is 21%.

13. At the end of the year, Walnut Grove will have $68,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 $142,500.

16. The sales tax rate is 6.3%.

17. At the end of the year, Walnut Grove has received full payment for 25 custom cabinet

    orders that will be completed in January 2021.