ACCT $212$ Project $3$ Instructions

This assignment should be completed alone. Students should treat this assignment

similar to the requirements of an in$-$class open$-$book exam.

INSTRUCTIONS:

Using the $2023$ trial balance and additional information below, prepare the

projected $(2024)$ 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$: $2023$ Trial Balance $($provided in this document$)$

$$ Tab $2$: $2024$ Projected Income Statement

$$ Tab $3$: $2024$ Projected Balance Sheet

$$ Tab $4$: $2024$ Projected Statement of Cash Flows Assumptions:

$1.$ Sales will change as follows:

a$.$ Material & Supplies Sales will increase $4\mathrm{.}7\%$

b$.$ Small Tool Sales will increase $9\mathrm{.}2\%$

c$.$ Tool Rental Revenue will continue throughout the $2024$ year. An

average of $17$ tools will be rented each week, at an average of $$105$

per week.

$2.$ 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.$)$

$3.$ Small tools, including blades and other items, will be expected, and are

expected to increase $3\mathrm{.}7\%$ over $2024.$

$4.$ Office supplies and postage are expected to increase by $7\%$ during $2024.$

$5.$ On January $1$st$,$ the company will invest $$130,000$ 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 property, plant, and equipment. The company will

finance the equipment purchase with a $8$ year note at $4\%$ interest. You will

need to use an amortization schedule to find the principal and interest

payment amounts. The loan is paid monthly.

$6.$ In relation to #$5$ above, the custom cabinet sales division begins operations

in $2024.$ The following assumptions must be used to project the impact on

the financial statements.

a$.$ Walnut Grove anticipates that it will sell $170$ cabinets at an average

selling price of $$3,150$ each during $2024.$

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

c$.$ The direct labor per cabinet is $7$ hours, and Walnut Grove pays

$$36/$hour for this labor.

d$.$ Factory overhead is calculated at $60\%$ of direct labor.

$7.$ The building is being depreciated over a $39-$year life. The computers &

software are being depreciated over a $3-$year life, and the furniture & fixtures

are being depreciated over a $5-$year life.

$8.$ Because of the new cabinet division, insurance costs will increase annually by

$$19,750,$ effective January $1.$ The company prepaid $2$ years of this insurance

and received a $3\%$ discount for the $2-$year prepayment.

$9.$ On March $1,$ a new cabinet division manager will be hired at a cost of

$$59,000.$ In addition to the new cabinet division manager, $3$ new employees

will be hired at an average wage of $$21\mathrm{.}00$ per hour, employees work an

average of $40$ hours per week. Payroll taxes should be calculated at $11\mathrm{.}5\%$ of

wages.

$10.$ With $18$ weeks remaining in the year, $4$ additional employees will be hired at

a rate of $$15\mathrm{.}00$ per hour, based on an average of $35$ hours per week.

$11.$ The income tax rate is $21\%.$

$12.$ At the end of the year, Walnut Grove will have $$80,750$ in ending inventory.

$13.$ Purchases are made evenly throughout the year and are paid in full in the

month following purchase.

$14.$ Sales are collected in full the month following the sale. During the month of

December, invoiced sales totaled $$195,000.$

$15.$ The sales tax rate is $5\mathrm{.}8\%.$

$16.$ At the end of the year, Walnut Grove has received full payment for $28$ custom

cabinet orders that will be completed and delivered in January $2025.$ WALNUT GROVE

TRIAL BALANCE