Question

Managerial Accounting Case 2 McMurray & Sons is a retailer of stuffed animals. Alles in the store sell for the same $18 selling price. McMurray estimates that 25% of its sales are for cash and 75% are on account Other information regarding the company's budgeted sales and collection of credit sales are as follows Budgeted December Jay February March Credit Sales Collection Pattern Collected in same month as sale Collected 1 months following sale es in units 9.000 1.000 2.000 2.500 3.000 McMurray buys its suffed animals from one supplier at a cost of $6 per animal. It pays for all of its merchandise purchases in the month following purchase McMurray stuffed animals merchandise purchased sted $49.200 in December and plans purchases of $6.600 in January $12.00 in February and $15.00 March McMurray's monthly expenses are as follows: 1.500 Depreciation of store building & Fictures 10.000 salaries and other payrolles 2.500 Advertising 2000 Uses 7 000 other operating expenses 23000 In addition to these expenses, McMurray pays insurance premiums of $4.000 in January and June, and pays $5000 in property taxes every February McMurray began January with $25.000 in its bank account. The company maintains a minimum cash balance of $25,000. An open line of credit is available from the company's bank to bolster is cash position when needed. Any excess cash over $25,000 should be applied against monies borrowed. Ognore interest) REQUIRED: (1) Prepare a schedule of cash collections for January February, and March (2) Prepare a schedule of merchandise cash payments for January February, and March (3) Prepare a cash budget for January February, and March NOTE: Assignment must be TYPED Suggest using EXCEL spreadsheet.

MUST BE DONE IN EXCEL

Answer 1

(1) Schedule of cash collection:

Particulars	January	February	March
Cash sales	4500	9000	11250
Collection of Credit sales of current month	6750	13500	16875
Collection of credit sales of previous month	60750	6750	13500
Total	72000	29250	41625

(2) Schedule of merchandise cash payments:

Particulars	January	February	March
Cash payment for merchandise	49200	6600	12300
Total	49200	6600	12300

(3) Cash budget:

Particulars	January	February	March
Opening cash	25000	25000	25000
Cash collected from sales	72000	29250	41625
Cash paid for merchandise purchase	(49200)	(6600)	(12300)
Salaries paid	(10000)	(10000)	(10000)
Advertising expenses	(2500)	(2500)	(2500)
Utilities expenses	(2000)	(2000)	(2000)
Other operating expenses	(7000)	(7000)	(7000)
Insurance premiums paid	(4000)
Property taxes paid		(5000)
Credit taken	2700	3850
Credit repaid			(6550)
Closing balance	25000	25000	26275

The question requires any cash balance in excess of $25000 must be applied against monies borrowed. In the last month the excess cash balance after maintaining $25000 balance is $ 7825. Whereas, the credit taken during first two months is $ 2700 and $ 3850 totalling to $ 6550. Now, I would still have cash left after paying off the credit to the tune of $ 1275.

Ideally this money should be kept in the cash balance, thus, our cash balance in March end would show $ 26275.

You could also reinvest the money in excess $1275 in market, which would be prudent. I have ignored that, but if you want you can include it by adding another row, and thereafter cash balance would stay at $25000.

Note. Depreciation expenses is non-cash item and should not be shown in cash budget.