Question:

In an excel $($show formulas$),$ Prepare a cash budget for BestKayak's roto$-$moulded kayaks for each month of $2024$ and a total for the year.

Sales Projections:

Monthly unit sales $($Jan$-$Dec$)$: $436,473,753,859,954,1121,1295,1034,565,451,355,108.$

Selling price: $$552/$unit$.$

$10\%$ discount in September, December.

Collection: $20\%$ in sale month, $50\%$ next month, $30\%$ in second month.

Nov $2023$ sales: $250$ units; Dec $2023$ sales: $1000$ units at $10\%$ discount.

Inventory Policy:

Finished goods inventory: $20\%$ of next month's sales.

Ending inventory Dec $31,2023,$ and preliminary $2025$ projections $($Jan: $550$ units, Feb: $420$ units$).$

Production Requirements:

Per kayak: $50$ kg polyethylene powder $($$$1\mathrm{.}3/$kg$),$ one finishing kit $($$$163).$

Labor: Type I $-1$ hr at $$15/$hr$,$ Type II $-2$ hrs at $$9/$hr$.$

Ending inventory: Polyethylene powder $21\%$ of next month's need, $50$ finishing kits.

Manufacturing Overhead Costs:

Variable $($per Type II labor hour$)$: Indirect materials $$1,$ Indirect labor $$1\mathrm{.}40,$ Utilities $$0\mathrm{.}50,$ Maintenance $$0\mathrm{.}20.$

Fixed monthly: Supervisory salaries $$19225,$ Depreciation $$3638,$ Property taxes$/$insurance $$8230,$ Maintenance $$5696.$

Selling$/$Administrative Expenses:

Variable: $4\%$ of sales $($commissions$),$ $$10/$unit $($freight$).$

Fixed monthly: Advertising $$7500,$ Sales salaries $$17000,$ Office salaries $$7000,$ Depreciation $$722,$ Property taxes$/$insurance $$1000.$

Payment Policies:

Direct materials: $70\%$ in purchase month, $30\%$ next month.

Other costs: Paid in month incurred.

Additional Assumptions:

Starting cash: $$95000.$

Ignore income taxes.

$2023$ and $2024$ costs constant.

Property, Plant, and Equipment value: $$420000($Dec $31,2023).$

Equity: $$600000,$ Dividends: $$120000$ in Oct $2024.$

Beginning retained earnings formula: $$95000+$ Accounts receivable $+$ Finished goods $+$ Raw materials $+$ $$420000($PPE$)$ Accounts payable $-$ $$200000($long$-$term liabilities$)-$ $$600000($common shares$).$

Long$-$term liabilities: $$200000$ with $7\%$ annual interest, $10\%$ repayment in December if cash allows.

Short$-$term financing: Line of credit at $8\%$ annual interest, borrow$/$repay in multiples of $$5000.$