INSTRUCTIONS:

Please show excel formulas

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$: $2023$ Projected Income Statement

$$ Tab $3$: $2023$ Projected Balance Sheet

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

$1.$ Sales will change as follows:

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

b$.$ Small Tool Sales will increase $3\mathrm{.}0\%$

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

tools will be rented each week. The weekly rental per tool is $$40.$ 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, including blades and other items, is expected to total $$6,625$ in $2024.$

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

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

interest. You will need to use an amortization schedule to find the principle and

interest payment amounts. The loan is paid monthly.

$6.$ In relation to #$6$ above, the custom cabinet sales division begins operations in $2024.$ 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 $180$ cabinets at an average selling price

of $$5,500.$

b$.$ Direct materials per cabinet are $$1,000$ per unit.

c$.$ The direct labor per cabinet is $6$ hours, and Walnut Grove pays $$30\mathrm{.}00/$hour for

this labor.

d$.$ Factory overhead is calculated at $35\%$ 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 $$27,000,$

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

discount for the $2-$year prepayment.

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

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

average wage of $$22\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, $3$ additional employees will be hired at a rate of

$$20\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 $$39,750$ in ending inventory.

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

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

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

orders that will be completed in January $2024.$

Please show excel formulas