Finance Project Questions?

capital_budgeting_template.xls

Home >Business & Finance homework help >Finance Project Questions?

MMCC

Majestic Mulch and Compost Company (MMCC)
	YEAR		0	1	2	3	4	5	6	7	8
Background Data:
	Unit Sales Estimates			3,000	5,000	6,000	6,500	6,000	5,000	4,000	3,000
	Variable Cost /unit	$ 60.00
	Fixed Costs per year	$ 25,000.00
	Sale Price per unit	$ 120.00		$ 120.00	$ 120.00	$ 120.00	$ 110.00	$ 110.00	$ 110.00	$ 110.00	$ 110.00
	Tax Rate	34.0%
	Required Return on Project	15.0%
	Yr 0 NWC	$ 20,000.00
	NWC % of sales	15%
	Equipment cost - installed	$ 800,000
	Salvage Value in year 8	20%	of equipment cost
Depreciation Calculations:
	Equipment Depreciable Base	800,000
	MACRS % (Eqpt-7 yr)			14.29%	24.49%	17.49%	12.49%	8.92%	8.93%	8.93%	4.46%
	Recovery Allowance			114,320	195,920	139,920	99,920	71,360	71,440	71,440	35,680
	Book Value			685,680	489,760	349,840	249,920	178,560	107,120	35,680	0
After-Tax Salvage Value
	Salvage Value	20%	160,000
	Book Value (Year 8)		0
	Capital Gain/Loss		160,000
	Taxes		54,400
	Net SV (SV-Taxes)		105,600
Required Net Working Capital Investment
			20,000	54,000	90,000	108,000	107,250	99,000	82,500	66,000	49,500
	YEAR		0	1	2	3	4	5	6	7	8
Initial Investment
	Equipment Cost		(800,000)
	Sales			360,000	600,000	720,000	715,000	660,000	550,000	440,000	330,000
	Variable Costs			180,000	300,000	360,000	390,000	360,000	300,000	240,000	180,000
	Fixed Costs			25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000
	Depreciation (Eqpt))			114,320	195,920	139,920	99,920	71,360	71,440	71,440	35,680
	EBT			40,680	79,080	195,080	200,080	203,640	153,560	103,560	89,320
	Taxes			13,831	26,887	66,327	68,027	69,238	52,210	35,210	30,369
	Net Operating Income			26,849	52,193	128,753	132,053	134,402	101,350	68,350	58,951
	Add back Depreciation			114,320	195,920	139,920	99,920	71,360	71,440	71,440	35,680
	CASH FLOW from Operations			141,169	248,113	268,673	231,973	205,762	172,790	139,790	94,631
	NWC investment & Recovery		(20,000)	(34,000)	(36,000)	(18,000)	750	8,250	16,500	16,500	66,000
	Salvage Value										105,600
	TOTAL PROJECTED CF		(820,000)	107,169	212,113	250,673	232,723	214,012	189,290	156,290	266,231
	Discounted Cash Flows		(820,000)	93,190	160,388	164,821	133,060	106,402	81,835	58,755	87,031
	Cumulative Cash flows		(820,000)	(712,831)	(500,718)	(250,046)	(17,323)	196,690	385,979	542,269	808,500
	NPV		$65,483
	IRR		17.24%
	Payback		4.08

Scenario

Scenario Analysis
			Base	Lower	Upper
	Units		6,000	5,500	6,500
	Price/unit		$ 80.00	$ 75.00	$ 85.00
	Variable cost/unit		$ 60.00	$ 58.00	$ 62.00
	Fixed cost/year		$ 50,000	$ 45,000	$ 55,000
			BASE	BEST	WORST
	Initial investment		$ 200,000
		Depreciated to salvage value of 0 over 5 years
		Deprec/yr	$ 40,000
	Project Life		5	years
	Tax rate		34%
	Required return		12%
			BASE	WORST	BEST
	Units		6,000	5,500	6,500
	Price/unit		$ 80.00	$ 75.00	$ 85.00
	Variable cost/unit		$ 60.00	$ 62.00	$ 58.00
	Fixed Cost		$ 50,000	$ 55,000	$ 45,000
	Sales		$ 480,000	$ 412,500	$ 552,500
	Variable Cost		360,000	341,000	377,000
	Fixed Cost		50,000	55,000	45,000
	Depreciation		40,000	40,000	40,000
	EBIT		30,000	(23,500)	90,500
	Taxes		10,200	(7,990)	30,770
	Net Income		19,800	(15,510)	59,730
	+ Deprec		40,000	40,000	40,000
	TOTAL CF		59,800	24,490	99,730
	NPV		15,566	(111,719)	159,504
	IRR		15.1%	-14.4%	40.9%
	NOTE:	Note in WORST CASE, tax credit for negative earnings
			$ (200,000)	$ (200,000)	$ (200,000)
			59,800	24,490	99,730
			59,800	24,490	99,730
			59,800	24,490	99,730
			59,800	24,490	99,730
			59,800	24,490	99,730
		PV	$215,566	$88,281	$359,504
		NPV	15,566	(111,719)	159,504

Sensitivity

Sensitivity Analysis
			Base	Units	Fixed Cost
	Units		6,000	5,500	6,000
	Price/unit		$ 80	80	80
	Variable cost/unit		$ 60	60	60
	Fixed cost/year		$ 50,000	50,000	55,000
	Initial investment		$ 200,000
		Depreciated to salvage value of 0 over 5 years
		Deprec/yr	$ 40,000
	Tax rate		34%
	Required Return		12%
			BASE	UNITS	FC
	Units		6,000	5,500	6,000
	Price/unit		$ 80	$ 80	$ 80
	Variable cost/unit		$ 60	$ 60	$ 60
	Fixed cost		$ 50,000	$ 50,000	$ 55,000
	Sales		$ 480,000	$ 440,000	$ 480,000
	Variable Cost		360,000	330,000	360,000
	Fixed Cost		50,000	50,000	55,000
	Depreciation		40,000	40,000	40,000
	EBIT		30,000	20,000	25,000
	Taxes		10,200	6,800	8,500
	Net Income		19,800	13,200	16,500
	+ Deprec		40,000	40,000	40,000
	TOTAL CF		59,800	53,200	56,500
	NPV		$ 15,566	$ (8,226)	$ 3,670
	% Change in NPV			-152.8%	-76.4%
	% Change in Variable			-8.3%	10.0%
	SENSITIVITY RATIO			18.34	-7.64
				DIRECT	INVERSE
	Sensitivity Ratio = (% Change in NPV)/% Change in Variable
		Positive = Direct relationship
		Negative = Inverse relationship
			$ (200,000)	$ (200,000)	$ (200,000)
			59,800	53,200	56,500
			59,800	53,200	56,500
			59,800	53,200	56,500
			59,800	53,200	56,500
			59,800	53,200	56,500
		PV	$ 215,566	$ 191,774	$ 203,670
		NPV	$ 15,566	$ (8,226)	$ 3,670