A B C D E F G H K L M N P R 1 THE STORY 2 B 1 7 B 9 Total Market 0 1 Gross Market Share 2 3 Revenue 4 5 Reinvesting 6 7 Cost of Capital 8 9 Risk Failure 0 THE ASSUMPTION Base Year year 1-5 years 6-10 story link 1 The Cash flows 2 3 2024 2025 4 EBIT 5 6 7 8 9 0 Unlevered FCF -1 2 3 4 Terminal Value 5 PV (terminal value) 6 7 8 THE VALUE 1 Walmart Save money. Live better. 6 Assets 7 8 9 2 Consolidated Balance Sheets 3 (in US$ millions) 4 5 On January 31 Current assets: Cash and cash equivalents Receivables, net Actuals Estimates 2021A 2022A 2023A 2024E 2025E 2026E 2027E 2028E Formula Projection Notes 10 Inventories 11 Prepaid expenses and other 12 13 Other current assets (discontinued operations) Total current assets 81,070.0 14,760.0 8,625.0 16,151.4 8,280.0 7,933.0 8,946.8 56,511.0 56,576.0 61,978.0 1,519.0 2,521.0 2,187.7 0.0 0.0 0.0 75,655.0 89,263.9 24,357.5 34,030.3 9,546.3 10,185.9 66,130.5 70,561.3 2,334.2 2,490.6 0.0 0.0 45,716.5 10,868.3 75,288.9 2,657.5 0.0 57,442.5 =F8+'Cash Flow Statement'!G47 11,596.5 =F9-'Cash Flow Statement'!G16 80,333.2 2,835.6 F10-'Cash Flow Statement'!G17 0.0 14 Property, plant and equipment, net 15 Operatintg lease right of use assets 94,515.0 1,00,760.0 13,758.0 13,555.0 13,555.0 16 17 finance lease right ofr use assets, net Goodwill 4,351.0 4,919.0 29,014.0 28,174.0 62 2222 18 Other assets and deferred charges 22,152.0 20,134.0 19 Total assets 20 Liabilities 21 Current liabilities: Short-term borrowings Accounts payable 23 < > 2,44,860.0 2,43,197.0 4,919.0 28,174.0 18,268.7 2,62,394.3 1,02,368.5 1,17,268.1 1,34,531.2 1,08,213.7 1,15,916.7 1,23,885.7 1,32,138.6 13,555.0 13,555.0 13,555.0 4,919.0 4,919.0 28,174.0 28,174.0 17,384.3 15,916.0 2,82,317.6 3,03,717.8 F11-'Cash Flow Statement'!G18 1,52,207.7 =SUM(E8:E12) 1,40,694.2 =F14-'Cash Flow Stateme Sum PPE that is owned + leased = '105098 +27 13,555.0 =F15+0 4,919.0 28,174.0 14,050.7 3,27,368.5 4,919.0 F16+0 28,174.0 F17-0 13,166.3 F18-'Cash Flow Statement'!G14-'Cash Flow Statement!!G30-'Cash Flow Statement'!F28-'Cash 3,52,716.3 =SUM(G13:G18) 410.0 55,261.0 372.0 53,742.0 372.0 59,773.4 372.0 63,778.3 372.0 68,051.4 372.0 72,610.9 Income Statement Balance Sheet Cash Flow Statement Depreciation 372.0 F22+'Cash Flow Statement'!G33 77,475.8 =F23+'Cash Flow Statem Sum PPE that is owned + leased = '105098 + 27 Operating Working Capital Debt Schedule ---> Valuation + AB Walmart Save money. Live better. C D E F G H | J K L M N P Q R 1 2 Consolidated Balance Sheets 3 (in US$ millions) Actuals Estimates 4 5 On January 31 2021A 2022A 2023A 2024E 2025E 2026E 2027E 2028E Formula Projection Notes 21 22 Current liabilities: Short-term borrowings 23 Accounts payable 410.0 55,261.0 372.0 53,742.0 372.0 59,773.4 372.0 63,778.3 372.0 68,051.4 24 Accrued liabilities 26,060.0 31,126.0 31,168.1 33,256.4 35,484.5 372.0 72,610.9 37,862.0 372.0 F22+'Cash Flow Statement'!G33 77,475.8 =F23+'Cash Flow Statem Sum PPE that is owned + leased = '105098 + 27 40,398.8 =F24+'Cash Flow Statement'!G20 25 Accrued income taxes 851.0 727.0 1,169.4 1,257.5 1,353.5 26 Long term debt due within one year 2,803.0 4,191.0 4,191.0 4,191.0 4,191.0 1,458.7 4,191.0 1,569.5 =F25+'Cash Flow Statement'!G21 4,191.0 F26+'Cash Flow Statement'!G35 27 Obligations under capital leases due within one year 28 Finance lease obligations due within one year 1,483.0 1,473.0 1,473.0 1,473.0 1,473.0 29 30 Current liabilities of discontinued operations Total current liabilities 511.0 87,379.0 567.0 92,198.0 567.0 567.0 98,713.9 1,04,895.1 567.0 1,11,492.4 1,473.0 567.0 1,18,534.6 1,473.0 31 82 83 34 35 36 37 Long-term debt Long-term operating lease obligations Long-term obligations under capital leases Deferred income taxes and other Commitments and contingencies Redeemable noncontrolling interest Total liabilities 34,864.0 34,649.0 13,009.0 12,828.0 12,828.0 4,243.0 4,843.0 13,474.0 14,688.0 34,649.0 34,649.0 34,649.0 34,649.0 12,828.0 12,828.0 12,828.0 567.0 =F29+0 1,26,047.0 =SUM(G22:G29) 34,649.0 F31+'Cash Flow Statement'!G34 12,828.0 =F32+0 4,843.0 4,843.0 4,843.0 4,843.0 16,679.8 18,513.4 19,582.4 20,038.9 4,843.0 =F33+'Cash Flow Statement'!G43 20,001.9 F34+'Cash Flow Statement'!G12 0.0 1,52,969.0 237.0 1,59,443.0 (207.0) 1,67,506.8 (651.0) 1,75,077.5 (1,095.0) 1,82,299.8 (1,539.0) (1,983.0) 1,89,354.5 1,96,385.9 =SUM(G30,G31:G36) 38 Shareholders' equity Income Statement Balance Sheet Cash Flow Statement Depreciation Operating Working Capital Debt Schedule ---> Valuation + Ready Calculate Scroll Lock Accessibility: Investigate AB Walmart Save money. Live better. C D E F G H | J K L M N P Q R S 1234531 2 Consolidated Balance Sheets 3 (in US$ millions) 5 On January 31 Actuals Estimates Deferred income taxes and other 35 Commitments and contingencies 36 Redeemable noncontrolling interest 37 Total liabilities 2021A 2022A 13,474.0 2023A 14,688.0 2024E 16,679.8 2025E 18,513.4 2026E 19,582.4 2027E 20,038.9 0.0 1,52,969.0 237.0 1,59,443.0 (207.0) 1,67,506.8 (651.0) 1,75,077.5 (1,095.0) 1,82,299.8 (1,539.0) 1,89,354.5 2028E Formula Projection Notes 20,001.9 F34+'Cash Flow Statement'!G12 (1,983.0) 1,96,385.9 =SUM(G30,G31:G36) 38 Shareholders' equity 39 40 Common stock par value + additional paid-in-capital capital in excess of par value 276.0 4,839.0 41 Retained earnings 42 Accumulated other comprehensive income (loss) 43 Total shareholders' equity 44 Noncontrolling interest 269.0 269.0 269.0 269.0 4,969.0 4,969.0 4,969.0 4,969.0 86,904.0 83,135.0 94,020.2 1,06,597.3 1,21,017.0 (8,766.0) (11,680.0) (11,431.7) (11,656.2) (11,898.0) 83,253.0 76,693.0 87,826.5 1,00,179.1 1,14,357.0 8,638.0 7,061.0 7,061.0 7,061.0 7,061.0 269.0 4,969.0 1,37,457.9 (11,743.0) 269.0 F39+0 1,30,953.0 7,061.0 7,061.0 F44+0 4,969.0 F40+0 1,56,025.4 =G41+'Cash Flow Statement'!H7+'Cash Flow Statement'!H37+'Cash Flow Statement'!H39+'Cash Fl (11,994.0) 1,49,269.4 =SUM(G39:G42) Sum commonstock pref stock and APIC TRUE 45 Total liabilities & equity 2,44,860.0 2,43,197.0 2,62,394.3 2,82,317.6 3,03,717.8 3,27,368.5 3,52,716.3 =SUM(G37,G43:G44) 46 SUPPLEMENTAL DATA: 47 Balance? (Y/N) 48 49 50 Y Y Y Y Y Y Y =IF(ROUND(G19-G45,1)=0,"Y", "N") < > Income Statement Balance Sheet Cash Flow Statement Depreciation Operating Working Capital Debt Schedule ---> Valuation + Ready Calculate Scroll Lock Accessibility: Investigate B AB Walmart Save money. Live better. C D E F G H J K L M N 1 2 Consolidated Statements of Cash Flows 3 (in US$ millions) 4 5 Period Ending January 31 6 Cash flows from operating activities Actuals Estimates 2021A 2022A 2023A 2024E 2025E 2026E 2027E 2028E Formula Projection Notes P 7 Net income 8 Loss (income) from discontinued operations 13,706.0 0.0 9 Depreciation and amortization 11,152.0 13,940.0 0.0 10,658.0 11,292.0 0.0 10,945.0 22,451.9 0.0 10,989.1 10 Net unrealized and realized (gains) and losses (8,589.0) 2,440.0 11 12 13 14 15 16 Losses on disposal of business operations Deferred income taxes Loss on extinguishment of debt Other Operating Activities Changes in operating working capital Changes in accounts receivable 8,401.0 433.0 1,911.0 (755.0) 1,683.0 0.0 449.0 1,683.0 0.0 1,991.8 24,143.9 0.0 11,963.5 1,683.0 0.0 1,833.5 25,986.4 0.0 13,003.1 1,683.0 0.0 1,069.0 28,007.7 30,134.3 'Income Statement'!D37 Pulling from IS 0.0 0.0 #N/A 14,112.3 15,295.9 Linking IS to the CF ='Income Statement!! G39 Make sure that you TBDL/Linked to Depreciation Schedule 1,683.0 0.0 456.5 1,683.0 0.0 (37.0) 1,521.0 2,410.0 1,652.0 0.0 1,919.0 0.0 1,697.3 0.0 1,697.3 0.0 1,697.3 0.0 1,697.3 0.0 1,697.3 Assumption: 17 Changes in inventory (1,086.0) (1,796.0) (2,395.0) (11,764.0) 240.0 (528.0) (1,013.8) (599.4) (5,402.0) (4,152.5) (639.6) (4,430.7) (682.5) (728.2) (4,727.6) (5,044.4) 18 Changes in prepaid expenses 19 Changes in accounts payable 20 Changes in accrued liabilities 6,966.0 4,623.0 5,520.0 (1,425.0) 1,404.0 4,393.0 333.3 6,031.4 (146.6) (156.4) (166.9) (178.1) 4,004.8 4,273.1 4,559.4 4,864.9 42.1 2,088.3 2,228.2 2,377.5 2,536.8 21 22 23 Accrued income taxes Net changes in operating working capital Total cash flows from operating activities (136.0) 7,972.0 39.0 (6,597.0) 36,074.0 24,181.0 (127.0) 2,553.0 28,841.0 442.4 88.1 96.0 105.3 433.4 1,282.7 1,370.5 1,465.3 39,246.7 42,603.9 44,809.4 47,422.1 110.8 > Income Statement Balance Sheet Cash Flow Statement 1,561.9 =SUM(D16:D21) 50,335.3 =SUM(D22,D7:D14) Depreciation Operating Working Capital Debt Schedule ---> Valuation + Ready Calculate Scroll Lock Accessibility: Investigate # AB C D E F G H J K L M N 1 Walmart Save money. Live better. 2 Consolidated Statements of Cash Flows 3 (in US$ millions) 1 5 Period Ending January 31 24 Cash flows from investing activities Payments for property and equipment (CAPEX) CAPEX as a % of Revenue Actuals Estimates 2021A 2022A 2023A 2024E 2025E 2026E 2027E 2028E Formula Projection Notes 0 P Q R S (23,671.6)=-G26*Income Statement STEP 2 0.0 =-D25/'Income Statement Capex as a % of Revenue line is inserted for projection purposes. (24,730.6) =SUM(D25:D30) Keyword search of your Annual Report using "acquisition" 25 (10,264.0) 26 1.8% (13,106.0) 2.3% (16,857.0) 2.8% (18,262.9) 0.0 (19,486.5) 0.0 (20,792.1) 0.0 (22,185.1) 0.0 27 Proceeds from disposal of property and equipment 215.0 394.0 170.0 0.0 0.0 0.0 0.0 0.0 28 Proceeds for disposal of certain operations, net of divested cash 56.0 7,935.0 0.0 0.0 0.0 0.0 0.0 0.0 9 Investments and business acquisitions, net of cash acquired (180.0) (359.0) (740.0) (180.0) (180.0) (180.0) (180.0) (180.0) =0 Other investing activities 102.0 1 Total cash from investing activities (10,071.0) (879.0) (6,015.0) (295.0) 102.0 (879.0) (295.0) 102.0 (879.0) (17,722.0) (18,340.9) (20,545.5) (21,267.1) (22,263.1) 2 Cash flows from financing activities Short-term borrowings (repayments) Long-term borrowings (repayments) repayments of ling term debt premiums paid to extinguish debt Dividends paid 3 4 5 36 =7 =8 =9 -0 -1 < > Dividends paid ($/share) Purchase of common stock [treasury stock] dividends paid to noncontrolling interest Purchase of redeemable noncontrolling interest Income Statement (324.0) 0.0 (5,382.0) 193.0 6,945.0 (13,010.0) 0.0 (2,317.0) (34.0) 5,041.0 A lot of variance! AR keyword search and Debt structure or borrowings? (2,689.0) (6,116.0) (6,152.0) 0.0 (6,114.0) (6,138.3) 2.3 (2,625.0) (9,787.0) (434.0) 0.0 (424.0) 0.0 (9,920.0) (444.0) (827.0) (5.428 5) (444.0) 0.0 (6,138.3) 2.3 (5,428.5) (444.0) 0.0 (6,138.3) 2.3 (5,428.5) (444.0) 0.0 (6,138.3) 2.3 (5,428.5) (444.0) 0.0 (6,138.3)='Income Statement!!G50*G38 2.3 (5,428.5) (444.0) 0.0 Inserted this row for projection purposes #N/A Balance Sheet Cash Flow Statement Depreciation Operating Working Capital Debt Schedule -> Valuation + AB Walmart Save money. Live better. C D E F G H J K L M N P Q R 1 2 Consolidated Statements of Cash Flows 3 (in US$ millions) Actuals 33 34 35 36 31 32 Cash flows from financing activities Short-term borrowings (repayments) Long-term borrowings (repayments) repayments of ling term debt premiums paid to extinguish debt 4 5 Period Ending January 31 2021A Total cash from investing activities (10,071.0) 2022A (6,015.0) 2023A (17,722.0) 2024E (18,340.9) 2025E Estimates (20,545.5) (21,267.1) 2026E 2027E (22,263.1) 2028E Formula Projection Notes (24,730.6)=SUM(D25:D30) (324.0) 0.0 (5,382.0) 0.0 193.0 6,945.0 (13,010.0) (34.0) 5,041.0 A lot of variance! AR keyword search and Debt structure or borro (2,317.0) 37 Dividends paid (6,116.0) (6,152.0) (2,689.0) 0.0 (6,114.0) 38 Dividends paid ($/share) 39 Purchase of common stock [treasury stock] (2,625.0) (9,787.0) (9,920.0) (6,138.3) 2.3 (5,428.5) (6,138.3) 2.3 (5,428.5) (6,138.3) 2.3 (5,428.5) (6,138.3) 2.3 (5,428.5) (6,138.3)='Income Statement'!G50*G38 2.3 (5,428.5) #N/A Inserted this row for projection purposes 40 dividends paid to noncontrolling interest 41 Purchase of redeemable noncontrolling interest (434.0) 0.0 42 sale of subsidiary stock 140.0 (424.0) 0.0 3,239.0 (444.0) (827.0) (444.0) 0.0 66.0 66.0 (444.0) 0.0 66.0 (444.0) 0.0 66.0 (444.0) 0.0 66.0 (444.0) 0.0 66.0 43 Capital Lease Obligations A most recent value 44 Other (1,376.0) (1,515.0) 45 Total cash from financing activities 46 Effect of Exchange Rate on Cash 47 Total change in cash and cash equivalents (16,117.0) 235.0 (22,828.0) 10,121.0 (140.0) (4,802.0) (5,993.0) (2,118.0) (17,039.0) (73.0) (1,669.7) (13,614.4) 235.0 (1,767.6) (13,712.3) (140.0) (1,851.7) (13,796.5) (73.0) (1,763.0) (13,707.8) 235.0 (1,794.1) (13,738.9)=SUM(D33:D37,D39:D44 A repeat the cycle method (140.0) 7,526.4 8,206.1 9,672.8 11,686.2 11,725.9 > > Income Statement Balance Sheet Cash Flow Statement Depreciation Operating Working Capital Debt Schedule ---> Valuation + AB C Discounted Cash Flow Analysis (in US$ millions) D E F H K Actuals Estimates Period Ending January 31 2021A 2022A 2023A 2024E 2025E 2026E 2027E 2028E FormulaText Unlevered Free Cash Flow EBIT Depreciation & Amortization Deferred Taxes Other Changes in Working Capital Capital Expenditures Taxes Total Unlevered Free Cash Flow Net Present Value Calulation Period Discounted Cash Flow. Total Net Present Value Terminal Value EBITDA Method Exit Year EBITDA Multiple Terminal Value Net Present Value Perpetuity Method Unlevered Free Cash Flow Growth Rate Terminal Value Net Present Value 30,096.4 32,364.5 34,834.4 10,989.1 11,963.5 13,003.1 1,991.8 1,833.5 1,069.0 1,697.3 1,697.3 1,697.3 37,543.8 14,112.3 456.5 1,697.3 433.4 1,282.7 1,370.5 (18,262.9) (19,486.5) (20,792.1) 1,465.3 (22,185.1) (7,644.5) 19,300.8 (8,220.6) (8,847.9) (9,536.1) 21,434.5 22,334.3 23,554.0 6.63% 1.0 18,101.2 2.0 18,852.8 3.0 18,423.4 4.0 18,221.9 91723.86142 55,690,4 16.6x 9,25,525.2 6,71,504.8 24,980.8 |=K14 2% hardcode 5,50,671.2 =F26*(1+F27)/(F17-F27) 3,99,533.6 Discounted Cash Flow Total Valuation EBITDA Method erpetuity Method Total of Present Value of Cash Flows Present Value of Terminal Value 91,723.9 6,71,504.8 91,723.9 Total Enterprise Value 7,63,228.7 3,99,533.6 4,91,257.4 Equity Value Share Count (millions) Net Debt, Non-controlling interests, preferred securities Estimated Equity Value per Share 57,596.0 7,05,632.7 2,734.0 $258.10 57,596.0 4,33,661.4 $158.62 2,734.0 Cost of Capital M N P Q 40,394.5 ='Income Statement!G33 15,295.9 ='Cash Flow Statement!G9 (37.0)='Cash Flow Statement!G12 1,697.3 ='Cash Flow Statement!G14 1,561.9 ='Cash Flow Statement!G22 (23,671.6) 'Cash Flow Statement!G25 (10,260.2) -'Income Statement!G36*G7 24,980.79 =SUM(G7:G13) 5.0 18,124.5 Second me 91723.86 =NPV(F17,G14:K14) Risk Free Rate Market Risk Premium 5.00% Debt YE 2012 4.18% Stock Price 58,923 169.64 Beta Cost of Equity Cost of Debt WACC 0.50 Shares Outstanding 7.09% Equity Value 4.000% 2,734 4,63,795.8 Entreprise Value 1" 6.63% Entreprise Value 2 5,23,906 5,22,718.76 Homework 4-5 sensitivity analysi Total of Present Value of Cash Flows Present Value of Terminal Value Discounted Cash Flow Total Valuation EBITDA Method erpetuity Method 91,723.9 6,71,504.8 91,723.9 3,99,533.6 Total Enterprise Value 7,63,228.7 4,91,257.4 Net Debt, Non-controlling interests, preferred securities 57,596.0 Equity Value 7,05,632.7 57,596.0 4,33,661.4 Share Count (millions) 2,734.0 2,734.0 Estimated Equity Value per Share $258.10 $158.62 Formula text Perpetuity Method =F18 =F29 =SUM(F32, F33) =021+'Balance Sheet'!F44+'Balance Sheet'!F36-'Balance Sheet'!F8 =F34-F35 =023 =F36F37 EBITDA Method =F18 =F24 =SUM(E32:E33) =F35 =E34-E35 =F37 =E36E 37 Homework 4-5 sensitivity analysi which one is important 3-4 lines about each Walmart Save money. Live better. Consolidated Income Statements in US$ millions except per share amounts) Period Ending January 31 Revenue Net Sales % Growth Membership and Other Income % Growth Total revenue Y/Y revenue growth (%) Cost of goods sold Actual $, Actual %, Projected/Est %, Projected/Est $ Actuals Estimates 2021A 2022A 2023A 2024E 2025E 2026E 2027E 2028E Formula Projection Notes 5,55,233.0 5,67,762.0 6,05,881.0 3,918.0 4,992.0 5,408.0 5,59,151.0 5,72,754.0 2.4% 6,11,289.0 6.7% 6,52,245.4 6.7% 6,95,945.8 6.7% 7,42,574.2 6.7% 7,92,326.6 6.7% 8,45,412.5 =SUM(D7,D9) =F11*(1+G12) 6.7% =E11/D11-1 PROJECT A:Projecting 6% growth rate -last year % growth rate. For 2023 Homework, read CEO, exec summary a Cost of goods sold 4,20,315.0 COGS as a % of revenue 75.2% 4,29,000.0 74.9% 4,63,721.0 75.9% 4,88,531.8 74.9% 5,21,263.4 74.9% 5,56,188.1 74.9% 5,93,452.7 74.9% 6,33,214.0 Gross profit 1,38,836.0 Gross profit margin (%) 24.8% 1,43,754.0 25.1% 1,47,568.0 24.1% 1,63,713.6 25.1% 1,74,682.4 25.1% 1,86,386.1 25.1% 1,98,874.0 25.1% 74.9% =D14/D11 2,12,198.5 D11-D14 25.1% =D16/D11 =G15*G11 PROJECT This is not YoY growth formula. Ratio. COGS*Revenue Proj. paf paf paf-made mnemonic = pull across formula Operating expenses Selling, general and administrative 1,05,136.0 SG&A as a % of revenue 18.8% 1,07,154.0 18.7% 1,16,195.0 19.0% 1,21,969.9 18.7% 1,30,141.9 18.7% 1,38,861.4 18.7% 1,48,165.1 18.7% 1,58,092.1 18.7% =G20*G11 PROJECT Subtract D&A from SGA. 79977-7157-72820,81361-7641-73720,85265-8130-77135' EBITDA 33,700.0 EBITDA margin (%) Depreciation and amortization 6.0% 36,600.0 6.4% 31,373.0 5.1% 41,743.7 6.4% 44,540.5 6.4% 47,524.7 6.4% 50,708.9 6.4% 54,106.4 =D16-D19 6.4% paf paf 11,152.0 10,658.0 10,945.0 10,989.1 11,963.5 BIT 22,548.0 EBIT margin (%) 4.0% 25,942.0 4.5% 20,428.0 3.3% 30,754.6 4.7% 32,577.1 4.7% 13,003.1 34,521.7 4.6% 14,112.3 15,295.9 TBD Later Dep & Amort Numbers are somewhat in part of SGA.To account for it, we insert a line and add Dep & A 36,596.6 4.6% 38,810.5 =D21-D23 4.6% paf paf < > Income Statement Balance Sheet Cash Flow Statement Depreciation Operating Working Capital Debt Schedule ---> Valuation + AB Walmart Save money. Live better. F H K L M N P 1 2 Consolidated Income Statements 3 (in US$ millions except per share amounts) 5 Period Ending January 31 6 Interest Actuals Estimates 2021A 2022A 2023A 2024E 2025E 2026E 2027E 2028E Formula Projection Notes 7 Interest expense (Debt) 8 Interest expense (Capital Leases) 1,976.0 339.0 1,674.0 1,787.0 9 Interest income (121.0) 320.0 (158.0) 341.0 (254.0) -0 Loss om extinguishment of debt 0 2,410.0 0.0 1 Other(gains) and losses -210 3,000.0 2 Net interest expense 1,984.0 7,246.0 1,538.0 3,412.0 1,500.5 34.7 (877.0) 0.0 0.0 658.1 1,500.5 34.7 (1,322.6) 0.0 0.0 212.5 1,500.5 34.7 (1,847.8) 0.0 1,500.5 0.0 (312.7) 34.7 (2,482.4) 0.0 0.0 (947.3) 1,500.5 34.7 TBD Later Report all numbers the way you see in the AR. If a number is reporte (3,119.1) 0.0 0.0 (1,584.0)=SUM(D27:D31) 3 EBT 4 EBT margin (%) 20,564.0 3.7% 18,696.0 17,016.0 3.3% 2.8% 30,096.4 4.6% 32,364.5 5% 34,834.4 5% 37,543.8 5% 40,394.5 5% 5 Income tax expense 6 Tax rate (%) 6,858.0 33.3% 4,756.0 25.4% 5,724.0 33.6% 7,644.5 25.4% 8,220.6 25.4% 8,847.9 25.4% -7 Net Income (Adjusted) 13,706.0 13,940.0 11,292.0 22,451.9 24,143.9 25,986.4 9,536.1 25.4% 28,007.7 10,260.2 25.4% 30,134.3 8 Non-recurring events -9 Discontinued operations 0 Other 1 Net Income (After non-recurring events) 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 13,706.0 13,940.0 11,292.0 22,451.9 24,143.9 25,986.4 28,007.7 30,134.3 =SUM(D37,D3 paf Matches up with Consolidated Net Income line in AR! 2 Distributions 3 Income attributable to non-controlling interests 4 Non-controlling interests % of Net Income (196.0) 1.4% (267.0) 1.9% 388.0 -3.4% (426.6) 1.9% (458.7) 1.9% (493.7) 1.9% (532.1) 1.9% (572.6) 1.9% =-D43/D41 =-G44*G41 PROJECT NCI is a distribution, reported in parentheses/outflow. When the perce 5 Net Income (as Reported) 13,510.0 13,673.0 11,680.0 22,025.3 23,685.2 25,492.7 27,475.6 29,561.7 =SUM(D41,D4 paf 1 Walmart Save money. Live better. 2 Consolidated Income Statements 3 (in US$ millions except per share amounts) Actuals Estimates 5 Period Ending January 31 2021A 2022A 2023A 2024E 2025E 2026E 2027E 2028E Formula Projection Notes 46 Earnings per share (EPS) 47 Basic 4.77 4.90 4.29 8.18 8.80 9.47 10.21 10.98 48 Diluted 4.75 4.87 4.27 8.18 8.80 9.47 10.21 10.98 49 Average common shares outstanding 50 Basic 2,831 2,792 2,724 2,692 2,692 2,692 2,692 51 Diluted 2,847 2,805 2,734 2,692 2,692 2,692 2,692 2,692 =D55/1000000 2,692 =D61/1000000 52 31.8 Diluted Shares 53 Notes 54 Share Price 55 Number of basic shares outstanding $169.64 2,69,22,33,703 56 Number of outstanding options (in the money) 57 Average option strike price 89,900 $167.23 58 Total option proceeds 1,50,33,977 =D56*D57 Latest stock price as of nov 28, 2023 Blooomberg Terminal/Yahoo Finance/Robinhood Obtained this share count # from the latest 10Q as of nov 28 2023. 10Q gives you an updated share count as opposed to the 10K of March 31 2012. From the 10K-AR, pg 41-43 of AR. For your 2023 homework, do a keyword search or go to the Bloomberg Terminal to find information From the 10K-AR, pg 41-43 of AR. For your 2023 homework, do a keyword search or go to the Bloomberg Terminal to find information Potential # of in the money options * exercisable price/average strike price 59 Treasury stock method shares repurchased 60 Additional shares outstanding 61 Total diluted shares outstanding 88,623 =D58/D54 1,277 =D56-D59 2,69,22,34,980 =D55+D60 Potential buy back/sale Potential (as opposed to realization) Net Dilution Basic shares + net potential dilution or additional shares (it could even be a buyback) > Income Statement Balance Sheet Cash Flow Statement Depreciation Ready Calculate Scroll Lock Accessibility: Investigate Operating Working Capital Debt Schedule ---> Valuation + B Table 9.2 Uber, the Urban Car Service Company The story Uber is an urban car service company, drawing new users into the car service sector. It will enjoy local networking benefits while preserving its current revenue sharing (80/20) and capital intensity (don't own cars or hire drivers) model. The assumptions Total market Base year Years 1-5 Years 6-10 100 billion Grow 6.00% a year After year 10 Story link Grow 2.50% new users Gross market share 1.50% 1.50% 10.00% 10.00% Urban car service + Local networking benefits Revenue share 20.00% Stays at 20.00% 20.00% Preserve revenue share Operating margin 3.33% 3.33% 40.00% 40.00% Strong competitive position Reinvestment NA Sales-to-capital ratio of 5.00 Reinvestment Cost of capital NA rate-10% 12.00% 12.00% 8.00% 8.00% Low capital-intensity model 90th percentile of U.S. firms Risk of failure 10% chance of failure (with equity worth zero) The cash flows (in $ millions) Young company Total market Market share Revenues EBIT (1-1)* Reinvestment FCFF 1 $106,000 3.63% $769 $37 $94 $(57) 2 $112,360 5.22% $1,173 $85 $81 $4 3 $119,102 6.41% $1,528 $147 $71 $76 4 $126,248 7.31% $1,846 $219 $64 $156 5 $133,823 7.98% $2,137 $301 $58 $243 6 $141,852 8.49% $2,408 $390 $54 $336 7 $150,363 8.87% $2,666 $487 $52 $435 8 $159,385 9.15% $2,916 $591 $50 $541 9 $168,948 9.36% $3,163 $701 $49 $652 10 $179,085 10.00% $3,582 $860 $84 $776 Terminal year $183,562 10.00% $3,671 $881 $88 $793 The value Terminal value $14,418 PV (terminal value) $5,175 PV (CF over the next 10 years) $1,375 Value of operating assets = $6,550 Probability of failure 10.00% Value in case of failure $- Adjusted value for $5,895 Venture capitalists priced Uber at operating assets *EBIT (1-1)=(Revenues Operating Margin) (1 *FCFF = Free cash flow to firm $17 billion at the time. - tax rate)