* Prepare a cash budget for the month by using MS Excel.
2. CCC is one of the leading construction company in Oman. The company appliers for tenders from various corporate and ministry. The company has been set up facilities for providing services in salalah. The project manager has estimated that if the company starts operation in Salalah it will lead to increase the revenue for the company in the Long term. However he is unsure about the Cash feasibility of the project. You have been appointed to make the Cash budget based on the expected sales and other expenses to be incurred. 4 Material: Suppliers for the purchases are paid 70% 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 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 same month of production. Production 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 Quarter and are expected to be incurred in cqual amounts cach month. 70% 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 one month in arrears. The details of sales projection is given below Months Sales Months March April May June July August September Sales 4.455.000 4,900,500 4.677,750 4.232.250 4.165.425 4.469,850 5.363.820 October November December Jan-21 Feb-21 Mar-21 Apr-21 5.578,373 5.837.297 5.020536 4945.932 5.021.339 5.392 300 5.151.750 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 15 One month later 30 Two month later 35 Three month later 15 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 15% of the sales for the month Selling and Distribution Expense will be 5 % of the sales for the month Cash balance on 1st May 2020 is expected to be RO 72,500 Company receives interest on Debentures of RO 5000 in March and September each year Company will purchase heavy equipment in September on lease for 10 years. The monthly lease rentals will be RO 9,000 is payable from October 2020 onwards. Advance Income tax of OMR 80,000 is due to be paid in December 2020. Required: -Prepare a cash budget for the month of May2020 to and April 2021 using MS Excel. Formatting guidelines: Headings font: Type = Times of Roman; Size = 14, Bold Others font: Type = Times of Roman; Size = 12 Column width: Auto fit 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 Orange Using the formula for calculation. (10 marks)
