Develop a sixmonth cash budget analysis for company a

Assignment Help Financial Management

Reference no: EM131344220

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

In this part, you are required to develop a Sixmonth Cash Budget (from October 2016 to March 2017) analysis for Company A to

(1) arrange for a shortterm 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

Aug/2016	Sep/2016	Oct/2016	Nov/2016	Dec/2016	Jan/2016	Feb/2016	Mar/2016	Apr/2016
$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 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 shortterm 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 shortterm 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 shortterm 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 shortterm 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 shortterm 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, tstatistics 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 shortterm debt and the other 30% will be raised using longterm debt. If DFN is a surplus, we assume that 70% of the surplus will be used to reduce the outstanding shortterm debt and the other 30% will be used to reduce the outstanding longterm 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.

Reference no: EM131344220

Questions Cloud

Oxidation and reduction : Now read the background for Part II before proceeding to the assignment: Chemistry V: Oxidation and Reduction Topic 10: Oxidation and reduction in metabolism Now watch this short lecture that Introduces Oxidation and Reduction provided by the Khan A..

Prepare a blue print for these firms : Prepare a blue print for these firms on the appropriate mode of entering the Asian markets, and list down the different methods of raising capital in these markets.

Production of ammonia via the haber process : In the production of ammonia via the Haber process, nitrogen gas is combined with hydrogen gas according to the following reaction.

Description of the known explanations or causes of the issue : Assignment (2-4 pages, APA format): Your paper should include: A description of the known explanations or causes of the issue you selected in your Social Issues paper. A description of the theoretical explanations and approaches scholars and policy a..

Develop a sixmonth cash budget analysis for company a : Develop a Sixmonth Cash Budget analysis for Company A to arrange for a shortterm borrowing agreement with the bank;

Explain when the different pk values : Calculate the pH for each of the following points in the titration of 50.0 mL of a 1.5 M H3PO3(aq) with 1.5 M KOH(aq).

Density of air when the atmospheric pressure : What is the difference in density of air when the atmospheric pressure is 1 atm between freezing 0C and what we experienced here in Rancho Mirage CA last summer: 52 C?

Capital budgeting techniques such use of net present value : For this discussion, explain some of the problems that international projects pose when using capital budgeting techniques such as the use of net present value (NPV). Can you think of any instances where the NPV for a project might be different in tw..

Explanation of the relationship between racism and privilege : Post an explanation of the relationship between racism and privilege. Furthermore, explain how the concepts of racism and privilege relate to "Working With Individuals: The Case of Mary." Explain the impact of racism and privilege on social work p..

User Account

All Pages