In support of making decisions to solve problems or analyze opportunities, you may be asked to prepare an analysis of actual and forecasted data. Knowing what data to consider, what technique to use to analyze the data, and the format in which to present the analysis is where your expertise will be applied. The analysis you will complete in this project provides you an opportunity to demonstrate these skills. The objective of this project is to use publicly available information to form an analysis of financial data reported by Lowe's to justify or not justify further expansion in the upcoming year of 2005. The analysis will touch on a number of the techniques employed in the course, including financial ratio analysis, breakeven analysis, budgeting, and capital budgeting. An important part of this course is analyzing the variety of available accounting tools and techniques to determine which one(s) to use. Therefore, as you apply these techniques to this particular project, consider how each technique can be applied to the other decisions that may be presented to you. Background It is January 28, 2005 and Mr. Robert A. Niblock is contemplating expansion plans during the upcoming year of 2005. He has an important meeting with several influential board members and he would like to provide a case regarding further expansion. The company has been enjoying recent success and his feeling is that further expansion is both justified and important although he wants to quantify an analysis that supports his sentiment. He is looking to his staff of management accounts to formulate a presentation with relevant analyses that he can make to the board members. He is looking for several types of analyses: Analysis of financial ratios through time for Lowe's using both tables and graphics. Analysis of financial ratios for Lowe's relative to financial ratios for Home Depot using both tables and graphics. Development of a breakeven analysis for Lowe's using both tables and graphics. Development of an initial (income statement) budget per store. Development of a capital budgeting model. The details of each analysis are presented in the unit in which they are due. The whole of the document with all of the analyses is due at the end of the term. An epilogue to the report will also require you to complete a variance assessment of the single store budget relative to the actual 2005 results that can be generated from data in the 2005 annual report. Executive Report The final executive report should provide a compilation of the supporting analyses presented in a professional manner using a combination of tables, graphics, charts, and explanations. Include the following components in your executive report. Cover page. Table of Contents. Executive Summary. Include a description of the analyses of the report together with the primary findings and conclusions. Also include an evaluation of the data analysis techniques used for this analysis. Discuss the value of each technique, and indicate which one you felt was most important to you for this situation and why. Financial Statements. Include a presentation of financial statements for the years 2002, 2003, and 2004. Financial Ratios. Include a presentation of relevant financial ratios using both tables and graphics, and a discussion of the key conclusions regarding the financial ratios. Breakeven analysis. Include a presentation of a breakeven analysis for 2004 data using both tables and graphics, as well as a discussion of the assumptions and the primary results of the analysis. Initial Budget. Include a presentation of a single store budget using relevant techniques for 2004 data. Capital Budgeting Model. Include a presentation of a capital budgeting model for a single store using relevant methods for 2004, as well as a presentation of graphics that show how the model adjusts to changes in sales growth, expenses, net working capital requirements, and costs per store. Also include a discussion of the assumptions, development, and primary conclusions from the capital budgeting model. Conclusion. Considering all the analyses together, make an argument for whether or not Lowe's should open a new store and an explanation of how you reached that conclusion. Epilogue. Considering that in this situation you have access to actual data for 2005, you have an opportunity to assess your conclusions. Include a presentation of standard and flexible budgeting techniques to calculate variances based on data from the 2005 annual report, as well as a discussion of the variances.

Professional Challenge Data Lowe's Income Statements 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 Additional Metrics Number of Stores Square Footage Number of Employees Average ticket price for customers Shares outstanding LT Assets Land Building Equipment Estimated cost for one store Net working capital for one store Tom Steiner Consulting ### ### ### ### 1/28/2005 36,464 24,208 12,256 1/30/2004 30,838 21,269 9,569 1/31/2003 26,112 18,164 7948 ### ### ### ### ### ### ### ### ### ### 7,562 123 859 176 8,720 3,536 1,360 2,176 0 2,176 5,578 128 739 180 6,625 2,944 1,115 1,829 15 1,844 4,625 129 640 182 5,576 2,372 893 1,479 12 1,491 ### ### ### ### ### 1,087 124 161,964 63.43 774 952 109 147,052 59.21 787 828 95 120,692 56.80 782 ### ### ### ### ### 4,197 7,007 5,405 19.77 1.15 3,635 5,950 4,355 16.55 2.44 3,133 5,092 3,663 14.36 2.40 Page 1 02/15/2013 Lowe's Balance Sheet ### 1/28/2005 1/30/2004 1/31/2003 Assets Current Assets Cash & Equivalents ST Investments Accounts Receivables Merchandise Inventory Def Inc Taxes Other CA Total current assets Net PPE LT Investments Other Assets ### ### ### ### ### ### ### ### ### ### 530 283 9 5,911 95 75 6,903 13,911 146 178 1,446 178 131 4,584 59 289 6,687 11,945 169 241 853 273 172 3,968 58 244 5,568 10,352 29 160 Total Assets ### 21,138 19,042 16,109 ### ### ### ### ### ### ### ### ### ### ### ### ### ### 0 630 2,695 0 386 467 539 931 5,648 3,060 736 159 9,603 11,535 0 77 2,366 74 335 0 0 1,516 4,368 3,678 657 30 8,733 10,309 50 29 1,943 88 306 0 0 1,162 3,578 3,736 478 15 7,807 8,302 Total Liabilities and Shareholder Equities ### 21,138 19,042 16,109 Liabilities and Shareholder Equity Current Liabilities 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 LTD Def Inc Taxes Other LT Liab Total Liabilities Total Shareholder Equity Tom Steiner Consulting Page 2 02/15/2013 Home Depot, Inc. 01/29/2006 01/30/2005 02/01/2004 Us dollars US dollars US dollars Cash ### 506,000 2,826,000 Marketable securities ### 1,659,000 26,000 Receivables ### 1,499,000 1,097,000 Inventories ### 10,076,000 9,076,000 Raw materials ### 0 0 Work in progress ### 0 0 Finished goods ### 10,076,000 9,076,000 Notes receivable ### 0 0 Other current assets & prepaid expenses ### 533,000 303,000 Total current assets ### 14,273,000 13,328,000 Property, plant & equipment ### 28,437,000 24,594,000 Accumulated depreciation ### 5,711,000 4,531,000 Net property, plant & equipment ### 22,726,000 20,063,000 Investments & advances to subsidiaries ### 0 0 Other non-current assets ### 369,000 84,000 Deferred charges & income ### 0 0 Intangibles ### 1,412,000 833,000 Deposits & other assets ### 240,000 129,000 Total assets ### 39,020,000 34,437,000 01/29/2006 01/30/2005 Us dollars US dollars ### 0 ### 5,766,000 ### 1,000 ### 10,000 ### 2,971,000 ### 161,000 ### 1,546,000 ### 10,455,000 ### 0 ### 1,388,000 ### 0 ### 1,807,000 ### 341,000 ### 871,000 ### 14,862,000 ### 0 ### 119,000 ### 24,039,000 ### 39,020,000 02/01/2004 US dollars 0 5,159,000 502,000 7,000 2,430,000 175,000 1,281,000 9,554,000 0 967,000 0 545,000 311,000 653,000 12,030,000 0 14,000 22,393,000 34,437,000 01/29/2006 01/30/2005 Us dollars US dollars Net sales ### 73,094,000 Cost of goods ### 48,664,000 Gross profit ### 24,430,000 R & D expenditures ### 0 Sell, general & admin expenses ### 16,504,000 Income before depreciation & amortization### 7,926,000 Depreciation & amortization ### 0 Non-operating income/expense ### 56,000 Interest expense ### 70,000 Income before tax ### 7,912,000 Provision for income taxes ### 2,911,000 Minority interest ### 0 Investments gains/losses ### 0 Other income ### 0 Net income before extra items ### 5,001,000 Extra items & discontinued ops ### 0 Net income ### 5,001,000 02/01/2004 US dollars 64,816,000 44,236,000 20,580,000 0 13,734,000 6,846,000 0 59,000 62,000 6,843,000 2,539,000 0 0 0 4,304,000 0 4,304,000 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 Convertible debt Long term debt Non-current capital leases Other long term liabilities Total liabilities Minority interest (liabilities) Other liabilities Shareholder Equity Total liabilities & net worth Home Depot, Inc. Tom Steiner Consulting Page 3 02/15/2013 Ratio Analysis Lowes Lowe's ( 2 / 3 / 2 0 0 6 )(1/28/2005) Performance Ratios ROE ROA PM Lowe's Lowe's Home Depot 1/30/2004) (1/31/2003) (1/29/2005) ### ### ### 18.86% 10.29% 5.97% 17.89% 9.68% 5.98% 17.96% 9.26% 5.71% 20.80% 12.82% 6.84% ### ### ### 5.97% 1.7250 1.8325 5.98% 1.6195 1.8471 5.71% 1.6210 1.9404 6.84% 1.8732 1.6232 Asset Management Inventory Turnover Inventory Days Average Collection Period Total Asset Turnover ### ### ### ### 6.17 89.12 0.09 1.7250 6.73 78.67 1.55 1.6195 6.58 79.74 2.40 1.6210 7.25 75.57 7.49 1.8732 Liquidity Ratios Current Ratio Quick Ratio ### ### 2.2012 1.5857 2.1805 1.6556 2.0634 1.5551 2.6255 1.9475 Debt Ratios Debt Ratio Equity Multiplier ### ### 45.43% 1.8325 45.86% 1.8471 48.46% 1.9404 38.09% 1.6232 ROE Decomposition PM TAT EM Tom Steiner Consulting Page 5 02/15/2013 Home Lowe's Lowe's Depot (2/3/06) (1/28/05) (1/29/06) ROE PM TAT EM Tom Steiner Consulting 10 9 8 7 6 5 4 3 2 1 0 Lowe's (2/3/06) Lowe's (1/28/05) Home Depot (1/29/06) Lowe's (2/3/06) Lowe's (1/28/05) Home Depot (1/29/06) Lowe's (2/3/06) Lowe's (1/28/05) Home Depot (1/29/06) Lowe's (2/3/06) Lowe's (1/28/05) Home Depot (1/29/06) ROE 10 9 8 7 6 5 4 3 2 1 0 PM 10 9 8 7 6 5 4 3 2 1 0 TAT 10 9 8 7 6 5 4 3 2 1 0 EM Page 6 02/15/2013 Breakeven Analysis ### 1/28/2005 1/30/2004 1/31/2003 Cost Type Stores 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 ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### 1,087 33.5455 22.2705 11.2751 0.0000 6.9568 0.1132 0.7902 0.1619 8.0221 3.2530 1.2511 2.0018 0.0000 2.0018 952 32.3929 22.3414 10.0515 0.0000 5.8592 0.1345 0.7763 0.1891 6.9590 3.0924 1.1712 1.9212 0.0158 1.9370 Average ticket price per transaction (P) Transactions per store (Q) - millions Variable expenses per transaction (VC) Fixed expenses (FC) Break Even Sales Customer Transactions ### 63.43 59.21 56.80 1 0.52885919 0.54708423 0.48289738 ### 42.1103949 40.8371973 39.511152 ### 10.05 8.76 7.16 Lowes 828 27.4286 19.0798 8.3487 0.0000 4.8582 0.1355 0.6723 0.1912 5.8571 2.4916 0.9380 1.5536 0.0126 1.5662 Calculate Tom Steiner Consulting Page 7 ### ### 29.89 0.4713 28.24 0.4769 23.52 0.4141 02/15/2013 Graphical Breakeven Analysis Breakeven Analysis for Sales Tom Steiner Consulting Variable Fixed Total Unit Sales Sales Expenses Expenses Expenses 0.3000 19.03 12.63 10.05 22.68 0.3250 20.61 13.69 10.05 23.73 0.3500 22.20 14.74 10.05 24.79 0.3750 23.79 15.79 10.05 25.84 0.4000 25.37 16.84 10.05 26.89 0.4250 26.96 17.90 10.05 27.94 0.4500 28.54 18.95 10.05 29.00 0.4750 30.13 20.00 10.05 30.05 0.5000 31.72 21.06 10.05 31.10 Breakeven Sales Level Sales Variable Expenses Fixed Expenses T otal Expenses 35.00 30.00 25.00 20.00 15.00 10.00 5.00 0.00 0.3000 0.3250 0.3500 0.3750 0.4000 0.4250 0.4500 0.4750 0.5000 Unit s (millions) Page 8 02/15/2013 My assumption was that sales would follow the standard national inflation rate for the country. Budget for a Lowe's Store All of the variances are within less than a percentage point between actual and planned budgets. Plan for 2005 Actual for 2005 Variance Lowe's Tom Steiner Consulting 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 33.36464 33.55 23.01163 22.27047 10.35301 11.27507 6.035021 0.138487 0.799548 0.194748 7.167805 3.18521 1.206355 1.978855 0.016229 1.995084 6.956762 0.113155 0.790248 0.161914 8.022079 3.25299 1.25115 2.00184 0 2.00184 This could mean that the stores on average all performed within standard parameters. However, some stores could have performed beyond the standard deviation and others below. 0.18 -0.74 0.92 0.00 0.92 -0.03 -0.01 -0.03 0.85 0.07 0.04 0.02 -0.02 0.01 Page 9 02/15/2013 Capital Budgeting Model Assumptions Percentage of sales 2004 2004 Growth in Sales Stores 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 Estimated cost for one store Net working capital for one store Total cost for one store LT Growth assumption WACC 2003 3.00% 952 32.39 22.34 10.05 3.00% 828 31.54 21.94 9.60 5.86 0.13 0.78 0.19 6.96 3.09 1.17 1.92 0.02 1.94 5.59 0.16 0.77 0.22 6.73 2.86 1.08 1.79 0.01 1.80 16.55 2.44 18.98 2% 9.5% 14.36 2.40 16.76 2005 33.3646 23.0116 6.0350 0.7995 2.9854 2006 34.3656 23.7020 6.2161 0.8235 3.0750 2007 35.3965 24.4130 6.4026 0.8482 3.1672 2008 36.4584 25.1454 6.5946 0.8737 3.2623 2009 37.5522 25.8998 6.7925 0.8999 3.3601 0.7995 2.5090 0.0731 2.1128 0.8235 2.5843 0.0753 2.1762 0.8482 2.6618 0.0775 2.2415 0.8737 2.7417 0.0799 2.3087 0.8999 2.8239 0.0822 2.3780 68.97% NPV PV Inflows PV Outflows $10.13 $29.11 $18.98 18.09% 0.42% 2.40% 0.58% 21.48% 37.87% 7.52% Estimation of Cash Flows Sales CGS SG&A Depreciation OCF Additional Cap NWC Additional NWC FCF V2009 32.3405 Develop WACC 0.095 Valuation of Cash Flows Tom Steiner Consulting 2005 2.1128 Present Value of Inflows Present Value of Outflows NPV 2006 2.1762 2007 2.2415 2008 2.3087 2009 34.7185 $29.11 18.98 $10.13 Page 10 02/15/2013 Sensitivity Analysis on Capital Budgeting Model Sales growth (same store) PV PV inflows outflows Sensitivity of Project Value on Sales Growth PV inflows PV outflows Va lue a nd C os t 12 10 8 6 4 2 0 Sales Growt h (same st ore) Tom Steiner Consulting PV PV inflows outflows Sensit ivity of Project Value on CGS % PV inflows PV outflows 12 Va lue a nd C os t CGS % 10 8 6 4 2 0 CGS % Page 11 02/15/2013