Question: develop aSix-month Cash Budget (from October 2019 to March 2020) (1) arrange for a short-term borrowing agreement with the bank; (2) decide the best schedule for the large capital expenditure. The following is the available data for the problem.

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

Sep/ 2019 Oct/ 2019 Nov/ 2019 Dec/ 2019 Jan/2020 Feb/2020 Mar/2020 Apr/2020

$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 = 58% 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 2019 and February 2020

8.Dividend = $28,000 in October 2019 and January 2020

9.Taxes, $25,000 in December 2019, and $28,000 in March 2020

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

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

12.Minimum Cash Balance = $50,000

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

b. 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?

c. The second strategy to reduce the short-term borrowing is to find the best schedule for the capital expenditure, which is originally scheduled in November 2019. Because the schedule is flexible, you can schedule this spending in any month from October 2019 to March 2020.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.