4. EAST WEST Trading is one of the leading business in the GCC. The company presently is in import of goods from Iran since last twenty years. The company is expanding its operation in a new region in the GCC for which it wants to some estimates for the project. You have been assigned the task of making the projection for the cash based on the expected sales and other expense. Material: Suppliers for the purchases are paid 60% in the month of purchase and the balance in the following month. There is no opening inventory. However it is the company's policy to hold inventory at the end of the month equal to 25% of the following month's sales. The material cost includes the cost of special components that will be bought in from other manufacturer. Wages: will be paid in the same month of production. Variable Overhead Expense: 65% of the expense will be paid in the month of production and the remainder will be paid one month later. Fixed Overhead expense: Fixed overheads are estimated at OMR 120,000 per annum and are expected to be incurred in equal amounts each month. 60% of the fixed overhead costs will be paid in the month in which they are incurred and 25% in the following month. The balance represents depreciation of noncurrent assets. Selling and Distribution Expense will be paid in the one month in arrears. Cash balance on 1st May 2020 is expected to be RO 122,500. Company pays interest on Debentures of RO 6000 in March and September each year Company will purchase Bus for staff in June 2020 at a cost of RO 45,000 on installment basis. The monthly Installment of RO 3,000 is payable from July 2020 onwards. The details of expected sales are given below Months Sales Months Sales March 2.970,000 October 3,718,915 April 3.267.000 November 3,891,531 May 3,118,500 December 3,347,024 2,821,500 3.297.288 July 2,776,950 Feb-21 3.347.559 August 2.979.900 3,595.000 September Apr-21 3.434,500 June Jan-21 Mar-21 3,575,880 Sales Receipts Sales will be mainly through large retail outlets. The pattern for the receipts from sales will be as follows Time of receipt % of Sales Value Immediately 10 One month later 30 Two month later 30 Three month later 20 The balance represent anticipated bad debt. Material cost is 35% of the sale value for the month. Wages will be are 25 % of the sales for the month. Variable Overhead Expense will be 5% of the sales for the month Selling and Distribution Expense will be 10 % of the sales for the month Advance Income tax of OMR 70,000 is due to be paid in November 2020. Dividend on Investment OMR 6,000 is to be received in December 2020 Required: - Prepare a cash budget for the month of May2020 to and April 2021 using MS Excel. 4. EAST WEST Trading is one of the leading business in the GCC. The company presently is in import of goods from Iran since last twenty years. The company is expanding its operation in a new region in the GCC for which it wants to some estimates for the project. You have been assigned the task of making the projection for the cash based on the expected sales and other expense. Material: Suppliers for the purchases are paid 60% in the month of purchase and the balance in the following month. There is no opening inventory. However it is the company's policy to hold inventory at the end of the month equal to 25% of the following month's sales. The material cost includes the cost of special components that will be bought in from other manufacturer. Wages: will be paid in the same month of production. Variable Overhead Expense: 65% of the expense will be paid in the month of production and the remainder will be paid one month later. Fixed Overhead expense: Fixed overheads are estimated at OMR 120,000 per annum and are expected to be incurred in equal amounts each month. 60% of the fixed overhead costs will be paid in the month in which they are incurred and 25% in the following month. The balance represents depreciation of noncurrent assets. Selling and Distribution Expense will be paid in the one month in arrears. Cash balance on 1st May 2020 is expected to be RO 122,500. Company pays interest on Debentures of RO 6000 in March and September each year Company will purchase Bus for staff in June 2020 at a cost of RO 45,000 on installment basis. The monthly Installment of RO 3,000 is payable from July 2020 onwards. The details of expected sales are given below Months March April May June July August September Sales 2.970,000 3,267,000 3,118,500 2,821,500 2,776,950 2,979,900 3,575,880 Months October November December Jan-21 Feb-21 Mar-21 Apr-21 Sales 3,718,915 3,891,531 3,347,024 3,297,288 3,347,559 3,595,000 3,434,500 Sales Receipts Sales will be mainly through large retail outlets. The pattern for the receipts from sales will be as follows Time of receipt % of Sales Value Immediately 10 One month later 30 Two month later 30 Three month later 20 The balance represent anticipated bad debt. Material cost is 35% of the sale value for the month. Wages will be are 25% of the sales for the month. Variable Overhead Expense will be 5% of the sales for the month Selling and Distribution Expense will be 10% of the sales for the month Advance Income tax of OMR 70,000 is due to be paid in November 2020. Dividend on Investment OMR 6,000 is to be received in December 2020 Required: -Prepare a cash budget for the month of May2020 to and April 2021 using MS Excel
