The management of Tampa Phosphate Company (TPC) is planning next year=s capital
ID: 2612546 • Letter: T
Question
The management of Tampa Phosphate Company (TPC) is planning next year=s capital budget. TPC expects net income to be $10,500 next year, and its payout ratio is 40 percent. The company=s earnings and dividends are growing at a constant rate of 5 percent; the last dividend, Do, was $0.90; and the current equilibrium stock price is $8.59. TPC can raise up to $10,000 of debt at a 12 percent before tax cost, the next $10,000 will cost 14 percent, and all debt after $20,000 will cost 16 percent. If TPC issues new common stock, a 10 percent underwriting cost will be incurred. TPC can sell the first $16,000 of new common stock at the current price, but to sell any additional new stock, TPC must lower the price to $7.63. TPC is at its optimal capital structure, which is 40 percent debt and 60 percent equity, and the firm=s marginal federal-plus-state tax is 40 percent. TPC has the following independent, indivisible and equally risky investment opportunities:
Project Cost IRR
A $15,000 17%
B $20,000 14
C $15,000 16
D $12,000 15
What is TPC’s Optimal Capital Budget? How would you find this solution using Excel?
Explanation / Answer
solution.
According to dividend growth model Cost of equity is :-
Ke = last paid dividend + frowth rate / Market price per share now.
Ke = $0.90 + 5% / $8.59 = 5.10%
Now calculation of cost of debt..
$20,000 will cost 16 percent
$20,000 x 16% = 3200 x .40 = 1,280
Cost of Debt in % = 16% x .40 = 6.4%
Calculation of Equity :-
$16,000 of new common stock at the current price
Remain @ 7.63
Its optimal capital structure, which is 40 percent debt and 60 percent equity
So if 20% Debt = 20,000
There for Equity = 20,000 / 20% = 50.000
= 50,000 - 20,000 = 30,000 Equity share.
According to payout ratio 40%
10,500 x 40% = 4,200 Distribute as a dividend.
Cost of Equity = 4,200 / 31,000 x 100 = 14%
Decesion :
Copany have Total capital is $50,000
So company can invest in these three high IRR oroject.
A $15,000 17%
B $20,000 14
C $15,000 16
2. How would you find this solution using Excel?
Instructions
Type each project's name into the second column for each row. Start with row 6 to leave room at the top for other calculations.
Title the first column "Decision" and leave its content empty. The "Decision" column will be completed by Solver.
Enter each project's NPV in the third column for each row.
Type the budget and labor requirements for the three next years of each project into the next six columns of the spreadsheet. Sum the total of each of these six columns at the top of the columns using the summation button on the toolbar.
Type the total capital and labor available for all combined projects for each year at the top of each year column.
Build a formula in cell B2 to total the NPV of all selected projects. Use the formula "=SUMPRODUCT(doit,NPV)" where the "NPV" range refers to all the cells in the NPV column for each project. The "doit" range refers to all the cells in the decision column for each project.
Copy the formula in cell B2 to cells E2 through J2 so individual year's capital and labor requirements are calculated.
Launch Solver. Click on the "Tools" menu and choose the "Solver" command.
Click in the "Set Target Cell" field and then click on cell B2 where the total NPV formula exists.
Change the "Equal To" field to "Max." This tells Solver that the goal is to maximize the total NPV in cell B2, based on all projects.
Click in the "By Changing Cells" field and then drag over all the cells in the Decision column created earlier.
Click the Add button and enter the "doit" range or its direct cell references ("A6:A25"). Choose the "bin" option from the middle drop-down menu. Solver is now configured to enter a "0" or "1" in those cells only.
Click the "Add" button and choose the cells E2 through J2. Select the "<=" indicator and choose cells E4 through J4 in the final field. This forces Solver to not choose a combination of projects that will exceed company resources.
Press the "Solve" button. Solver will analyze all the data and produce a "1" next to each project that should be implemented to increase revenue during the following three years.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.