Provide working answers in excel to Q1 See attached pdf

CASH BUDGET Q1) Prepare a cash budget of a company for April, May and June 2001 in a columnar form using the following information: Months 2001 Jan (actual) Feb (actual) March (actual) April (Budgeted) May (Budgeted) June (Budgeted) Sales Rs. 80,000 80,000 75,000 90,000 85,000 80,000 Purchases Rs. 45,000 40,000 42,000 50,000 45,000 35,000 Wages Rs. 20,000 18,000 22,000 24,000 20,000 18,000 Expenses Rs. 5,000 6,000 6,000 7,000 6,000 5,000 You are further informed that: sh is ar stu ed d vi y re aC s o ou urc rs e eH w er as o. co m a) 10% of the purchases and 20% of the sales are for cash. b) The average collection period of the company is month and the credit purchases are paid off regularly after one month. c) Wages are paid half monthly and the rent of Rs 500 included in expenses is paid monthly. d) Cash and bank balances as on April 1 was Rs 15,000 and the company wants to keep it on the end of every month below this figure the excess cash being put in fixed deposits in multiples of 1000. Q2) Gold Stone Ltd has given the following particulars you are required to prepare a cash budget for 3 months ending 31st Dec 2003 Month Aug Sept Oct Nov Dec Sales Materials Wages Overheads 40,000 20,400 7,600 3,800 42,000 20,000 7,600 4,200 46,000 19,600 8,000 4,600 50,000 20,000 8,400 4,800 60,000 21,600 9,000 5,000 Th Credit Terms are: i) Sales/Debtors - 10% sales are on cash basis; 50% of the Credit Sales are collected next month and the balance in the following months. Creditors - Materials 2 months, wages 1/5 month, overheads month. ii) Cash balance on 1st oct 2003 is expected to be Rs. 8,000 iii) A machinery will be installed in Aug 03 at a cost of Rs 1, 00,000. The monthly installment of Rs 5,000 is payable from October onwards. iv) Dividend at 10% on preference shares capital of Rs 3,00,000 will be paid on 1st Dec 03. v) Advance to be received for sale of vehicle Rs. 20,000 in December. vi) Income Tax (advance) to be paid in December Rs. 5,000 https://www.coursehero.com/file/47718760/Cash-budget-1pdf/ Q3) A firm adopts a six - monthly time span, subdivided into monthly intervals for its cash budget. A) The foll information is available in respect of its operation (Rs. Lakh) 2 3 4 5 6 1 Sales 40 50 60 60 60 60 2 Purchases 1 1.5 2 2 2 1 3 D. Lab 6 7 8 8 8 6 4 Mfg OH 13 13.5 14 14 14 13 5 Adv Exp 2 2 2 2 2 2 6 Dist Exp 7 sh is ar stu ed d vi y re aC s o ou urc rs e eH w er as o. co m 1 Raw mat - 30 days credit 2 3 4 4 4 2 14 15 16 16 16 15 B) Assume the foll financial flows during the period: (1) Inflows : 1) Int recd in month 1 and month 6 Rs 1 lakh each. 2) Dividend recd during month's 3 and 6, Rs 2 lakh each. 3) Sales of shares in month 6, Rs 160 lakh. (2) Outflows: 1) Interest paid during month 1, Rs 0.4 lakh 2) Dividends paid during months 1 & 4, Rs. 2 lakh each. 3) Installment payment on machine in month 6, Rs 20 lakh Th 4) Repayment of loan in month 6, Rs 80 lakhs. C) Assume that 10% of each month sales are for cash; the balance 90% are on credit. The terms and credit experience of the firm are:1) No cash discount 2) 1% of credit sales is returned by the customers. 3) 1% of total accounts receivable in bad debt. 4) 50% of all accounts that are going to pay, do so within 30 days. 5) 100% of all accounts that are going to pay, do so within 60 days. https://www.coursehero.com/file/47718760/Cash-budget-1pdf/ Powered by TCPDF (www.tcpdf.org)