The question is as follows: Please write the formulas in Excel, take a screenshot of it, or place the formula of each step here. I do not need a new solution to my problem. I have already found a solution, but I need help with the formatting of the Excel spreadsheet.
Siebel Incorporated, a non-publicly traded company, has 2002 earnings before interest and taxes (EBIT) of $33.3 million, which is expected to grow at 5% annually into the foreseeable future. The firm's combined federal, state, and local tax rate is 40%; capital spending will equal the firm's rate of depreciation; and the annual change in working capital is expected to be minimal. The firm's beta is estimated to be 2.0, the 10-year Treasury bond is 5%, and the historical risk premium of stocks over the risk-free rate is 5.5%.
Rand Technology, a direct competitor of Siebel's, recently was sold at a purchase price of 11 times 2002 EBIT, which included a 20% premium. Siebel's equity owners would like to determine what it might be worth if they were to attempt to sell the firm in the near future. They have chosen to value the firm using the discounted cash flow and comparable recent transactions methods. They believe that either method would provide an equally valid estimate of the firm's value.
a. What is the value of Siebel using the DCF method?
b. What is the value using the comparable recent transactions method?
c. What would be the value of the firm if we combine the results of both methods?