ALL QUESTIONS ARE COMPULSARY 1. Al Salam Catering is one of division of Oman Air Oman. The company presently started providing catering facility to the Airline industry. The Finance Manager wants to know the Cash feasibility of the project. You have been appointed to prepare the Cash budget based on the expected sales and other expenses to be incurred Consider the following expected sales figure for the coming months Months Sales Months Sales March 3.712.500 October 5.810,805 April 4,083,750 November 4.648,644 May 3,898,125 December 4,183,780 Junc 3.526,875 Jan 2021 3.718,915 July 3.471.188 Feb 2021 3.254,051 August 3,724,875 March 2021 2,789,186 September 4.469.850 April 2021 2.324,322 Sales Receipts Sales will be mainly through large retail outlets. The pattem for the receipts from sales will be as follows a) Cash sales are 15% of the sales for the month. b) 25% of the sales for the month is recevied one month later c) 40% of the sales for the month is recevied two months later d) 15% of the sales for the month is recevied Three months later e) The balance represent anticipated bad debt. Material cost is 30% of the sale value for the month. Wages will be are 15% of the sales for the month. Overhead Expense will be 10% of the sales for the month Selling and Distribution Expense will be 5% of the sales for the month Fixed Overhead expense: Fixed overheads are estimated at OMR 84,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 15% in the following month. The balance represents depreciation of noncurrent assets. Selling and Distribution Expense will be paid in the month following the sales. Cash balance on Ist May 2020 is expected to be RO 35,500. Company pays half yearly dividends RO 4,000 in March and Sept cach year Company pays bonus to new staff in July RO 2000 Material: Payment for the purchases will be made in the month following the receipt of material. There is no opening inventory. However it is the company's policy to hold inventory at the end of the month equal to 20% 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 month of Production. Overhead Expense: 60% of the expense will be paid in the month of production and the remainder will be paid one month later. Income tax of OMR 100,000 is due to be paid in September 2020. Share premium @ 10% of OMR 50,000 is to be received in August 2020 Required: -Prepare a cash budget for the month of May2020 to and April 2021 using MS Excel. Formatting guidelines: Headings font: Type = Arial = 12, Bold Others font: Type = Arial ; Size = 10 Column width: 22 Merge and center cells: Main Heading, All the cells for amounts = category=Number; Decimal places=0 and Use 1,000 separator, Shade the cells in the Heading row = Light Blue Using the formula for calculation. (10 marks)
