Using the single store budget you prepared for Lowe's in Unit 4, calculate the variances using the 2011 budget and the 2012 actual results. Discuss the conclusion you draw from the analysis.

Assumptions Sales grow CGS % SG&A % Store Operating Costs Depreciation % Interest, net % Tax rate Budget For Lowe's Store Per Stor Sales CGS Gross Profit Expenses SG&A Store Ope Depreciati Interest Total Expe Pre-Tax Earnings Tax Earnings from conti Discontinued ops Net Income Number of Stores 2/3/2006 43,243 28,443 14,800 1/28/2005 1/30/2004 1/31/2003 36,464 30,838 26,112 24,208 21,269 18,164 12,256 9,569 7948 9,014 142 980 158 10,294 4,506 1,735 2,771 0 2,771 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,234 1,087 952 828 Standard 5.00% (expected % growth) 68.97% 18.09% 0.42% 2.40% 0.58% 37.87% (2004-2005) Store Operating Costs Depreciation % Interest, net % Budget For Lowe's Store Per Store Plan for 2005 Actual Variance Previous Year for 2005 (Actual- 1/30/2004 Budget) Lowe's Sales CGS Gross Profit Expenses SG&A Store Operating Costs Depreciation Interest Total Expenses Pre-Tax Earnings Tax Earnings From Continuing Ops Discontinued Ops Net Income 34.01 23.46 10.55 33.55 22.27 11.28 -0.47 -1.19 0.72 32.39 22.34 10.05 6.15 0.14 0.82 0.20 7.31 3.25 1.23 2.02 0.00 2.02 6.96 0.11 0.79 0.16 8.02 3.25 1.25 2.00 0.00 2.00 0.80 -0.03 -0.02 -0.04 0.72 0.01 0.02 -0.02 0.00 -0.02 5.86 0.13 0.78 0.19 6.96 3.09 1.17 1.92 0.02 1.94 Flexible 33.55 (actual sales for 2005) 68.97% 18.09% 0.42% 2.40% 0.58% 37.87% Plan for 2005 Actual for 2005 Variance (ActualBudget) 33.55 23.14 10.41 33.55 22.27 11.28 0.00 -0.87 0.87 6.07 0.14 0.80 0.20 7.21 3.20 1.21 1.99 0.00 1.99 6.96 0.11 0.79 0.16 8.02 3.25 1.25 2.00 0.00 2.00 0.89 -0.03 -0.01 -0.03 0.82 0.05 0.04 0.01 0.00 0.01 Home Depot Income Statements 1/29/2006 1/30/2005 81,511 54,191 27,320 73,094 48,664 24,430 16,485 1,472 17,957 9,363 15,256 1,248 16,504 7,926 62 -143 -81 9,282 3,444 5,838 56 -70 -14 7,912 2,911 5,001 Weighted average common shares Basic earnings per share Diluted weighted average common shares Diluted earnings per share 2,138 2.73 2,147 2.72 2,207 2.27 2,216 2.26 Number of stores Capital expenditures Square feet Number of customer transactions Average ticket 2,042 3,881 215 1,330 57.98 1,890 3,948 201 1,295 54.89 0.45 40 0.265 40.41 Sales Cost of goods sold Gross profit Operating expenses Selling, general and administrative Depreciation and amortization Total operating expenses Operating income Interest income (expense) Interest and investment income Interest expense Interest, net Earnings before taxes Provision for income taxes Net income Annual dividends Stock price Home Depot Balance Sheets 1/29/2006 1/30/2005 2/1/2004 Assets Current assets Cash and cash equivalents Short term investments Receivables, net Merchandise inventory Other current assets Total current assets 793 14 2,396 11,401 742 15,346 506 1,659 1,499 10,076 533 14,273 1,103 1,749 1,097 9,076 303 13,328 Property and equipment, at cost Land Buildings Furniture, fixtures and equipment Leasehold improvements Construction in progress Capital leases Gross property and equipment Less accumulated depreciation and amortization Net property and equipment 7,924 14,056 7,073 1,207 843 427 31,530 6,629 24,901 6,932 12,325 6,195 1,191 1,404 390 28,437 5,711 22,726 6,397 10,920 5,163 942 820 352 24,594 4,531 20,063 Notes receivable Cost in excess of fair value acquired Other assets Total assets 348 3,286 601 44,482 369 1,394 258 39,020 84 833 129 34,437 Liabilities and Shareholders' Equity Current liabilities Short term debt Accounts payable Accrued salaries and related expenses Sales taxes payable Deferred revenue Income taxes payable Current installment of long term debt Other accrued expenses Total current liabilities 900 6,032 1,176 488 1,757 388 513 1,647 12,901 0 5,766 1,055 412 1,546 161 11 1,504 10,455 0 5,159 801 419 1,281 175 509 1,210 9,554 Long term debt - excluding current Other long term liabilities Deferred income taxes Total liabilities 2,672 977 1,023 17,573 2,148 871 1,388 14,862 856 653 967 12,030 120 7,287 28,943 409 -138 119 6,650 23,962 227 -108 119 6,184 19,680 90 -76 -9,712 26,909 -6,692 24,158 -3,590 22,407 44,482 39,020 34,437 Stockholders' Equity Common stock (par=0.05, authorized: 10,000; issued 2,401 as of 1/29/06, 2385 as of 1/30/05; outstanding 2,124 as of 1/29/06, 2,185 as of 1/30/05) Paid in capital Retained earnings Accumulated other income Unearned compensation Treasury stock, at cost (277 shares at 1/29/06 200 shares at 1/30/05) Total Stockholders' Equity Total liabilities and stockholders' equity Capital Budgeting Model Example Capital Budgeting Model - for 2005 decisions by Home Depot Step 1: Select a sales growth assumption based on historical growth rates. 2004 Assumptions Percentage of sales 1/30/2005 Growth in Sales (same store) Stores Sales CGS Gross Profit Expenses SG&A Depreciation Interest, net Total Expenses Pre-Tax Earnings Tax Net Income Step 2: Each expense percentage is calculated as the expense item divided by the sales. 3.00% 1,890 38.67 25.75 12.93 66.5773% 8.07 0.66 -0.01 8.74 4.19 1.54 2.65 20.87% 1.71% -0.02% 22.60% $9.23 $36.78 $27.55 Step 3: The tax rate is the historical tax expense as a percentage of the pretax earnings. 36.79% Estimated cost for one store Net working capital for one store Total cost for one store LT Growth assumption WACC 25.53289 2.020106 27.553 2% 9.5% Sales CGS SG&A Depreciation OCF 2005 39.8343 26.5206 8.3141 0.6801 3.4104 2006 41.0293 27.3162 8.5635 0.7005 3.5127 2007 42.2602 28.1357 8.8204 0.7215 3.6181 2008 43.5280 28.9798 9.0851 0.7432 3.7266 2009 44.8339 29.8492 9.3576 0.7655 3.8384 0.6801 2.0807 0.0606 2.6696 0.7005 2.1431 0.0624 2.7497 0.7215 2.2074 0.0643 2.8322 0.7432 2.2736 0.0662 2.9172 0.7655 2.3419 0.0682 3.0047 0.05223411 Step 4: Sales is estimated using the prior year sales level together with the sales growth assumption. Estimation of Cash Flows Additional Cap Exp NWC Additional NWC FCF NPV PV Inflows PV Outflows V2009 40.8638 Develop WACC Step 5: Expenses are estimated using the forecasted sales together with the expense percentage assumptions. Step 6: Operating Cash Flows are calculated as sales less expenses after taxes and then adding back depreciation expenses. Depreciation is added back because it is a non-cash item. Interest expenses are not included as they are factored into the discount rate. Step 7: Free Cash Flows are defined as Operating Cash Flows less additional contribution that must be invested in capital expenditures and net working capital. Step 8: V2009 is an estimate of the value of free cash flows beyond 2009. This value exists at the end of 2009. A formula called the constant growth model is applied to generate this value. 0.095 Valuation of Cash Flows 2005 2.6696 Present Value of Inflows $36.78 Present Value of Outflows $27.55 NPV $9.23 2006 2.7497 2007 2.8322 2008 2.9172 2009 43.8685 Step 9: The WACC is the weighted average cost of capital. Essentially it is the average interest rate or return that is due to be paid to stockholders, bondholders and bankers. Step 10: The Present value of the inflows is the cash flows discounted back to the present time period, year 2004 in this case. Sales growth (same PV PV store) inflows outflows -10.0% 22.96 27.55 -9.0% 23.83 27.55 -8.0% 24.73 27.55 -7.0% 25.66 27.55 -6.0% 26.62 27.55 -5.0% 27.61 27.55 -4.0% 28.64 27.55 -3.0% 29.70 27.55 -2.0% 30.79 27.55 -1.0% 31.92 27.55 0.0% 33.08 27.55 1.0% 34.28 27.55 2.0% 35.51 27.55 3.0% 36.78 27.55 4.0% 38.10 27.55 5.0% 39.45 27.55 Valu e an d Co st Sensitivity Analysis on Capital Budgeting Model Sensitivity of Project Val 45.00 40.00 35.00 30.00 25.00 20.00 15.00 10.00 5.00 0.00 PV inflows Sales Gro -5.06% PV PV CGS % inflows outflows 64.58% 43.72 27.55 65.58% 40.25 27.55 66.58% 36.78 27.55 67.58% 33.31 27.55 68.58% 29.85 27.55 69.58% 26.88 27.55 70.58% 22.91 27.55 Critical value ### Va lue a nd Cost Critical value Sensitivity of Project 50.00 PV inflow 40.00 30.00 20.00 10.00 0.00 C Sensitivity of Project Value on Sales Growth PV inflows PV outflows Sales Growth (same store) Sensitivity of Project Value on CGS % PV inflows CGS % PV outflows