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 |