Mr. A. and Mr. C. are partners in a local food catering business named BBQ City. Their business currently generates $100,000 per year in sales. They are considering making an investment in a portable BBQ Smoker. The smoker comes in two different models: The Model 100 and the Model 200. Because both models of the BBQ smokers are portable, the catering company will be able to increase the distance they can travel to serve their customers. This means that the company will be able to increase its sales (see below). The companys tax rate is 35%. The companys current structure is such that a $1 increase in sales will increase its EBDT by $0.32. The company uses 15% as its required rate of return (cost of capital) for evaluating new projects.

The Model 100 has only half the capacity of the Model 200. Cost to purchase the machines is $13,000 (Model 100) and $22,000 (Model 200). The machines are depreciable under the 5-year MACRS depreciation schedule (see below). The company feels that any potential investment project should be evaluated based on a five year sales forecast.

The expected increase in sales is different, depending on which model is being considered. Because of smaller capacity, the Model 100 will not allow the company to accept catering jobs for groups above a certain size. The expected sales increase for each model is as follows:

Expected Increase in Sales	Year 1	Year 2	Year 3	Year4	Year 5
Model 100	$10,000	$12,000	$15,000	$15,000	$15,000
Model 200	$18,000	$23,000	$25,000	$36,000	$43,000

The investments are mutually exclusive. In other words, the company must choose only one. Which model should the company invest in? Set up the appropriate cash flows for each model. Calculate Payback, IRR, and NPV for each model, based on the above sales forecast. Justify your recommendation, based on your calculation of these numbers. For your reference the MACRS depreciation schedule for a 5 year asset is as follows:

Yr1 .20

Yr2 .32

Yr3 .192

Yr4 .115

Yr5 .115

Yr6 .058

I was able to use excel to figure out some of the answers but I got lost on the Depreciatable assests. This is my answers but I'm not sure I'm correct

Capital Budgeting Case: BBQ City
Capital Budgeting		Excel Assignment
									MACRS depreciation schedule
Cost of Investment
Enter:	Depreciable Assets	$ 100,000.00	Enter Current Corporate Tax Rate:		35%				(5-year category)
Enter:	Cost of Non-Depreciable Assets	$ 25,000.00	Enter Corporate Cost of Capital:		15%				Year 1	20.00%
	Net Cost of Investment	$ 125,000.00							Year 2	32.00%
	Note: Model assumes 5 year MACRS schedule for Depreciation								Year 3	19.20%
	Your Project's IRR is:	115%							Year 4	11.50%
									Year 5	11.50%
	Your Project's NPV is	$63,520.00							Year 6	5.80%
	Cash Flows Resulting from Investment Model 100
	Item	Yr 0	Yr 1	Yr 2	Yr 3	Yr 4	Yr 5
Enter	EBDT		$ 10,000.00	$ 12,000.00	$ 15,000.00	$ 15,000.00	$ 15,000.00
	minus Depreciation		$ 20,000.00	$ 32,000.00	$ 19,200.00	$ 11,500.00	$ 11,500.00
	Earnings before Tax		$ (10,000.00)	$ (20,000.00)	$ (4,200.00)	$ 3,500.00	$ 3,500.00
	Minus Tax		$ (3,500.00)	$ (7,000.00)	$ (1,470.00)	$ 1,225.00	$ 1,225.00
	Earnings After Tax		$ (6,500.00)	$ (13,000.00)	$ (2,730.00)	$ 2,275.00	$ 2,275.00
	Plus Depreciation		$ 20,000.00	$ 32,000.00	$ 19,200.00	$ 11,500.00	$ 11,500.00
	Incremental Cash flow	$ (13,000.00)	$ 13,500.00	$ 19,000.00	$ 16,470.00	$ 13,775.00	$ 13,775.00