Question

PLEASE SHOW WORK IN EXCEL THANK YOU!

Green-Log Manufacturing

Capital Budgeting Analysis

In 1997 Michelle Green started Green-Log Manufacturing, a company dedicated to manufacturing environmental friendly man made logs that can be burned in fire places, fire pits in the backyard, and when camping. The logs are made from environmental friendly products like cardboard and clean wax and emit fewer greenhouse gases and less harsh chemicals. The logs come in three pound and five pound sizes. Green-logs are also available with citronella to ward off mosquitos. The Green-Logs have been well received. Revenue and profits have grown steadily.

Based on the recommendation from her sales and marketing department Ms. Green is considering adding a new product line of Green-Log fire starters. These fire starters would be ideal for the outdoor market of camping, fishing, backpacking, and tailgating. Ms. Green has asked her sales and marketing team to come up with a sales forecast for units and pricing. She has also asked her manufacturing team to come up with alternatives for the production of the fire starters including what equipment is needed and what the projected costs would be.

The sales and marketing team hired Smith and Smith Consulting to conduct a market survey. The total cost for this consulting was $32,500. Based on the survey and their own experience the sales and marketing has provided a sales forecast. The suggested price of the fire starter is $2.50 per starter and they would be sold as a four pack for $10.00. The unit sales forecast is 20,000 4-packs in year 1, 45,000 in year 2, 60,000 in year 3, 75,000 in year 4, and then increasing by 5,000 each year thereafter. Sales and marketing expenses are expected to be 10% of total revenue.

The production team forecasts that the fixed costs needed for the fire starter production line will be $90,000 per year. Variable costs for materials (cardboard, wood shavings, wax, packaging, etc.) will be $0.85 per unit or $3.40 per four pack. The labor and maintenance costs will vary based on what equipment will be purchased.

There are two brands of equipment that will do the job; The ABC brand and the XYZ brand.

The ABC brand is more expensive, but higher quality and more efficient. It will cost $525,000 plus an additional $30,000 for shipping and installation. The equipment would be depreciated to zero over 5 years using straight line depreciation. It is expected that the equipment would last for 8 years and would be sold then for $55,000. Maintenance of the ABC equipment would cost $5,000 per year but every 3 years the equipment would need an overhaul that would increase the cost to $75,000 for that year. Since the ABC equipment is more efficient the variable labor cost would be $0.60 per four pack.

The XYZ brand is less expensive. It will cost $395,000 plus an additional $40,000 for shipping and installation. The equipment would be depreciated to zero over 5 years using straight line depreciation. It is expected that the equipment would last for 8 years and would be sold then for $35,000. Maintenance of the ABC equipment would cost $10,000 per year but every 3 years the equipment would need an overhaul that would increase the cost to $85,000 for that year. The variable labor cost with the XYZ brand equipment would be $0.80 per four pack.

The increase in working capital (accounts receivable and inventory) is expected to be $60,000 at the beginning of the project and will be the same for both machines. The company’s cost of capital is 14% and its tax rate is 40%. Since her production team believes that both brands of equipment will last for eight years Michelle wants this analyzed as an eight year project.

Michelle has always believed in buying quality so she is leaning towards the ABC brand equipment. But after hearing that you have learned about capital budgeting in your Finance class at UVU she wants to take advantage of your expertise. Michelle has asked you to analyze her choices and give her some advice on which option would provide the best financial outcome for Green-Log Manufacturing.

Prepare an analysis and professional report for Michelle. The report should be professionally written and include a two page letter, plus attached schedules. The letter should explain what analytical techniques you are using, why you are using those techniques, what the results show, what you would recommend to Michelle and why. Make sure that the letter is well organized and professionally written. Also make sure that the letter includes the following:

1.         The cash flows associated with the different equipment brands for each year of the           project.

2.         The PB period, Discounted PB, IRR, and NPV for the two alternatives.

3.         Your recommendation of which brand of equipment should be purchased.

4.         Attach to your letter schedules that show your analysis and your work.

            Please submit files. A Word file with your letter and an Excel file with your analysis.

Answer 1

1. The cost incurred towards market survey of $32,500 is sunk cost and will not affect decision making relating to buying equipment XYZ or ABC.
2. PB period - how many years it takes for cash flows to equal initial investment. FORMULA = initial investment + cash flows -
3. Discounted PB - how many years it takes for discounted cash flows to equal initial investment. FORMULA = initial investment + discounted cash flows
4. IRR - rate at which cash inflows = cash outflows FORMULA = use hit and trial method or in excel use =IRR(values of cash flows)
5. NPV is the net present value or cash outflows + cash inflows FORMULA = sum total of all cash flows
6. Table of analysis

		ABC	XYZ	Remarks
	Net present value	697,263	726,156	XYZ is more profitable than ABC. Hence, XYZ is preferable
rate at which cash outflows = cash inflows	IRR	19.36%	23.63%	XYZ will give a higher return than ABC. Hence, XYZ is preferable
payback period	PBP (in years)	4	3	XYZ pays back earlier than ABC. Hence, XYZ is preferable
discounted payback period	DPBP (in years)	4 years (approx)	4 years (approx)	either XYZ or ABC

remarks	time in years	0	1	2	3	4	5	6	7	8
	INITIAL INVESTMENT
395000+40000	-buying cost	(435,000)
	-increase in Working Capital	(60,000)
	TERMINAL CASH FLOWS
	-salvage value of equipment									35,000
	OPERATING CASH FLOWS
	price		2.50
	pack price		10	10	10	10	10	10	10	10
	units		20,000	45,000	60,000	75,000	80,000	85,000	90,000	95,000
	Revenue		200,000	450,000	600,000	750,000	800,000	850,000	900,000	950,000
0.8	-variable cost		(16,000)	(36,000)	(48,000)	(60,000)	(64,000)	(68,000)	(72,000)	(76,000)
	Contribution		184,000	414,000	552,000	690,000	736,000	782,000	828,000	874,000
	-fixed cost		(90,000)	(90,000)	(90,000)	(90,000)	(90,000)	(90,000)	(90,000)	(90,000)
	Gross Profit		94,000	324,000	462,000	600,000	646,000	692,000	738,000	784,000
@10% of revenue	-sales & marketing expense		(20,000)	(45,000)	(60,000)	(75,000)	(80,000)	(85,000)	(90,000)	(95,000)
overhaul after every 3 years. Hence, at T=4 and T=8	-labour & maintenance cost		(10,000)	(10,000)	(10,000)	(85,000)	(10,000)	(10,000)	(10,000)	(85,000)
	EBITDA		64,000	269,000	392,000	440,000	556,000	597,000	638,000	604,000
SLM method, 0% salvage value	-depreciation		(87,000)	(87,000)	(87,000)	(87,000)	(87,000)	-	-	-
	EBIT		(23,000)	182,000	305,000	353,000	469,000	597,000	638,000	604,000
nil	- interest		0	-	-	-	-	-	-	-
	PBT		(23,000)	182,000	305,000	353,000	469,000	597,000	638,000	604,000
@40%	-tax		9,200	(72,800)	(122,000)	(141,200)	(187,600)	(238,800)	(255,200)	(241,600)
	PAT		(13,800)	109,200	183,000	211,800	281,400	358,200	382,800	362,400
-added back because it’s a non-cash expenditure	-+depreciation		87,000	87,000	87,000	87,000	87,000	-	-	-
	AFTER TAX CASH FLOW	(495,000)	73,200	196,200	270,000	298,800	368,400	358,200	382,800	397,400
	CUMULATIVE CASH FLOWS	(495,000)	(421,800)	(225,600)	44,400	343,200	711,600	1,069,800	1,452,600	1,850,000
discounted @14% formula:- cash flow/(1+discount rate)^time	14%	(495,000)	64,211	150,970	182,242	176,914	191,335	163,191	152,981	139,312
	CUMULATIVE DISCOUNTED CFs	(495,000)	(430,789)	(279,820)	(97,578)	79,336	270,671	433,862	586,844	726,156
	NET PRESENT VALUE	726,156
	IRR	23.63%