Part I: Cash Budget Problem

In this part, you are required to develop a Six-month Cash Budget (from March 2021 to August 2021) analysis for Company A 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. Actual and expected sales data are given in the following table.

Jan/2021	Feb/2021	Mar/2021	Apr/2021	May/2021	Jun/2021	Jul/2021	Aug/2021	Sep/2021
$251,000	$267,000	$272,000	$293,000	$306,000	$312,000	$320,000	$347,000	$361,000

2. Sales: 60% cash, 30% collected in the following month, 10% collected two months later.

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

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

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

6. Monthly interest payments = $12,000.

7. Principal payments $35,000 in April 2021 and July 2021

8. Dividend = $35,000 in March 2021 and June 2021

9. Taxes, $25,000 in May 2021 and $28,000 in August 2021

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

11. In February 2021, the ending cash balance is $35,000.

12. 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 the six-month period (March 2021 to August 2021), 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 end of August 2021? 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%, 25%, 5%) to (70%, 30%, 0%), and decrease the cash payments to inventory from (50%, 50%) to (30%, 70%), what is the total amount of borrowing? What are the disadvantages of these strategies?

(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 May 2021. Because the schedule is flexible, you can schedule this spending in any month from March 2021 to August 2021. 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.

(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 cash budget table. (Note: we assume the capital expenditure is scheduled in May 2021 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.0% and the annual lending rate is 3.6%. The maximum cash balance is $50,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 the Word report