《NYSF_Walmart_Solutionv2.xlsx》由会员分享，可在线阅读，更多相关《NYSF_Walmart_Solutionv2.xlsx（40页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、The Analyst ExchangeOn - Line Td130fad4f5cee25781d57d7d132a02f5.xlsx, Income Statement1 of 40 ActualsEstimates 2010A2011A2012A2013E2014E2015E2016E2017E Net Sales405,132.0418,952.0443,854.0 % Growth3.4%5.9% Membership and Other Income2,953.02,897.03,096.0 % Growth-1.9%6.9% Total revenue408,085.0421,8
2、49.0446,950.0473,767.0497,455.4517,353.6532,874.2543,531.7 Y/Y revenue growth (%)3.4%6.0%6.0%5.0%4.0%3.0%2.0% Cost of goods sold304,106.0314,946.0335,127.0355,325.3373,091.5388,015.2399,655.6407,648.7 COGS as a % of revenue74.5%74.7%75.0%75.0%75.0%75.0%75.0%75.0% Gross profit103,979.0106,903.0111,82
3、3.0118,441.8124,363.8129,338.4133,218.5135,882.9 Gross profit margin (%)25.5%25.3%25.0%25.0%25.0%25.0%25.0%25.0% Selling, general and administrative72,820.073,720.077,135.081,961.786,059.889,502.292,187.294,031.0 SG&A as a % of revenue17.8%17.5%17.3%17.3%17.3%17.3%17.3%17.3% 31,159.033,183.034,688.0
4、36,480.138,304.139,836.241,031.341,851.9 EBITDA margin (%)7.6%7.9%7.8%7.7%7.7%7.7%7.7%7.7% Depreciation and amortization7,157.07,641.08,130.08,591.79,188.69,809.410,448.911,101.1 24,002.025,542.026,558.027,888.429,115.530,026.830,582.430,750.8 EBIT margin (%)5.9%6.1%5.9%5.9%5.9%5.8%5.7%5.7% Interest
5、 expense (Debt)1,787.01,928.02,034.02,083.82,083.82,083.82,083.82,083.8 Interest expense (Capital Leases)278.0277.0288.0281.6281.6281.6281.6281.6 Interest income(181.0)(201.0)(162.0)(168.5)(193.3)(234.6)(281.8)(344.9) Net interest expense1,884.02,004.02,160.02,197.02,172.12,130.82,083.72,020.5 22,11
6、8.023,538.024,398.025,691.426,943.327,896.028,498.828,730.3 EBT margin (%)5.4%5.6%5.5%5%5%5%5%5% Income tax expense7,156.07,579.07,944.08,478.28,891.39,205.79,404.69,481.0 Tax rate (%)32.4%32.2%32.6%33.0%33.0%33.0%33.0%33.0% 14,962.015,959.016,454.017,213.218,052.018,690.319,094.219,249.3 The Analys
7、t ExchangeOn - Line Td130fad4f5cee25781d57d7d132a02f5.xlsx, Income Statement2 of 40 ActualsEstimates 2010A2011A2012A2013E2014E2015E2016E2017E Discontinued operations(79.0)1,034.0(67.0)0.00.00.00.00.0 Other0.00.00.00.00.00.00.00.0 Total non-recurring events(79.0)1,034.0(67.0)0.00.00.00.00.0 14,883.01
8、6,993.016,387.017,213.218,052.018,690.319,094.219,249.3 Income attributable to non-controlling interests(513.0)(604.0)(688.0)(723.0)(758.2)(785.0)(802.0)(808.5) Non-controlling interests % of Net Income3.4%3.6%4.2%4.2%4.2%4.2%4.2%4.2% 14,370.016,389.015,699.016,490.317,293.817,905.318,292.218,440.8
9、Basic3.724.484.544.915.305.665.976.22 Diluted3.714.474.524.905.295.655.966.21 Basic3,8663,6563,4603,3613,2623,1633,0642,965 Diluted3,8773,6703,4743,3663,2673,1683,0692,970 The Analyst ExchangeOn - Line Td130fad4f5cee25781d57d7d132a02f5.xlsx, Cash Flow Statement3 of 40 ActualsEstimates 2010A2011A2012
10、A2013E2014E2015E2016E2017E Net income14,883.016,993.016,387.017,213.218,052.018,690.319,094.219,249.3 Loss (income) from discontinued operations79.0(1,034.0)67.00.00.00.00.00.0 Depreciation and amortization7,157.07,641.08,130.08,591.79,188.69,809.410,448.911,101.1 Deferred income taxes(504.0)651.01,
11、050.0715.91,003.5791.0596.3411.4 Other Operating Activities318.01,087.0398.0318.0318.0318.0318.0318.0 Changes in operating working capital Changes in accounts receivable(297.0)(733.0)(796.0)146.5(289.5)(243.2)(189.7)(130.3) Changes in inventory2,213.0(3,205.0)(3,727.0)(148.4)(2,043.1)(1,716.2)(1,338
12、.7)(919.2) Changes in prepaid expenses and other0.00.00.0(773.9)(122.9)(103.3)(80.6)(55.3) Changes in accounts payable1,052.02,676.02,687.0701.21,865.51,567.01,222.2839.3 Changes in accrued liabilities1,348.0(433.0)59.01,425.7979.0822.3641.4440.5 Changes in accrued income taxes0.00.00.0(459.8)34.326
13、.116.56.3 Net changes in operating working capital4,316.0(1,695.0)(1,777.0)891.3423.2352.8271.3181.3 Total cash flows from operating activities26,249.023,643.024,255.027,730.128,985.329,961.530,728.731,261.1 Payments for property and equipment (CAPEX)(12,184.0)(12,699.0)(13,510.0)(14,213.0)(14,923.7
14、)(15,520.6)(15,986.2)(16,305.9) Capex as a % of Revenue3.0%3.0%3.0%3.0%3.0%3.0%3.0%3.0% Proceeds from disposal of property and equipment1,002.0489.0580.00.00.00.00.00.0 Investments and business acquisitions, net of cash acquired0.0(202.0)(3,548.0)0.00.00.00.00.0 Other investing activities(438.0)219.
15、0(131.0)(438.0)219.0(131.0)(438.0)219.0 Total cash from investing activities(11,620.0)(12,193.0)(16,609.0)(14,651.0)(14,704.7)(15,651.6)(16,424.2)(16,086.9) Short-term borrowings (repayments)(1,033.0)503.03,019.00.00.00.00.00.0 Long-term borrowings (repayments)(487.0)7,316.0466.00.00.00.00.00.0 Long
16、 term debt due within one year0.00.00.00.00.00.00.00.0 Capital lease obligations due within one year0.00.00.00.00.00.00.00.0 Dividends paid(4,217.0)(4,437.0)(5,048.0)(5,344.7)(5,187.3)(5,029.9)(4,872.5)(4,715.1) Dividends paid ($/share)1.591.591.591.591.59 Purchase of common stock treasury stock(7,2
17、76.0)(14,776.0)(6,298.0)(7,308.2)(7,308.2)(7,308.2)(7,308.2)(7,308.2) Purchase of redeemable noncontrolling interest(436.0)0.00.00.00.00.00.00.0 Capital Lease Obligations(346.0)(363.0)(355.0)0.00.00.00.00.0 Other(396.0)(271.0)(242.0)(242.0)(242.0)(242.0)(242.0)(242.0) Total cash from financing activ
18、ities(14,191.0)(12,028.0)(8,458.0)(12,894.9)(12,737.5)(12,580.1)(12,422.6)(12,265.2) Effect of Exchange Rate on Cash194.066.0(33.0)194.066.0(33.0)194.066.0 The Analyst ExchangeOn - Line Td130fad4f5cee25781d57d7d132a02f5.xlsx, Cash Flow Statement4 of 40 632.0(512.0)(845.0)378.31,609.21,696.82,075.82,
19、974.9 Cash flow before debt paydown378.31,609.21,696.82,075.82,974.9 The Analyst ExchangeOn - Line Td130fad4f5cee25781d57d7d132a02f5.xlsx, Balance Sheet5 of 40 ActualsEstimates 2010A2011A2012A2013E2014E2015E2016E2017E Current assets: Cash and cash equivalents7,395.06,550.06,928.38,537.410,234.212,31
20、0.015,285.0 Receivables, net5,089.05,937.05,790.56,080.06,323.26,512.96,643.2 Inventories36,437.040,714.040,862.442,905.544,621.745,960.446,879.6 Prepaid expenses and other2,960.01,685.02,458.92,581.82,685.12,765.62,820.9 Other current assets (discontinued operations)131.089.089.089.089.089.089.0 To
21、tal current assets52,012.054,975.056,129.060,193.863,953.367,638.071,717.7 Property, plant and equipment, net107,878.0112,324.0117,945.3123,680.4129,391.6134,928.9140,133.7 Goodwill16,763.020,651.020,651.020,651.020,651.020,651.020,651.0 Other assets and deferred charges4,129.05,456.05,576.05,039.04
22、,852.04,972.04,435.0 Total assets180,782.0193,406.0200,301.3209,564.2218,847.8228,189.9236,937.4 Current liabilities: Short-term borrowings1,031.04,047.04,047.04,047.04,047.04,047.04,047.0 Accounts payable33,676.036,608.037,309.239,174.640,741.641,963.842,803.1 Accrued liabilities18,701.018,154.019,
23、579.720,558.721,381.122,022.522,463.0 Accrued income taxes157.01,164.0704.2738.5764.6781.1787.4 Long term debt due within one year4,655.01,975.01,975.01,975.01,975.01,975.01,975.0 Obligations under capital leases due within one year336.0326.0326.0326.0326.0326.0326.0 Current liabilities of discontin
24、ued operations47.026.026.026.026.026.026.0 Total current liabilities58,603.062,300.063,967.066,845.869,261.271,141.472,427.5 Long-term debt40,692.044,070.044,070.044,070.044,070.044,070.044,070.0 Long-term obligations under capital leases3,150.03,009.03,009.03,009.03,009.03,009.03,009.0 Deferred inc
25、ome taxes and other6,682.07,862.08,577.99,581.410,372.410,968.711,380.2 Redeemable noncontrolling interest408.0404.0404.0404.0404.0404.0404.0 Total liabilities109,535.0117,645.0120,028.0123,910.2127,116.7129,593.2131,290.7 Common stock par value + additional paid-in-capital3,929.04,034.04,034.04,034
26、.04,034.04,034.04,034.0 Retained earnings63,967.068,691.073,251.478,807.985,160.292,073.799,299.7 Accumulated other comprehensive income (loss)646.0(1,410.0)(1,458.0)(1,634.0)(1,909.0)(1,957.0)(2,133.0) Total shareholders equity68,542.071,315.075,827.481,207.987,285.294,150.7101,200.7 Noncontrolling
27、 interest2,705.04,446.04,446.04,446.04,446.04,446.04,446.0 Total liabilities & equity 180,782.0193,406.0200,301.3209,564.2218,847.8228,189.9236,937.4 The Analyst ExchangeOn - Line Td130fad4f5cee25781d57d7d132a02f5.xlsx, Balance Sheet6 of 40 Balance? (Y/N)YYYYYYY The Analyst ExchangeOn - Line Td130fa
28、d4f5cee25781d57d7d132a02f5.xlsx, Depreciation7 of 40 Depreciation (in US$ millions)ActualsEstimates Period Ending January 312010A2011A2012A2013E2014E2015E2016E2017E Property, plant & equipment (beg. of year)112,324.0 Capital expenditures (beg. of year)14,213.014,923.715,520.615,986.216,305.9 Book (G
29、AAP) depreciation Useful Life PP&E Years14 CAPEX Years2525252525 Depreciation Existing PP&E8,023.18,023.18,023.18,023.18,023.1 2013 CAPEX568.5568.5568.5568.5568.5 2014 CAPEX596.9596.9596.9596.9 2015 CAPEX620.8620.8620.8 2016 CAPEX639.4639.4 2017 CAPEX652.2 Total book depreciation8,591.79,188.69,809.
30、410,448.911,101.1 Accelerated depreciation (%) Existing PP&E8.75%9.13%8.21%7.39%6.65% 2013 CAPEX6.56%7.00%6.48%6.00%5.55% 2014 CAPEX6.56%7.00%6.48%6.00% 2015 CAPEX6.56%7.00%6.48% 2016 CAPEX6.56%7.00% 2017 CAPEX6.56% Accelerated depreciation Existing PP&E9,828.410,255.29,221.88,300.77,469.5 2013 CAPE
31、X932.8994.9921.3852.2788.3 2014 CAPEX979.41,044.7967.4894.8 2015 CAPEX1,018.61,086.41,006.0 2016 CAPEX1,049.21,119.0 2017 CAPEX1,070.2 Total tax depreciation10,761.112,229.512,206.412,255.912,347.9 Difference in depreciation2,169.53,040.92,396.91,807.01,246.7 Tax rate (%)33%33%33%33%33% Deferred tax
32、es715.91,003.5791.0596.3411.4 The Analyst ExchangeOn - Line Td130fad4f5cee25781d57d7d132a02f5.xlsx, Operating Working Capital8 of 40 Operating Working Capital Schedule (OWC) (in US$ millions)ActualsEstimates On January 312010A2011A2012A2013E2014E2015E2016E2017E Current assets Receivables, net5,089.0
33、5,937.05,790.56,080.06,323.26,512.96,643.2 Days receivable4.44.44.44.44.44.4 Inventories36,437.040,714.040,862.442,905.544,621.745,960.446,879.6 Inventory turnover days41.441.441.441.441.441.4 Prepaid expenses and other2,960.01,685.02,458.92,581.82,685.12,765.62,820.9 Days prepaid10.810.810.810.810.
34、810.8 Total current assets44,486.048,336.049,111.751,567.353,630.055,238.956,343.7 Current liabilities Accounts payable33,676.036,608.037,309.239,174.640,741.641,963.842,803.1 Days payable37.837.837.837.837.837.8 Accrued liabilities18,701.018,154.019,579.720,558.721,381.122,022.522,463.0 Days payabl
35、e86.086.086.086.086.086.0 Accrued taxes157.01,164.0704.2738.5764.6781.1787.4 Days Payable29.929.929.929.929.929.9 Total current liabilities52,534.055,926.057,593.060,471.862,887.264,767.466,053.5 Total operating working capital(8,048.0)(7,590.0)(8,481.3)(8,904.5)(9,257.2)(9,528.5)(9,709.8) Change in
36、 total operating working capital(891.3)(423.2)(352.8)(271.3)(181.3) The Analyst ExchangeOn - Line Td130fad4f5cee25781d57d7d132a02f5.xlsx, Debt Schedule9 of 40 Debt Schedule (in US$ millions)ActualsEstimates Period Ending January 312010A2011A2012A2013E2014E2015E2016E2017E Cash available to pay down d
37、ebt Cash at beginning of year6,550.06,928.38,537.410,234.212,310.0 Cash flow before debt paydown378.31,609.21,696.82,075.82,974.9 Minimum cash cushion(1,000.0)(1,000.0)(1,000.0)(1,000.0)(1,000.0) Total cash available to pay down debt5,928.37,537.49,234.211,310.014,285.0 Short term borrowings / revol
38、ver Short term borrowings (beginning of year)4,047.04,047.04,047.04,047.04,047.0 Mandatory issuances / (retirements) 0.00.00.00.00.0 Non-mandatory issuances / (retirements)0.00.00.00.00.00.0 Short term borrowings (end of year)4,047.04,047.04,047.04,047.04,047.04,047.0 Short term interest expense4.04
39、.04.04.04.0 Short term interest rate0.1%0.1%0.1%0.1%0.1% Long term debt due within one year Long term debt due within one year (beginning of year)1,975.01,975.01,975.01,975.01,975.0 Mandatory issuances / (retirements) 0.00.00.00.00.0 Non-mandatory issuances / (retirements)0.00.00.00.00.0 Long term d
40、ebt due within one year (end of year)1,975.01,975.01,975.01,975.01,975.01,975.0 Interest expense96.896.896.896.896.8 Interest rate4.9%4.9%4.9%4.9%4.9% Obligations under capital leases due within one year Obligations under capital leases due within one year (beginning of year)326.0326.0326.0326.0326.
41、0 Mandatory issuances / (retirements) 0.00.00.00.00.0 Non-mandatory issuances / (retirements)0.00.00.00.00.0 Obligations under capital leases due within one year (end of year)326.0326.0326.0326.0326.0326.0 Interest expense27.527.527.527.527.5 Interest rate8.4%8.4%8.4%8.4%8.4% Long term debt Long ter
42、m debt (beginning of year)44,070.044,070.044,070.044,070.044,070.0 Mandatory issuances / (retirements) 0.00.00.00.00.0 Non-mandatory issuances / (retirements)0.00.00.00.00.0 Long term debt (end of year)44,070.044,070.044,070.044,070.044,070.044,070.0 Interest expense1,983.01,983.01,983.01,983.01,983
43、.0 Interest rate4.5%4.5%4.5%4.5%4.5% The Analyst ExchangeOn - Line Td130fad4f5cee25781d57d7d132a02f5.xlsx, Debt Schedule10 of 40 Long term obligations under capital leases Long term obligations under capital leases (beginning of year)3,009.03,009.03,009.03,009.03,009.0 Mandatory issuances / (retirem
44、ents) 0.00.00.00.00.0 Non-mandatory issuances / (retirements)0.00.00.00.00.0 Long term obligations under capital leases (end of year)3,009.03,009.03,009.03,009.03,009.03,009.0 Interest expense254.1254.1254.1254.1254.1 Interest rate8.4%8.4%8.4%8.4%8.4% Total issuances / (retirements)0.00.00.00.00.0 T
45、otal interest expense2,365.52,365.52,365.52,365.52,365.5 Cash at the end of the year6,550.06,928.38,537.410,234.212,310.015,285.0 Interest income168.5193.3234.6281.8344.9 Discounted Cash Flow Analysis (in US$ millions)ActualsEstimates Period Ending January 312010A2011A2012A2013E Unlevered Free Cash
46、Flow EBIT27,888.4 Depreciation & Amortization8,591.7 Deferred Taxes715.9 Other318.0 Changes in Working Capital891.3 Capital Expenditures(14,213.0) Taxes(9,203.2) Total Unlevered Free Cash Flow14,989.1 Net Present Value Calulation Period1.0 Discounted Cash Flow5.16%14,254.1 Total Net Present Value67,
47、814.7 Terminal Value EBITDA Method Exit Year EBITDA41,851.9 Multiple8.7x Terminal Value362,181.9 Net Present Value281,669.1 Perpetuity Method Unlevered Free Cash Flow16,308.9 Growth Rate1% Terminal Value396,263.5 Net Present Value308,174.4 Discounted Cash Flow Total Valuation EBITDA Method Perpetuit
48、y Method Total of Present Value of Cash Flows67,814.767,814.7 Present Value of Terminal Value281,669.1308,174.4 Total Enterprise Value349,483.8375,989.1 Net Debt, Non-controlling interests, preferred securities51,727.051,727.0 Equity Value297,756.8324,262.1 Share Count (millions)3,365.73,365.7 Estim
49、ated Equity Value per Share$88.47$96.34 2014E2015E2016E2017E 29,115.530,026.830,582.430,750.8 9,188.69,809.410,448.911,101.1 1,003.5791.0596.3411.4 318.0318.0318.0318.0 423.2352.8271.3181.3 (14,923.7)(15,520.6)(15,986.2)(16,305.9) (9,608.1)(9,908.8)(10,092.2)(10,147.8) 15,517.015,868.516,138.516,308.9 2.03.04.05.0 14,032.413,646.613,198.212,683.5 Cost of Capital Risk Free Rate 2.93% Debt YE 201253,427 Market Risk Premium 6.00% Stock Price 73.82 Beta 0.42 Shares Outstanding3,366 Cost of Equity 5.45% Equity Value248,459.0 Cost of Debt 5.625% WACC5.16% Estimates The Analyst Exchange O