fin 451

Bruce Alex

MS-Template.xlsx

Home >Business & Finance homework help >Economics homework help >fin 451

Intro

Murphy Stores: Capital Projects

The worksheets tabs provide base case scenarios and models for the three projects.

The EAS projects are run with 3 different benefit measures:

(Differential Gross Margin $; Differential Sales $; Differential Cost Savings from COGS: Lower Replacement Merchandise Costs)

The Lighting project analysis follows the EAS analyses.

Other tabs present summary base case results; value drivers/sensitivity analysis; scenario analysis; and conclusions/outcomes.

Summary of Base Case Results

IM Exhibit 1
Summary of Project Base Case Results
EAS Full Line Stores
Base case assumptions:	Reduction in shrink 30%, shrink rate without EAS grows 0.1% per year, base rate sales forecast
EAS Hardware Stores
Base case assumptions:	Reduction in shrink 30%, shrink rate without EAS grows 0.1% per year, base rate sales growth
Lighting
Base case assumptions:	Energy cost savings at average of estimated range (35%); energy costs rise with inflation
Project	EAS Full Line Stores	EAS Full Line Stores	EAS Full Line Stores	EAS Hardware Stores	EAS Hardware Stores	EAS Hardware Stores	Lighting
Measure of Benefit	Differential Sales	Differential Costs	Differential Gross Margin $	Differential Sales	Differential Costs	Differential Gross Margin $	Cost Savings
NPV	$14,712,340	$3,811,088	$1,226,004	$17,799,104	$9,743,226	$6,110,183	$1,151,539
IRR	58%	27%	17%	97%	66%	50%	16%
Profitability Index	4.69	1.96	1.31	9.00	5.38	3.75	1.16

EAS Full-line Store (Sales)

IM Exhibit 2
EAS Full-Line Stores Base Case: Sales as Benefit Measure

Sales (23 Full-Line Stores)	$ 500,000,000	23 stores*$21.8 mm avg sales
Sales growth %	6%
Gross Margin	44.7%
Inflation	4%
Tax Rate	39%
Shrinkage	3.60% JSS: JSS: in this case I assumed that EAS would be concentrated in those depts with higher shrink than full-line store average
Shrinkage Decline	30.0%
Estimated Annual Growth in Shrinkage Rate without EAS	0.10%
Capital Expenditure	$ 3,036,000
Cost of Tags	$1,564,000
Year	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016
	0	1	2	3	4	5	6	7	8	9	10
Expected Revenue		500,000,000	530,000,000	561,800,000	595,508,000	631,238,480	669,112,789	709,259,556	751,815,129	796,924,037	844,739,480
% Shrinkage		3.60%	3.70%	3.80%	3.90%	4.00%	4.10%	4.20%	4.30%	4.40%	4.50%
Revenue Loss due to Shrinkage		18,000,000	19,610,000	21,348,400	23,224,812	25,249,539	27,433,624	29,788,901	32,328,051	35,064,658	38,013,277
Differential Revenue - Savings from EAS		2,700,000	5,883,000	6,404,520	6,967,444	7,574,862	8,230,087	8,936,670	9,698,415	10,519,397	11,403,983
Differential Gross Profit		1,206,900	2,629,701	2,862,820	3,114,447	3,385,963	3,678,849	3,994,692	4,335,192	4,702,171	5,097,580
Differential Operating Expenses (Tags)	1,564,000	1,626,560	1,691,622	1,759,287	1,829,659	1,902,845	1,978,959	2,058,117	2,140,442	2,226,060	2,315,102
Depreciation MACRS %		14.29%	24.49%	17.49%	12.49%	8.93%	8.92%	8.93%	4.46%
Differential Depreciation		433,844	743,516	530,996	379,196	271,115	270,811	271,115	135,406	- 0	- 0
Differential EBIT		639,596	3,447,861	4,114,236	4,758,588	5,400,902	5,980,317	6,607,438	7,422,568	8,293,338	9,088,881
Differential Taxes	(609,960) JSS: JSS: Note that tags purchased up front are a tax-deductible expense in year 0.	249,442	1,344,666	1,604,552	1,855,849	2,106,352	2,332,324	2,576,901	2,894,801	3,234,402	3,544,664
Differential Change in Operating Income after Taxes		390,153	2,103,195	2,509,684	2,902,739	3,294,550	3,647,993	4,030,537	4,527,766	5,058,936	5,544,217
Capital Expenditure	(3,036,000)
Differential Free Cash Flow	(3,990,040)	823,998	2,846,712	3,040,681	3,281,935	3,565,665	3,918,805	4,301,652	4,663,172	5,058,936	5,544,217

EAS - Hardware (Sales)

IM Exhibit 5
EAS Hardware Stores Base Case: Sales as Benefit Measure

Sales (Hardware Stores)	$ 406,000,000	(110 stores*$3.7 mm per store)
Sales growth %	9%
Gross Margin	40.8%
Inflation	4%
Tax Rate	39%
Shrinkage	3.10%
Shrinkage Decline	30%
Estimated Annual Growth in Shrinkage Rate without EAS	0.1%
Capital expenditure	$ 2,090,000
Cost of tags	$ 220,000
Year	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016
	0	1	2	3	4	5	6	7	8	9	10
Expected Revenue		406,000,000	442,540,000	482,368,600	525,781,774	573,102,134	624,681,326	680,902,645	742,183,883	808,980,433	881,788,671
% Shrinkage		3.10%	3.20%	3.30%	3.40%	3.50%	3.60%	3.70%	3.80%	3.90%	4.00%
Revenue Loss due to Shrinkage		12,586,000	14,161,280	15,918,164	17,876,580	20,058,575	22,488,528	25,193,398	28,202,988	31,550,237	35,271,547
Differential Revenue - Savings from EAS		1,887,900	4,248,384	4,775,449	5,362,974	6,017,572	6,746,558	7,558,019	8,460,896	9,465,071	10,581,464
Differential Gross Profit		770,263	1,733,341	1,948,383	2,188,093	2,455,170	2,752,596	3,083,672	3,452,046	3,861,749	4,317,237
Differential Operating Expenses (Tags)	220,000	57,200	62,348	67,959	74,076	80,742	88,009	95,930	104,564	113,975	124,232
Depreciation MACRS %		14.29%	24.49%	17.49%	12.49%	8.93%	8.92%	8.93%	4.46%
Differential Depreciation		298,661	511,841	365,541	261,041	186,637	186,428	186,637	93,214	- 0	- 0
Differential EBIT		1,532,039	3,674,195	4,341,949	5,027,857	5,750,193	6,472,121	7,275,452	8,263,118	9,351,096	10,457,232
Differential Taxes	(85,800) JSS: JSS: Note that tags purchased up front are a tax-deductible expense in year 0.	597,495	1,432,936	1,693,360	1,960,864	2,242,575	2,524,127	2,837,426	3,222,616	3,646,928	4,078,320
Differential Change in Operating Income after Taxes		934,544	2,241,259	2,648,589	3,066,993	3,507,618	3,947,994	4,438,026	5,040,502	5,704,169	6,378,911
Capital Expenditure	(2,090,000)
Differential Free Cash Flow	(2,224,200)	1,233,205	2,753,100	3,014,130	3,328,034	3,694,255	4,134,422	4,624,663	5,133,716	5,704,169	6,378,911

Lighting Project

Base Case Analysis - Lighting
IM Exhibit 8
Total Number of Stores	187
Installation Cost/Store	37,400
Total Cost (Capital Expenditure)	6,993,800
Cost per kwh	0.075
Inflation	4%
Tax rate	39%
Lighting
Average kwh	56.9
Annual hours of use	5100
Cost per kwh	0.075
Energy Cost	4,069,915
Reduction in Energy Consumption	35% JSS: JSS: Average of reduction of 30-40%
Savings from Lighting	1,424,470
Air Conditioning
Air Conditioning hours/yr/store	2000
Cost per kwh	0.075
Average Reduction in kw	9.5 JSS: JSS: Average reduction of range 9-10
Total savings from air conditioning	266,475
	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016
	0	1	2	3	4	5	6	7	8	9	10
Total Energy Savings JSS: JSS: This represents incremental EBITDA		845,473	1,758,583	1,828,926	1,902,083	1,978,167	2,057,293	2,139,585	2,225,168	2,314,175	2,406,742
Depreciation MACRS %		14.29%	24.49%	17.49%	12.49%	8.93%	8.92%	8.93%	4.46%
Depreciation		999,414	1,712,782	1,223,216	873,526	624,546	623,847	624,546	311,923	- 0	- 0
Incremental EBIT		(153,941)	45,801	605,711	1,028,558	1,353,620	1,433,446	1,515,039	1,913,245	2,314,175	2,406,742
Taxes		(60,037)	17,863	236,227	401,138	527,912	559,044	590,865	746,166	902,528	938,629
Incremental Operating Profit after Taxes		(93,904)	27,939	369,484	627,420	825,708	874,402	924,174	1,167,079	1,411,647	1,468,113
Capital Expenditure	(6,993,800)
Free Cash Flow	(6,993,800)	905,510	1,740,720	1,592,699	1,500,946	1,450,255	1,498,249	1,548,720	1,479,003	1,411,647	1,468,113