Question

ACC1511 MANAGEMENT ACCOUNTING FUNDAMENTALS CASH BUDGET EXERCISES Question 1 The accountant of DJ Herbs, a medium size company, approached the company's bank late in the current year seeking short-lerm financing, and was told that money was very sght and that any borrowing over the next year would have to be supported by a detailed statement of cash colections and disbursements. The accountant also was tokld that it would be very helpful to the bank if borrowers would indicate the quarters in which they would be needing funds, as well as the amounts that would be neaded, and the quarters in which repayments could be made Because the accountant is unsure as to the particular quarters in which bank financing will be needed, he has assembled the folowing information to assist in preparing a detailed cash budget for next year a Sales and merchandise purchases are made cn account. The account assistant has gathered al relevant intomation as summarized in the folowing table: Quarter 1 Quarter 2 Quarter 3 RM400,000 RM365,000 Quarter 4 RM300,000 RM200,000 RM290.000 RM120.000 Sales Cash Collections Cash Disbursements RM200,000 RM196,000 RM120,000 RM261,000 RM176,000 RM200,000 b. The company wil pay 5% sales commission quarterly to al salespersons as part of the remuneration package. Seling and administranive expenses are budgeted at RM50,000 per quarter. Ot the amount, RM20,000 each quarter is deprecianon d. The company's policy is to declare and pay dividends in the last quarter of every year. The expected dividends is RM50,000 . Land purchases of RM46,000 tor the new factory will be made in the first quarter. The purchase wil be for cash t. Salary expenses are esimated at RM40,000 per quarter. The company pays RM30,000 for insurance annualy. The payment will be made in the last quarter h. The company is committed in helping the orphanages and is planning to donste RM20.000 in the last quarter. iThe cash balance at the end of the current year is RM10,000. The accountant feels that maintaining a minimum RM30,000 cash balance in evary quarter for the coming year is necessary to sustain the operation The company has no outstanding loan at present k The company has an agreement on overdraft facity with a local bank up to a total of RM100,000, with an interest rate of 5 % per year, with bomowing taking place on the first day of the month and repayment cccurring on the last day of the month. Interest is paid when the loan is repaid Required: Prepare a cash budget for DJ Herbs, by guarter and in total, for next year

Answer 1

The cash budget is provided below:

		Q1	Q2	Q3	Q4	Total for year
1	Cash balance at the beginning of the period	10,000	30,000	30,000	80,100	10,000
	Cash inflows:
2	Cash collections	196,000	261,000	365,000	290,000	1,112,000
	Cash outflows:
	Cash disbursements	120,000	176,000	200,000	120,000	616,000
	Sales commissions (5% of sales)	10,000	15,000	20,000	10,000	55,000
	Cash SG&A expenses (excluding depreciation)	30,000	30,000	30,000	30,000	120,000
	Dividend				50,000	50,000
	Land purchase				46,000	46,000
	Salary expenses	40,000	40,000	40,000	40,000	160,000
	Insurance				30,000	30,000
	Donation to orphanage				20,000	20,000
3	Total cash outflows	200,000	261,000	290,000	346,000	1,097,000
4	Cash surplus (defecit) = 1+2-3	6,000	30,000	105,000	24,100	25,000
5	Add: borrowings to maintain desired minumum balance (30,000-6,000)	24,000	-	-	5,900	29,900
6	less: loan and interest repayment	-	-	24,900	-	24,900
	Cash balance at the end of the period = 4+5-6	30,000	30,000	80,100	30,000	30,000

Interest amount: Loan is repaid in 3rd quarter and so period of loan is 3 quarters or 9 months. Thus interest amount = 24,000*5%*9/12 = 900

Total amount paid for loan = 24000+900 = 24900

Formulas can be seen in image attached below:

X q quant 2019 20 - Microsoft Excel 15 Home File Insert Page Layout Formulas Data Review View Cut AutoSum AA A * = E *Wrap Text Merge & Center Custom % - Insert Delete Format Fill - U , 8: 11 D- Font for 10000 12 Conditional Format Formatting as Table Styles Cell Styles Calibri Ea Copy Paste Format Painter B I Clipboard A066 AM 2 Clear Sort & Filter Editing Find & Select Alignment Number Cells AN АО АР AQ AR 65 Q1 10000 Q2 !=A082 Q3 =AP82 Q4 =AQ82 Total for y =A066 66 1 67 68 2 69 196000 261000 365000 290000 =SUM(AOE 120000 =0.05*200000 30000 Cash balance at the beginning of the period Cash inflows: Cash collections Cash outflows: Cash disbursements Sales commissions (5% of sales) Cash SG&A expenses (excluding depreciation) Dividend Land purchase Salary expenses Insurance Donation to orphanage Total cash outflows Cash surplus (defecit) = 1+2-3 176000 200000 120000 =SUM(A07 =0.05*300000 =0.05*400000 =0.05*200000 =SUM(A07 30000 30000 30000 =SUM(A07 50000 =SUM(A07 46000 =SUM(A07 40000 40000 40000 =SUM(A07 30000 =SUM(A07 20000 =SUM(A07 =SUM(AP70:AP77) -SUMAQ70:AQ77) ESUM(AR70:AR77) ESUMASZ =AP66+AP68-AP78 EAQ66+AQ68-AQ78 =AR66+AR68-AR78 EAS66+ASE 40000 78 3 79 4 =SUM(AO70:A077) =A066+A068-A078 80 5 =30000-A079 Add: borrowings to maintain desired minumum balance (30,000-6,000) less: loan and interest repayment Cash balance at the end of the period = 4+5-6 =30000-AR79 =SUM(AO 24900 =SUM(AO8 =AP79+AP80-AP81 =AQ79+AQ80-AQ81 =AR79+AR80-AR81 =AS79+ASE =A079+A080-A081 95 96 ► Ready April May June July Aug Sept Oct Nov Sheet1 30. 100% 0 + EN P E 07/11/2019