Please show all steps in Excel Sheet.
Slocumb Industries Inc. Slocumb Industries Inc. is a new start up firm created by a private equity spin-off from Third Capital Ventures a private equity firm. The firm is known for its high tech based in Austin, Texas. s/ artificial intelligence capability and is At this point Terafly Inc. (consulting firm) estimates that unit sales likely will come in at 76,347, 79,682, 86,623, 88,339 and 94,717 units respectively for the next five years if the company adheres to an average sales price of $267 per unit for the first three years and S284 in years four and five. Thereafter growth in unit sales are expected to be 3.5% indefinitely. Due to the nature of the sub-sector of the high-tech industry the firm resides in, the company concentrates its efforts on a single product line. After the results of an internal company analysis by the Corporate Finance staff, it has been determined that the company can manufacture its product line at a variable cost per unit expected to be $13 growing at 3.25% per year for the first 5 years and 3.75% per year indefinitely thereafter while overall fixed costs are estimated to be $6,230,300 annually for the first year and then grow 2.75% per year indefinitely The necessary capital expenditures are projected to be $17,387,500 upfront and due to the nature of the investments that Slocumb makes, it is deemed by the IRS to be depreciated on the five-year MACRS schedule. In the terminal phase of growth, investment strategy will likely change to that of a maintenance orientation in support of AI opportunities. As such, an average annual depreciation charge following a straight-line depreciation method is antic ipated to be $587,000 re flecting a scaled down one time 3 year investment strategy working capital to support sales is estimated to be 9.65% of yearly sales for the first 5 years and then is projected to slow to a 3.25% annual growth rate thereafter The marginal corporate income tax rate is expected to average 29.35% barring any changes to the corporate tax code and the projected annual growth rate of Free Cash Flow (FCF) in the terminal phase is expected to grow approximately 3.95% indefinitely. Historically, the debt-equity ratio has consistently been 140% for which Slocumb's current debt level is $41.25 Million with an average maturity of 5 years and an interest rate on this debt averaging 8.35%. The equity beta is deemed to be 1 .675 while the risk-free rate of return is given as 2.32% and the market risk premiums 8.85%. Currently there are 2,873,000 shares of common stock outstanding. You have been hired by Slocumb Industries Ine. to determine the following: Operating Cash Flow for each of the first 5 years and the Terminal Phase 2) Free Cash Flow for each of the first 5 years and the Terminal Phase 3) What the Asset Beta (Industry Beta) is that can use in its analysis 4) What the appropriate discount rate is for valuing the firm and hence stock price 5) What the asset value of the firm is 6) What the equity value of the firm is 7) What the appropriate stock price is 8) Make recommendations to improve the stock price Must be in the form of a single professionally prepared Excel spreadsheet Focus on eye appeal that will create a flowing development throughout so clean it up and label all references. You must NOT have constant number inputs which would destroy cell referencing for future use. Be careful, you will be penalized for any and all cell referencing that is destroyed. Additionally, you are to have separate worksheet tabs for Data Entry, Operating Cash Flow, Free Cash Flow, Asset Beta, Discount Rate, Asset Value, Equity Value, Stock Price, and Recommendations
