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 . 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 $11,000 in September and April, $15,000 in January and March, and $25,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. Wages and salaries amount to 21% of the preceding month's sales. Rent of $19,000 per month must be paid. Interest payments of $9,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 $21,000 in January and April. Taxes of $80,000 are due in April. The firm also intends to make a $27,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 $13,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. Sept Oct NOV Forecast sales $ 209.000 $ 251,000 $ 168,000 Cash sales $ 33600 Collections $ 836001 $ 100400 $ 0 $ 184000 $ 118,000 $ 146,000 $ Lag 1 month Lag 2 month Other cash receipts Total cash receipts Forecast Purchases Cash purchases Payments Lag 1 month Lag 2 month Salaries and wages Rent 136,000 13600 $ $ 47200 $ 73000 $ 49000 $ 19000 $ Interest payments Principal payments $ 9000 0 0 Dividends Taxes $ 0 Purchases of fixed assets $ 0 Total cash disbursements $ 210800 Net cash flow $ Oct Nov Dec $ 251,000 $ 168,000 $ $ 159,000 31800 100400 67200 $ $ 5 $ $ $ 0 199400 $ 146,000 $ 136,000 $ Forecast sales Cash sales Collections Lag 1 month Lag 2 month Other cash receipts Total cash receipts Forecast Purchases Cash purchases Payments Lag 1 month Lag 2 month Salaries and wages Rent Interest payments Principal payments Dividends 96,000 9600 $ $ S 58400 6800 34200 19000 9000 $ $ $ 30000 $ 21000 Taxes 0 $ Purchases of fixed assets Total cash disbursements $ 27000 295000 2106301 Net cash flow $ CA 210630 Net cash flow Add: Beginning cash Ending cash $ 22000 37990 Complete the January cash budget for Xenocore, Inc. below: (Round to the nearest dollar.) Xenocore, Inc. Nov Dec Jan 168,000 $ 159,000 $ 142,000 $ 28400 $ A 67200 $ $ 63600 S 15000 A 174200 Forecast sales Cash sales Collections Lag 1 month Lag 2 month Other cash receipts Total cash receipts Forecast Purchases Cash purchases Payments Lag 1 month Lag 2 month Salaries and wages Rent $ 136,000 $ 96,000 $ 77,000 7700 $ 69300 $ $ $ 54400 48000 BA 19000 Interest payments 9000 $ $ 9000 30000 21000 $ $ 0 Interest payments Principal payments Dividends Taxes Purchases of fixed assets Total cash disbursements Net cash flow Add: Beginning cash Ending cash $ 0 $ $ 197010 -3690 18920 195320 $ $ Complete the February cash budget for Xenocore, Inc. below: (Round to the nearest dollar.) Xenocore, Inc. Dec Jan 159,000 $ 142,000 $ Feb $ 176,000 35200 $ $ 63600 Forecast sales Cash sales Collections Lag 1 month Lag 2 month Other cash receipts Total cash receipts Forecast Purchases $ 56800 $ 25000 A 180600 96,000 $ 77,000 $ 108,000 $ $ 9000 30000 21000 $ $ 0 Interest payments Principal payments Dividends Taxes Purchases of fixed assets Total cash disbursements Net cash flow Add: Beginning cash Ending cash $ 0 $ $ 197010 -3690 18920 195320 $ $ Complete the February cash budget for Xenocore, Inc. below: (Round to the nearest dollar.) Xenocore, Inc. Dec Jan 159,000 $ 142,000 $ Feb $ 176,000 35200 $ $ 63600 Forecast sales Cash sales Collections Lag 1 month Lag 2 month Other cash receipts Total cash receipts Forecast Purchases $ 56800 $ 25000 A 180600 96,000 $ 77,000 $ 108,000 38400 Lag 1 month Lag 2 month Salaries and wages $ $ 38500 $ 48000 Rent $ 19000 $ $ $ $ Interest payments Principal payments Dividends Taxes Purchases of fixed assets Total cash disbursements Net cash flow Add: Beginning cash Ending cash $ $ BA 6 $ Jan Feb Mar $ 142,000 $ 176,000 $ 198,000 HA CA $ $ $ 77,000 $ 108,000 $ 97,000 Forecast sales Cash sales Collections Lag 1 month Lag 2 month Other cash receipts Total cash receipts Forecast Purchases Cash purchases) Payments Lag 1 month Lag 2 month Salaries and wages Rent Interest payments Principal payments Dividends Taxes Purchases of fixed assets Total cash disbursements $ $ 69 69 $ $ $ $ Turchases or lixeu assets EA $ 6 Total cash disbursements Net cash flow Add: Beginning cash Ending cash Complete the April cash budget for Xenocore, Inc. below: (Round to the nearest dol Xenocore, Inc. Feb Mar 176,000 $ 198,000 $ $ Apr 246,000 $ $ GA $ $ $ $ Forecast sales Cash sales Collections Lag 1 month Lag 2 month Other cash receipts Total cash receipts Forecast Purchases Cash purchases Payments Lag 1 month Lag 2 month Salaries and wages $ 108,000 $ 97,000 $ 91,000 $ $ S 69 Salaries and wages $ Rent $ Interest payments $ Principal payments $ Dividends $ Taxes $ Purchases of fixed assets $ Total cash disbursements $ Net cash flow $ Add: Beginning cash Ending cash $ b. Assuming that the firm wishes to maintain a $13,000 minimum cash balance, determine the required total financing or excess cash balance for each month, November through April. Complete the table below: (Round to the nearest dollar.) Xenocore, Inc. Nov Dec Jan Feb Mar Apr Ending cash $ $ Minimum cash balance 13.000 13,000 13,000 13,000 13.000 13,000 Required total financing $ 1 Excess cash balance c. The line of credit should be at least $ to cover the maximum borrowing needs for the month of (Round to the nearest dollar. Type the full name of the month, e.g., February.)