attachedHELP TAB_Asumptions STEPS - START 1 - In the "ROPI Model" tab - update the name of...

Question

attachedHELP TAB_Asumptions 		STEPS - START 		 1 - 	In the "ROPI Model" tab - update the name of company in row 2 and the Year/Date of Reported period in cell H12. 		 2 - 	Download most recent 10-k Annual report in both PDF and the Excel version for SEC.gov 		 3 - 	Look for Income Statement and Balance Sheet 		 4 - 	Also look for the Note on Borrowings and/or Long-Term Debt of the company 		 5 - 	Also look for Effective Tax Rate for most recent year 		ASSUMPTIONS 		 1 - 	Sales / Revenue Growth Rate for (1) Horizon Period (4 years) and for (2) Terminal Period forever after year 5 -  				 - Analyze the Sales history trend of the firm for past 3 to 5 years.  Determine an appropriate sales growth rate.   				 - Also review Management Discussion in 10-k and any Analysts news and estimate of future sales of company 		 2 - 	Calculate NOPAT from Income Statement for most recent year. Show work in "NOPAT calculation" tab. 		 3 - 	Calculate NOA from Balance Sheet for most recent year. Show work in "NOA" tab. 		 4 - 	Calculate the WACC for firm in the "WACC" tab. Estimate Cost of Debt (Kd) and Cost of Equity (Ke or Re). Also, the Target Capital Structure.   				NOTE: Use the "Cost of Debt weighted average" tab for Kd. 		 5 - 	Calculate the NNO (Long-Term) from Balance Sheet 		 6 -	Determine current shares outstanding from Annual Report 10-k 		STEPS - FINISH 		 1 - 	Update the ROPI Model tab and complete the model with all the assumptions from steps 1 to 4 above. NOPAT, NOA, WACC, Sales Growth, etc. 			Update the "Yellow" highlighted cells.   		 2 - 	Review the Intrinsic Value and compare to current stock price 		 3 - 	Make Recommendation if company is worth investing now (Buy recommendation, Hold or Sell recommendation)? 		 4 - 	Write brief Recommendation in row 49 in ROPI Model tab. 		TABs in your Model 		 1 - 	ROPI Model 		 2 - 	WACC 		 3 - 	Cost of Debt weighted average 		 4 - 	NOA 		 5 - 	NOPAT calculation 		Legend: 		Input Cell &F &A		 ROPI Model 	COMPANY NAME:   _____________________ 	Residual Operating Income Model (ROPI) Model 	Assumptions of this ROPI Model summarized here: 	The forecasts are for sales, NOPAT and NOA. These forecasts assume an annual XX.XX% sales growth during the horizon period, a terminal period sales growth of XX.XX%, net operating profit margin (NOPM) of XX.XX%, and a year end net operating asset turnover (NOAT) of X.X. 	Application of the Discounted Cash Flow Model 	In Millions, except per share values and discount factors		Actual		Estimated - Horizon Period					Perpetuity 			Assumptions 			0		1	2	3	4		Terminal Period 	Sales Growth Rates%		N/A		0.0%	0.0%	0.0%	0.0%		0.0%			Reported Fiscal Year-End 	Sales Dollar Amount (unrounded)		0		0	0	0	0		0			Number of Shares Outstanding (in millions) 													Long-Term Debt in Final Reported Fiscal-Year (in millions) 	NOPAT		0		ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!		ERROR:#DIV/0!			Sales in Final Reported Fiscal-Year (in millions) 	NOA		0		ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!		ERROR:#DIV/0!			Sales Growth (1-short-term) 													Sales Growth (2-long-term-perpetual) 	ROPI (NOPAT - [NOAbeg X rw])				ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!		ERROR:#DIV/0!			Net Operating Profit After Taxes (NOPAT) (in millions) 													Net Operating Profit Margin (NOPM) ratio (calculates)		ERROR:#DIV/0! 	Discount Factor ([1/(1+rw)t]				1.000000	1.000000	1.000000	1.000000					NOTE about NOPAT in Horizon: = 		ERROR:#DIV/0! 													Net Operating Assets (NOA) (in millions) 	Present value of Horizon ROPI				ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!					Net Operating Assets Turnover (NOAT) ratio (calculates)		ERROR:#DIV/0! 													NOTE about NOAT in Horizon: = 		ERROR:#DIV/0! 	Cumulative Present value of Horizon FCFF		ERROR:#DIV/0!										WACC 													Target - Debt Capitalization 	Present value of terminal ROPI (ROPIT / (rw - g) / (1+rw)t]		ERROR:#DIV/0!										Target - Equity Capitalization 													Total Capitalization (should be =100%)		0.0% 	NOA		0										Risk Free Rate 													Beta 	Total Firm Value = NOA + Present Value of Expected ROPI		ERROR:#DIV/0!										Market Risk Premium 													Cost of Equity 		0.0% 	Less (plus) NNO or aka Long-Term Debt		- 0										Cost-of-Debt (5) 													Tax Rate 	Firm Equity Value		ERROR:#DIV/0!										After-Tax Cost of Debt		0.0% 													WACC (rw) = 		0.00% 	Shares outstanding (in millions)		- 0 	Intrinsic Stock value per share (FORMULA- Calculated)		ERROR:#DIV/0! 	Current Stock Price (INPUT here and compare) 	Recommendation (Buy/Sell/Hold): 	Recommendation: Therefore, given the stock price on the date of this valuation of $., do you think this is a good purchase or a bad purchase?  	Would you recommend this stock's purchase based on your valuation? 	Type Recommendation Answer Here:   Cost of Debt 	Long-Term Debt - see Financial Statement - 10K and find the Note about Debt and Borrowings are all disclosed (ties to Long-Term in Balance Sheet) 	Change this tab…. Make it your own.   						Less 					Long Term	 - Short Term	Total		Weight		Effective		Weight 		Unsecured debt securities			Value	Value	Value		Value		Interest Rate		 x Int. Rate 			0.95% - 4.88% due through 2048				$0		ERROR:#DIV/0!				ERROR:#DIV/0! 			5.80% - 6.88% due through 2043				$0		ERROR:#DIV/0!				ERROR:#DIV/0! 			7.25% - 8.75% due through 2043				$0		ERROR:#DIV/0!				ERROR:#DIV/0! 			Commercial paper				$0		ERROR:#DIV/0!				ERROR:#DIV/0! 		Non-recourse debt and notes 			6.98% - 7.38% notes due through 2021				$0		ERROR:#DIV/0!				ERROR:#DIV/0! 		Total with Effective Interest Rates					$0		ERROR:#DIV/0!				ERROR:#DIV/0!

HELP TAB_Asumptions STEPS - START 1 - In the "ROPI Model" tab - update the name of company in row 2 and the Year/Date of Reported period in cell H12. 2 - Download most recent 10-k Annual report in...

Get Answer To This Question

Related Questions & Answers

Submit New Assignment