Bay Water Ceramics is a wholesaler and retailer of tiles and decorative stone. They sell to a range of hardware stores, building and landscape supply companies. Also, they sell direct to the public. They have provided the following information on their sales, purchases cash payments and cash collections. Sales & Revenue Information Bay Water Ceramics - Sales Data Month February (Actual) March (Actual) April (Forecast) May (Forecast) June (Forecast) Sales $50,000 $55,000 $60,000 $60,000 $65,000 It is estimated that 25% of the sales are for cash and 75% are on credit. Of the credit sales - 40% pay their accounts within the month of sale - 35% pay in the month after-sale -25% pay during the second-month after-sale Purchases and Payments Information Month Purchases Bay Water Ceramics - Purchases Data March (Actual) $27,500 April (Forecast) May (Forecast) June (Forecast) $30,250 $36,000 $39,000 All purchases of inventory are made on credit and 40% is paid for in the month they are purchased and 60% in the following month. Selling and administrative expenses are incurred at the rate of $13,000 per month. Payment is made in the month in which the expense is incurred. Selling and administrative expenses include a depreciation charge of $5000 per month. New equipment costing $18,000 will be purchased in March and the payment will be made in May. Bay Water Ceramics has a loan of $360,000. Interest at the rate of 5% per annum is paid quarterly, the next due date for payment is 30 June. Opening Bank Balance The balance of the bank account on 31 March is $10,500 Required: 1. Open the excel spreadsheet file FNSACC412_AE_Sk_1of3_SR_1of1.xlsx. Go to the worksheet called 'Bay Water Ceramics Cash Budget'. There is a partially completed template for a cash budget. Using this template, prepare a cash budget for Bay Water Ceramics for the period April to June. Ignore the effects of GST in this question. Your cash budget will need to include: a. Cash receipts budget b. Cash payments budget c. Total cash budget 2. Examine the cash budget you have just created and answer the following questions a. What is Bay Water Ceramics forecast cash position at the end of June? Your answer should be a number. Thank you for sending the assessment for review. Please review the following and return. (update any info in the word document this update affects) The total cash payments amounts (41,600 and so on) are not reduced by the depreciation amounts. These amounts have been typed in, they should be summed using a formula. The opening cash balance is not correct. 33,875 This amount is given as the closing balance for March which will become the opening balance for April. Please call the helpdesk if you have any questions or need further information. TOTAL CASH PAYMENTS $13,000 $24,000 $41,600 $63,550 $54,700 $159,850 CASH BUDGET Opening Cash Balance $14,500 $33,875 $49,088 $44,600 $33,875 Cash receipts $27,500 $43,375 $56,813 $59,063 $62,750 $178,625 Cash Payments $13,000 $24,000 $41,600 $63,550 $54,700 $159,850 CLOSING CASH BALANCE $14,500 $33,875 $49,088 $44,600 $52,650 $52,650 Cash Budget April-June CASH RECEIPTS BUDGET BAY WATER CERAMICS February (Actual) March (Actual) April (Forecast) May (Forecast) June (Forecast) TOTAL April-June Sales $50,000 $55,000 $60,000 $60,000 $65,000 $185,000 Credit Sales 75% $37,500 $41,250 $45,000 $45,000 $48,750 $138,750 Cash sales 25% $12,500 $13,750 $15,000 $15,000 $16,250 $46,250 Collections current month 40% $15,000 $16,500 $18,000 $18,000 $19,500 $55,500 Collections next month 35% $0 $13,125 $14,438 $15,750 $15,750 $45,938 Collections 2nd month 25% $0 $0 $9,375 $10,313 $11,250 $30,938 Total Credit Collections $15,000 $29,625 $41,813 $44,063 $46,500 $132,375 Total Cash Receipts $27,500 $43,375 $56,813 $59,063 $62,750 $178,625 CASH PAYMENTS BUDGET Purchases $27,500 $30,250 $36,000 $39,000 $105,250 Payments current month 40% $11,000 $12,100 $14,400 $15,600 $42,100 Payments following month 60% $16,500 $18,150 $21,600 $56,250 Depreciation -$5,000 -$5,000 -$5,000 -$5,000 -$5,000 -$5,000 Net Cash Expenses $13,000 $13,000 $13,000 $13,000 $13,000 $39,000 Interest expense $4,500 $4,500 New Equipment $18,000 $18,000 TOTAL CASH PAYMENTS $13,000 $24,000 $41,600 $63,550 $54,700 $159,850 CASH BUDGET Opening Cash Balance $14,500 $33,875 $49,088 $44,600 $33,875 Cash receipts $27,500 $43,375 $56,813 $59,063 $62,750 $178,625 Cash Payments $13,000 $24,000 $41,600 $63,550 $54,700 $159,850 CLOSING CASH BALANCE $14,500 $33,875 $49,088 $44,600 $52,650 $52,650