You will create a complete cash budget for the four quarters of 2022 for the above...

   

Transcribed Image Text:

You will create a complete cash budget for the four quarters of 2022 for the above company. Assumptions used in the cash budgeting process include: 1) Actual and expected sales from the second quarter of 2022 through the third quarter of 2023 are as given below (in the unit of Million $). Please note the sales in June 2022 means the sales in the second quarter of 2022, and Sep 2022 sales mean the sales in the third quarter of 2022. Sales Jun-2022 49,013 Sep-2022 52,251 Dec-2022 Mar-2023 Jun-2023 53,245 58,835 61,734 Sep-2023 42,108 2) 20% of sales are for cash. Of the remaining sales, 70% is collected in the following month and 30% is collected two months after the sale. 3) Raw materials inventory purchases are equal to 60% of the current quarter's sales (e.g., the first quarter purchases are 60% of expected first quarter sales). Among the purchases, 55% of purchases are paid for in the same quarter and 45% of purchases are paid for in the following quarter (e.g., among the first quarter purchases, 55% are paid in the first quarter and 45% are paid in the second quarter). 4) Wages are forecasted to be equal to 25% of expected sales in the same quarter. 5) Interest payments of $1,000 million on long-term debt are due in each quarter. 6) A $3,000 million dividend will be paid to shareholders in each quarter. 7) Tax prepayments of $9,000 million will be paid in each quarter. 8) Suppose A Inc. must keep a minimum cash balance of $40,000 million by agreement with its bank. Its cash balance at the end of the fourth quarter of 2021 was $40,000 million. Please finish the following work: a) Using the following template to create a complete cash budget for the four quarters of 2022 for this company. You may adjust the template if needed. Sales For the Period December 2022 to September 2023 in million dollars Dec- 2022 Collections: Cash The Following Quarter Second Quarter Total Collections Purchases Payments: Current Quarter The Following Quarter Total Payments Collections Less Disbursements: Alphebet A Inc. Cash Budget Inventory Payments Jun- 2022 Sep-2022 Mar- 2023 Jun- 2023 Sep- 2023 Collections Less Disbursements: Inventory Payments Wages Interest Dividend (Common) Taxes Total Disbursements Beginning Cash Balance Collections - Disbursement Unadjusted Cash Balance Current Borrowing Ending Cash Balance Cumulative Minimum Acceptable Cash b) Please calculate the Accumulated Borrowing from the last quarter of 2022 till the third quarter of 2023 and find the appropriate amount of Borrowing Need for those periods. c) Currently, raw materials inventory purchases are equal to 60% of the current quarter's sales. Please create three scenarios, assuming that raw materials inventory purchases are equal to 55%, 60%, or 65% of the current quarter's sales. What is the maximum that the firm would need to borrow to maintain its minimum cash balance in all three cases? Use "Scenario Manager" and create a summary of your results. You will create a complete cash budget for the four quarters of 2022 for the above company. Assumptions used in the cash budgeting process include: 1) Actual and expected sales from the second quarter of 2022 through the third quarter of 2023 are as given below (in the unit of Million $). Please note the sales in June 2022 means the sales in the second quarter of 2022, and Sep 2022 sales mean the sales in the third quarter of 2022. Sales Jun-2022 49,013 Sep-2022 52,251 Dec-2022 Mar-2023 Jun-2023 53,245 58,835 61,734 Sep-2023 42,108 2) 20% of sales are for cash. Of the remaining sales, 70% is collected in the following month and 30% is collected two months after the sale. 3) Raw materials inventory purchases are equal to 60% of the current quarter's sales (e.g., the first quarter purchases are 60% of expected first quarter sales). Among the purchases, 55% of purchases are paid for in the same quarter and 45% of purchases are paid for in the following quarter (e.g., among the first quarter purchases, 55% are paid in the first quarter and 45% are paid in the second quarter). 4) Wages are forecasted to be equal to 25% of expected sales in the same quarter. 5) Interest payments of $1,000 million on long-term debt are due in each quarter. 6) A $3,000 million dividend will be paid to shareholders in each quarter. 7) Tax prepayments of $9,000 million will be paid in each quarter. 8) Suppose A Inc. must keep a minimum cash balance of $40,000 million by agreement with its bank. Its cash balance at the end of the fourth quarter of 2021 was $40,000 million. Please finish the following work: a) Using the following template to create a complete cash budget for the four quarters of 2022 for this company. You may adjust the template if needed. Sales For the Period December 2022 to September 2023 in million dollars Dec- 2022 Collections: Cash The Following Quarter Second Quarter Total Collections Purchases Payments: Current Quarter The Following Quarter Total Payments Collections Less Disbursements: Alphebet A Inc. Cash Budget Inventory Payments Jun- 2022 Sep-2022 Mar- 2023 Jun- 2023 Sep- 2023 Collections Less Disbursements: Inventory Payments Wages Interest Dividend (Common) Taxes Total Disbursements Beginning Cash Balance Collections - Disbursement Unadjusted Cash Balance Current Borrowing Ending Cash Balance Cumulative Minimum Acceptable Cash b) Please calculate the Accumulated Borrowing from the last quarter of 2022 till the third quarter of 2023 and find the appropriate amount of Borrowing Need for those periods. c) Currently, raw materials inventory purchases are equal to 60% of the current quarter's sales. Please create three scenarios, assuming that raw materials inventory purchases are equal to 55%, 60%, or 65% of the current quarter's sales. What is the maximum that the firm would need to borrow to maintain its minimum cash balance in all three cases? Use "Scenario Manager" and create a summary of your results.

Answer rating: 100% (QA)

2 The company expects to collect 80 of sales in the same quarter 15 in the following quarter and 5 i... View the full answer