Question

In: Accounting

Please solve this case in Excel,Many thanks! The Problem: You are the technology controller for a...

Please solve this case in Excel,Many thanks!

The Problem:

You are the technology controller for a mid-sized publicly traded company. You have been assigned to evaluate an investment in a new process that would cost $2.5 million. You have determined that the internal rate of return of the positive operating cash flows associated with the investment is 13.0%.

On a market value basis, the firm's capital structure is as follows:

Debt: 40%; Preferred Stock: 5%; Common equity: 55%;

This capital structure is in line with the peer group industry average, and management has no appetite for altering the existing ratios, which they consider to be optimal.

Your firm’s current stock prices are $95 per share for common equity and $60 per share for 9% $100 par value cumulative preferred stock. The 2018 common dividend was $10 per share, and the company has increased this dividend by 5% per year for the past ten years. The company also has $1,000 par value 10% coupon bonds outstanding, with a remaining maturity of ten years. The bonds are currently trading at 87.5% of par value. The capital markets are very receptive to the company's bonds and preferred common equity, all of which are actively traded both regionally and nationally.

In addition to this, you determine that the 20-year average yield on 10-year US Treasury obligations is 5% and that the market risk premium is 7%. Your firm's common equity has been given a beta of 1.65. The firm's effective rate of income tax is 25%.

In organizing financing for the proposed project, you have approached your firm's bank. The bank has agreed to a limited amount of long-term secured financing to a maximum of $ 600,000, at 6%. The bank would be willing to supply an additional $ 400,000 of long-term funds on an unsecured basis, at a rate of 15%, as an alternative to a bond issue. Based upon the company's conservative retention policy and healthy cash reserves, you are confident that it could fund up to $1.5 million of the proposed investment with internally generated retained earnings.

Based upon the information provided above, come up with a funding plan for the proposed investment and make a recommendation as to its suitability from a financial standpoint.

Solutions

Expert Solution

Hello

In this Question their is total fund requirement of $ 2.5 Million

Out of which $ 1.5 Million is arranged from internal sources or surplus cash wit company

and for the rest $ 1 Million company has two alternatives which are as below

1- Arrange fund from market in proportion to this Debt: 40%; Preferred Stock: 5%; Common equity: 55%

OR

2- From Banker 600,000 and 400,000

let analyse both of them

1)

Ke(cost of equity) = 5+1.65(7)= 16.55%

Kp(cost of Preferred Stock) = 9%

Effective cost of Bond = 10%(1-.25)= 7.5%

but here bonds are traded at value 87.5% of par so their will be 40% of bond of total $10,00,000

so value of bond comes to $ 400000

which will be from issue of 400000/87.5%= 457143 value bonds

WACC= 16.55*.55+9*.05+7.5*4.57= 12.98%

2)

Effective Cost from other option will be

Secured = 6(1-.25)=4.5%

Unsecured =15(1-.25)= 11.25%

WACC= 4.5*.6+11.25*.4=7.2%

It is viable to company for arrangement of funds from banker's option because it saves a lot in terms of cost and generate good return


Related Solutions

Can you please not solve this on excel. can you show clearly steps you took. Thanks...
Can you please not solve this on excel. can you show clearly steps you took. Thanks An office uses 1,000 photocopies per working day and there are 200 working days per year. Brand A copier costs $3,000 and will produce a total of one million copies before it wears out. Brand B’s copier costs $5,000 and will produce 2 million copies over its life. Maintenance and materials cost 3 cents pr copy with either machine, and neither machine will have...
*** PLEASE SHOW HOW TO SOLVE IN EXCEL*** Case Problem 2:        Finding the Best Car Value...
*** PLEASE SHOW HOW TO SOLVE IN EXCEL*** Case Problem 2:        Finding the Best Car Value (Copy the worksheet named “FamilySedans” in QMB3200-Homework#10Data.xlsx into your file for this problem) When trying to decide what car to buy, real value is not necessarily determined by how much you spend on the initial purchase. Instead, cars that are reliable and don’t cost much to own often represent the best values. But, no matter how reliable or inexpensive a car may cost to...
***PLEASE SHOW HOW TO SOLVE IN EXCEL*** Case Problem 3:        Consumer Research, Inc. (Copy the worksheet...
***PLEASE SHOW HOW TO SOLVE IN EXCEL*** Case Problem 3:        Consumer Research, Inc. (Copy the worksheet named “Consumer” in QMB3200-Homework#10Data.xlsx into your file for this problem) Consumer Research, Inc., is an independent agency that conducts research on consumer attitudes and behaviors for a variety of firms. In one study, a client asked for an investigation of consumer characteristics that can be used to predict the amount charged by credit card users. Data were collected on annual income, household size, and...
Please solve it in Excel if possible with explanations. Thanks. P10-11 Camco Manufacturers Inc., a publicly...
Please solve it in Excel if possible with explanations. Thanks. P10-11 Camco Manufacturers Inc., a publicly listed company, has two machines that are accounted for under the revaluation model. Technology in Camco's industry is fast-changing, causing the fair value of each machine to change significantly approximately every two years. The following information is available: Machine #1  Machine #2   Acquisition date Jan. 2, 2014  June 30, 2013 Original cost $440,000  $540,000 Original estimate of useful life 8 years  12 years Original...
***Excel is required to solve this problem. Please use excel and show all formulas used in...
***Excel is required to solve this problem. Please use excel and show all formulas used in each cell I would really appreciate the work*** Three-Stage FCFE Model: Biomet Inc., designs, manufactures and markets reconstructive and trauma devices, and reported earnings per share of $0.56 in 1993, on which it paid no dividends. (It had revenues per share in 1993 of $2.91). It had capital expenditures of $0.13 per share in 1993 and depreciation in the same year of $0.08 per...
(PLEASE READ ) :) Use the data below to solve the following problem using excel: (...
(PLEASE READ ) :) Use the data below to solve the following problem using excel: ( I would like to know how do you input the formula for each category, so please explain the process) I will RATE and comment your answer accordingly 1 a) Import the data into an Excel file. Done! b) Create a new column in the spreadsheet to assign the category of each car according to the engine horsepower. For this exercise use IF statements in...
Can someone please advise how to solve this problem step-by-step on a calculator? Thanks! Option to...
Can someone please advise how to solve this problem step-by-step on a calculator? Thanks! Option to wait. Your company is deciding whether to invest in a new machine. The new machine will increase cash flow by $435,000 per year. You believe the new technology used in the machine has a 10 year life. Obsolete 10 years from today. Machine is currently priced at 2.8 Million. Cost of machine will decline $215,000 per year until it reaches $2.155 million, where it...
How can I solve this in Excel? Please show how to solve it in excel step...
How can I solve this in Excel? Please show how to solve it in excel step by step. 1) Speedy Wheels is a wholesale distributor of bicycles for the western United States. Its Inventory Manager, Ricky Sapolo, is currently reviewing the inventory policy for one popular model — a small, one-speed girl's bicycle that is selling at the rate of 250 per month. The administrative cost for placing an order for this model from the manufacturer is $200 and the...
Please solve the problem below and include the excel formulas to the answers: Date MSFT SFY...
Please solve the problem below and include the excel formulas to the answers: Date MSFT SFY GM Portfolio Russell 3000 7/2/2012 -0.0364 -0.1328 -0.0005 -0.0566 0.0087 8/1/2012 0.0529 0.0062 0.0832 0.0474 0.0225 9/4/2012 -0.0275 0.0392 0.0655 0.0257 0.0246 10/1/2012 -0.0412 0.0249 0.1212 0.0350 -0.0185 11/1/2012 -0.0595 0.0489 0.0147 0.0014 0.0051 12/3/2012 0.0128 0.0682 0.1142 0.0651 0.0102 1/2/2013 0.0275 0.0741 -0.0257 0.0253 0.0537 2/1/2013 0.0210 0.2394 -0.0334 0.0757 0.0110 3/1/2013 0.0380 0.1122 0.0246 0.0583 0.0376 4/1/2013 0.1571 -0.1383 0.1084 0.0424 0.0153...
Please give a detailed explanation how to solve this with excel 1)This problem demonstrates a possible...
Please give a detailed explanation how to solve this with excel 1)This problem demonstrates a possible (though rare) situation that can occur with group comparisons. The groups are sections and the dependent variable is an exams score. Section 1 Section 2 Section 3 84.9 58.5 74.1 70.6 57.6 81.5 76.4 65.8 74.9 62.5 84.1 79 81.2 80.5 72.3 67.5 44.4 65.8 75.5 68.6 71 68.6 41.8 67.8 79.2 64.8 75.5 Run a one-way ANOVA (fixed effect) with α=0.05α=0.05. Round the...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT