The first part of your project should provide you with some useful information about basic characteristics of the industry. The goal of the second part is to use this information and apply it in the context of capital budgeting you learned in class.
You have identified a good business opportunity and want to find out whether it is worth investing in. To make this decision, you need to make some assumptions, estimate future cash flows, and evaluate the viability of your investment. To this end, we provide you with a basic capital budgeting tool in an Excel spreadsheet that requires specific inputs and a few basic calculations on your part.
Assume that the business opportunity lasts for five years. You need to make assumptions (or predictions) for some key inputs listed below. You can use financial information from the company or its industry average you chose in Part 1, if your hypothetical business idea is in the same industry. Alternatively, make up your own assumptions. Make sure you indicate in your response what assumptions you make and why (i.e., you should try to base your analysis on well-grounded facts and arguments). You can look up financial data for all public firms from Yahoo Finance, in case you need guidance on your assumptions (e.g., sales growth or the ratio of net working capital to sales).
1. What are the initial sales (year 1)?
2. What is the annual sales growth (years 2 to 5)?
3. What are the operating expenses (as a % of sales) and what are the fixed costs?
4. What is the upfront investment in net fixed assets (i.e., the initial capital spending) you need to make today (year 0)? Depreciate the value of these assets on a straight-line basis to zero over the five-year horizon.
5. What is the salvage value of these assets at the end? (has to be a positive number)
6. What is your upfront investment in net working capital (year 0)?
7. Starting in year 1, net working capital (the liquidity you need) will be based on how much are your sales. How much (as a % of sales) do you need to retain in NWC every year? (for example, you can assume that you need NWC equal to 10% of sales each year after year 0 and then figure out the change in NWC each year). The last year you assume that you recover all the dollar amount invested during this period in NWC.
8. Assume that the corporate income tax rate is 21%.
Cost of Capital (WACC)
To find your weighted average cost of capital (WACC) you’ll need to find certain key inputs for your company.
I. How much of your total assets will be financed with debt and how much by equity (i.e., your own capital)? Use this information to determine the equity weight (i.e., E/A) and debt weight (i.e., D/A). In other words, you should choose your “target” debt-equity mix. If you are not sure what this mix should be, find the (Total) Debt/Equity ratio or the “debt ratio” (Total) Debt/ Assets for the industry (both numbers should be based on market values). Otherwise let us know what you chose and why.
II. What is the cost of debt for your company? Assume that the cost of debt for your company is: The 3-month Treasury Bill rate + 7%. In the current economic environment, the T-Bill rate is unusually low, so we want you to use 3%. (NOTE: In future classroom or business situations, when things settle down in the economy, you can find the 3-month T-Bill rate from the Saint Louis Fed’s FRED https://fred.stlouisfed.org/.) The 7% premium is to account for your company’s default risk. So, your company’s cost of debt is 10%.
This is a team project. My work is Key information1-8 and Cost of Capital 1-2. You need answer it in excel sheet. I will attach part1 excel sheet(You need use the data information from there), and answer the work in part2 excel sheet.