NSTRUCTIONS:

Using the $2024$ trial balance and additional information below, prepare the projected $(2025)$

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

$$ Tab $2$: $2025$ Projected Income Statement

$$ Tab $3$: $2025$ Projected Balance Sheet

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

$1.$ Sales will change as follows:

a$.$ Material & Supplies Sales will increase $5\mathrm{.}1\%$

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

c$.$ Tool Rental Revenue will continue throughout the $2025$ year. An average of $18$

tools will be rented each week. The weekly rental per tool is $$35.$ Assume that

the average number of tools given will be rented for all $52$ weeks.

$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 expense, including blades and other items, is expected to total $$7,800$ in $2025.$

$4.$ Office supplies and postage are expected to increase by $5\mathrm{.}0\%$ during $2025.$

$5.$ On January $1$st$,$ the company will invest $$145,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 $6\mathrm{.}75\%$

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 $2025.$ 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 $165$ cabinets at an average selling price

of $$2,500.$

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

c$.$ The direct labor per cabinet is $5$ hours, and Walnut Grove pays $$27\mathrm{.}50/$hour for

this labor.

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

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

$8.$ Because of the new cabinet division, annual insurance costs will increase to $$22,000,$

effective January $1.$ The company prepaid $2$ years of this insurance and received a $3\mathrm{.}5\%$

discount for the $2-$year prepayment.

$9.$ On January $1,$ a new cabinet division manager will be hired at a cost of $$60,000.$ In

additional to the new cabinet division manager, $3$ new employees will be hired at an

average wage of $$20\mathrm{.}50$ per hour, employees work an average of $40$ hours per week.

Payroll taxes and benefits should be calculated at $20\%$ of wages.

$10.$ With $20$ weeks remaining in the year, $2$ additional employees will be hired at a rate of

$$17\mathrm{.}50$ per hour, based on an average of $35$ hours per week.

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

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

$13.$ In relation #$12,$ 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 $$125,000.$

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

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

orders that will be completed in January $2026$