please answer question 5 in excel format

Silicon Valley M Technologies (SIVMED) was founded in San Jose, California, in 1992 by Kelly O'Brien, David R. its, and Barbara Smalley. O'Brien and Roberts, both MDs, were on the research faculty at the UCLA Medical School at the time; O'Brien specialized in biochemistry and molecular biology, and Roberts specialized in immunology and medial mierobiology. Smalley, who has a PhD, served as the department chair of the Microbiology Department at UC-Berkeley. The company started as a research and development firm, which performed its own basic research, obtained patents on promising technologies, and then either sold or licensed the technologies to other firms which marketed the products. In recent years, however, the firm has also contracted to perform rescarch and testing for larger genetic engineering and biotechnology firms, and for the U.S. government. Since its inception, the company has enjoyed enormous success - even its founders were surprised at the scientific breakthroughs made and the demand for its services. One event that contributed significantly to the firms' rapid growth was the AIDS epidemic. Both the U.S. government and private foundations have spent billions of dollars in AIDS research, and SIVMED had the right combination of skills to gamer significant grant funds, as well as perform as a subcontractor to other firms receiving AIDS research prants. The founders were relatively wealthy individuals when they started the company, and they had enough confidence in the business to commit most of their own funds to the new venture. Still, the capital requirements brought on by extremely rapid growth soon exhausted their personal funds, so they were forced to raise capital from outside sources. First, in 2001, the firm borrowed heavily, and then in 2003 when it used up its conventional debt capacity, it issued \$15 million of preferred stock. Finally, in 2006, the firm had an initial public offering (IPO) which raised $50 million of common equity. Currently, the stock trades in the over-the-counter market, and it has been selling at about \$30 per share. SIVMED is widely recognized as the leader in an emerging growth industry, and it won an award in 2008 for being one of the 100 best-managed small companies in the United States. The company is organized into two divisions: (1) the Clinical Research Division and (2) the Genetic Enginecring Division. Although the two divisions are housed in the same buildings, the equipment they use and their personnel are quite different. Indeed, there are few synergies between the two divisions. The most important synergies lay in the general overhead and marketing areas. Personnel, payroll, and similar functions ate all done at the corporate level, while technical operations at the divisions are completely separate. The Clinical Research Division conducts most of the firm's AIDS research. Since most of the grants and coatracts associated with AIDS research are long-term in nature, and since biltions of new dollar will tikely be spent in this area, the business risk of this division is low. Conversely, the Genetic Engineering Division works mostly on in-house rescurch and short-term contricts where the funting. duration, and payoffs ure very uncertain. A line of researeh may look good initially, but it is not unusual to hit some inag, which precludes further exploration. Because of the uncertainties inherent in genetic research, the Genetie Engincering Division is judged to have high business risk. The founders are still active in the businesh, but they no longer work 70-hour weeks. Increasingly. they are enjoying the fruits of their past labors, and they have let professional managens take over day-today operations. They are all on the board of directors, though, and David Roberts is chaiman. Although the firm's growth has been phenomenal, it has been more random than planned. The founders would simply decide on new avenues of research, and then count on the skills of the research teams - and good luck - to produce commercial successes. Formal decision structures were almost nonexistent, but the company's head start and its bright, energetic founders easily overcame any deficiencies in its managerial decision process. Recently, however, competition has become stiffer, and such large biotochnology firms as Genentech, Amgen, and even Bristol-Myers Squibb bave begun to recognize the opportunities in SIVMED's research lines. Because of this increasing competition, SIVMED's founders and board of directors have concluded that the firm must apply state-of-the-art techniques in its managerial processes as well as in its technological processes. As a first step, the board directed the financial vice president, Gary Hayes, to develop an estimate for the firm's cost of capital and to use this number in capital budgeting decision. Hayes, in turn, directed SIVMED's treasurer, Julie Owens, to have a cost of capital estimate on his desk in one week. Owens has an accounting background, and her primary task since taking over as treasurer has been to deal with the banks. Thus, she is somewhat apprehensive about this new assignment, especially since one of the board members is a well-known Northwestem University finance professor. Table 1 Silicon Valley Medical Techaologies, Ine. Balance Sheet For the Year Ended Deeember 31, 2009 (In Millions of Dollars) To begin, Owens reviewed SIVMED's 2009 balance sheet, which is shown in Table 1. Next, she ussembled the following data: (1) SIVMED's long-term debt consists of 9.5% coupons, semianatal puyment bonds with fifleen years remaining to maturity. The bonds last traded at a price of $1129.50 per $1,000 par value bond. The bonds are not callable, and they are nated BBB. (2) The founders have an aversion to ahort-term debt, so the firm uses such debt only to fund eyclical working capital needs. (3) SIVMED's fedenal-plus-state tax rate is 40%. (4) The compaay' a preferred stock pays a dividend of $1.80 per quarter, it has a par value of $100; if is noncallable and perpetual and it in rraded in the over-the-coualer market at a current price of $104.00 per share. A flotation coet of $2.00 per share would be required on a new issue of preferted. (Although not planned at this time the company is interested in how the analysis woeld chapge if the preferred stock had a mandatory redemption provinion, which upecified that the firm must redeem the isiue in 5 years at a price of $100 per thare) (5) The finn's last dividend (D0) was 51.09 , and dividends ace expected to jrow at about an ash rate in: the foresceable future. Some analyats expect the company's recent grow th rate to continue, othern expect it to go to xeto as acw compecition caters the inarket, but the majority anticipate that a frowth rite of about 8% will conetinue inderinitely. must redeem the issue in 5 years at a price of $100 per share.) (5) The flrm's last dividend (D0) was $1.09, and dividends are expected to grow at about an 8% rate in the foresecable future. Some analysts expect the company's recent growth rate to continue, others expect it to go to zero as new competition enters the market, but the majority anticipate that a growth rate of about 8% will continue indefinitely. (6) An important minority of analysts have noted that over the last few years, the company has had i 14% average return on equity (ROE) and has paid out about 40% of its net income as dividends. They believe the firm's expected future growth rate, g, should be based on this information and used to estimate the cost of equity ( ). (7) The firm's per share dividend payment over the past five years has been as follows: (8) STMIAND's common stock now sells at a price of about $30 per sharo. The company has 5 million common shares outstanding. (9) The curnest yield on long-term T-bonds is 4.5%, and a promineot inventment banting firm hins recently entimated than the market risk premium is nix percentage points over Treasury bonds. The firm's historical beth, as measured by several analysts who follow the stock, is 1.2 . 10) The reguited rate of teturn on an average (A-rated) company'in long term debt is 7%. AII SIVMPD iv forecauting retained eaminge of $1,800,000 and deprectution of $4,500,000 for the comineycar. from nezative siemating effinet - of 30%. Answer the following questions regarding the cost of retained earnings ( ). 1. Use the discounted cash flow (DCF, or Dividend Growth Approach) method to obtain an citimate of SIVMED's cost of retained earnings. b. Suppose SIVMED, over the last fow years, has had a 14% average return on equity (ROB) and hal paid out about 40% of its net income as dividends. Estimate the firm's expected future growth rate, g, based on the retention growth model