[answered] Module 13 Student's Model 12/04/16 ATLAS METALS COMPAN

Can someone please help me with this spreadsheet assignment? ?It's about WACC.

Module 13 Student's Model 12/04/16 ATLAS METALS COMPANY

 

Establishing the Optimal Capital Budget

 

This case illustrates basic cost of capital calculations, the

 

construction of the marginal cost of capital and investment opportunity schedules, and the development of the optimal capital budget.

 

The model develops the firm's market value capital structure, component

 

costs, MCC schedule assuming two break points and available depreciation

 

generated funds. The model could easily be expanded to include more

 

break points in the MCC schedule, but most firms do not precisely define

 

their MCC break schedules past the retained earnings break point.

 

If you are using the student version of the model, some of the cells have been blanked out.

 

All of the formulas and inputs have been blanked out but not labels.

 

Before using the model, it is necessary to fill in the empty cells with the appropriate formulas.

 

Once this is done, the model is ready for use.

 

======= =============== =========== ======= ============== ===========

 

INPUT DATA:

 

KEY OUTPUT:

 

Short-Term Debt:

 

Total $ amount

 

Marginal cost rate Market Value Capital Structure:

 

$29,010,000

 

7.00% Long-Term Debt:

 

Total $ amount

 

Par value

 

Years to maturity

 

Coupon rate

 

Current req return

 

Add. sr. debt

 

Marginal cost rate:

 

Senior debt

 

Junior debt $121,326,000

 

$1,000

 

10

 

8.00%

 

9.00%

 

$41,000,000 9.50%

 

11.80% % short-term debt

 

% long-term debt

 

% preferred stock

 

% common stock 7%

 

31%

 

9%

 

53% Break Points:

 

RE BP

 

RE+Dep BP $0

 

$0 Debt BP

 

Debt+Dept BP $0

 

$0 Component costs:

 

Short-term debt 0.00% Long-term debt:

 

First interval

 

Second interval 0.00%

 

0.00% Preferred Stock:

 

Total $ amount

 

Par value

 

Dollar dividend $36,010,000

 

$100

 

$12.69 Flotation costs

 

Common Stock:

 

Common stock

 

Retained earnings

 

Shares outstanding

 

Current price

 

New issue net price $6.00 $211,140,000

 

$29,587,000

 

8,500,000

 

$24.84

 

$29.34 Preferred stock 0.00% Common stock:

 

Retained earnings

 

New common stock 0.00%

 

0.00% WACCs:

 

First interval

 

Second interval

 

Third interval Other Data:

 

Div. payout ratio

 

Dep. expense

 

Tax rate

 

1998 Earnings available to

 

com. stockholders

 

1998 EPS (est)

 

1988 EPS

 

growth (% previous) 0.00%

 

0.00%

 

0.00% ======= =============== =========== ======= ============== ===========

 

MODEL-GENERATED DATA:

 

Market Value Capital Structure:

 

# bonds outstanding

 

Type of Financing

 

Market Value

 

------------------------- ------------------Short-term debt

 

$29,010,000

 

Long term debt

 

$121,326,000

 

------------------Total debt

 

$150,336,000

 

Preferred stock

 

Common equity

 

Total financing Retained Earnings Break Point:

 

RE BP =

 

RE BP inc. dep. $36,010,000

 

$211,140,000

 

------------------$397,486,000

 

=========== %

 

---7%

 

31%

 

---38%

 

9%

 

53%

 

---100%

 

==== Debt BP =

 

Debt BP inc, dep.

 

Dividend Data:

 

Div. growth rate

 

End-of-year div.

 

Component Costs:

 

Short-term debt

 

Long-term debt:

 

First interval

 

Second interval

 

Preferred stock

 

Retained earnings

 

New common stock

 

WACCs:

 

First Interval:

 

ST debt, senior LT debt, retained earnings & preferred stock

 

Component

 

Fraction

 

Cost

 

Short-term

 

Long-term

 

Preferred stock

 

Retained earn Product ----WACC = Ka =

 

Second Interval:

 

ST debt, Senior LT debt, external equity and preferred stock

 

Component

 

Fraction

 

Cost

 

Short-term

 

Long-term

 

Preferred stock

 

New CS Product ----WACC = Ka =

 

Third Interval:

 

ST debt, junior LT debt, external equity and preferred stock

 

Component

 

Fraction

 

Cost Product Short-term

 

Long-term

 

Preferred stock

 

New CS

 

----WACC = Ka =

 

======= =============== =========== ======= ============== ===========

 

END

 

---

Solution details:

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Sep 18, 2020

EXPERT

Tutor

ANSWER RATING

GET INSTANT HELP/h4>

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

• As a reference for in-depth understanding of the subject.
• As a source of ideas / reasoning for your own research (if properly referenced)
• For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).

This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

NEW ASSIGNMENT HELP?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN YOUR SET DEADLINE.