Question

Milo Company buys and sells beach umbrellas. The selling price of each beach umbrella is R150 and it costs the company R70. The company is preparing budgets for the last quarter of the year and has assembled information to assist in the budget preparation. The marketing department has estimated sales as follows for the remainder of the year (in units):

Month Expected demand
September
4 000
October
3 000
November
7 000
December
5 000

The company sold 3 900 beach umbrellas during August and wrote off bad debts of R26 500. All sales are on account. The company normally collects 30% of a month’s sales before the same month ends, collects 40% in the month following the month of the sale and collects 25% two months after the sale. The remaining 5% is written off three months after the sale because it is generally not recoverable. Milo Company expects to sell 2 000 beach umbrellas during January 2020. The company estimates that its bank balance will be sitting at R80 000 at 30 September 2019.
Each beach umbrella is hard to acquire. Therefore, the company requires that the ending inventory of beach umbrellas to be equal to 20% of the following month’s estimated sales needs. Sixty percent of a month’s purchases of beach umbrellas is paid for in the month of purchase; the remainder is paid for in the following month. Milo Company decided to trade in its old and fully depreciated machine for R15 000 for a new machine that will cost the company R250 000 on 1 October 2019. The machine will be used for five years. Other expenses, including bad debts and depreciation, that are estimated to cost R120 000 per month.

REQUIRED:

a)Prepare the cash budget of Milo Company for each of the following three months: October, November and December 2019.

b)Analyse the projected cash budget of Milo Company for the last quarter of the 31 December 2019 financial year.

Answer 1

Cash budget of Milo company
Particular	Month
October	November	December
Opening Cash Balance	80000	100250	361650
Add: Collection from Debtor	521250	645000	757500
Less: Payment to Creditors	266000	383600	369600
Less: Machine purchase (250000-15000)	235000
Closing cash balance	100250	361650	749550

Note: bad debts and depreciation not taken in consideration because there will be no cash flow from these expenses.

Collection from Debtor
Particular	August	September	October	November	December
Sales in unit	3900	4000	3000	7000	5000
sales price per unit	150	150	150	150	150
Sales in R	585000	600000	450000	1050000	750000
30% Received in same month	175500	180000	135000	315000	225000
40% Received in the next month		234000	240000	180000	420000
25% after after 2 months			146250	150000	112500
Total amount received from debtors			521250	645000	757500

Payment to Creditors
Particular	August	September	October	November	December	January
(A) Opening stockin units		800	600	1400	1000	400
(B) Sales in units	3900	4000	3000	7000	5000	2000
(C) Closing stock @ 20% of next month sale	800	600	1400	1000	400
(D) Purchase in units (B+C-A)	4700	3800	3800	6600	4400	1600
Purchase price per unit	70	70	70	70	70	70
Purchase in R	329000	266000	266000	462000	308000	112000
60% paid in same month		159600	159600	277200	184800
40% paid in following month			106400	106400	184800
Total Amount paid			266000	383600	369600

Answer b :

Analysis of cash budget
Particluar	October	November	December	Average
Collection from debtor to sales ratio (collection from debtor/Sales)	1.158333333	0.614285714	1.01	0.927539683
Payment to creditors/Purchase ratio (Payment to creditor/purchase)	1	0.83030303	1.2	1.01010101
Payment to collection ratio(payment / collection) extraordinary payment not included	0.510311751	0.594728682	0.487920792	0.530987075
Closing cash balance	100250	361650	749550
1. From the above working we can say that collection from debtors are very slow as company on average receive the 92.75% from debtors against sale but payment to creditors is almost equal to purchase. So company require to strengthen the collection process. 2. Company have enough cash balance, so company can use this excess cash for investment or expansion of the business.