Question 5. Ahmed and Kamran have recently won a design award for their wooden box' storage and have established a company, AK Wooden box Ltd, to commence production of the storage box. The 'wooden box' storage have an innovative design. Abdullah has just read a newspaper article about the importance of cash budgets in highlighting the finances required for business operations. Consequently, he has asked for your assistance in preparing a cash budget for the first-year operations, from July 2020 to June 2021. The following information has been obtained from a planning meeting with the Khalid: Consider the following sales figures for a newly formed company that makes wooden boxes: July August September October November December Lars February March April May OMR OMR OMR OMR OMR OMR OMR OMR OMR OMR OMR OMR Sales 354,000 472,000 649,000 708,000 885,000 1,026,600 649,000 566,400 413,000 295,000 236,000 590,000 Above figures are based on orders that customers have already placed with the company after considerable hard work by the sales team. In order to secure the orders, the sales team had to negotiate payment terms with the customers. Only 15% of customers agreed to pay immediately for the wooden boxes. Of the remaining customers, 50% agreed to pay after one month and 50% after two months. Within the wooden box industry it is known that 3% of credit customers never pay (because they go out of business or dispute the invoices) the AK Wooden box Ltd has made the decision to reduce the budgeted cash inflow from the credit customers who should pay after two months to reflect this fact (making the percentage who pay after two month 47%). If the AK Wooden box Ltd has a labour cost equal to 20% of the sales value a material cost equal to 35% of the sales value and an overhead cost equal to 30% of the sales value Consider that labour is paid one month in arrears. The material supplier will not allow the AK Wooden box Ltd any credit as because it is a newly formed company, it has no track record of paying its debts. The supplier of materials is also aware that new companies often fail and go out of business thus creating irrecoverable debt. Therefore, the supplier is insisting on cash at time of delivery for all materials purchased. However, the AK Wooden box Ltd must buy the materials before they can be made into boxes. The materials required for production must be purchased and paid for in the month prior to sale. Now let us consider the overhead. Overhead is paid for in the month in which it is incurred. Included in the overhead figures above is an OMR15,000 monthly charge for depreciation. Income tax of OMR28,500 is due to be paid on 20 January,2021. Bank Loan received OMR 1300,000 in the month of September,2020and December, 2020 The company is to pay bonus to worker of OMR 105,000 in the month of Decenber,2020. Plant has been ordered and is expected to be received in month of September and paid in March, 2021. It will be cost OMR 100,000. The cash balance at the beginning of is estimated to be OMR159,400. Required: -Prepare a cash budget for the month of July 2020 to and June 2021 using MS Excel. (10 Marks)
