SAUDI ARAMCO is one of the leading petroleum company in the GCC. The company presently is in collaboration with the Saudi Government since last forty years. The company is expanding its operation in a new region for which it wants to some projection of the cash. You have been assigned the task of making the projection for the cash based on the expected sales and other expense .

a) The details of Estimated Sales are given below

Sales Receipts

Sales will be mainly through large retail outlets. The pattern for the receipts from sales will be as follows a) Cash sales are 15% of the sales for the month.

b) 20% 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 40% of the sale value for the month.

Wages will be are 15 % of the sales for the month.

Manufacturing Overhead Expense will be 20% of the sales for the month Selling and Distribution Expense will be 10 % of the sales for the month

Fixed Overhead expense:

Fixed overheads are estimated at OMR 180,000 per annum and are expected to be incurred evenly throughout the year. 80% of the fixed overhead costs will be paid in the month in which they are incurred and 10% 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 292,500.

Company receives interest on Debentures of RO 7,500 in March and September each year

Company will purchase Earth Drilling Machine in November on lease for 5 years. The monthly lease rentals will be RO 10,000 is payable from December 2020 onwards.

Advance Income tax of OMR 40,000 is due to be paid in June and October 2020. Material :

Suppliers for the purchases are paid 80% in the month of purchase and the balance in the following month. There is no opening inventory. However it is the companys policy to hold inventory at the end of the month equal to 20% of the following months 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.

Required: -Prepare a cash budget for the month of May2020 to and April 2021 using MS Excel.

please use Excel to answer.

Months Sales Months Sales March 2,789,186 April May June July August September 2,227,500 2,450,250 2,338,875 2,116,125 2,082,713 2,234,925 2,681,910 October November December Jan-21 Feb-21 Mar-21 Apr-21 2,918,648 2,510,268 2,472,966 2,510,669 2,696,250 2,575,875