INSTRUCTIONS: Using the 2017 trial balance and additional information below, pre

Question

INSTRUCTIONS: Using the 2017 trial balance and additional information below, prepare the projected (2018) 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: 2017 Trial Balance (provided in this document)

• Tab 2: 2018 Projected Income Statement

•Tab 3: 2018 Projected Balance Sheet

• Tab 4: 2018 Projected Statement of Cash Flows Assumptions:

1. Sales will change as follows:

a. Material & Supplies Sales will increase 5%

b. Small Tool Sales will increase 6%

c. Tool Rental Revenue will continue throughout the 2018 year. An average of 25 tools will be rented each week, at an average of $105 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 $8,700 in 2018.

5. Office supplies and postage are expected to increase by 45% during 2018.

6. On January 1st, the company will invest $125,000 in new equipment for its custom cabinet division. 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. The company will finance the equipment purchase with a 5 year note at 3.25% 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 2018. 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,250 cabinets at an average selling price of $1550 each during 2018.

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

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

d. 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, insurance costs will increase annually by $22,000, effective January 1. The company prepaid 2 years of this insurance and received a 4% discount for the 2-year prepayment.

10. On March 1, a new cabinet division manager will be hired at a cost of $48,000. In additional to the new cabinet division manager, 2 new employees will be hired at an average wage of $20 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 $18 per hour, 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,275 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 $156,800.

16. The sales tax rate is 5.5%.

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

WALNUT GROVE TRIAL BALANCE For the Year Ended December 31, 2017 Credit 250,000 Computers & Software 60,000 325,000 Material & Supplies Sales Tool Rental Revenue COGS: Material & Supplies 825,482

Explanation / Answer

Calculating the monthly payment on the 5 Year Note ,using the PVOA formula, 125000=Pmt.*(1-(1+(0.0325/12))^-60)/(0.0325/12) we get the Monthly payment as 2260 Note payable amortisation (2018) No.of mth. Mthly.annuity Tow. Int. Tow. Princ. Prin. Bal. 0 125000 1 2260 339 1921 123079 2 2260 333 1927 121152 3 2260 328 1932 119220 4 2260 323 1937 117283 5 2260 318 1942 115341 6 2260 312 1948 113393 7 2260 307 1953 111440 8 2260 302 1958 109482 9 2260 297 1963 107518 10 2260 291 1969 105550 11 2260 286 1974 103575 12 2260 281 1979 101596 27120 3716 23404 101596 Projected Income Statement (2018) Custom cabinet sales 1250*1550= 1937500 Material &supplies sales 304284*1.05= 319498 Small tool sales 23513*1.06= 24924 Tool Rental Revenue 105*52= 5460 Vendor compensation revenue (589/(304284+23513))*(319498+24924) 619 Total revenues 2288001 COGS: Materials &supplies (97371/304284*319498)= 102239 COGS: Small Tools (16292/23513*24924)= 17270 Small Tool expense 8700 Office supplies expense 1765*1.45= 2559 Postage expense 268*1.45= 389 Depreciation expense 125000/5= 25000 Direct material(C/C) 1250*195= 243750 Direct labor 1250*3.5*25= 109375 Factory OH 109375*65%= 71094 Depreciation-Buildings 250000/39= 6410 Insurance expense 4034+(96%*22000) 25154 Expired prepaid expense 1474 Cabinet divn. Mgr. salary 48000 Employee wages (2*20*40*52)= 83200 Addl. Employee wages (2*20*18*35)= 25200 Payroll taxes (48000+83200+25200+109375)*15%= 39866 Total operating expenses 809680 Operating Income 1478321 Non-operating expense Interest on Note (as per amortsn.table) 3716 Income before tax 1474605 Less: Income Tax at 21% 309667 Net Income after tax 1164938 Cash Projections: Opening Balance 287567 Add: Sales collections Receivables collections 27402 2018 sales (1937500+319498+24924+5460)-156800= 2130582 Unearned revenue (18*1550) 27900 2185884 Total cash available 2473451 Less: Cash Disbursements: Accounts payables 13332 Payroll tax payable 901 Sales tax payable 1507 Payment for Purchases(102239+17270+243750+38275-31851)/12*11 338876 Small Tool expense 8700 Office supplies expense 2559 Postage expense 389 Direct labor 109375 Factory OH 71094 Insurance expense 25154 Prepaid insurance(22000*96%) 21120 Cabinet divn. Mgr. salary 48000 Employee wages 83200 Addl. Employee wages 25200 Payroll taxes 39866 Principal repayment 23404 Income tax expense 309667 Line of credit 325000 Total disbursements 1447344 Ending cash balance 1026107 Projected Balance Sheet Current assets Cash 1026107 Accounts Receivable 156800 Inventory 38275 Prepaid expenses(22000*96%) 21120 Building 250000 Computers &software 1500 Furn. & Fix. 5000 Land 60000 Equipment 125000 Acc. Depn.(7710+6410+25000) -39120 Total assets 1644682 Liabilities & Equity Accounts payable(102239+17270+243750+38275-31851)/12*1 30807 Unearned revenue 27900 Sales tax payable(1937500+319498+24924)*5.5% 125506 Note Payable(125000-23404) 101596 Peter J capital 1000 Peter M capital 1000 Retained earnings (137947+1164938) 1302885 Liabilities & Equity 1590694 53988

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.