Suppose you own and operate a successf retail shop and that today is $30$ March $2024.$ You experienced some cash flow difficulties this time last year and want to avoid similar problems this year by obtaining a line of credit from your bank for the next three months $($April $2024$ June $2024).$ You obtained the following sales and cost of sales figures for January $$ March $2024)$ and last year $($April $2023$ June $2023).$

Data for $2023$ are as follow:

Sales for April $2023$ are R$9433232$ and for May $2023$ are R$10700382$ and for June $2023$ are R$8166081.$

The Cost of sales for April $2023$ are R$7546585$ and for May $2023$ are R$7704275$ and for June $2023$ are R$6124561.$

Data for $2024$ are as follow:

Sales for January $2024$ are R$7526106$ and for February $2024$ are R$7839694$ and for March $2024$ are R$7857927.$

The Cost of sales for January $2024$ are R$6020885$ and for February $2024$ are R$6271755$ and for March $2024$ are R$6532865.$

You have the following additional information:

$$

Sales and cost of sales figures for April$-$June $2023($in the table above$)$ must be used as estimates for April$-$June $2024.$ Since quarter $2$ of $2023$ was a good year, you estimate that the $2023$ monthly figures should be adjusted downwards by $14\%$ for $2024.$

$$

Payment by customers in $2024$ so far has been estimated by the credit department to be as follows:

o

Cash collections within the month of sale is $30\%,$ while the rest make up credit sales.

o

Debtors $($from credit sales$)$ are expected to pay as follows:

$$

Payment within one month following sales: $60\%$

$$

Payment within two months following sales: $25\%$

$$

Payment within three months following sales: $10\%$

$$

Remaining credit balance written off as bad debts.

$$

All raw material purchases are on credit. Payment for these credit purchases $($i$.$e$.$ cost of sales$)$ are typically made two months after purchases occurred $($i$.$e$.,60-$day creditor payment period$).$

$$

General and administrative salaries did amount to approximately $18\%$ of the monthly cost of sales amount for $2024$ so far. It is estimated that this percentage will reduce to $15\%$ of cost of sales for the period April$-$June $2024.$

$$

Rent payments are R$45000$ a month in $2024.$

$$

Depreciation charges are R$30000$ a month in $2024.$

$$

Utilities expenses $($water & electricity$)$ averaged R$32500$ a month during $2023$ so far. It is estimated that this amount will increase by $6\%$ for the period April$-$June $2024.$

$$

Insurance costs are R$17000$ a month in $2024.$

$$

A provisional tax payment of R$420000$ is due in May.

$$

Marketing costs amounting to $1\%$ of the previous month$$s sales is planned for the period April$-$June $2024.$

$$

A deposit of R$650000$ must be paid in June for office renovations.

$$

Assume interest to be paid on cumulative borrowings amounts to $1\%$ per month.

$$

Cumulative borrowings to the amount of R$74182$ was recorded in the actual cash budget for March $2024.$

$$

A minimum monthly cash balance of R$30000$ must be maintained throughout the cash budget period.

$$

Cash on hand on $30$ March is R$30000.$

Question$1$: Prepare a monthly cash budget for the three$-$month period ending $30$ June $2024$ and estimate the required financing $($or excess$)$ for each month during this period.