Question

PetPlace requires a minimum cash balance of $30,000 at the end of March of the current year in order to remodel its store. Any required borrowings take place in increments of $10,000 with interest of 1.5% per month. Repayments of borrowed funds are also made in increments of $10,000. Assume that borrowings are made on the first day of the month in which the cash is required, and that repayments are made on the last day of the month. The cash balance at the beginning of March is $7,000. During March, the store expects to collect $100,500 from sales made during January and February. The store expects monthly sales of $360,000 during March, $432,000 in April and $388,800 in May. Each unit sold has a price of $12.00. All sales are on credit. Experience indicates that the store will collect 60% of credit sales in the month of the sale, 30% in the month following the sale, and 7% in the second month following the sale. The remaining amount is uncollectible. In February, the store sold some display cases for $5,000 and expects to collect this amount in March. The inventory balance at the beginning of March is $50,000. Purchases in a given month average 50% of projected sales for that month. The owners pay all accounts payable arising from inventory purchases in time to receive a 2% discount. On average, 75% of the accounts payable related to inventory are paid for in the month of purchase, with the remaining 25% being paid in the following month. The accounts payable outstanding from February inventory purchases is $35,280 at the end of February Budgeted operating expenses for March include rent at 8% of sales, advertising ($27,000), employee compensation ($72,000 ), depreciation ($2,000 ), and insurance ($3,000 ). Depreciation is calculated on a straight-line basis and insurance is paid one a year in June, and the related expense is recorded in all other months by reducing the prepaid insurance account. Interest is paid monthly on the last day of the month. (Note that there will be interest expense in the month in which funds are borrowed. Required: Prepare a cash budget for PetPlace for March of the current year. How much, if any, will need to be borrowed at the end of March?

please full calculation and explanation.

Answer 1

Please give positive ratings so I can keep answering. It would help me a lot. Please comment if you have any query. Thanks!

Pet Place
Sales Budget	March	Note
Budgeted Sales Revenue	360,000.00	A
Budgeted cash receipts
60% of sales of March	216,000.00	B=A*60%
Collection of sales made in January & February	100,500.00	C
Collection of sales of display cases	5,000.00	D
Budgeted cash receipts	321,500.00	E=B+C+D
Budgeted cost of Raw Material Purchases	March
Budgeted Purchases- 50% of sales	180,000.00	F=A*50%
Payment for purchases Budget
75% of payable of March	135,000.00	G=F*75%
February payable	35,280.00	H
Total	170,280.00	I=G+H
Less: 2% discount	3,405.60	J=I*2%
Payment for purchases Budget	166,874.40	K=I-J
Payment for Operating Expenses Budget
Rent at 8% of sales	28,800.00	L= A*8%
Advertising	27,000.00	M
Employee compensation	72,000.00	N
Payment for Operating Expenses Budget	127,800.00	O=L+M+N
Cash budget
Beginning Cash Balance	7,000.00
Plus: Budgeted cash receipts	321,500.00	See E
Total cash available	328,500.00
Less: Budget cash payments
Payment for purchases Budget	166,874.40	See K
Payment for Operating Expenses Budget	127,800.00	See O
Preliminary cash balance	33,825.60
Minimum cash balance	30,000.00
Cash borrowed/ (repaid)	-
Ending cash balance	33,825.60
The ending cash balance is $ 33,825.60 which is greater than minimum requirement of $ 30,000 so borrowing is not needed in March.
Depreciation is non cash expense so not considered.
Insurance is prepaid so not considered.