Question

Cash budget: Advanced The actual sales and purchases for Xenocore, Inc., for Sep- tember and October 2015, along with its forecast sales and purchases for the period November 2015 through April 2016, follow. The firm makes 20% of all sales for cash and collects on 40% of its sales in each of the 2 months following the sale. Other cash inflows are expected to be $12,000 in September and April, $15,000 in January and March, and $27,000 in February. The firm pays cash for 10 % of its purchases. It pays for 50% of its purchases in the following month and for 40% of its purchases 2 months later. Year 2015 Sales Purchases Month $210,000 $120,000 September October 2015 2.50,000 150,000 140,000 2015 November 170,000 2015 December 100,000 160,000 2016 80,000 January 140,000 2016 February 180,000 110,000 2016 March 100,000 200,000 2016 April 2.50,000 90,000 Wages and salaries amount to 20% of the preceding month's sales. Rent of $20,000 per month must be paid. Interest payments of $10,000 are due in January and April. A principal payment of $30,000 is also due in April. The firm expects to pay cash dividends of $20,000 in January and April. Taxes of $80,000 are due in April. The firm also intends to make a $25,000 cash purchase of fixed assets in December a. Assuming that the firm has a cash balance of $22,000 at the beginning of November, determine the end-of-month cash balances for each month, November through April. b. Assuming that the firm wishes to maintain a $15,000 minimum cash balance, determine the required total financing or excess cash balance for each month, November through April. c. If the firm were requesting a line of credit to cover needed financing for the period November to April, how large would this line have to be? Explain your answer.

Answer 1

a) Opening Cash Balance of 22000 Particulars Opening Balance Add:- Receipts Receipts from debtors Other Cash Inflows Working Note November december January 22,000 53,000 40,000 February 31,000 March 87,000 April 131,000 218,000 200,000 160,000 15,000 156,000 27,000 168,000 15,000 202,000 12,000 Total (A) 218,000 | 200,000 175,000 183,000 183,000 214,000 137,000 30,000 20,000 140,000 28,000 20,000 114,000 20,000 20,000 91,000 16,000 20,000 97,000 22,000 20,000 Less: Payments Payments to creditors Wages & salaries Rent Principal payment Interest Purchase of fixed assets Cash Dividend Taxes Total (B) Closing Balance 103,000 20,000 20,000 30,000 10,000 10,000 25,000 20,000 20,000 80,000 283,000 62,000 187,000 53,000 213,000 40,000 184,000 31,000 127,000 87,000 139,000 131,000

Working Notes 1 Receipts from debtors Particulars Sales Cash Sales (Formula) Cash Sales (Answer)(A) Receipts from Credit sales (Formula) September 210,000 October 250,000 November December January February March 170,000 160,000 140,000 180,000 200,000 170000*20% 160000 20% 140000*20% 180000*20% 200000*20% 34,000 32,000 28,000 36,000 40,000 210000*40% 250000*40% 170000*40% 160000*40% 140000*40% 250000*40% 170000-40%) 160000*40%) 140000*40% 180000-40% 84,000 100,000 68,000 64,000 56,000 100,000 68,000 64,000 56,000 72,000 184,000 168,000 132,000 120,000 128,000 218,000 200,000 160,000 156,000 168,000 April 250,000 250000*20% 50,000 180000*40% 200000-40% 72,000 80,000 152,000 202,000 Receipts from Credit sales (Answer) Total Cash Receipts (A+B) 2 September 120,000 October 150,000 Payment to creditors Particulars Purchases Cash purchases (Formula) Cash purchases (Answer) (A) Payments to creditors (Formula) November December January February March 140,000 100,000 80,000 110,000 100,000 140000*10% 100000*10% 80000*10% 110000*10% 100000*10% 14,000 10,000 8,000 11,000 10,000 150000*50% 140000*50% 100000*50% 80000*50% 110000*50% 120000*40% 150000*40% 140000*40% 100000*40% 80000*40% 75,000 70,000 50,000 40,000 55,000 48,000 60,000 56,000 40,000 32,000 123,000 130,000 106,000 80,000 87,000 137,000 140,000 114,000 91,000 97,000 April 90,000 90000*10% 9,000 100000*50% 110000*40% 50,000 44,000 94,000 103,000 Payments to creditors (Answer) (B) Total cash Payments

3 Wages &Salaries Particulars Sales Wages & Salaries (Formula) Wages & Salaries (Answer) October 150,000 November December January February 140,000 100,000 80,000 110,000 150000*209140000 20% 100000*20% 80000*20% 30,000 28,000 20,000 16,000 March 100,000 110000 20% 22,000 April 90,000 100000 20% 20,000

b) April February 15,000 If a firm Wishes to maintain balance of 15,000 Cash Balance Particulars Working Note November december January Opening Balance 22,000 15,000 Add:- Receipts Receipts from debtors 218,000 200,000 160,000 Other Cash Inflows 15,000 March 15,000 15,000 15,000 168,000 156,000 27,000 202,000 12,000 15,000 Total (A) 218,000 200,000 175,000 183,000 183,000 214,000 137,000 30,000 20,000 140,000 28,000 20,000 114,000 20,000 20,000 91,000 16,000 20,000 97,000 22,000 20,000 103,000 20,000 20,000 30,000 10,000 10,000 25,000 Less: Payments Payments to creditors Wages & salaries Rent Principal payment Interest Purchase of fixed assets Cash Dividend Taxes Total (B) Closing Balance (C) Opening Balance + A-B) Desired Cash Balance Excess cash to be Invested (C-15000) Financing Needed (15000-C) 20,000 187,000 53,000 15,000 38,000 213,000 2,000 15,000 184,000 6,000 15,000 127,000 71,000 15,000 56,000 139,000 59,000 15,000 44,000 20,000 80,000 283,000 -54,000 15,000 13,000 9,000 69,000

C) If the firm is requesting for line of credit, It should be $69000 as it is peak financing neede required during the poeriod November to April.