Please read instructions carefully and answer with an excel worksheet showing all of your work!
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 2019. Due to location and proximity to the company's 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 sevenyear 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 additional 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 at the current risk-free rate of return indefinitely. Historically, the debt-equity ratio has averaged roughly 147% for which Avondale's 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: 1) Operating Cash Flow: First 5 years and Terminal Phase 2) Free Cash Flow: First 5 years and Terminal Phase 3) Asset Beta (Industry Beta) 4) Discount Rate 5) Asset Value of the firm 6) Equity Value of the firm 7) Stock Price 8) Recommendations to enhance stock price Your presentation must be in the form of a single professionally prepared Excel spreadsheetmultiple 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! 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 2019. Due to location and proximity to the company's 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 sevenyear 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 additional 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 at the current risk-free rate of return indefinitely. Historically, the debt-equity ratio has averaged roughly 147% for which Avondale's 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: 1) Operating Cash Flow: First 5 years and Terminal Phase 2) Free Cash Flow: First 5 years and Terminal Phase 3) Asset Beta (Industry Beta) 4) Discount Rate 5) Asset Value of the firm 6) Equity Value of the firm 7) Stock Price 8) Recommendations to enhance stock price Your presentation must be in the form of a single professionally prepared Excel spreadsheetmultiple 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
