Budgeting Case study Objectives: (a) To create a spreadsheet for comprehensive budgeting. (b) To apply and reinforce Excel skills. (25 MARKS) Process: 1. 2. Read the case Al Menhali Company. Prepare the following budgets and schedules using MS Excel. a. Schedule of expected cash collections from sales (25 MARKS) b. Schedule of expected cash payments (25 MARKS) c. Cash budget for the period April June 2020. (25 MARKS) 6. You must have separate and clearly marked input and output sections. You may enter the details in the repayments and interest lines of the cash budget manually. This is because the programming skills required to perform this function are quite complicated. Apart from repayments and interest, there is to be no manual input into the Output Section. All cells are to contain formula. For this project - use the exact amount of borrowing. (No need for multiples of 1,000) For example: Cash Deficiency (51,500) Minimum Cash Requirement 40,000 Borrowing 91,500 7. 8. - Read the helpful hints given below Hand in the hard copy of your group's spreadsheet file. The spreadsheet should be uploaded to BBLearn Budget Preparation You are to prepare the cash budget for the Al Menhali Company for April, May and June 2020. PART A 40% of the Company's total sales are cash sales, 60% are credit sales. Collection of the credit sales is as follows: 20% of the Company's credit sales are collected in the month of the sale 30% of the Company's credit sales are collected one month after the date of sale. 50% of the Company's credit sales are collected two months after the date of sale. Actual sales are as follows: February March AED 25,000 AED 25,000. Forecasted sales are as follows: April May June AED 20,000 AED 20,000 AED 20,000 Required: Complete the Schedule of Cash Collections for the months of April, May and June 2020. (25 marks) Schedule of Cash Collections Feb Mar Apr May Jun Total Sales Credit Sales Collection of Credit Sales: Collected in month of sale Collected in One Month after sale Collected Two Months after sale Total Cash Collections of Credit Sales Cash Sales Total Cash Receipts PART B om Actual sales are as follows: February AED 25,000 March AED 25,000. Forecasted sales are as follows: April AED 20,000 May AED 20,000 June AED 20,000 Purchase of inventory is 60% of sales - - - 30% of the Company's purchases are paid in cash in the same month 30% of the Company's purchases are paid in the month after purchase 40% of the Company's purchases are paid two months after purchase Rent of AED 2500 will be paid each month Wages and Salaries of AED 1500 will be paid each month. New equipment will be purchased as follows: - Equipment costing AED 5000 will be purchased and paid for in April 2020 - Equipment costing AED 5000 will be purchased and paid for in May 2020 Insurance of AED 3000 will be paid in April 2020 Required: Prepare the disbursement schedule for the months April, May and June using the data provided. (25 marks) Schedule of Expected Cash Disbursements Feb Mar Apr May Jun Total Sales Total Purchases Payment for Purchases: Cash Purchases Paid the following Month Paid the Second Month Rent Payments Wages and Sales Equipment Insurance Total Cash Disbursements PART C The Company would like to maintain a minimum cash balance of AED 20,000. Any outstanding amount will be repaid at the end of the month when the company has enough cash to do so. Interest on borrowing is charged at 12% a year The cash balance at the end of March 2020 was AED 24,000. Required: Prepare a Cash Budget for the Company using the details which are in parts A and B. (25 marks) Cash Budget April May June Cash Balance, Beginning Cash Receipts Total Cash Available Less: Cash Disbursements Excess (Deficiency) Financing: Borrowing Repayment Interest Total Financing Cash Balance, Ending