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 $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. 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 Apri, how large would this line have to be? Explain your answer. Sept Art Nou Year 2019 2019 2019 2019 2020 2020 2020 2020 Month September October November December January February March April Sales $210,000 250,000 170,000 160,000 140,000 180,000 200,000 250,000 Purchases $120,000 150,000 140,000 100,000 80,000 110,000 100,000 90,000 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 Complete the November cash budget for Xenocore, Inc. below: (Round to the nearest dollar.) Xenocore, Inc. Sept Oct 210,000 $ 250,000 $ $ Nov 170,000 $ $ $ $ $ 120,000 $ 150,000 $ 140,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 $ $ $ $ $ Dividends Taxes > $ $ $ $ Purchases of fixed assets Total cash disbursements Net cash flow Add: Beginning cash LA Ending cash $ $ Complete the December cash budget for Xenocore, Inc. below: (Round to the nearest dollar.) Xenocore, Inc. Oct Nov Dec 250,000 $ 170,000 $ 160,000 $ Forecast sales Cash sales Collections Lag 1 month Lag 2 month Other cash receipts Total cash receipts Forecast Purchases $ $ 3 $ $ $ 150,000 $ 140,000 $ 100,000 Cash 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 $ Net cash flow $ Add: Beginning cash 69 Ending cash $ Complete the January cash budget for Xenocore, Inc. below: (Round to the nearest dollar) Xenocore, Inc. Nov Dec 170,000 $ 160,000 $ Jan Forecast sales 140,000 Cash sales Collections Lag 1 month $ Collections Lag 1 month A Lag 2 month $ III. $ Other cash receipts Total cash receipts $ $ Forecast Purchases $ 140,000 $ 100,000 $ 80,000 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 Net cash flow Add: Beginning cash $ $ Ending cash Complete the February cash budget for Xenocore, Inc. below: (Round to the nearest dollar.) Xenocore, Inc. Dec Jan Feb Forecast sales $ 160,000 $ 140,000 $ 180,000 $ $ Cash sales Collections Lg 1 month Lag 2 month Other cash receipts Total cash receipts Forecast Purchases $ $ $ $ 100,000 $ 80,000 $ 110,000 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 16 Xenocore, Inc. Feb Jan Mar Forecast sales $ 140,000 $ 180,000 $ 200,000 Cash sales $ Collections $ $ $ Lag 1 month Lag 2 month Other cash receipts Total cash receipts Forecast Purchases Cash purchases) Payments Lag 1 month $ $ 80,000 $ 110,000 $ 100,000 $ $ Lag 2 month $ $ $ Salaries and wages Rent Interest payments Principal payments Dividends Taxes $ $ $ $ $ $ $ Purchases of fixed assets Total cash disbursements Net cash flow Complete the April cash budget for Xenocore, Inc. below: (Round to the nearest dollar.) Xenocore, Inc. Feb Mar Apr Forecast sales $ 180,000 $ 200,000 $ 250,000 Cash sales $ Collections Lag 1 month Lag 2 month $ $ Other cash receipts Total cash receipts $ $ $ 110,000 $ 100,000 $ 90,000 $ Forecast Purchases Cash purchases Payments Lag 1 month Lag 2 month Salaries and wages Rent Interest payments Principal payments Dividends III $ $ $ $ $ $ $ Taxes A Purchases of fixed assets $ ... $ Cash budget-Advanced The actual sales and purchases for Xenocore, Inc., for September and October 2019, alon Lag 2 month Salaries and wages Rent $ Interest payments Principal payments Dividends $ BA Taxes $ $ $ $ Purchases of fixed assets Total cash disbursements $ Net cash flow BA Add: Beginning cash Ending cash 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. Complete the table below: (Round to the nearest dollar.) Xenocore, Inc. Nov Dec Jan Feb Mar Apr Ending cash $ $ $ Minimum cash balance 15,000 15,000 15,000 15,000 15,000 15,000 Required total financing $ $ $ $ $ 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.)