Bay Water Ceramics is a wholesaler and retailer of tiles and decorative stone. They sell to...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 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
Expert Answer:
Related Book For
Managing Business Ethics Making Ethical Decisions
ISBN: 9781506388595
1st Edition
Authors: Alfred A. Marcus, Timothy J. Hargrave
Posted Date:
Students also viewed these finance questions
-
Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...
-
Melissa buys an iPod for $120 and gets consumer surplus of $80. a. What is her willingness to pay? b. If she had bought the iPod on sale for $90, what would her consumer surplus have been? c. If the...
-
The piston/cylinder shown in Fig. P4.48 contains carbon dioxide at 300 kPa, 100C with a volume of 0.2 m3. Mass is added at such a rate that the gas compresses according to the relation PV1.2 =...
-
What does Starbucks hope to achieve by dropping its name and word "coffee" from the company's logo?
-
Using the AISC equations, select from Appendix B the lightest-weight wide-flange A992 steel column that is \(30 \mathrm{ft}\) long and supports an axial load of \(200 \mathrm{kip}\). The ends are...
-
One of our best customers has had a major plant breakdown and wants us to make as many widgets for him as possible during the next few days, until he gets the necessary repairs done. With our...
-
You plan to migrate an on-premises Apache Spark deployment to a managed Apache Spark service in Azure. To which Azure service can you migrate the on-premises deployment?
-
Jolie bought a property today that she thinks is worth $1,000,000. She thinks the property will appreciate at the rate of 9 percent annually. If she is correct, what will the property be worth in...
-
What criteria do you use to filter the list of BOs?
-
List some of the patterns related to the corruption analysis pattern.
-
How do you apply a stable analysis pattern to a particular context?
-
List the use cases in classifying politician data applying the corruption analysis pattern.
-
What characteristics do you check for each BO?
-
Governments are encouraged by GASB to publish service efforts and accomplishments (SEA) data. What is the role of auditors in attesting to SEA data?
-
Listed below are several terms and phrases associated with basic assumptions, broad accounting principles, and constraints. Pair each item from List A (by letter) with the item from List B that is...
-
A plane gate of uniform thickness holds back a depth of water as shown. Find the minimum weight needed to keep the gate closed. 0=30 L=3m Water w=2m P3.45
-
Obtain an expression for the kinetic energy flux, \(\int\left(V^{2} / 2 ight)\) \(ho \vec{V} \cdot d \vec{A}\), through cross section (1) of the control volume shown. x CV Width = w P4.12 h V
-
The velocity distribution for laminar flow in a long circular tube of radius \(R\) is given by the one-dimensional expression, \[\vec{V}=u \hat{i}=u_{\max }\left[1-\left(\frac{r}{R} ight)^{2} ight]...
Study smarter with the SolutionInn App