ACCOUNTING PROBLEM

Below is an Income and cash flow statements that management has approved.  (If there are errors or oversights, that is their problem, not yours).  Start each question from the original data.  Cells F14:F22 contain the original values in case you need to get back to them.  									
a	Determine the unit price that would achieve a cash flow in week 6 of $500,000								
b	Determine the sensitivity of the internal rate of return to a 10%, 25% and 50% increase in investment? (start with original values)								
c	Determine the expected present worth for the following data where Forecast 1 is the current values. (start with original values)								
		Forecast 1 (current Values)	Forecast 2	Forecast 3	cell				
	Unit Price	$35.99 	$40.00 	$35.00 	C14				
	COGS each	$12.50 	$15.00 	$11.50 	C15				
	S.G. & A.	$800,000 	$1,000,000 	$900,000 	C16				
	Sales Quantity Forecast year 1	50,000	40,000	60,000	D23				
	Probability	50%	30%	20%					
									
					Original Values				
	Unit Price	$35.99 			$35.99 				
	COGS each	$12.50 			$12.50 				
	S.G.& A.	$800,000 			$800,000 				
	salvage	$100,000 	in year 6		$100,000 				
	Income tax rate	35%			35%				
	Capital Gains Tax rate	15%			15%				
	Working capital	no change			no change				
	MARR	15%			15%				
	Investment	$2,000,000 			$2,000,000 				
	Sales Quantity Forecast		50,000	60,000	72,000	86,400	103,680	124,416	
	Depreciation MACRS	5	20.00%	32.00%	19.20%	11.52%	11.52%	5.76%	
									
	Income Statement	0	1	2	3	4	5	6	
	Sales revenue 		$1,799,500 	$2,159,400 	$2,591,280 	$3,109,536 	$3,731,443 	$4,477,732 	
	Cost of goods sold 		($625,000)	($750,000)	($900,000)	($1,080,000)	($1,296,000)	($1,555,200)	
	Gross Margin		$1,174,500 	$1,409,400 	$1,691,280 	$2,029,536 	$2,435,443 	$2,922,532 	
	General, Sales and Admin.		($800,000)	($800,000)	($800,000)	($800,000)	($800,000)	($800,000)	
	Depreciation 		($400,000)	($640,000)	($384,000)	($230,400)	($115,200)	($57,600)	
	EBIT		($25,500)	($30,600)	$507,280 	$999,136 	$1,520,243 	$2,064,932 	
	Income tax 		$8,925 	$10,710 	($177,548)	($349,698)	($532,085)	($722,726)	
	Net income 		($16,575)	($19,890)	$329,732 	$649,438 	$988,158 	$1,342,206 	
									
	Cash Flow Statement								
	Net Income		($16,575)	($19,890)	$329,732 	$649,438 	$988,158 	$1,342,206 	
	Add depreciation 		$400,000 	$640,000 	$384,000 	$230,400 	$115,200 	$57,600 	
	Investment	-2,000,000		  	  	  	  	  	
	Change in Working Capital		($179,950)	($35,990)	($43,188)	($51,826)	($62,191)	($74,629)	
	Salvage							$100,000 	
	Tax on gain							$15,000 	
	Cash flow 	($2,000,000)	$203,475 	$584,120 	$670,544 	$828,013 	$1,041,167 	$1,440,177 	
									
		Present Worth =	IRR						
		$673,198 	23.95%