Question

Cash budget-Advanced The actual sales and purchases for Xenocore, Inc., for September and October 2019, along with its forecast sales and purchases for the period November 2019 through April 2020, follow A. The firm makes 26% of all sales for cash and collects on 37% 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 $25,000 in February. The firm pays cash for 11% of its purchases. It pays for 50% of its purchases in the following month and for 39% of its purchases 2 months later. Wages and salaries amount to 20% of the preceding month's sales. Rent of $19,000 per month must be paid. Interest payments of $10,000 are due in January and April. A principal payment of $35,000 is also due in April. The firm expects to pay cash dividends of $18,000 in January and April. Taxes of $79,000 are due in April. The firm also intends to make a $26,000 cash purchase of fixed assets in December. a. Assuming that the firm has a cash balance of $21,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 $17,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.

Please Help solve

Year    Month    Sales    Purchases
2019   September    $209,000   $122,000
2019   October    248,000   152,000
2019   November    173,000   136,000
2019   December    165,000   104,000
2020   January    138,000   80,000
2020   February    176,000   114,000
2020   March    199,000   95,000
2020   April    252,000   88,000

Answer 1

Answer with working notes is given below

November December $173,000 $165,000 January $138,000 February $176,000 March 199,000 April | Total 252,000 $1,103,000 Sales Budget Cash Sales - 26% Credit Sales November December 44,980 42,900 128,020 122,100 January 35,880 102,120 February 45,760 130,240 March 51,740 147,260 April | 65,520 186,480 Total 286,780 816,220 Expected Cash Collection January February March April November December $77,330 $91,760 $91,760 $64,010 $64,010 $61,050 September month (37% of $209,000) October month (37% of $248,000 for each month) November month December month January month February month March month Total Collection from Customers $61,050 $51,060 Total $77,330 $183,520 $128,020 $122,100 $102,120 $130,240 $73,630 $816,960 $51,060 $65,120 $65,120 $73,630 $138,750 $169,090 $155,770 $125,060 $112,110 $116,180 November December $136,000 $104,000 January $80,000 February $114,000 March $95,000 April | $88,000 Total $617,000 Purchase Cash Sales - 11% Credit Sales November December 14,960 11,440 121,040 92,560 January 8,800 71,200 February 12,540 101,460 March 10,450 84,550 April | 9,680 78,320 Total 67,870 549,130 Cash payment for Credit purchases January February March April | November December $47,580 $76,000 $59,280 $68,000 $53,040 $52,000 September month (39% of $122,000) October month (50%-November and 39% for December) November month December month January month February month March month Total payment for purchases $40,560 $40,000 Total $47,580 $135,280 $121,040 $92,560 $71,200 $101,460 $47,500 $616,620 $31,200 $57,000 $44,460 $47,500 $91,960 $123,580 $127,280 $105,040 $80,560 $88,200 November December $49,600 $34,600 January $33,000 February $27,600 March $35,200 April $39,800 Total $219,800 Wages and Salaries (for November month October sales- 20% November December $21,000 $27,930 $44,980 $42,900 $169,090 $155,770 January $8,280 $35,880 $125,060 $15,000 $184,220 February $9,620 $45,760 $112,110 $25,000 $173,250 March $33,550 $51,740 $116,180 $15,000 $216,470 April | Total $63,620 $21,000 $65,520 $286,780 $138,750 $816,960 $12,000 $67,000 $279,890 $1,191,740 $235,070 $226,600 Cash Budget Opening Balance Cash sales Cash collections from customers Other Cash inflows Total cash available Less: cash disbursements Cash purchase Cash paid for Credit purchase Wages and Salaries Rent Interest Principal repayment Dividend Taxes Fixed Asset purchase Total cash disbursement Closing balance Cash $14,960 $123,580 $49,600 $19,000 $11,440 $127,280 $34,600 $19,000 $8,800 $105,040 $33,000 $19,000 $10,000 $12,540 $80,560 $27,600 $19,000 $10,450 $88,200 $35,200 $19,000 $9,680 $67,870 $91,960 $616,620 $39,800 $219,800 $19,000 $114,000 $10,000 $20,000 $35,000 $35,000 $18,000 $36,000 $79,000 $79,000 $26,000 $302,440 $1,214,290 $22,550 $22,550 $18,000 $207,140 $27,930 $26,000 $218,320 $8,280 $193,840 $9,620 $139,700 $33,550 $152,850 $63,620 November December $21,000 $27,930 $44,980 $42,900 $169,090 $155,770 January $17,000 $35,880 $125,060 $15,000 $192,940 February $17,000 $45,760 $112,110 $25,000 $199,870 March $60,170 $51,740 $116,180 $15,000 $243,090 April | Total $90,240 $21,000 $65,520 $286,780 $138,750 $816,960 $12,000 $67,000 $306,510 $1,191,740 $ 235,070 $226,600 $14,960 $123,580 $49,600 $19,000 $11,440 $127,280 $34,600 $19,000 $8,800 $105,040 $33,000 $19,000 $10,000 $12,540 $80,560 $27,600 $19,000 b) Cash Budget Opening Balance Cash sales Cash collections from customers Other Cash inflows Total cash available Less: cash disbursements Cash purchase Cash paid for Credit purchase Wages and Salaries Rent Interest Principal repayment Dividend Taxes Fixed Asset purchase Total cash disbursement Closing balance Cash Excess Cash Financing needed Borrowing Needed Closing balance Cash $10,450 $88,200 $35,200 $19,000 $9,680 $67,870 $91,960 $616,620 $39,800 $219,800 $19,000 $114,000 $10,000 $20,000 $35,000 $35,000 $18,000 $36,000 $79,000 $79,000 $26,000 $302,440 $1,214,290 $4,070 $22,550 $18,000 $207,140 $26,000 $218,320 $8,280 $193,840 $900 $139,700 $60,170 $43,170 $152,850 $90,240 $73,240 $10,930 $8,720 $8,720 $17,000 $17,900 $17,900 $17,000 $12,930 $12,930 $17,000 $27,9301 $60,170 $90,240 $39,550 $17,000 c) Line of credit would be needed for 3 months December $8,720 January $17,900 April $12,930| Firm needs to have arrangement with bank to borrow above money in each month to ensure it has minimum balance of $17,000 in its books