I was able to calcualte the figures whith a scientific calcualtor. I am in need of help in excel. How do I incorportate the formulas to show my answer.

Analyzing a Firms Capital Structure

Mr. Hillbrandt has learned a lot about the financial side of running the business during the first year with the company and is now contemplating making changes to the corporate capital structure. He needs your assistance one more time.

ABC Golf Equipment Corporation has $10 million in assets (where the market value of the assets is equal to the book value of the assets) and no debt. The companys marginal tax rate is currently 35% and has 500,000 shares outstanding. The companys earnings before interest and taxes (EBIT) are $3.88 million. The firms stock price is $27 per share and the cost of equity is 11%.

The company is thinking of issuing bonds and simultaneously repurchasing a portion of its stock. If the company changes its capital structure from no debt to 25% debt based on market values, the firms cost of equity will increase to 13% because of the increased risk. The bonds can be sold at a cost of 9%. The firms earnings are not expected to grow over time. All of its earnings will be paid out as dividends.

Probability	EBIT ($)
0.05	- 1 million
0.25	2.3 million
0.40	4 million
0.25	5.8 million
0.05	6.1 million

Required:

Computations (use Excel).

Make the computations necessary to answer the questions below. Dont forget that Mr. Hillbrandt does appreciate your step-by-step computations to guide him through the analysis.

What impact will this utilization of this debt have on the value of the company?

Whats going to be the companys EPS after the recapitalization?

Whats going to be the companys new stock price?

The $3.88 million EBIT discussed above is determined from this probability distribution.

Whats the times interest earned ratio at each probability level?

Memo (use Word).

Interpret the analysis already prepared and use the Excel computations as a basis for a memo to the CEO. Write a four or five paragraph memo. Make sure each question listed above is addressed. Start with an introduction and end with a recommendation. Each of the four or five paragraphs should have a heading.