In this problem, you are required to develop a 9-month Cash Budget (October 2022 to June 2023) analysis for a firm to (1) develop its monthly cash budget; (2) decide the best schedule for the large capital expenditure; (3) answer the questions. The following is the available data for the problem.

1. The historical monthly sales data (Oct/2021 ~ Sep/2022) are given in the following table. Use TREND function to perform linear trend extrapolation to forecast the companys monthly sales from Oct/2022 to Jul/2023 for the purpose of cash budget analysis.

Date Format	10/31/2021	11/30/2021	12/31/2021	01/31/2022	02/28/2022	03/31/2022	04/30/2022	05/31/2022	06/30/2022
Sales	$585,000	$655,000	$685,000	$735,000	$785,000	$815,000	$835,000	$855,000	$885,000

2. Sales: 40% is paid in cash, and 60% collected in the following month.

3. Inventory = 55.9% of the sale in the following month.

4. Inventory payments: 50% of inventory is paid for in the month of delivery, and 50% is paid one month later.

5. Wages and Salary = 36.5% of sales in the previous month.

6. Principal and interest payments: $115,000 in October 2022, $125,000 in January 2023, and $135,000 in April 2023

7. Taxes: $25,000 in December 2022, $28,000 in March 2023, and $30,000 in June 2023

8. Capital expenditure is scheduled in January 2023. The total amount is $250,000. The schedule is flexible and can be changed.

9. In September 2022, the ending cash balance is $38,000.

10. Minimum Cash Balance = $30,000

You are required to:

(1) Develop the basic cash budget table without repaying short-term borrowing for the company. Directly link the Cash Budget worksheet with the sales forecast worksheet to obtain the sales (Note: DO NOT round up/down the predicted sales numbers) for the cash budget analysis. Calculate total collections, total payments to inventory, total disbursements, the unadjusted cash flows, current borrowing, and the ending cash balance for nine months (October 2022 to June 2023), and develop the basic cash budget table without repaying short-term borrowing for the company.

(2) Develop the basic cash budget table with repaying previous short-term borrowing for the company.

(3) Our purpose is to control the total amount of short-term borrowing. What is the total amount of borrowing at the end of June 2023? One way to reduce the total borrowing is to speed cash collections from sales, and another way is to slow down the payments for inventory purchase. If we increase the cash sales from 40% to 50%, and decrease the cash payments to inventory purchase from 50% to 35%, what is the total amount of borrowing? Discuss the advantages and disadvantages of these strategies? (Note: Based on Step 1 without repaying short-term borrowing)

(4) The second strategy to reduce the short-term borrowing is to find the best schedule for the capital expenditure, which is originally scheduled in January 2023. Because the schedule is flexible, you can schedule this spending in any month from October 2022 to June 2023. Use the Scenario Manager in Excel to decide the best time for the capital expenditure to keep the total amount of borrowing at the lowest level (Note: Based on Step 1 without repaying short-term borrowing).

(5) (Complex Cash Budget Problem) Now we need to take current borrowing, current investing, and short-term interest payments into account and develop a separate complex cash budget for the company. You are required to add such items as interest expense for short-term borrowing (investing), current investing, cumulative borrowing (investing), and cumulative interest expenses into the Step 1 cash budget table. (Note: we assume the capital expenditure is scheduled in January 2023 for the complex cash budget analysis).

A) If the firm plans to borrow and it has some investments, you should sell investments to reduce the amount of borrowing. If the unadjusted cash balances greater than the minimum and the firm has previous borrowing, then use the cash above the minimum to reduce the outstanding borrowing.

B) If the sum of the unadjusted cash balance and current borrowing is less than the minimum required cash, the firm needs to sell some investments. If the sum of the unadjusted cash balance and current borrowing is greater than the maximum acceptable cash, the firm must invest the excess cash in the short-term securities.

C) The annual borrowing rate is 6.60% and the annual lending rate is 4.32%. The maximum cash balance is $60,000.

(5) To understand the cash budget table, you must work through the cash budget table and explain your results about the borrowing and investing month by month in a separate Word report.