Part I: Cash Budget Analysis (100 points)

In this problem, you are required to develop a9-month Cash Budget (April 2018 to December 2018) analysis for a high-growth firm to (1) arrange for a short-term borrowing agreement with the firm's bank; (2) decide the best schedule for its large capital expenditure; (3) answer the questions.

The following is the available data for the problem:

1.The historical monthly sales data (April/2017 ~ March/2018) are given in the following table. UseTRENDfunction to perform linear trend extrapolation to forecast the company's monthly sales from April 2018to January 2019 for the purpose of cash budget analysis.Note:When you use trend analysis to forecast sales, (a) you must use the date input format as "Month/the end of the Day in the month/Year ", for example: 3/31/2018. Otherwise you will get slightly different sales; (b)LINK the worksheet for cash budget table with the worksheet for trend analysis directly to obtain the sales data for the cash budget purpose.

Date Format

4/30/2017

5/31/2017

6/30/2017

7/31/2017

8/31/2017

9/30/2017

10/31/2017

11/30/2017

12/31/2017

1/31/2018

2/28/2018

3/31/2018

Sales

$575,000

$635,000

$695,000

$715,000

$785,000

$815,000

$835,000

$855,000

$895,000

$935,000

995,000

1,055,000

2.Sales: 35% is paid in cash, and 65% collected in the following month.

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

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

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

6.Interest payments: $20,000 each month

8.Principal payments: $58,000 in June 2018 and $68,000 in September 2018

9.Taxes: $25,000 in August 2018 and $28,000 in November 2018

10.Capital expenditure is scheduled in July 2018. The total amount is $218,000. The schedule is flexible and can be changed.

11.InMarch 2018, the ending cash balance is $55,000.

12.Minimum Cash Balance = $35,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 (April 2018 ~ December 2018), 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 December 2018? 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 35% to 50%, and decrease the payments to inventory purchase from 55% to 35%, what is the total amount of borrowing? Discuss the advantages and 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 July 2018. Because the schedule is flexible, you can schedule this spending in any month from April 2018 to December 2018.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 July 2018 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 $65,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 your Word report.

Part II: Financial Forecasting (100 points)

In this part, you are required to find a listed firm to perform the following financial forecasting analysis. The selected company (1) is a manufacturing firm, and (2) has significant amount of short-term and long-term debts on its balance sheet for the iteration purpose.

(1)Find the historical financial statements (2013~2017) of the selected company from UHV online libraryMergent Onlinedatabase.

(2)UseTRENDfunction in Excel to perform linear trend extrapolation for the sales of the company from 2018 to 2022.

(3)Perform regression analysis to analyze the relation of sales and accounts receivables (A/R) for the company. Interpret the regression results: coefficient, t-statistic for the coefficient, R square, R square adjusted, and F statistic.

(4)Use the percent of sales method to forecast the 2018 financial statements (Income Statement, Balance Sheet) of the company.

(5)(Iteration calculations)Use iteration calculations in Excel to eliminate DFN in the pro forma balance sheet if DFN is not equal to zero.Assumption:If the DFN is a deficit, we assume the deficit amount will be raised through borrowing long-term debt. If DFN is a surplus, the company will use the surplus to pay off the long-term debt first. If the DFN is greater than the total amount of long-term debt, the remaining DFN can be used to pay off short-term debt. You can set a dummy variable (0, 1) in Excel to control (enable/disable) the iterative calculations for each year.

(6)Summarize/interpret your major findings and results in the Word file.