Question: Prepare the cash budget by month and in total for $2024.$

Rachel Janet is preparing the $2024$ budget for one of $$BestKayak$$s$$ roto$-$moulded kayaks. Extensive meetings with members of the sales department and executive team have resulted in the following unit sales projections for $2024.$

Jan $436$

Feb $473$

Mar $753$

Apr $859$

May $954$

Jun $1121$

Jul $1295$

Aug $1034$

Sep $565$

Oct $451$

Nov $355$

Dec $108$

Selling price is $$552.$ For promotion activities, BestKayak$$s gives a $10\%$ discount on selling price in September and December. All sales are on credit. The company expects that it can collect $20\%$ of sales in the month of sales, $50\%$ of sales in the next month, and the remaining $30\%$ in second month.

Sales in November $2023$ totaled to $250$ units at the selling price of $$552$ and in December $2023$ to $1000$ units at the $10\%$ discount selling price.

BestKayaks$$ policy is to have finished goods ending inventory in a month equal to $20\%$ of the next month$$s anticipated sales. This policy also applied to ending inventory of finished goods on December $31,2023.$

Preliminary sales projections for $2025$ are $550$ units for January and $420$ for February.

Production of each kayak requires $50$ kg of polyethylene powder and one finishing kit $($rope$,$ seat, hardware, etc.$).$ Company policy is that the ending inventory of polyethylene powder should be $21\%$ of the amount needed for production in the next month. Further, BestKayaks$$ policy is to always keep $50$ finishing kits as ending inventory. This means for example that the ending inventory of finishing kits is $50$ units on December $31,2023.$ The polyethylene powder used in these kayaks cost $$1\mathrm{.}3$ per kilogram, and the finishing kits cost $$163$ each.

Production of a single kayak requires $1$ hours of time by more experienced, type I employees and $2$ hours of finishing time by type II employees. Direct labour costs for type I employees are $$15$ per hour and $$9$ per hour for type II employees.

BestKayak expects variable manufacturing overhead costs to fluctuate with the production volume based on the following rates per type II employees direct labour hours:

Indirect materials: $$1$ per type II employees direct labour hour

Indirect labour: $$1\mathrm{.}40$ per type II employees direct labour hour

Utilities: $$0\mathrm{.}50$ per type II employees direct labour hour

Maintenance: $$0\mathrm{.}20$ per type II employees direct labour hour

In addition, BestKayak expects the following fixed manufacturing overhead to occur each month:

Supervisory salaries: $$19225$

Depreciation: $$3638$

Property taxes and insurance: $$8230$

Maintenance: $$5696$

The Company uses machine hours of type II employees to allocate manufacturing overhead. The predetermined manufacturing overhead is calculated based on annual estimates.

Variable selling and administrative expenses for this line are expected to be $4\%$ of sales for sales commissions and $$10$ per unit for freight out. In addition, monthly fixed selling and administrative expenses are estimated to be as follows:

Advertising: $$7500$

Sales salaries: $$17000$

Office salaries: $$7000$

Depreciation: $$722$

Property taxes and insurance: $$1000$

BestKayak pays $70\%$ of its direct materials purchases in the month purchased and the remaining $30\%$ in the next month. All other cash$-$effective costs are paid in the months incurred.

Include following additional assumptions in the preparation of the Master Budget:

The beginning cash balance is $95000.$

Ignore income taxes.

Assume that the direct materials prices, direct labour rates, variable manufacturing rates, and monthly fixed manufacturing costs remain constant in $2023$ and $2024.$ This means, for example, that you can use the product unit cost from $2024$ to determine the beginning finished goods inventory or the direct materials prices from $2024$ to calculate the beginning raw materials inventory.

The balance of Property, Plant, and Equipment, net of depreciation is $$420000$ as of December $31,2023.$

Equity: BestKayak finances its operations with common share equity of $$600000.$ It plans to pay out dividends of $$120000$ in October $2024.$

Use the following formula to determine the beginning retained earnings balance: Beginning retained earnings $=$ $$95000$ beginning cash balance $+$ Beginning accounts receivable balance $+$ Beginning finished goods balance $+$ Beginning raw materials balance $+$ $$420000$ Property, Plant, and Equipment, net of depreciation $$ Beginning accounts payable balance $-$ $$200000$ beginning long$-$term liabilities $-$ $$600000$ beginning common shares balance. Note that the amount of retained earnings is most likely as decimal number $$ do not round it in your Excel sheet.

Long$-$term liabilities: BestKayak finances its operations with an interest$-$bearing long$-$term loan of $$200,000.$ An interest expense of $7\%$ per annum is expected for the long$-$term loan, which is paid monthly. Note that the interest expense for the long$-$term loan is included in the financing section of the cash budget. BestKayak plans to pay back $10\%$ of its long$-$term loan in December, if the cash balance