Six-month Cash Budget (from October 2017 to March 2018) analysis for Company A to (1) arrange for a short-term borrowing agreement with the bank; (2) decide the best schedule for the large capital expenditure; (3) answer the required questions. The following is the available data for the problem.

1. Actual and expected sales data are given in the following table.

Sep/ 2017	Oct/ 2017	Nov/ 2017	Dec/ 2017	Jan/2018	Feb/2018	Mar/2018	Apr/2018
$568,000	$575,000	$598,000	$618,000	$632,000	$650,000	$667,000	$681,000

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

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

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

5. Wages = 25% of the sales in the previous month.

6. Monthly interest payments = $15,000.

7. Principal payments $25,000 in November 2017 and February 2018

8. Dividend = $28,000 in October 2017 and January 2018

9. Taxes, $25,000 in December 2017, and $28,000 in March 2018

10. Capital expenditure scheduled in November 2017: $300,000, but the schedule is flexible and may be changed.

11. In September 2017, the ending cash balance is $38,000.

12. Minimum Cash Balance = $55,000

You are required to:

(1) Develop a basic cash budget for the company. Calculate total collections, total payments to inventory, total disbursements, the unadjusted cash flows, current borrowing, and the ending cash balance for six months (from October 2017 to March 2018).

(2) Our purpose is to control the total amount of short-term borrowing. What is the total amount of borrowing at end of March 2018? One strategy to reduce the total amount of debt is to speed cash collections from sales and slow down the payments for inventory purchase. If we increase the cash sales from (60%, 40%) to (70%, 30%), and decrease the cash payments to inventory from (60%, 40%) to (50%, 50%), what is the total amount of borrowing? What are the disadvantages of these strategies?

The second strategy to reduce short-term borrowing is to find a best schedule for the capital expenditure, which is originally scheduled to spend in November 2017. Because the schedule is flexible, we may put this capital expenditure in any month from October 2017 to March 2018. Use the Excel Scenario Manager function to decide the best schedule for the capital expenditure if we want to keep the total amount of borrowing at the lowest level.

(4) To understand the cash budget, you must work through the table and explain your results in your report.

(5) (The Complex Cash Budget Problem) Now we 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 cash budget table. We assume the capital expenditure is scheduled in November 2017.

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 maximum cash balance is $85,000. The Annual Borrowing rate is 6% and the Annual Lending Rate is 4.8%.

D) To understand the complex cash budget, you must work through and explain your results regarding borrowing and investing month by month in your report.