please see attached pdf and excel sheet for directions and sheet to put answers. please make sure to also include answers on word doc per directions

FINC 4352 - Intermediate Finance Project 3 The goal of this project is to explore the topic of the Free Cash Flow (FCF) valuation model. The project requires you to work in Excel with the provided spreadsheet. Be sure to fill in the yellow boxes in the Excel file for full credit. In addition, type up a report in Word with an introduction (description of the mini project), findings (answer assignment questions, plots, etc.), and conclusion (summary). Your grade will depend on both quantity and quality. Upon completion, please submit both your Word report and Excel file to blackboard. Question A is worth 20 points. Questions B, C, D, E, and F are worth 8 points each. The report is worth 10 points and the excel file is worth 5 points. If you employ external references, please cite them in a bibliography section. Free Cash Flow (FCF) Valuation Model Mara-Jade Corporation has a 15% weighted average cost of capital (WACC). Its most recent sales were $980 million and its total net operating capital is $870 million. The following table shows estimates of the forecasted sales growth rate, operating profitability ratios, and capital requirement ratios for the next three years. All of these ratios are expected to remain constant after the third year. Year 1 15% 12% 60% Annual sales growth rate Operating profitability (NOPAT/Sales) Capital requirement (OpCap/Sales) Year 2 12% 10% 60% Year 3 5% 6.5% 60% Mara-Jade Corporation has $250 million in marketable Securities, $100 million in notes payables, $300 million in long-term bonds, and $50 million in preferred stock. The company also has 40 million shares of stock outstanding. A) Use the data to forecast sales, net operating profit after taxes (NOPAT), total net operating capital (OpCap), free cash flow (FCF), and growth rate in FCF, for the next three years. (10 points) B) Calculate the horizon value. (10 Points) C) Calculate the current value of operations. (10 points) D) Calculate the total intrinsic value. (10 Points) E) Calculate the intrinsic value of equity. (10 points) F) Calculate the intrinsic stock price. (10 Points) NOTE: Make sure you reference cells (numbers in your Excel file). Do not simply type in the final answers. Points will be deducted if work (i.e., referencing cells) is not shown. Use the excel functions where applicable (e.g., PV Excel function in part C) Formulae: = Sales t Sales t-1 (1 + g ) Net operating profit after taxes t = = Total net operating capitalt Operating profitability t )( Sales t ) (= Capital requirementt )( Sales t ) (= Investment in OpCap = OpCapt OpCapt-1 t Growth in FCF = FCFt 1 FCFt-1 NOPATt ( Sales t ) Sales t Operating capitalt ( Sales t ) Sales t FINC 4352 - Project 3 - Free Cash Flow (FCF) Valuation Model Last Name First Name Year 1 15% 12% 60% Annual sales growth rate (g) Operating profitability (NOPAT/Sales) Capital requirement (OpCap/Sales) Year 2 12% 10% 60% Year 3 5% 6.5% 60% A) Current 0 $980 Sales Net operating profit after taxes (NOPAT) Total net operating capital (OpCap) Investment in OpCap FCF = NOPAT - Investment in OpCap Growth in FCF 1 Forecast Year 2 $970 B) Free cash flow at beginning of the constant growth phase (FCF 3) = Weighted average cost of capital (WACC) = Constant growth rate (gL) = Horizon value (HV3) = C) FCF1 Present Value Value of Operations D) Value of Operations Short-term Investments FCF2 FCF3 Total Intrinsic Value E) Total Intrinsic Value All Debt Preferred Stock Intrinsic Value of Equity F) Intrinsic Value of Equity Number of Shares Intrinsic Stock Price ecast Year 3 Horizon Value