For this component of the project, you are asked to develop a capital budgeting model for the Lowe's Expansion Plans Analysis.

A capital budgeting model should be formed for a single Lowe's store. The analysis consists of three steps.

Develop cash flows.

Identify the weighted average cost of capital.

Discount the cash flows using the weighted average cost of capital.

Complete the cash flow estimation using sales growth assumptions as well as assumptions for the relation between expense items and sales.

Refer to these assumptions:

Capital Budgeting Assumptions as of 1/28/2005 Use the following assumptions for the Capital Budgeting Model. The same store sales growth should be estimated based upon historical movements in same store sales and your assessment of future prospects for Lowes. Cost of Goods Sold should be estimated as a percentage of sales. Selling, general, and administrative expenses should be estimated as a percentage of sales. Depreciation expenses should be estimated as a percentage of sales. Tax expense will be estimated as the past year tax expense divided by the past year per-tax earnings. Operating Cash Flows will be equal to {(Sales CGS SG&A-Dep)*(1-tax rate)}+Dep. Additional capital expenditure (needed each year to support the stores capital assets) is set equal to depreciation expense. The notion is that the company will have to reinvest money into the long-term assets at an amount equal to the depreciation of those assets each year. Net Working Capital (NWC) is equal to total current assets less total current liabilities. Net Working Capital (NWC) will be estimated as a percentage of sales. Additional Net Working Capital (change from year to year in NWC) will be equal to the current year NWC less the past year NWC. Free Cash Flows (Asset Cash Flows) will be equal to Operating Cash Flows less additional capital expenditures less additional net working capital. The long term growth in Free Cash Flows (Asset Cash Flows) beyond 2009 will be assumed to equal 2% which considers 1% population growth plus 1% inflation. V2009 will be estimated as, FCF09*(1+long term growth rate)/(WACC long term growth rate). Weighted Average Cost of Capital (WACC) is set equal to 9.5%, or 0.095 in decimal form.

The primary metric of concern in this analysis is the net present value (NPV). A positive NPV means that the project under consideration generates value in excess of the up-front costs of the project. The excess value is claimed by shareholders such that a positive NPV means shareholder wealth is increased while a negative NPV means that shareholder wealth is decreased by doing the project.

Lowe's Balance Sheet 1/28/2005 1/30/2004 1/31/2003 Assets Current Assets 853 273 172 3,968 58 244 5,568 10,352 29 160 530 283 Cash & quivalents ST Investments Accounts Receivables Merchandise Inventory Def Inc Taxes Other CA Total current assets 5,911 95 75 6,903 13,911 146 178 1,446 178 131 4,584 59 289 6,687 11,945 169 241 Net PPE LT Investments Other Assets Total Assets 21,138 19,042 16,109 Liabilities and Shareholder Equity Current Liabilities 50 29 1,943 Short term borrowings Current LT Debt Accounts Payable Employee retirement plans Accrued salaries and wages Self Insurance liability Def Revenue Other current liabilities Total current liabilities 630 2,695 2,366 74 335 386 467 539 931 5,648 3,060 736 159 9,603 11,535 306 LTD Def Inc Taxes Other LT Liab Total Liabilities Total Shareholder Equity 1,516 4,368 3,678 657 30 8,733 10,309 1,162 3,578 3,736 478 15 7,807 8,302 Total Liabilities and Shareholder Equities 21,138 19,042 16,109 Home Depot, Inc 01/30/2005 02/01/2004 US dollars US dollars Cash Marketable securities Receivables Inventories Raw materials Work in progress Finished goods Notes receivable Other current assets & prepaid expenses Total current assets Property, plant & equipment Accumulated depreciation Net property, plant & equipment Investments & advances to subsidiaries Other non-current assets Deferred charges & income Intangibles Deposits & other assets Total assets 506,000 2,826,000 26,000 1,499,000 1,097,000 10,076,000 9,076,000 1,659,000 10,076,000 9.076,000 303,000 14,273,000 13,328,000 28,437,000 24,594,000 5,711,000 4,531,000 22,726,000 20,063,000 533,000 369,000 84,000 833,000 129,000 39,020,000 34,437,000 1,412,000 240,000 01/30/2005 02/01/2004 US dollars US dollars Notes payable Accounts payable Current long term debt Current portion of cap leases Accrued expenses Income taxes Other current liabilities Total current liabilities Mortgages Deferred charges/income 5,766,000 5,159,000 502,000 7,000 2,971,000 2,430,000 175,000 1,546,000 1,281,000 10,455,000 9,554,000 1,000 10,000 161,000 1,388,000 967,000 Income taxes Other current liabilities Total current liabilities Mortgages Deferred charges/income Convertible debt Long term debt Non-current capital leases Other long term labilities Total liabilities Minority interest (liabilities) Other liabilities Shareholder Equity Total liabilities & net worth 175,000 1,546,000 1,281,000 10,455,000 9,554,000 161,000 1,388,000 967,000 545,000 311,000 653,000 14,862,000 12,030,000 1,807,000 341,000 871,000 14,000 24,039,000 22,393,000 39,020,000 34,437,000 119,000 Home Depot, Inc 01/30/2005 02/01/2004 US dollars US dollars 73,094,000 64,816,000 48,664,000 44,236,000 24,430,000 20,580,000 Net sales Cost of goods Gross profit R & D expenditures Sell, general & admin expenses Income before depreciation & amortization Depreciation & amortization Non-operating income/expense Interest expense Income before tax Provision for income taxes Minority interest Investments gains/losses Other income Net income before extra items Extra items & discontinued ops Net income 16,504,000 13,734,000 7,926,000 6,846,000 59,000 62,000 7,912,000 6,843,000 2,911,000 2,539,000 56,000 70,000 5,001,000 4,304,000 5,001,000 4,304,000 Breakeven Analysis 1/28/2005 1/30/2004 1/31/2003 Cost Type Lowes Stores Sales CGS Gross Profit Expenses 1,087 952 828 33.5455 32.3929 27.4286 22.2705 22.3414 19.0798 10.0515 0.0000 5.8592 0.1345 0.7763 0.1891 6.9590 3.0924 1.1712 1.9212 0.0158 1.9370 11.2751 0.0000 6.9568 0.1132 0.7902 0.1619 8.0221 3.2530 1.2511 2.0018 0.0000 2.0018 8.3487 0.0000 4.8582 0.1355 0.6723 0.1912 5.8571 2.4916 0.9380 1.5536 0.0126 1.5662 SG&A Store Opening Costs Depreciation Interest Total Expenses Pre-Tax Earnings ax Earnings from continuing ops Discontinued ops Net Income Calculate 63.43 Average ticket price per transaction (P) Transactions per store (Q) millions Variable expenses per transaction (VC) Fixed expenses (FC) Break Even 56.80 0.5288592 0.54708423 0.4828974 59.21 Sales Customer Transactions Budget for a Lowe's Store Plan for Actual for 2005 200g Variance Lowe's Sales CGS Gross Profit Expenses SG&A Store Opening Costs Depreciation Interest Total Expenses Pre-Tax Earnings Tax Earnings from continuing ops Discontinued ops Net Income Capital Budgeting Model Percentage of sales 2004 Assumptions 2004 2003 Growth in Sales Stores Sales CGS Gross Profit Expenses NPV PV Inflows PV Outflows SG&A Store Opening Costs Depreciation Interest Total Expenses Pre-Tax Earnings Tax Earnings from continuing ops Discontinued ops Net Income Estimated cost for one store Net working capital for one store Total cost for one store LT Growth assumption WACC 2% 9.5% Estimation of Cash Flows 2005 2006 2007 2008 2009 Estimation of Cash Flows 2005 2006 2007 2008 2009 Sales CGS SG&A Depreciation OCF Additional Cap NWC Additional NWC FCF V2009 Develop WACC 0.095 Valuation of Cash Flows 2005 2006 2007 2008 2009 Present Value of Inflows Present Value of Outflows NPV Sensitivity Analysis on Capital Budgeting Model Sales growth (same Py store Py inflows outflows Sensitivity of Project Value on Sales Growth PV inflows -PV outflows 1.20 1.00 8 0.80 0.80 0.40 0.20 0.00 Sales Growth (same store) Py Py CGS % inflows outflows Sensitivity of Project Value on CGS % _PV inflows-PV outflows 1.00 0.80 0.60 040 020 0.00 CGS % Lowe's Balance Sheet 1/28/2005 1/30/2004 1/31/2003 Assets Current Assets 853 273 172 3,968 58 244 5,568 10,352 29 160 530 283 Cash & quivalents ST Investments Accounts Receivables Merchandise Inventory Def Inc Taxes Other CA Total current assets 5,911 95 75 6,903 13,911 146 178 1,446 178 131 4,584 59 289 6,687 11,945 169 241 Net PPE LT Investments Other Assets Total Assets 21,138 19,042 16,109 Liabilities and Shareholder Equity Current Liabilities 50 29 1,943 Short term borrowings Current LT Debt Accounts Payable Employee retirement plans Accrued salaries and wages Self Insurance liability Def Revenue Other current liabilities Total current liabilities 630 2,695 2,366 74 335 386 467 539 931 5,648 3,060 736 159 9,603 11,535 306 LTD Def Inc Taxes Other LT Liab Total Liabilities Total Shareholder Equity 1,516 4,368 3,678 657 30 8,733 10,309 1,162 3,578 3,736 478 15 7,807 8,302 Total Liabilities and Shareholder Equities 21,138 19,042 16,109 Home Depot, Inc 01/30/2005 02/01/2004 US dollars US dollars Cash Marketable securities Receivables Inventories Raw materials Work in progress Finished goods Notes receivable Other current assets & prepaid expenses Total current assets Property, plant & equipment Accumulated depreciation Net property, plant & equipment Investments & advances to subsidiaries Other non-current assets Deferred charges & income Intangibles Deposits & other assets Total assets 506,000 2,826,000 26,000 1,499,000 1,097,000 10,076,000 9,076,000 1,659,000 10,076,000 9.076,000 303,000 14,273,000 13,328,000 28,437,000 24,594,000 5,711,000 4,531,000 22,726,000 20,063,000 533,000 369,000 84,000 833,000 129,000 39,020,000 34,437,000 1,412,000 240,000 01/30/2005 02/01/2004 US dollars US dollars Notes payable Accounts payable Current long term debt Current portion of cap leases Accrued expenses Income taxes Other current liabilities Total current liabilities Mortgages Deferred charges/income 5,766,000 5,159,000 502,000 7,000 2,971,000 2,430,000 175,000 1,546,000 1,281,000 10,455,000 9,554,000 1,000 10,000 161,000 1,388,000 967,000 Income taxes Other current liabilities Total current liabilities Mortgages Deferred charges/income Convertible debt Long term debt Non-current capital leases Other long term labilities Total liabilities Minority interest (liabilities) Other liabilities Shareholder Equity Total liabilities & net worth 175,000 1,546,000 1,281,000 10,455,000 9,554,000 161,000 1,388,000 967,000 545,000 311,000 653,000 14,862,000 12,030,000 1,807,000 341,000 871,000 14,000 24,039,000 22,393,000 39,020,000 34,437,000 119,000 Home Depot, Inc 01/30/2005 02/01/2004 US dollars US dollars 73,094,000 64,816,000 48,664,000 44,236,000 24,430,000 20,580,000 Net sales Cost of goods Gross profit R & D expenditures Sell, general & admin expenses Income before depreciation & amortization Depreciation & amortization Non-operating income/expense Interest expense Income before tax Provision for income taxes Minority interest Investments gains/losses Other income Net income before extra items Extra items & discontinued ops Net income 16,504,000 13,734,000 7,926,000 6,846,000 59,000 62,000 7,912,000 6,843,000 2,911,000 2,539,000 56,000 70,000 5,001,000 4,304,000 5,001,000 4,304,000 Breakeven Analysis 1/28/2005 1/30/2004 1/31/2003 Cost Type Lowes Stores Sales CGS Gross Profit Expenses 1,087 952 828 33.5455 32.3929 27.4286 22.2705 22.3414 19.0798 10.0515 0.0000 5.8592 0.1345 0.7763 0.1891 6.9590 3.0924 1.1712 1.9212 0.0158 1.9370 11.2751 0.0000 6.9568 0.1132 0.7902 0.1619 8.0221 3.2530 1.2511 2.0018 0.0000 2.0018 8.3487 0.0000 4.8582 0.1355 0.6723 0.1912 5.8571 2.4916 0.9380 1.5536 0.0126 1.5662 SG&A Store Opening Costs Depreciation Interest Total Expenses Pre-Tax Earnings ax Earnings from continuing ops Discontinued ops Net Income Calculate 63.43 Average ticket price per transaction (P) Transactions per store (Q) millions Variable expenses per transaction (VC) Fixed expenses (FC) Break Even 56.80 0.5288592 0.54708423 0.4828974 59.21 Sales Customer Transactions Budget for a Lowe's Store Plan for Actual for 2005 200g Variance Lowe's Sales CGS Gross Profit Expenses SG&A Store Opening Costs Depreciation Interest Total Expenses Pre-Tax Earnings Tax Earnings from continuing ops Discontinued ops Net Income Capital Budgeting Model Percentage of sales 2004 Assumptions 2004 2003 Growth in Sales Stores Sales CGS Gross Profit Expenses NPV PV Inflows PV Outflows SG&A Store Opening Costs Depreciation Interest Total Expenses Pre-Tax Earnings Tax Earnings from continuing ops Discontinued ops Net Income Estimated cost for one store Net working capital for one store Total cost for one store LT Growth assumption WACC 2% 9.5% Estimation of Cash Flows 2005 2006 2007 2008 2009 Estimation of Cash Flows 2005 2006 2007 2008 2009 Sales CGS SG&A Depreciation OCF Additional Cap NWC Additional NWC FCF V2009 Develop WACC 0.095 Valuation of Cash Flows 2005 2006 2007 2008 2009 Present Value of Inflows Present Value of Outflows NPV Sensitivity Analysis on Capital Budgeting Model Sales growth (same Py store Py inflows outflows Sensitivity of Project Value on Sales Growth PV inflows -PV outflows 1.20 1.00 8 0.80 0.80 0.40 0.20 0.00 Sales Growth (same store) Py Py CGS % inflows outflows Sensitivity of Project Value on CGS % _PV inflows-PV outflows 1.00 0.80 0.60 040 020 0.00 CGS %