FIN630 Assignment

fin630_session_6_assignment.xls

Home >Business & Finance homework help >FIN630 Assignment

Problem-1


PROBLEM 1									Solution Legend
									Value given in problem
									Formula/Calculation/Analysis required
									Qualitative analysis or Short answer required
									Goal Seek or Solver cell
									Score (filled by professor)
Solution
a. Download Financial Data
	Dec 31, 2013	Dec 31, 2012	Jun 30, 2011		Your Score	Max
Assets			If available			5
Current assets:
Cash and cash equivalents
Accounts receivable
Prefunded coupons cash deposits
Prepaid expenses and other current assets
Total current assets
Property and equipment, net
Intangible assets, net
Goodwill
Deferred tax assets
Other assets
Total Assets
Liabilities
Current liabilities:
Accounts payable
Accrued compensation and benefits
Other current liabilities
Prefunded coupons cash obligations
Deferred revenues
Debt obligations, current
Debt obligations, related party
Total current liabilities
Debt obligations, related party
Other non-current liabilities
Deferred rent
Deferred tax liabilities
Total Liabilities
Convertible preferred stock
Stockholders’ equity (deficit):
Common stock, par value
Additional paid-in capital
Treasury stock, at cost
Accumulated other comprehensive income (loss)
Accumulated deficit
Total stockholders’ equity (deficit)
Total liabilities, redeemable convertible preferred stock and stockholders’ equity (deficit)
Year ended December 31
	2013	2012	2011
Income Statement			if available
Revenue
Costs and expenses:
Cost of revenues
Sales and marketing
Research and development
General and administrative
Total costs and expenses
Loss from operations
Interest expense
Other income (expense), net
Loss before benefit from income taxes
Benefit from income taxes
Net loss
Deemed dividend to investors in relation to tender offer
Net loss attributable to common stockholders
b. Calculate historical ratios, which you will need to create proforma statements					Your Score	Max
						5
					Your Score	Max
c. Using historical data from a) and ratios from b) create pro-forma statements						5
Assets	(from a)	12/31/14	12/31/15	12/30/16	12/30/17
Current assets:
Cash and cash equivalents
Accounts receivable
Prefunded coupons cash deposits
Prepaid expenses and other current assets
Total current assets
Property and equipment, net
Intangible assets, net
Goodwill
Deferred tax assets
Other assets
Total Assets
Liabilities
Current liabilities:
Accounts payable
Accrued compensation and benefits
Other current liabilities
Prefunded coupons cash obligations
Deferred revenues
Debt obligations, current
Debt obligations, related party
Total current liabilities
Debt obligations, related party
Other non-current liabilities
Deferred rent
Deferred tax liabilities
Total Liabilities
Convertible preferred stock
Stockholders’ equity (deficit):
Common stock, par value
Additional paid-in capital
Treasury stock, at cost
Accumulated other comprehensive income (loss)
Accumulated deficit
Total stockholders’ equity (deficit)
Total liabilities, redeemable convertible preferred stock and stockholders’ equity (deficit)
	Year ended December 31
Income Statement	(from a)	2014	2015	2016	2017
Revenue
Costs and expenses:
Cost of revenues
Sales and marketing
Research and development
General and administrative
Total costs and expenses
Loss from operations
Interest expense
Other income (expense), net
Loss before benefit from income taxes
Benefit from income taxes
Net loss
Deemed dividend to investors in relation to tender offer
Net loss attributable to common stockholders
d. Using Pro-Forma Statements, estimate FCF for 2013-2016							Your Score	Maximum
	Year							5
		2014	2015	2016	2017
EBIT
EBIT(1-T) = NOPAT
Plus: Depreciation Expense
Less: CAPEX
Less: Working Capital Investment
Firm Free Cash Flow
e) What risks is the company facing? Discuss, how its tornado diagram would look like.							Your Score	Maximum
								5
Bonus: The Tornado diagram itself							Your Score	Maximum
								5

Page &P

Coupons.com (COUP) went public in 2014. This exercise asks you to forecast the company's free cash flow and discuss risks the company is facing. You may use and modify if necessary the template below or you may create your own template. a) download 2011-2013 historical financial data for the company, using one of the sources listed in Course Content (you may enter numbers in the template below or create your own). I suggest using the SEC Edgar website (http://www.sec.gov/edgar/searchedgar/companysearch.html) and download S-1 or form 424B from there; b) Based on historical financial data calculate ratios that to be used later in pro-forma financial statements (revenue growth, gross margin etc., see chapter 6 for details); c) Using historical data from a) and ratios from b) create pro-forma statements (see chapter 6 for details); d) Estimate free cash flows FCF for 2014-2017 (see chapters 2 and 6 for details); e) What are risks the company is facing? Discuss, how its tornado diagram would look like (see chapter 3 for details). You don't have to build the diagram itself, unless you want to earn bonus points (5 points maximum);

Problem-2


	PROBLEM 2									Solution Legend
										Value given in problem
										Formula/Calculation/Analysis required
										Qualitative analysis or Short answer required
										Goal Seek or Solver cell
										Score (filled by professor)
	Given				MACRS Depreciation:
	Investment cost (today)	$ (900,000)			Year 1	33.33%
	Project Life	5	years		Year 2	44.45%
	Net Working Capital	10%	of revenues		Year 3	14.81%
	Year 1 revenues	$ 375,000			Year 4	7.41%
	Operating costs	40%	of revenues
	After-market value	25%	of initial investment
	Revenue annual growth	3%
	Required rate of return	12%
	Tax rate	38%
	Solution
a.		Year
	Cash flow estimation	0	1	2	3	4	5
	Investment	$ (900,000)
	Revenues
	Operating costs
	EBITDA
	Less: Depreciation
	Incremental EBIT
	Less: Taxes
	NOPAT
	Plus: Depreciation
	Change in NWC
	Cash from Asset Sale
	FFCF
	NPV							Your Score	Maximum
	IRR								5
	Analysis
b.	b) The marketing and operations department disagree with current projections for operating costs, first year revenues and revenue growth . Considering one factor at a time, at what level of operating costs, initial revenues, and revenues growth the project will break-even (NPV=0)? (see chapter 3 for details)							Your Score	Maximum
		Base case	Break-even						7
	Operating costs (% of revenues)	40.00%
	Year 1 revenues	$ 375,000
	Revenues growth	3.00%
	Cash flow estimation	0	1	2	3	4	5
	Investment	$ (900,000)
	Revenues
	Operating costs
	EBITDA
	Less: Depreciation
	Incremental EBIT
	Less: Taxes
	NOPAT
	Plus: Depreciation
	Change in NWC
	Cash from Asset Sale
	FFCF
	NPV
	IRR
c.	Looking at percentage difference between the predicted level and critical (break-even) level of each of the three factors, which of them is the most critical?							Your Score	Maximum
									3
		Base case	Break-even	Difference (%)
	Operating costs (% of revenues)	40.00%
	Year 1 revenues	$ 375,000
	Revenues growth	3.00%
	Your answer.

Using Goal Seek

Californian start-up Ksenia-Maria considers installing on campus of a well known university in Maryland a communication system, which will compete with various SMS applications and social networks. The management anticipates that new system will have the first year revenues of $375,000 with subsequent annual growth of 3%. Operating costs are 40% of revenues. The project requires $900,000 investment in equipment, which will have a five year anticipated life and will be depreciated using MACRS depreciation method toward a zero book value (MACRS depreciation rates are given below). However, the company will be able to sell the equipment on the after-market at the end of year 5 for 25% of its original cost. The company requires a 12% rate of return from its investment and faces a 38% tax rate (overall the company is profitable). In addition to capital investment, the project requires an outlay of net working capital equal to 10% of revenues in the coming year. I.e., at time 0 (beginning of year 1) net working capital requirement is $37,500 and will grow in subsequent years. All NWC will be recovered after the project's end. a) Calculate the NPV and IRR for the project. Should the company undertake the project? (see chapter 2 for details) b) The marketing and operations department disagree with current projections for operating costs, first year revenues and revenue growth . Considering one factor at a time, at what level of operating costs, initial revenues, and revenues growth the project will break-even (NPV=0)? (see chapter 3 for details) c) Looking at percentage difference between the predicted level and critical (break-even) level of each of the three factors, which of them is the most critical? (see chapter 3 for details)

Problem-3


PROBLEM 3							Solution Legend
							Value given in problem
							Formula/Calculation/Analysis required
							Qualitative analysis or Short answer required
							Goal Seek or Solver cell
							Score (filled by professor)
	Given
		31-Jul-13
	Liabilities and Owners' Capital	Balance Sheet (Book Values)	Invested Capital (Market Values)
	Current Liabilities
	Accounts payable	$ 17,550,000
	Notes payable	10,000,000	10,000,000
	Other current liabilities`	22,266,000
	Total current liabilities	$ 49,816,000	$ 10,000,000
	Long-term debt (6.5% interest paid semiannually, due in 2017)	$ 650,000,000	$ 714,653,161
	Total liabilities	$ 699,816,000	$ 724,653,161
	Owners' Capital
	Common stock (at $1 par value per share)	$ 20,000,000
	Paid-in-capital	200,025,000
	Accumulated earnings	255,000,000
	Total owners' capital	$ 475,025,000	$ 1,400,000,000
	Total liabilities and owners' capital	$ 1,174,841,000	$ 2,124,653,161
	US Treasury Bond Yield	2.00%
	Estimated Market or Equity Risk Premium	4.50%
	Current Share Price	$ 70.00
	Market value of owners' equity
	Current yield on the firm's long-term debt	4.25%
	Current yield on the firm's short-term notes	3.00%
	Dollar value of short term notes outstanding	$ 10,000,000
	Corporate tax rate	35%
	Solution
	a) What are the company's enterprise value and capital structure weights?				Your Score	Maximum
	Enterprise value = Market capitalization + Debt					3
	Notes payable / Enterprise Value
	Long-Term Debt / Enterprise Value
	Equity / Enterprise Value
	b. What is the company's cost of equity according to CAPM?				Your Score	Maximum
	After-Tax Cost of Sources of Capital					4
	Notes Payable (after-taxes)
	Long-term Debt (after-taxes)
	Levered equity beta		1.20
	Cost of Equity (using the CAPM)
	c. What is company's WACC?				Your Score	Maximum
	Source of Capital	Capital Structure Weight (Proportion)	After-Tax Cost	Weighted After-Tax Cost		3
	Notes Payable
	Long-term Debt
	Equity
			WACC

LEO Inc. has the balance sheet as shown below. Recently the yield on bonds similar to the ones that company has had increased to 4.25%, so that the market value of the bonds is now about $715 million The rate on company' short-term notes is equal the market's rate on these notes, which is 3%. What are the company's enterprise value and capital structure weights? What is the company's cost of equity according to CAPM, if the U.S. T-bond yield is 2.00 %, the long-term market risk premium is 4.5% and the company's levered equity beta is 1.2? What is the company's WACC?