Deliverable 3 - Ranking Capital Investment Projects

FIN4489-Scenario-3-Data.xlsx

Home >Business & Finance homework help >Operations Management homework help >Deliverable 3 - Ranking Capital Investment Projects

Summary

			Cash Flow Comparisons*			Net Present Value Comparisons
	Cash Flow	Net Present Value	vs Opt 1	vs Opt 2	vs Opt 3	vs Opt 1	vs Opt 2	vs Opt 3
Option 1 - Purchase and Renovate Plant	$ 3,297,790	$ 81,396		$ 2,136,000	$ 225,000		$ 81,396	$ 81,396
Option 2 - Lease Co-Manufacturing Capacity	$ 1,161,790	$ - 0	$ (2,136,000)		$ (1,911,000)	$ (81,396)		$ - 0
Option 3 - Expand Existing Plant Location	$ 3,072,790	$ - 0	$ (225,000)	$ 1,911,000		$ (81,396)	$ - 0

&"arial,Regular"&KFFFFFF &"arial,Regular"&KFFFFFF &"arial,Regular"&KFFFFFF

Evaluating Capital Inv Projects

				Growth	1	2	3	4	5	5	5	5	5	5
Option 1 - Purchase and Renovate Plant		Description	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10	Terminal Value	Company Factors (All Options)
Start-up delay	12 months	Product sales		$ 1,150,000	$ 1,207,500	$ 1,267,875	$ 1,331,269	$ 1,397,832	$ 1,467,724	$ 1,467,724	$ 1,467,724	$ 1,467,724	$ 1,467,724		Discount rate	10%
Purchase land	$ 500,000	Product cost of sales		$ (690,000)	$ (724,500)	$ (760,725)	$ (798,761)	$ (838,699)	$ (880,634)	$ (880,634)	$ (880,634)	$ (880,634)	$ (880,634)		Income tax rate	20%
Purchase plant	$ 1,000,000	Purchase land	$ (500,000)											$ 500,000	Expected sales units (annual to yr 2)	500,000
Purchase/install equipment	$ 380,000	Purchase plant	$ (1,000,000)											$ 1,300,000	Sales unit annual growth (yrs 2-5)	5%
Renovate plant	$ 1,600,000	Renovate plant	$ (1,600,000)												Average sales price per unit to retailer	$ 2.30
Deprecation life equipment	10 years	Purchase equipment	$ (380,000)												Average contribution margin per unit	40%
Deprecation life plant	20 years	Depreciation adjustment plant		$ (130,000)	$ (130,000)	$ (130,000)	$ (130,000)	$ (130,000)	$ (130,000)	$ (130,000)	$ (130,000)	$ (130,000)	$ (130,000)	$ (1,300,000)	1) Depreciation adjustments to income tax are calculated
Capacity for current volume	300%	Depreciation adjustment equipment		$ (38,000)	$ (38,000)	$ (38,000)	$ (38,000)	$ (38,000)	$ (38,000)	$ (38,000)	$ (38,000)	$ (38,000)	$ (38,000)		on a straight-line basis for this analysis
(Current volume plus 200% growth)		Income tax	$ - 0	$ (58,400)	$ (63,000)	$ (67,830)	$ (72,902)	$ (78,227)	$ (83,818)	$ (83,818)	$ (83,818)	$ (83,818)	$ (83,818)	$ 260,000	2) Terminal values are simplified for this analysis
		Net cash flow excl depreciation	$ (3,480,000)	$ 401,600	$ 420,000	$ 439,320	$ 459,606	$ 480,906	$ 503,272	$ 503,272	$ 503,272	$ 503,272	$ 503,272	$ 2,060,000	3) Expected sales units only includes new customer;
		Rolling sum of cash flows	$ (3,480,000)	$ (3,078,400)	$ (2,658,400)	$ (2,219,080)	$ (1,759,474)	$ (1,278,568)	$ (775,296)	$ (272,024)	$ 231,247	$ 734,519	$ 1,237,790	$ 3,297,790	it does not include potential additions of other chains
										8	46%
		Net present value	$ 81,396							Use positive cash flow point
		Internal rate of return	10.4%							for payback period calculation
		Payback period in years	8.5
Option 2 - Lease Co-Manufacturing Capacity		Description	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10	Terminal Value
Start-up delay	3 months	Product sales	$ 862,500	$ 1,150,000	$ 1,207,500	$ 1,267,875	$ 1,331,269	$ 1,397,832	$ 1,467,724	$ 1,467,724	$ 1,467,724	$ 1,467,724	$ 1,467,724
Start-up vendor costs	$ 500,000	Product cost of sales	$ (517,500)	$ (690,000)	$ (724,500)	$ (760,725)	$ (798,761)	$ (838,699)	$ (880,634)	$ (880,634)	$ (880,634)	$ (880,634)	$ (880,634)
Monthly vendor expense	$ 30,000	Start-up vendor costs	$ (500,000)
Capacity for current volume	100%	Monthly vendor costs	$ (270,000)	$ (360,000)	$ (360,000)	$ (360,000)	$ (360,000)	$ (360,000)	$ (360,000)	$ (360,000)	$ (360,000)	$ (360,000)	$ (360,000)
(Current volume with no growth)		Income tax	$ 85,000	$ (20,000)	$ (24,600)	$ (29,430)	$ (34,502)	$ (39,827)	$ (45,418)	$ (45,418)	$ (45,418)	$ (45,418)	$ (45,418)	$ - 0
		Net cash flow	$ (340,000)	$ 80,000	$ 98,400	$ 117,720	$ 138,006	$ 159,306	$ 181,672	$ 181,672	$ 181,672	$ 181,672	$ 181,672	$ - 0
		Rolling sum of cash flows	$ (340,000)	$ (260,000)	$ (161,600)	$ (43,880)	$ 94,126	$ 253,432	$ 435,104	$ 616,776	$ 798,447	$ 980,119	$ 1,161,790	$ 1,161,790
		Net present value		Solve for
		Internal rate of return		values using
		Payback period		formulas as above
				Solve for beginning values based on start-up delay
Option 3 - Expand Existing Plant Location		Description	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10	Terminal Value
Start-up delay	18 months	Product sales			$ 1,207,500	$ 1,267,875	$ 1,331,269	$ 1,397,832	$ 1,467,724	$ 1,467,724	$ 1,467,724	$ 1,467,724	$ 1,467,724
Purchase land	$ 50,000	Product cost of sales			$ (724,500)	$ (760,725)	$ (798,761)	$ (838,699)	$ (880,634)	$ (880,634)	$ (880,634)	$ (880,634)	$ (880,634)
Construct plant expansion	$ 2,700,000	Purchase land	$ (50,000)											$ 50,000
Purchase/install equipment	$ 380,000	Construct plant	$ (2,700,000)											$ 1,485,000
Deprecation life equipment	10 years	Purchase equipment	$ (380,000)											$ 38,000
Deprecation life plant	20 years	Depreciation adjustment plant			$ (135,000)	$ (135,000)	$ (135,000)	$ (135,000)	$ (135,000)	$ (135,000)	$ (135,000)	$ (135,000)	$ (135,000)	$ (1,485,000)
Capacity for current volume	150%	Depreciation adjustment equipment			$ (38,000)	$ (38,000)	$ (38,000)	$ (38,000)	$ (38,000)	$ (38,000)	$ (38,000)	$ (38,000)	$ (38,000)	$ (38,000)
(Current volume plus 50% growth)		Income tax	$ - 0	$ - 0	$ (62,000)	$ (66,830)	$ (71,902)	$ (77,227)	$ (82,818)	$ (82,818)	$ (82,818)	$ (82,818)	$ (82,818)	$ 304,600
		Net cash flow excl depreciation	$ (3,130,000)	$ - 0	$ 421,000	$ 440,320	$ 460,606	$ 481,906	$ 504,272	$ 504,272	$ 504,272	$ 504,272	$ 504,272	$ 1,877,600
		Rolling sum of cash flows	$ (3,130,000)	$ (3,130,000)	$ (2,709,000)	$ (2,268,680)	$ (1,808,074)	$ (1,326,168)	$ (821,896)	$ (317,624)	$ 186,647	$ 690,919	$ 1,195,190	$ 3,072,790
		Net present value		Solve for
		Internal rate of return		values using
		Payback period		formulas as above

&"arial,Regular"&KFFFFFF &"arial,Regular"&KFFFFFF &"arial,Regular"&KFFFFFF