Capital Budgeting Model

profileswapma
week5capitalbudgetingexample.xls
Home>Business & Finance homework help>Capital Budgeting Model

Capital Budgeting

Sample only
Capital Budgeting Model - for 2005 decisions by Home Depot
2004
Assumptions 1/30/05 Percentage of sales
Growth in Sales (same store) 3.00%
Stores 1,890
Sales 38.67 NPV $9.23
CGS 25.75 66.5773% PV Inflows $36.78
Gross Profit 12.93 PV Outflows $27.55
Expenses
SG&A 8.07 20.87%
Depreciation 0.66 1.71%
Interest, net -0.01 -0.02%
Total Expenses 8.74 22.60%
Pre-Tax Earnings 4.19
Tax 1.54 36.79%
Net Income 2.65
Estimated cost for one store 25.5328947368
Net working capital for one store 2.0201058201 0.0522341095
Total cost for one store 27.5530005569
LT Growth assumption 2%
WACC 9.5%
Estimation of Cash Flows
2005 2006 2007 2008 2009
Sales 39.8343 41.0293 42.2602 43.5280 44.8339
CGS 26.5206 27.3162 28.1357 28.9798 29.8492
SG&A 8.3141 8.5635 8.8204 9.0851 9.3576
Depreciation 0.6801 0.7005 0.7215 0.7432 0.7655
OCF 3.4104 3.5127 3.6181 3.7266 3.8384
Additional Cap Exp 0.6801 0.7005 0.7215 0.7432 0.7655
NWC 2.0807 2.1431 2.2074 2.2736 2.3419
Additional NWC 0.0606 0.0624 0.0643 0.0662 0.0682
FCF 2.6696 2.7497 2.8322 2.9172 3.0047
V2009
40.8638
Develop WACC
0.095
Valuation of Cash Flows
2005 2006 2007 2008 2009
2.6696 2.7497 2.8322 2.9172 43.8685
Present Value of Inflows
$36.78
Present Value of Outflows
$27.55
NPV
$9.23
&CCapital Budgeting Model Example
Step 1: Select a sales growth assumption based on historical growth rates.
Step 2: Each expense percentage is calculated as the expense item divided by the sales.
Step 3: The tax rate is the historical tax expense as a percentage of the pre-tax earnings.
Step 4: Sales is estimated using the prior year sales level together with the sales growth assumption.
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.
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.

Income Statement

Sample only Actual income statement for HD Actual income Statement for Lowes
Home Depot Home Depot Income Statements FY 2011 FY 2010 FY 2009 Lowe's
1/30/12 1/30/11 1/31/10
Income Statements Sales 67,997 66,176 71,288 Income Statements
1/29/06 1/30/05 COGS 44,693 43,764 47,298 2/3/12 1/28/11 1/29/10
Gross Profit 23,304 22,412 23,990
Sales 81,511 73,094 Expenses Sales 50,208 48,815 47,220
Cost of goods sold 54,191 48,664 SG&A 15,849 15,902 17,846 Cost of goods sold 32,858 31,663 30,757
Gross profit 27,320 24,430 Depreciation 1,616 1,707 1,785 Gross profit 17,350 17,152 16,463
Operating expenses Interest 530 676 624 Operating expenses
Selling, general and administrative 16,485 15,256 other 51 163 163 Selling, general and administrative 12,593 12,006 11,737
Depreciation and amortization 1,472 1,248 Total Expenses 18,046 18,448 20,418 Depreciation and amortization 1,480 1,586 1,614
Total operating expenses 17,957 16,504 Pre-Tax Earnings 6,068 5,273 3,982 Total operating expenses 14,073 13,592 13,351
Operating income 9,363 7,926 Tax 2,185 1,935 1,362 Operating income 3,277 3,560 3,112
Interest income (expense) Earnings from continuing ops 3,883 3,338 2,620 Interest, net 371 332 287
Interest and investment income 62 56 Discontinued ops 0 0 41 Earnings before taxes 2,906 3,228 2,825
Interest expense -143 -70 Net Income 3,883 3,338 2,620 Taxes 1,067 1,218 1,042
Interest, net -81 -14 Earning continuous ops 1,839 2,010 1,783
Earnings before taxes 9,282 7,912 Additional Metrics Net income 1,839 2,010 1,783
Provision for income taxes 3,444 2,911 Number of Stores 2,252 2,248 2,244
Net income 5,838 5,001 Square Footage 235 235 235
Number of Employees 331,000 321,000 317,000 Weighted average common shares 1,271 1,401 1,462
Average ticket price for customers 53.28 51.93 52 Basic earnings per share 1.43 1.42 1.21
Weighted average common shares 2,138 2,207 Shares outstanding 1.537 1.623 1.698 Diluted weighted average common shares 1,273 1,403 1,464
Basic earnings per share 2.73 2.27 LT Assets Diluted earnings per share 1.43 1.42 1.21
Diluted weighted average common shares 2,147 2,216 Land 8,480 8,497 8,451
Diluted earnings per share 2.72 2.26 Building 17,737 17,606 17,391 Number of stores 1,745 1,749 1,710
Equipment 10,040 9,687 9,091 Capital expenditures 1,829 1,329 1,799
Number of stores 2,042 1,890 Estimated cost for one store 16.10 15.92 15.57 Square feet 197 197 193
Capital expenditures 3,881 3,948 Net working capital for one store 2.28 1.49 1.58 Number of customer transactions 810 786 766
Square feet 215 201 Average ticket 62 62.07 61.66
Number of customer transactions 1,330 1,295 Weighted Average Common Shares 1,562 1,648 1,683
Average ticket 57.98 54.89 Basic Earnings per share 2.03 1.58 1.34 Annual dividends 0.53 0.42 0.360
Diluted weighted Avg common share 1,658 1,692 1686.00
Annual dividends 0.45 0.265 Diluted earnings per share 2.01 1.57 1.34
Stock price 40 40.41

Balance Sheet

Sample only Actual balance sheet HD Actual balance sheet Lowes
Home Depot Home Depot Income Statements FY 2011 FY 2010 FY 2009 Lowe's Balance Sheet (need to add FY 2009)
1/30/12 1/30/11 1/31/10 FY2011 FY 2010
Balance Sheets Sales 67,997 66,176 71,288 2/3/12 1/28/11
1/29/06 1/30/05 2/1/04 COGS 44,693 43,764 47,298 Assets
Gross Profit 23,304 22,412 23,990 Current assets
Assets Expenses Cash and cash equivalents 1,014 652
Current assets SG&A 15,849 15,902 17,846 Short term investments 286 471
Cash and cash equivalents 793 506 1,103 Depreciation 1,616 1,707 1,785 Merchandise inventory - net 8,355 8,321
Short term investments 14 1,659 1,749 Interest 530 676 624 Deferred income taxes - net 183 193
Receivables, net 2,396 1,499 1,097 other 51 163 163 Other current assets 234 330
Merchandise inventory 11,401 10,076 9,076 Total Expenses 18,046 18,448 20,418 Total current assets 10,072 9,967
Other current assets 742 533 303 Pre-Tax Earnings 6,068 5,273 3,982
Total current assets 15,346 14,273 13,328 Tax 2,185 1,935 1,362 Property and equipment, at cost
Earnings from continuing ops 3,883 3,338 2,620 Land 6,936 6,742
Property and equipment, at cost Discontinued ops 0 0 41 Buildings an building improvements 16,640 16,531
Land 7,924 6,932 6,397 Net Income 3,883 3,338 2,620 Equipment 9,835 9,142
Buildings 14,056 12,325 10,920 Construction in progress 921 930
Furniture, fixtures and equipment 7,073 6,195 5,163 Additional Metrics 34,332 33,345
Leasehold improvements 1,207 1,191 942 Number of Stores 2,252 2,248 2,244 Less accumulated depreciation and amortization 12,362 11,256
Construction in progress 843 1,404 820 Square Footage 235 235 235 21,970 22,089
Capital leases 427 390 352 Number of Employees 331,000 321,000 317,000
Gross property and equipment 31,530 28,437 24,594 Average ticket price for customers 53.28 51.93 52 Long-term Investments 504 1,008
Less accumulated depreciation and amortization 6,629 5,711 4,531 Shares outstanding 1.537 1.623 1.698 Other assets 1,013 635
Net property and equipment 24,901 22,726 20,063 LT Assets Total Assets 33,559 33,699
Land 8,480 8,497 8,451
Notes receivable 348 369 84 Building 17,737 17,606 17,391 Liabilities and Shareholders' Equity
Cost in excess of fair value acquired 3,286 1,394 833 Equipment 10,040 9,687 9,091 Current liabilities
Other assets 601 258 129 Estimated cost for one store 16.10 15.92 15.57 Short term debt 592 36
Total assets 44,482 39,020 34,437 Net working capital for one store 2.28 1.49 1.58 Accounts payable 4,352 4,351
Accrued salaries and related expenses 613 667
Liabilities and Shareholders' Equity Weighted Average Common Shares 1,562 1,648 1,683 Deferred revenue 801 707
Current liabilities Basic Earnings per share 2.03 1.58 1.34 Other current liabilities 1,533 1,358
Short term debt 900 0 0 Diluted weighted Avg common share 1,658 1,692 1686.00 Total current liabilities 7,891 7,119
Accounts payable 6,032 5,766 5,159 Diluted earnings per share 2.01 1.57 1.34 Long Term Debt
Accrued salaries and related expenses 1,176 1,055 801 Long term debt - excluding current 7,035 6,537
Sales taxes payable 488 412 419 Deferred income taxes 531 467
Deferred revenue 1,757 1,546 1,281 Other long term liabilities 865 833
Income taxes payable 388 161 175 Deferred revenue - extended protection plans 704 631
Current installment of long term debt 513 11 509 Total liabilities 17,026 15,587
Other accrued expenses 1,647 1,504 1,210
Total current liabilities 12,901 10,455 9,554 Stockholders' Equity
Preferred Stock - $5 par value, none issued
Long term debt - excluding current 2,672 2,148 856 Common Stock - $.50 par value;
Other long term liabilities 977 871 653 Shares issued and outstanding
Deferred income taxes 1,023 1,388 967 February 3,2012 1,241
Total liabilities 17,573 14,862 12,030 January 28,2011 1,354 621 677
Capital in excess of par value 14 11
Stockholders' Equity Retained earnings 15,852 17,371
Common stock (par=0.05, authorized: 10,000; Accumulated other income 46 53
issued 2,401 as of 1/29/06, 2385 as of 1/30/05; Total Stockholders' Equity 16,533 18,112
outstanding 2,124 as of 1/29/06, 2,185 as of
1/30/05) 120 119 119 Total liabilities and stockholders' equity 33,559 33,699
Paid in capital 7,287 6,650 6,184
Retained earnings 28,943 23,962 19,680
Accumulated other income 409 227 90
Unearned compensation -138 -108 -76
Treasury stock, at cost (277 shares at 1/29/06
200 shares at 1/30/05) -9,712 -6,692 -3,590
Total Stockholders' Equity 26,909 24,158 22,407
Total liabilities and stockholders' equity 44,482 39,020 34,437

Graphics

Sample only
Sensitivity Analysis on Capital Budgeting Model
Sales growth (same store) PV inflows PV 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
Critical value -5.06%
CGS % PV inflows PV 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 69.2373%

Graphics
PV inflows
PV outflows
Sales Growth (same store)
Value and Cost
Sensitivity of Project Value on Sales Growth
PV inflows
PV outflows
CGS %
Value and Cost
Sensitivity of Project Value on CGS %