Question

Prepare a spreadsheet to provide: A cash budget for North Sea Seafood for January through June of 2020 based on the following information: Sales: November 2019 December January 2020 February March April May June $450,000 620,000 380,000 510,000 490,000 300,000 380,000 300,000 280,000 July The credit manager says that our collection pattern is as follows: 60% of Sales are cash sales 75% of new Accounts Receivable are collected in 30 days 20% of new Accounts Receivable are collected in 60 days 5% of new Accounts Receivable are bad debts and will not be received. Purchases are equal to 60% of the following month's sales. North Sea Seafood takes pride in paying their suppliers in a timely fashion. 80% of Purchases are paid in the same month as the purchase and the balance is paid in the following month.

Answer 1

Cash Budget for North sea seafood for the month Jan 2020 to June 2020 Particulars Jan Feb Mar Apr May June Opening Balance 25,000 25,000 25,000 25,000 25,000 25,000 Receipts: Cash sales (Note 1) 2,28,000 3,06,000 2,94,000 1,80,000 2,28,000 1,80,000 From Accounts Receivable (Note 1) 2,22,000 1,63,600 1,83,400 1,87,800 1,29,200 1,38,000 Loan Taken 74,300 1,12,200 78,500 76,200 66,300 Total Cash Available (A) 5,49,300 6,06,800 5,02,400 4,71,300 4,58,400 4,09,300 Payments: Creditors salaries Wages Communications Amortization Rent Expenses Utilities Expenses Office Supplies Expenses Interest Taxes Equipment Loan Paid GIC'S Total Payments (B) Closing Balance (A-B) 2,90,400 1,25,000 95,000 800 5,000 4,700 700 200 2,500 2,96,400 1,25,000 1,27,500 800 5,000 4,700 700 200 2,500 2,02,800 1,25,000 1,22,500 800 5,000 4,700 700 200 2,500 2,18,400 1,25,000 75,000 800 5,000 4,700 700 200 2,500 14,000 1,89,600 1,25,000 95,000 800 5,000 4,700 700 200 2,500 1,70,400 1,25,000 75,000 800 5,000 4,700 700 200 2,500 19,000 9,900 13,200 5,24,300 25,000 5,81,800 25,000 4,77,400 25,000 4,46,300 4,33,400 3,84,300 25,000 25,000 25,000 Working Notes 1) Receipts From Accounts Receivable Particulars Total Sales Cash Sales @ 60 % Credit Sales/ Account Receivable Bad Debts 5% Cash Collection 75% of Accounts Receivable in 30 Days 20% Of Accounts Receivable in 60 Days Cash receipt form Accounts Receivable Nov-19 Dec-19 Jan-20 Feb-20 Mar-20 Apr-20 May-20 Jun-20 4,50,000 6,20,000 3,80,000 5,10,000 4,90,000 3,00,000 3,80,000 3,00,000 2,70,000 3,72,000 2,28,000 3,06,000 2,94,000 1,80,000 2,28,000 1,80,000 1,80,000 2,48,000 1,52,000 2,04,000 1,96,000 1,20,000 1,52,000 1,20,000 9,000 12,400 7,600 10,200 9,800 6,000 7,600 6,000 1,86,000 1,14,000 1,53,000 1,47,000 36,000 49,600 30,400 40,800 2,22,000 1,63,600 1,83,400 1,87,800 90,000 39,200 1,29,200 1,14,000 24,000 1,38,000 Dec-19 6,20,000 Jan-20 3,80,000 Feb-20 5,10,000 Mar-20 4,90,000 Apr-20 3,00,000 May-20 Jun-20 3,80,000 3,00,000 Jul-20 2,80,000 2) Payment for Purchase Particulars Total Sales Total Purchase (60% of following month's sales Cash Paid 80% in same Month 20% in next month Total Cash Paid to Creditors 2,28,000 3,06,000 2,94,000 1,80,000 2,28,000 1,80,000 1,68,000 2,44,800 2,35,200 1,44,000 1,82,400 1,44,000 1,34,400 45,600 61,200 58,800 36,000 45,600 36,000 2,90,400 2,96,400 2,02,800 2,18,400 1,89,600 1,70,400 3) Payment of Wages Particulars Total Sales Wages Paid @ 25% of current month sales Jan-20 Feb-20 Mar-20 Apr-20 May-20 Jun-20 3,80,000 5,10,000 4,90,000 3,00,000 3,80,000 3,00,000 95,000 1,27,500 1,22,500 75,000 95,000 75,000 4) Loan Payment / taken Particulars Opening Balance of Cash Receipts Excluding laon Total Cash Available Payments Excluding loan and GIC's balance Loan taken/ (Paid) Less: paid to GIC'S Closing Jan-20 Feb-20 Mar-20 Apr-20 May-20 Jun-20 25,000 25,000 25,000 25,000 25,000 25,000 4,50,000 4,69,600 4,77,400 3,67,800 3,57,200 3,18,000 4,75,000 4,94,600 5,02,400 3,92,800 3,82,200 3,43,000 5,24,300 5,81,800 4,64,200 4,46,300 4,33,400 3,84,300 -49,300 -87,200 38,200 -53,500 -51,200 -41,300 74,300 1,12,200 -13,200 78,500 76,200 66,300 25,000 25,000 25,000 25,000 25,000 25,000