Cash Budget Analysis and Financial Forecasting
Part I: Cash Budget Problem

In this part, you are required to develop a Six­month Cash Budget (from October 2016 to March 2017) 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

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 November 2016 and February 2017

8. Dividend = $35,000 in October 2016 and January 2017

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

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

11. In September 2016, the ending cash balance is $35,000.

12. Minimum Cash Balance = $30,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 2016 to March 2017).

(2) Our purpose is to control the total amount of short­term borrowing. What is the total amount of borrowing at end of March 2017? 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%, 30%, 10%) to (70%, 25%, 5%), 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?

(3) 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 October 2016. Because the schedule is flexible, we may put this capital expenditure in any month from October 2016 to March 2017. 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) (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 October 2016.

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 $55,000. The Annual Borrowing rate is 5% and the Annual Lending Rate is 3.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.

Part II: Financial Forecasting Problem
In this part, you are required to perform the following analysis for The Home Depot, Inc. (Ticker: HD).

(1) Find the historical financial statements of Home Depot in the most recent five years from UHV online library Mergent Online database or other finance website.

(2) Use TREND function in Excel to perform linear trend extrapolation for the sales of the firm in years 2017~2025.

(3) Perform regression analysis to determine the relationship between sales and inventory of the company. Interpret the regression results: coefficients, t­statistics for coefficients, R square, R square adjusted, and F statistics.

(4) Use percent of sales method to forecast 2017 financial statements (income statement, balance sheet) for the firm.

(5) (Iteration calculations) Use iteration calculations in Excel to eliminate DFN in the pro forma balance sheet if DFN is not equal to 0.

Assumption: If DFN is a deficit, we assume that 70% of the deficit amount will be raised using short­term debt and the other 30% will be raised using long­term debt. If DFN is a surplus, we assume that 70% of the surplus will be used to reduce the outstanding short­term debt and the other 30% will be used to reduce the outstanding long­term debt. You should set a dummy variable (0, 1) in Excel to control  (disable/enable) the iterative calculations.

(6) Summarize/interpret your findings and results.