Question: Bricks Ltd is a trading company selling many types of products. They have provided the following budgeted revenue and budgeted cost information for two months in 2019 (November and December) July): and seven months of 2020 (January to 2019 2020 Dec Jan Nov Feb Mar Apr May Jun Jul 9,400 8,800 10,500 11,000 12,500 13,000 14,200 8,000 9,900 Sales (units) Selling Price 16 16 20 20 20 20 per unit ($) 20 20 24 Expenditure: 12,000 12,000 14,000 14,000 16,000 16,000 18,000 18,000 20,000 Salaries ($) 10% of sales revenue Commission 3,000 3,300 3,700 3,800 3,900 Utilities ($) 3,500 3,600 4,000 4,200 Advertisement(S) 4,000 4,000 5,000 6,000 5,000 5,000 6,000 6,000 7,000 Travelling (S) 3,000 3,500 4,000 3,000 3,000 2,500 4,500 5,000 5,500 2,000 3,000 3,000 3,000 Rental (S) 3,000 3,000 3,000 3,000 Additional information is given below: Receipts from trade receivables: (a) 30% of the total sales is on Cash basis and 70% is on Credit basis. 1. (b) 60% of credit customers will pay in the month following the month of sale and 30 % will pay in 2 months time. There will be 10% bad debts recognized in 2 months time. Monthly purchases of finished goods from their credit supplier represents 60 % of the next month's sales and payments to trade creditors are made one month after purchases. 2. 20% of the purchases of a month will be the opening inventory for following month. 3. 4. All expenses are Salaries and Travelling expenses are Commission and Utilities will be paid in arrear by one month. Advertisement and Rental are paid one month in advance. paid in the following manner: paid in the same month of incurrence. The rental agreement will expire in March 2020 and the new agreement will result in an increase 5. of 30% rental with effect from April 2020. 6. A down payment of $40,000 will be paid in the month of February 2020 for a new costing $240,000. The balance amount owing will be paid in 10 equal monthly installments starting from March 2020. Depreciation annum using the straight line method. equipment equipment will be calculate at 10% per on the new Mrs. Jones, the proprietor, will invest $100,000 cash into the business in April 2020 7. 8. As at 31 December 2019, the bank account in the company's books showed a debit balance of $25,000. The month of December and February 9. are festive seasons. Required: (a) Prepare the Cash Budget for each of the six months from January to 30th June 2020 using Excel (30 Marks) spreadsheet programme. (b) Prepare the Budgeted Profit and Loss Account for the six months ending 30th June 2020 using (10 Marks) Excel spreadsheet. Note: All figures to be rounded-up to the nearest dollar (no decimal point). All workings must be shown clearly and in addition to your written submission, a soft copy to be up-loaded in Blackboard for the Excel spreadsheet for part (a) and (b) above
