Question

Q 3. Prepare the cash budget of the company from January to June based on the following (15) information.(Rs.in lakh) Production Overheads 6.000 Month January February March April May June Sales 72.000 97,000 86,000 88,600 102,500 108,700 Material Purchases 25,000 31,000 25,500 30.600 37,000 38,800 Salaries & wages 10,000 12.100 10.600 25.000 22.000 23.000 6,300 6,000 6,500 8,000 8,200 Office & Sedligads 5,500 6.700 7.500 8.900 11,000 11,500 The opening cash balance for the year may be taken as Rs.72,500 lakh.50 percent of sales can be assumed as credit salcs. Asscts are to be acquired in the month of February and April. Therefore provisions are to be made for the payment of Rs.8,000 and Rs.25,000 respectively for the same. An application has been Page 2 of 4 made to a bank for the grant of a loan of Rs.30,000 and it is expected that the loan amount will be received in the month of May. There is a dividend payment liability of Rs.35,000 in the month of June. The debtors are allowed one month credit. The creditors for material purchases and overheads can be paid after one month from the date of purchase /incurring the expenditure. A commission of 3% of sales is paid to the salesmen on a monthly basis.

Answer 1

Hi,

The provision amount for purchase of assets, loan amount & dividend amount are mentioned only as Rs, but these figures are in Lakhs too as a company which has sales & overheads of huge figures it cant have asset / loan values of so less.

Below is the table of cashflow, Below that I shall write the detailed explanation.

Cash Flow Budget of the company
Rs. In Lakhs
Month->	Jan	Feb	Mar	Apr	May	June
Opening Cash Balance	72500	96,340	121,330	155650	151292	205767
Cash Inflows
Cash Sales	36000	48500	43000	44300	51250	54350
Collection from Previous month Sales	0	36000	48500	43000	44300	51250
Loan from Bank					30000
Total Cash Inflows	36000	84500	91500	87300	125550	105600
Cash Outflows
Material cost		(25,000)	(31,000)	(25,500)	(30,600)	(37,000)
Production Overheads		(6,000)	(6,300)	(6,000)	(6,500)	(8,000)
Fixed Overheads
Salaries & Wages	(10,000)	(12,100)	(10,600)	(25,000)	(22,000)	(23,000)
Variable Overheads
Sale Commissions	(2,160)	(2,910)	(2,580)	(2,658)	(3,075)	(3,261)
Office & Selling Overheads		(5,500)	(6,700)	(7,500)	(8,900)	(11,000)
Dividend Payment						(35,000)
Asset Purchase		(8,000)		(25,000)
Total Cash Outflows	(12,160)	(59,510)	(57,180)	(91,658)	(71,075)	(117,261)
Closing Cash Balance	96,340	121,330	155,650	151,292	205,767	194,106

In the above if you see I have maintained a different sequence of plotting the values & not going by the sequence given in the question. I have prepared it in a sequence same that we maintain in income statement, this is so as we first add what & from where are we getting money & then keep reducing our expenses first the fixed ones as they come to us every month with same amount & then the variables which are specific to that month / dependent on sales.

Opening balance will be at the top of cashflow as we shall add & subtract the changes of cash to this value to get the month end closing balance which ultimately shall be the opening balance of next month, i.e. Jan month closing balance is Feb month opening balance & so on..

Whatever is added to op. balance is the cash inflow which increases the cash balance & whatever is reduced is the cash outflow thereby reducing the cash balance.

In the question there are 2 places sales & loan from bank contribute to inflows. In sales 50% is cash sales & 50% is credit sales of 1 month, so in same month we would realise 50% of current month sales + 50% of previous month, so in Feb month we have 50% of Feb month sales+ 50% of Jan month sales = Rs. (97000 * 50%) + (72000 * 50%) Lakhs & so for other months. As Loan from bank is expected to be received in May so I have added. Total cash inflow is sum of cash realisation from sales & loan from bank.

Lets start with outflows, first take the costs that contribute directly to our product cost i.e. material cost & other production overheads (these are directly linked to the production of final product that is sold to customer, like electricity charges paid for plant) for which we have 1 month credit with vendors so Jan value is paid in Feb month & so on. Then take fixed expenses which is salaries / wages which is paid to employees & workers every month. These will not change with change in sales so are fixed amounts unless there is change in manpower. In variables costs we have sales commissions which is 3% of each month sales, Jan month is 3% * (72000) = Rs 2160 Lakhs. Then we have office & selling expenses which again have 1 month credit period , in these the advertising , stationary purchase costs, any promotional expense incurred are considered. then we have dividend payments, that are paid out of company profits to the shareholders, these also affect cashflow. Lastly we have to take provision for planned purchase of assets in Feb & Apr. If we add up all these we get Total Cash outflows.

So closing cash balance = Opening cash balance + Cash inflows - Cash outflows which is brought forward in next month.

Answer 2

Prepare a Cash budget for the Quarter April to June based on the following data and additional information.