Excel spreadsheets

JustExcelSpreadsheetsweek3-5.docx

Home >Business & Finance homework help >Economics homework help >Excel spreadsheets

Week 2 - Assignment 2: The Cost of Capital

Canyon Drilling, Inc. has just come under new management. One of the first things the new management wants to accomplish is to identify its capital structure and the cost of additional funding, if needed.

According to the accounting department, the current balance sheet is accurate and reflects the financial structure of the company. They have also calculated the marginal tax rate to be 40%. The company’s beta is currently 1.15.

Your Chief Financial Officer, Marge, has also provided you the following information about the market and the company’s financials:

Company Specifics

Debt:

3,600 par value ($1,000) bonds outstanding. All have a 7% coupon, and will mature in 20 years. Market value is currently $1,050 and interest is paid once a year.

Equity:

Common Stock

The company has 40,000 shares of common stock outstanding, and has a market price of $50 per share. The stock last paid a dividend of $1.40 and had a constant growth of 5% per year.

Preferred Stock

The company has 7,500 shares of 5% preferred stock outstanding. All have $100 par value and are selling for $80 per share.

Floatation costs: Debt = 4%, Equity = 5%

Market Specifics

Market risk premium = 7%

Risk free rate = 4% Return on the average stock = 11%

Required:

· Assuming the same capital structure is to be maintained, what is the optimal capital structure for Canyon Drilling?

· What is the component cost of capital for the firm?

· Calculate Canyon Drilling’s after tax weighted average cost of capital, using the information above.

Deliverables:

· In an executive summary of 3 to 5 pages, submit your findings from the above-noted requirements in a Microsoft Word or Excel document to the W2: Assignment 2 Dropbox, by Tuesday, September 12, 2017. Use an MS Excel document to illustrate your calculations

Week 3 – Assignment 2: Accept or Reject the Project?

In this assignment, you will undertake calculations in order to evaluate a project, and decide if it should be accepted or rejected.

Texas Roks, Inc. is considering a new quarry machine. The costs and revenues associated with the machine have been provided to you for analysis:

Cost of the new project	$4,000,000
Installation costs	$100,000
Estimated unit sales in year 1	50,000
Estimated unit sales in year 2	75,000
Estimated unit sales in year 3	40,000
Estimated sales price in year 1	$150
Estimated sales price in year 2	$175
Estimated sales price in year 3	$160
Variable cost per unit	$120
Annual fixed cost	$50,000
Additional working capital needed	$435,000
Depreciation method	3 years straight-line method, no salvage value
Texas Rok's tax rate	40%
Texas Rok's cost of capital	13%

Required:

1. Calculate operating cash flow and the change in net working capital.

2. Determine the NPV and IRR of the project.

3. Should the company accept or reject the project based on the NPV? Why?

4. Should the company accept or reject the project based on the IRR? Why?

5. What is your final accept or reject decision? Why?

6. What is the payback period for this project? Would this influence your decision to accept or reject?

Deliverables:

· Submit your findings to the above questions in a Microsoft Word or Excel document by Tuesday, September 19, 2017. Use an Excel document to illustrate your calculations.

Week 4 - Assignment 2: External Funding Requirement and Degree of Leverage

Part One: External Funding Requirement

Your company, Martin Industries, Inc., has experienced a higher than expected demand for its new product line. The company plans to expand its operation by 25% by spending $5,000,000 for an additional building.

The firm would like to maintain its 40% debt to total asset ratio in its capital structure and its dividend payout ratio of 50% of net income. Last year, net income was $2,500,000.

Required:

1. What are retained earnings for last year?

2. How much debt will be needed for the new project?

3. How much external equity must Martin use at the beginning of this year in order to finance the new expansion?

4. If Martin decides to retain all earnings for the coming year, how much external equity will be required?

Part Two: The Degree of Leverage

Assume that two companies, Brake, Inc. and Carbo, Inc., have the following operating results:

	Brake, Inc.	Carbo, Inc.
Sales	$300,000	$300,000
Variable Costs	60,000	180,000
Fixed Costs	210,000	90,000
Operating Income	$30,000	$30,000

Required:

1. Calculate the contribution margins for the two companies.

2. Calculate the break-even point for each firm, in dollars and in units.

3. Compare the two companies. What conclusions could you make regarding the use of operating leverage employed by the two firms?

4. Assume that both companies experience an increase in sales by 15% next year. What would be the operating income for each firm net year? Explain the difference in the change in operating income between the two companies.

5. Based on the information from the above questions, what recommendations would you make to the two companies and why?

Deliverables:

· Submit your findings from Parts One and Two above in a Microsoft Excel document by Tuesday, September 26, 2017. Show all your calculations.

Week 5 - Assignment 2: Working Capital Management, EOQ, and External Funds

Part One: Working Capital Analysis

Capers, Inc. has just promoted you to Chief financial officer. Since this is a new office in the company, you are understaffed and many of the responsibilities have been assigned to you.

The first task you have been assigned concerns the cash conversion cycle. Your boss has asked that you examine the following data:

1. Inventory conversion period is 60 Days

2. Payables deferral period is 30 days

3. Receivables collection period of 40 days

The second task concerns the cost of bank loans under differing conditions. Specifically:

1. The company needs $1,500,000 for a new project.

2. The loan will cost 10% simple interest, for 4 months, with a 20% compensating balance.

Required:

1. What is the firm’s cash conversion cycle?

2. How many times per year is the firm’s inventory turnover, if sales are $4,000,000 per year?

3. If sales are all credit sales and amount to $4,000,000 per year, what is the firm’s average investment in receivables?

4. What is the nominal interest rate on the loan?

Part Two: Cash Budget

Capers, Inc. is developing its cash budget for the next year. Of Capers’ sales, 20% is for cash, another 60% is collected in the month following sale, and 20% is collected in the second month following sale. November and December sales for 2010 were $229000 and $250,000, respectively.

Capers’ purchases its raw materials two months in advance of its sales equal to 70% of its final sales price. The supplier is paid one month after it makes delivery. For example, purchases for April sales are made in February, and payment is made in March.

In addition, Capers pays $10,000 per month for rent and $20,000 each month for other expenditures. Tax prepayments for $32,000 are made each quarter beginning in March.

The company’s cash balance at December 31, 2010, was $26,000 and minimum balance of $25,000 must be maintained at all times. Assume that any short-term financing needed to maintain cash balance would be paid off in the month following the month of financing if sufficient funds are available.

Interest on short-term loans (12%) is paid monthly. Borrowing to meet estimated monthly cash needs takes place at the beginning of the month. For example, if in the month of April the firm expects to have a need for an additional $60,500, these funds would be borrowed at the beginning of April with interest of $605 (.12 x 1/12 x $60,500) owed for April and paid at the beginning of May.

Sales for Capers Inc.:

January	$229,000
February	$250,000
March	$270,000
April	$275,000
May	$280,000
June	$290,000
July	$280,000
August	$260,000

Required:

· Prepare a monthly cash budget for Capers Inc. covering the first 7 months of 2010.

Part Three: EOQ

Capers Inc. is also initiating an inventory management program using EOQ. Capers needs fastener supplies to manufacture its products. The CFO estimates that the company will need about 250,000 cases next year. The cost of storing cases is about $1.10 each. The ordering cost is $400 for a shipment.

Required:

1. What is the EOQ?

2. How many times will you order?

3. What are the shortcomings of the EOQ? What is your rationale?

Deliverables:

· By Tuesday, October 3, 2017, complete the requirements for Parts One, Two, and Three above. Submit your results in a Microsoft Excel document showing all calculations, and in good form.