Help input the data entry given the information

Avondale Inc.

Avondale Industries Inc. is an established mid-cap tech firm concentrating on the development and implementation of cybersecurity intelligence tools (CIT) with platform analysis geared and marketed primarily to the U.S. Department of Defense. The company also distributes CIT to other countries allied with the United States and countries pre-approved by the U.S. State Department after rigorous analysis. The company is based in Round Rock, Texas for which its origin is credited to an equity carve out from Cyber Select Industries, Inc. Subsequently, the company merged with Elevation Associates, Inc. a very successful private equity firm that eventually took the firm public in an IPO in October, 2013. Due to location and proximity to the companys central target market, and the inherent high-tech applications for CIT, the company concentrates its efforts on a single central product and ancillary services. Avondale has expressed an interest in developing other product lines as it desires a way to diversify sales and ultimately cash flows. It is surmised this will make the company more consistent and stable; reduce risk; and increase the probability the company will be more highly valued by Wall Street analysts through a greater P/E multiple. It is further theorized these efforts will assist management in pursuing a transaction (sale of company) as the board of directors asserts its opinion that the federal government will eventually favor moving its business in time to a relatively larger firm to handle growth in the perceived demand of the federal government.

Currently, Avondale has hired a consulting firm (Premiere Associations Inc.) to estimate the size of the market and hence expected unit sales they assert will likely emerge from public budget analysis of the federal government over the next five years. Their work has resulted in an estimate of 237,331, 258,342, 277,869, 216,548, and 166,532 units respectively for the next five years conditioned on Avondale adhering to an average sales price of $367 per unit for the first two years and $318 in years three, four, and five. From that point going forward, growth in unit sales are predicted to be 3.25% indefinitely.

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 $112.75 growing at 3.45% per year for the first 5 years and 4.13% per year indefinitely thereafter while overall fixed costs are estimated to be $14,223,300 annually for the first year and then grow 4.86% per year indefinitely.

The necessary capital expenditures are projected to be $37,456,200 upfront and due to the nature of the investments that Avondale makes, it is deemed by the IRS to be depreciated on the seven-year MACRS schedule. In the terminal phase of growth, it is projected that investment strategy will morph into and likely change to that of a support structure for AI/AR opportunities closely aligned with cyber security applications as service revenues become more annuitized. As such, an average annual depreciation charge following a straight-line depreciation method is anticipated to be $1,427,900 reflecting a scaled down one-time 3-year investment strategy.

Working capital to support sales is estimated to be 12.95% of yearly sales for the first 5 years and then is projected to slow to a 2.28% annual growth rate thereafter.

The marginal corporate income tax rate is expected to average 21.33% 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 the current risk-free rate of return indefinitely. Historically, the debt-equity ratio has averaged roughly 147% for which Avondales current debt level is $18,189,400 with an average maturity of 6 years and an interest rate on this debt averaging 8.175%. Upon a regression analysis, the historical equity Beta was calculated to be 1.982, while the risk-free rate of return is given as 2.876% and the market rate of return is assumed to be 11.375%. Currently there are 4,879,334 shares of common stock outstanding.

You have been hired by Avondale Industries Inc. to determine the following items used in its financial analysis of the stock price:

Operating Cash Flow: First 5 years and Terminal Phase

Free Cash Flow: First 5 years and Terminal Phase

Asset Beta (Industry Beta)

Discount Rate

Asset Value of the firm

Equity Value of the firm

Stock Price

Recommendations to enhance stock price

Your presentation must be in the form of a single professionally prepared Excel spreadsheet-multiple documents are not allowed! 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. Do not submit your computer project early as I may come back with additional requirements! Should there be additional requirements-you will need a separate worksheet tab for each and every additional requirement that is assigned! When you are done, properly title and submit your work through Moodle by the deadline which is the start of class on the date shown in the syllabus. If you have any questions, feel free to contact me and good luck with your presentation!

	Year 1	Year 2	Year 3	Year 4	Year 5
sales units	237,331	258,342	277,869	216,548	166,532
average sales	$ 87,100,477.00	$ 94,811,514.00	$ 88,362,342.00	$ 68,862,264.00	$ 52,957,176.00
v/c unit
FC
capital spending	37,456,200
7 years MARCS deprictaion
additional depriciation
deprecitaion years
working capital of year revenue
marginal income statement
fcf growth
debt to equity
current debt level
average maturity of debt
intrest on debt
equity beta
risk free rate of return
market rate of return
common stock outstanding