Here is the information you and your cross functional team have developed

The starting point agreed is the sales budget, forecasting how many units they believe they will sell in

each quarter of the upcoming $6/4.$ They agreed on the following projected sales levels:

Quarter $1,20242,100$ units

Quarter $2,20242,900$ units

Quarter $3,20241,900$ units

Quarter $4,20241,700$ units

Quarter $1,20252,100$ units

Quarter $2,20252,600$ units

Jack Doulton Co$.$ forecasts this balance sheet at the beginning of $2024$:

$1.$ Your team needs to start with the sales budget. The units are expected to sell for $$50$ each. $($We will

use US dollars $($$$)$ in this exercise instead of British sterling $().)$ Jack Doulton sells all merchandise on

credit. Historically, Jack Doulton collects $60\%$ of each quarter's sales during the quarter and $40\%$ in the

next quarter.

$2.$ Jack Doulton plans to stock ending inventory of finished goods to equal $25\%$ of the next quarter's

sales. Finished Goods inventory at $1/1/2024$ is $330$ units at a cost of $$25$ per unit. Jack Doulton uses

FIFO to account for finished goods inventory.

$3.$ The units are made with a high$-$quality resin and paint. The units each require $8$ ounces of resin at a

cost of $$0\mathrm{.}75$ per ounce. In addition, the units require $2$ ounces of paint at $$0\mathrm{.}40$ per ounce. At the

beginning of $2024,$ Jack Doulton had $524$ ounces of resin on hand and $100$ ounces of paint on hand. Jack

Doulton plans to keep $30\%$ of the resin and paint required for next quarter's production in inventory at

the end of each quarter. Jack Doulton pays for all purchases $45\%$ in the quarter of purchase and $55\%$ in

the quarter following purchase.

$4.$ Each unit requires $0\mathrm{.}8$ hours of direct labor at $$18$ per hour. Wages are paid on the last day of each

month for that month's work. Other costs are paid in the quarter unless otherwise noted.

Cash $ $10,000$

Accounts receivable $19,000$

Raw materials inventory $433$

Finished goods inventory $8,250$

Land $10,000$

Building and equipment $67,000$

Accumulated depreciation $(29,200)$

Total assets $ $85,483$

Accounts Payable $ $2,600$

Income taxes payable $-$

Common stock $20,000$

Retained earnings $62,883$

Total liabilities & equity $ $85,483$

$4$

$5.$ Variable overhead is estimated at $$6\mathrm{.}00$ per direct labor hour. Fixed overhead costs are $$32,000$ per

year. Fixed overhead includes depreciation of $$7,000$ per year.

$6.$ Variable selling costs, consisting of royalty payments to the Bond franchise, are $$2\mathrm{.}25$ per unit. Fixed

selling and admin costs are $$35,000$ per year. This includes $$3,000$ of depreciation per year.

$7.$ Jack Doulton makes quarterly income tax payments of $$6,500$ each. The corporate income tax rate is

$20\%$ of net income before taxes $($consider any overpayments or underpayments on your balance sheet$).$

$8.$ Jack Doulton pays quarterly dividends of $$2,000.$

$9.$ Jack Doulton will purchase additional equipment on the following schedule $($depreciation is already

included above$)$:

Quarter $1$ $ $4,100$

Quarter $2$ $ $3,000$

Quarter $3$ $ $-$

Quarter $4$ $ $-$

$10.$ Jack Doulton is required by its bank loan agreement to maintain a minimum cash balance of

$$10,000.$ Any projected borrowings are assumed to be borrowed at the beginning of the quarter.

Payments are made at the end of the following quarters to the extent cash is available. All borrowings

and repayments are made in $$1,000$ increments. Interest is paid at the time of repayment, as part of

the end$-$of$-$quarter repayment, if any. Interest is calculated at $10\%$ per year $($no compounding$).$

CAn you make an excel sheet please!