Question 1 The project involves a comprehensive financial analysis and valuation of a large publicly traded firm. 50 Paints As in the example sheet below, your submitted Excel file should have forecasted statements for the next five years, including an income statement (also called the statement of operation), a balance sheet, and a statement of cash flows. Besides, it should include profitability analysis, credit risk analysis, debt maturity table, market return, beta, effective tax rate, long-term debt, cost of capital, and FCFF Detailed instructions on each section will be provided in lectures, Attempts 3 attempts left Originality Report SafeAssign enabled Grading Maximum points Date Project Instruction 2/13 2/29 Data collection, profitability analysis, and credit risk analysis Forecasting financial Statements 3/21 Valuation 1) Data collection. Select a company from the following list (ticker symbols are in parenthesis). Amazon (AMZN), Adobe (ADBE), Best Buy (BBY) Lululemon Athletica (LULU), Microsoft (MSFT), Micron Technology (MU), Netflix (NFLX) Nike (NKE), Nvidia (NVDA), Pfizer (PFE), Starbucks (SBUX), Target (TGT), Ulta (ULTA), Walmart (WMT). Collect your selected company's recent 5-year financial statements (10-K from the EDGAR website. Copy the financial statements into one Excel file and sort them in time ascending order. Here is an example sheet for Apple. Forecast Financial Statement Example.xlsx ... Note: The financial statement items may vary across companies. For example, your selected company may have operating lease liabilities that Apple does not have, or your selected company does not have R&D exp that Apple has. Thus, you do NOT need to adjust your financial statement items to be consistent with my example. Just copy and sort your financial statements in time ascending order. 2) Profitability and credit risk analysis. This step is to understand the company's profitability and credit risk via trend analysis. Calculate financial ratios following the example sheet using historical financial statements and forecasted financial statements. Note that the financial ratios based on forecasted information should be done ofter step 3 (pro forma statements). 3) Pro forma statements. This step is to construct a set of pro forma financial statements for the next 5 years. Several lectures have discussed topics related to forecasting financial statements. Sales forecast is the first step, Use relevant information (e.g. company guidance, news, etc.) to form the assumption of sales growth rate for the next 5 years. If you use any information beyond the firm's financial statements to form sales growth assumption, remember to list a reference in the pro forma income statement. If you did not use additional information, calculate the average historical sales growth rate as the projected sales growth rate for the next 5 years. For any other assumptions you make, please list them below your pro forma statements (see the "Assumptions" in my example sheet). Forecast other items following the approaches and techniques outlined in class and refer to the example sheet. I will look at the formulas you used to forecast accounting Rems and check whether they are used correctly, whether the accounting equation holds (forecasted asset forecasted liabilities + forecasted equities. Remember that you could choose cash as a plug). whether the ending balances of cash on the statement of cash flows are consistent with the cash balances on the balance sheet. (f your forecasted cash balance is negative, you can assume the company will borrow money or issue stocks to raise funds. That means you will increase the cash to the normal level and increase debt or contributed equity by the same amount (see module 7. page 30 for more details). 4) Valuation. This step is to estimate the share price of your firm's stock using the Discounted Cash Flow Model (DCF). The basic input of DCF is free cash flows (FCFF), so you should project the firm's free cash flows (FCFF) for each of the next 5 years. After year S, assume that free cash flows grow at a constant rate g forever. You have to estimate g as well in the example sheet, I use the forecasted sales growth rate as the terminal growth rate of FCFF). Then, you can estimate the terminal value of your firm. Finally, estimate WACC and use it as the discount rate in DCF Note that you need to calculate your own estimate of the cost of equity (Re), cost of debt (Rd), and WACC for your selected company following the lectures. For the "market return", you could use the one I provided in the example sheet. Once you have the estimated share price of the selected firm compare your estimated price with the actual price (as of the most recent 10-K filing date). Add a note about whether your company's stock is overvalued or undervalued on that filing date according to its intrinsic value. 5) Check your work and submit the project. Please name your Excel file as "FINA310 your first name last name" and submit it below. Make sure there are no wref errors in the formulas you used. Use the editor to format your answer