Using the 2016 trial balance and additional information below, prepare the projected (2017) 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: 2016 Trial Balance & 2017 Projected Trial Balance

Tab 2: 2017 Projected Income Statement

Tab 3: 2017 Projected Balance Sheet

Tab 4: 2017 Projected Statement of Cash Flows Assumptions

WALNUT GROVE
TRIAL BALANCE
For the Year Ended December 31, 2016
		Debit	Credit
Cash		369,006
Accounts Receivable		25,372
Inventory		21,234
Prepaid Expenses		1,445
Building		128,400
Computers & Software		1,043
Furniture & Fixtures		3,932
Land		53,500
Machinery & Equipment		-
Accumulated Depreciation			5,798
Accounts Payable			12,697
Payroll Tax Payable			4,986
Sales Tax Payable			2,602
Unearned Revenue			-
Line of Credit			294,376
Notes Payable			-
Peters, J., Capital			1,070
Peters, M., Capital			1,070
Retained Earnings			144,139
Material & Supplies Sales			276,622
Small Tool Sales			9,405
Tool Rental Revenue			4,672
Vendor Compensation Revenue			429
COGS: Material & Supplies		88,933
COGS: Small Tools		13,577
COGS: Wages		18,027
Depreciation Expense		5,798
Insurance Expense		4,334
Office Supplies Expense		127
Payroll Tax Expense		1,379
Postage Expense		51
Interest Expense		14,976
Small Tool Expense		6,732
		757,866	757,866

1. Sales will change as follows:

a. Material & Supplies Sales will increase 4%

b. Small Tool Sales will increase 3%

c. Tool Rental Revenue will continue throughout the 2017 year. An average of 20 tools will be rented each week, at an average of $100 per week.

2. Vendor compensation will increase consistently with 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, will be expected, and are expected to total $7,500 in 2017.

5. Office supplies and postage are expected to increase by 50% during 2017.

6. On February 1st, the company will invest $175,000 in new equipment for its custom cabinet division. This equipment will havea 5-year life and should be depreciated using the straight-line method. This purchase represents the only expected change to property, plant, and equipment. The company will finance the equipment purchase with a 5 year note at 2.5% interest.

7. In relation to #6 above, the custom cabinet sales division begins operations in 2017. 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 1,000 cabinets at an average selling price of $850 each during 2017.

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

c. The direct labor per cabinet is 3.75 hours, and Walnut Grove pays $20/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 $25,000, effective June 1. The company prepaid 2 years of this insurance and received a 5% discount for the 2-year prepayment.

10. On March 1, a new cabinet division manager will be hired at a cost of $32,000. In additional to the new cabinet division manager, 2 new employees will be hired at an average wage of $15 per hour, employees work an average of 40 hours per week. Payroll taxes should be calculated at 15% of wages.

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

12. The income tax rate is 30%.

13. At the end of the year, Walnut Grove will have $23,226 in ending inventory.

14. 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 $126,300.

16. The sales tax rate is 5.5%.

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