The “Question 1” tab of the spreadsheet contains the most recent (“Year 0”) income statement for...

Question

The “Question 1” tab of the spreadsheet contains the most recent (“Year 0”) income statement for Flitwick Corporation. You’ll need to forecast the next 5 years of the income statement (including the Free Cash Flow) using the following assumptions:• Sales will grow by 13% each year.• COGS and SG&A will be forecast using the percent of sales technique.• Depreciation will grow by 9% each year.• Interest expense will grow by 11% each year.• Flitwick’s tax rate is 31%. • CapEx and Change in NWC will grow by 12% each year.• At the end of year 5, you will sell the company for $40,000,000 (note: this number should be added to the year 5 free cash flow).• The appropriate discount rate is 16%.
Once you’ve estimated the free cash flows, find the value of the firm (i.e. the present value of the free cash flows.)
Question 2 You’re considering purchasing a house. You’ve estimated that the maximum monthly payment you can afford is $3,500. Your local bank is currently charging 0.4% monthly interest for a 30-year (360 month) mortgage. What is the most you can borrow?(Hint: set up the“Question 2” tab as we did in the mortgage example in the lecture video. Then use solver/goalseek to find the mortgage amount that makes the final ending balance equal to zero).
Question 3 You’re trying to estimate the value of a stock. They don’t currently pay a dividend, but you think they will begin to pay a dividend of $0.25 per share in 6 years. You predict that the dividends will double every year for the first 5 dividend payments, then afterward will grow at a stable 4% growth rate forever. The appropriate discount rate for this stock is 12%. What is the value of a share?

hw2template-qb0xelio.xlsx

Rinki · Accepted Answer

Question 1
	Flitwick Corporation
	PARTICULAR	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5
	Sales	$   51,000,000	$   57,630,000	$   65,121,900	$   73,587,747	$   83,154,154	$   93,964,194
	Cost of Goods Sold	$   29,500,000	$   33,335,000	$   37,668,550	$   42,565,461	$   48,098,971	$   54,351,838
	Gross Profit	$   21,500,000	$   24,295,000	$   27,453,350	$   31,022,285	$   35,055,183	$   39,612,356
	Selling, General and Administrative Costs	$   7,800,000	$   8,814,000	$   9,959,820	$   11,254,597	$   12,717,694	$   14,370,994
	Depreciation	$   3,300,000	$   3,597,000	$   3,920,730	$   4,273,596	$   4,658,219	$   5,077,459
	Earnings Before Interest and Tax (EBIT)	$   10,400,000	$   11,884,000	$   13,572,800	$   15,494,093	$   17,679,269	$   20,163,903
	Interest Expense	$   2,100,000	$   2,331,000	$   2,587,410	$   2,872,025	$   3,187,948	$   3,538,622
	Earnings Before Tax	$   8,300,000	$   9,553,000	$   10,985,390	$   12,622,068	$   14,491,321	$   16,625,281
	Taxes (31%)	$   2,573,000	$   2,961,430	$   3,405,471	$   3,912,841	$   4,492,310	$   5,153,837
	Net Income	$   5,727,000	$   6,591,570	$   7,579,919	$   8,709,227	$   9,999,012	$   11,471,444
	Operating Cash Flow
	CapEx	$   3,250,000	$   3,640,000	$   4,076,800	$   4,566,016	$   5,113,938	$   5,727,610
	Sale of Company						$   40,000,000
	Change in NWC	$   2,200,000	$   2,464,000	$   2,759,680	$   3,090,842	$   3,461,743	$   3,877,152
	Free Cash Flow	$   3,577,000	$   4,084,570	$   4,664,169	$   5,325,965	$   6,081,550	$   46,944,141
	Present Value @12% 
	Present Value Factor	0.893	0.893	0.893	0.893	0.893	0.893
	Present Value 	$   3,193,750	$   3,646,937	$   4,164,437	$   4,755,326	$   5,429,956	$   41,914,411
	Value of firm 	$   63,104,817
Question 2
			Mortgage Amount 		$667,092.00	(using solver)
			Monthly Payment		$3,500.00
								Calculation using formula
		Month	Beginning Balance	Interest @0.4% PM	Payment	Ending Balance
		1	$667,092	$2,668	$3,500	$666,260		P	=	EMI* ((1+R)^N)-1/R*(1+R)^N
		2	$666,260	$2,665	$3,500	$665,425
		3	$665,425	$2,662	$3,500	$664,587		P	=	3500*((1+0.004)^360)-1/0.004*(1+0.004)^360
		4	$664,587	$2,658	$3,500	$663,745
		5	$663,745	$2,655	$3,500	$662,900		P	=	3500*(4.208589925-1)/0.004*4.20859925
		6	$662,900	$2,652	$3,500	$662,052
		7	$662,052	$2,648	$3,500	$661,200		P	=	3500*(3.20859925)/0.016834397
		8	$661,200	$2,645	$3,500	$660,345
		9	$660,345	$2,641	$3,500	$659,486		P	=	667092
		10	$659,486	$2,638	$3,500	$658,624
		11	$658,624	$2,634	$3,500	$657,759
		12	$657,759	$2,631	$3,500	$656,890
		13	$656,890	$2,628	$3,500	$656,017
		14	$656,017	$2,624	$3,500	$655,142
		15	$655,142	$2,621	$3,500	$654,262
		16	$654,262	$2,617	$3,500	$653,379
		17	$653,379	$2,614	$3,500	$652,493
		18	$652,493	$2,610	$3,500	$651,603
		19	$651,603	$2,606	$3,500	$650,709
		20	$650,709	$2,603	$3,500	$649,812
		21	$649,812	$2,599	$3,500	$648,911
		22	$648,911	$2,596	$3,500	$648,007
		23	$648,007	$2,592	$3,500	$647,099
		24	$647,099	$2,588	$3,500	$646,187
		25	$646,187	$2,585	$3,500	$645,272
		26	$645,272	$2,581	$3,500	$644,353
		27	$644,353	$2,577	$3,500	$643,430
		28	$643,430	$2,574	$3,500	$642,504
		29	$642,504	$2,570	$3,500	$641,574
		30	$641,574	$2,566	$3,500	$640,640
		31	$640,640	$2,563	$3,500	$639,703
		32	$639,703	$2,559	$3,500	$638,762
		33	$638,762	$2,555	$3,500	$637,817
		34	$637,817	$2,551	$3,500	$636,868
		35	$636,868	$2,547	$3,500	$635,916
		36	$635,916	$2,544	$3,500	$634,959
		37	$634,959	$2,540	$3,500	$633,999
		38	$633,999	$2,536	$3,500	$633,035
		39	$633,035	$2,532	$3,500	$632,067
		40	$632,067	$2,528	$3,500	$631,096
		41	$631,096	$2,524	$3,500	$630,120
		42	$630,120	$2,520	$3,500	$629,140
		43	$629,140	$2,517	$3,500	$628,157
		44	$628,157	$2,513	$3,500	$627,170
		45	$627,170	$2,509	$3,500	$626,178
		46	$626,178	$2,505	$3,500	$625,183
		47	$625,183	$2,501	$3,500	$624,184
		48	$624,184	$2,497	$3,500	$623,180
		49	$623,180	$2,493	$3,500	$622,173
		50	$622,173	$2,489	$3,500	$621,162
		51	$621,162	$2,485	$3,500	$620,147
		52	$620,147	$2,481	$3,500	$619,127
		53	$619,127	$2,477	$3,500	$618,104
		54	$618,104	$2,472	$3,500	$617,076
		55	$617,076	$2,468	$3,500	$616,044
		56	$616,044	$2,464	$3,500	$615,009
		57	$615,009	$2,460	$3,500	$613,969
		58	$613,969	$2,456	$3,500	$612,924
		59	$612,924	$2,452	$3,500	$611,876
		60	$611,876	$2,448	$3,500	$610,824
		61	$610,824	$2,443	$3,500	$609,767
		62	$609,767	$2,439	$3,500	$608,706
		63	$608,706	$2,435	$3,500	$607,641
		64	$607,641	$2,431	$3,500	$606,571
		65	$606,571	$2,426	$3,500	$605,498
		66	$605,498	$2,422	$3,500	$604,420
		67	$604,420	$2,418	$3,500	$603,337
		68	$603,337	$2,413	$3,500	$602,251
		69	$602,251	$2,409	$3,500	$601,160
		70	$601,160	$2,405	$3,500	$600,064
		71	$600,064	$2,400	$3,500	$598,965
		72	$598,965	$2,396	$3,500	$597,860
		73	$597,860	$2,391	$3,500	$596,752
		74	$596,752	$2,387	$3,500	$595,

The “Question 1” tab of the spreadsheet contains the most recent (“Year 0”) income statement for Flitwick Corporation. You’ll need to forecast the next 5 years of the income statement (including the...

Answer To: The “Question 1” tab of the spreadsheet contains the most recent (“Year 0”) income statement for...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment