Note: Please show excel formulas

Question 1: MAKE SURE TO SHOW ALL EXCEL FORMULA

Free Cash Flow Calculation. Calculate each company's Free Cash Flow for each of the last 2 fiscal years (2020 & 2021). Use a tax rate of 25% and use the depreciation and amortization from the Cash Flow statement. Please do not use any other data from the Cash Flow statement, calculate changes in the relevant balance sheet accounts from year to year (for example: total current assets, gross property plant & equipment, accounts payable & accrued expenses). UPDATED NOTE: Just Net Property, Plans & Equipment is available on the 2021 balance sheet for each company. To adjust your 2021 FCF calculation for this, OMIT Depreciation & Amortization from the first half of the equation and use the change in Net Property, Plant and Equipment for capital expenditures from 2020 to 2021.

2 Cash Flow Coca 12 months Dec-31-2019 USD 12 months Dec-31-2020 USD Press Release 12 months Dec-31-2021 USD 8,920.0 9,771.0 1,452.0 1,245.0 120.0 1,365.0 7,747.0 1,333.0 203.0 1,536.0 1,452.0 (1,365.0) 78.0 (615.0) 337.0 1,642.0 For the Fiscal Period Ending 3 4 Currency 5 6 Net Income 7 Depreciation & Amort. 8 Amort. of Goodwill and Intangibles 9 Depreciation & Amort., Total 10 11 (Gain) Loss From Sale Of Assets 12 Asset Write down & Restructuring Costs 13 (Income) Loss on Equity Invest. 14 Stock-Based Compensation 15 Other Operating Activities 16 Change in Acc. Receivable 17 Change In Inventories 18 Change in Acc. Payable 19 Change in Inc. Taxes 20 Change in Other Net Operating Assets 21 Cash from Ops. 22 23 Capital Expenditure 24 Sale of Property, Plant, and Equipment 25 Cash Acquisitions 26 Divestitures 27 Invest. in Marketable & Equity Securt. 28 Net (Inc.) Dec. in Loans Originated/Sold 29 Other Investing Activities 30 Cash from Investing (467.0) 42.0 (421.0) 201.0 465.0 (158.0) (183.0) 1,318.0 96.0 (707.0) 10,471.0 (914.0) 239.0 (511.0) 126.0 931.0 882.0 99.0 (860.0) (16.0) 585.0 9,844.0 1,325.0 12,625.0 (2,054.0) 978.0 (5,542.0) 429.0 2,269.0 (1,177.0) 189.0 (1,052.0) 189.0 (1,367.0) 108.0 (4.766.0) 2.180.0 1,029.0 252.0 (56.0) (3,976.0) 122.0 (1,477.0) 51.0 (2,765.0) (3,976.0) (1,477.0) (2.765.0) Coca 23,009.0 26.934.0 26,934.0 13.094.0 13,094.0 23,009.0 (24,850.0) (24,850.0) (28,796.0) (28,796.0) (12.866.0) (12,866.0) 1,012.0 (1,103.0) 647.0 (118.0) 702.0 (111.0) (6,845.0) (6,845.0) (7,047.0) (7,047.0) (7.252.0) (7,252.0) 30 Cash from Investing 31 32 Short Term Debt Issued 33 Long-Term Debt Issued 34 Total Debt Issued 35 Short Term Debt Repaid 36 Long-Term Debt Repaid 37 Total Debt Repaid 38 39 Issuance of Common Stock 40 Repurchase of Common Stock 41 42 Common Dividends Paid 43 Total Dividends Paid 44 45 Special Dividend Paid 46 Other Financing Activities 47 Cash from Financing 48 49 Foreign Exchange Rate Adj. 50 Net Change in Cash 51 52 Supplemental Items 53 Cash Interest Paid 54 Cash Taxes Paid 55 Levered Free Cash Flow 56 Unlevered Free Cash Flow 57 Change in Net Working Capital 58 Net Debt Issued 59 Filing Date 60 Restatement Type 61 Calculation Type 310.0 (227.0) (9,004.0) (353.0) (6,786.0) (8,070.0) (72.0) (2.581.0) 76.0 373.0 (159.0) 2.915.0 NA 921.0 2,126.0 6,651.4 7,242.6 (1,090.0) (1,841.0) Feb-25-2021 NC REP 935.0 1,268.0 6,886.0 7,784.1 (1,061.0) (1,862.0) Feb-25-2021 NA 8,497.6 9,495.8 (2,105.0) 228.0 Feb-10-2022 o P REP REP N Cash Flow 3 For the Fiscal Period Ending Pepsi 12 months Dec-28-2019 12 months Dec-26-2020 USD 12 months Dec-25-2021 4 Currency USD USD 7,314.0 2,597.0 7,120.0 2,784.0 7,618.0 2,989.0 91.0 81.0 90.0 2,678.0 2,874.0 3,080.0 166.0 152.0 135.0 20.0 34.0 237.0 (287.0) (650.0) (190.0) 735.0 5 6 Net Income 7 Depreciation & Amort. 8 Amort. of Goodwill and Intangibles 9 Depreciation & Amort., Total 10 11 Other Amortization 12 Asset Writedown & Restructuring Costs 13 Stock-Based Compensation 14 Other Operating Activities 15 Change in Acc. Receivable 16 Change In Inventories 17 Change in Acc. Payable 18 Change in Inc. Taxes 19 Change in Other Net Operating Assets 20 Cash from Ops. 21 22 Capital Expenditure 23 Sale of Property, Plant, and Equipment 24 Cash Acquisitions 25 Divestitures 26 Invest in Marketable & Equity Securt. 27 Net (Inc.) Dec. in Loans Originated/Sold 28 Other Investing Activities 29 Cash from Investing 30 264.0 472.0 (420.0) (516.0) 766.0 (159.0) (9.0) 301.0 (227.0) (651.0) (582.0) 1,762.0 30.0 (287.0) (87.0) 9,649.0 26.0 159.0 10,613.0 11,616.0 (4,232.0) 170.0 (2.717.0) 253.0 (4,240.0) 55.0 (6,372.0) 6.0 (1,108.0) (4,625.0) 166.0 (61.0) 169.0 1,077.0 97.0 5.0 (8.0) (6,437.0) 40.0 (11,619.0) (3,269.0) Cash from Investing (6,437.0) (11,619.0) (3,269.0) Pepsi 6.0 Short Term Debt Issued Long-Term Debt Issued Total Debt Issued Short Term Debt Repaid Long-Term Debt Repaid Total Debt Repaid 4,621.0 4,627.0 (5.0) (3,970.0) (3,975.0) 4,077.0 13,809.0 17,886.0 (3.663.0) (1,830.0) (5,493.0) 442.0 4,122.0 4,564.0 (397.0) (3,455.0) (3,852.0) 185.0 Issuance of Common Stock Repurchase of Common Stock 329.0 (3,114.0) 179.0 (2,096.0) (1980) Common Dividends Paid Total Dividends Paid (5,304.0) (5,304.0) (5,509.0) (5,509.0) (5,815.0) (5,815.0) Special Dividend Paid Other Financing Activities Cash from Financing (1,052.0) (8,489.0) (1,148.0) 3,819.0 (5,664.0) (10,780.0) 78.0 Foreign Exchange Rate Adj. Net Change in Cash (129.0) 2.684.0 (114.0) (2.547.0) (5.199.0) Supplemental Items Cash Interest Paid Cash Taxes Paid Levered Free Cash Flow Unlevered Free Cash Flow Change in Net Working Capital Net Debt Issued Filing Date Restatement Type Calculation Type 1,076.0 2,226.0 4,954.4 5,534.4 20.0 1,156.0 1,770.0 5,599.4 6,385.0 (490.0) 12,393.0 Feb-10-2022 1,184.0 1,933.0 5,096.6 6,221.0 11.0 712.0 652.0 Feb-10-2022 Feb-10-2022 NC NC O REP REP REP 2 Cash Flow Coca 12 months Dec-31-2019 USD 12 months Dec-31-2020 USD Press Release 12 months Dec-31-2021 USD 8,920.0 9,771.0 1,452.0 1,245.0 120.0 1,365.0 7,747.0 1,333.0 203.0 1,536.0 1,452.0 (1,365.0) 78.0 (615.0) 337.0 1,642.0 For the Fiscal Period Ending 3 4 Currency 5 6 Net Income 7 Depreciation & Amort. 8 Amort. of Goodwill and Intangibles 9 Depreciation & Amort., Total 10 11 (Gain) Loss From Sale Of Assets 12 Asset Write down & Restructuring Costs 13 (Income) Loss on Equity Invest. 14 Stock-Based Compensation 15 Other Operating Activities 16 Change in Acc. Receivable 17 Change In Inventories 18 Change in Acc. Payable 19 Change in Inc. Taxes 20 Change in Other Net Operating Assets 21 Cash from Ops. 22 23 Capital Expenditure 24 Sale of Property, Plant, and Equipment 25 Cash Acquisitions 26 Divestitures 27 Invest. in Marketable & Equity Securt. 28 Net (Inc.) Dec. in Loans Originated/Sold 29 Other Investing Activities 30 Cash from Investing (467.0) 42.0 (421.0) 201.0 465.0 (158.0) (183.0) 1,318.0 96.0 (707.0) 10,471.0 (914.0) 239.0 (511.0) 126.0 931.0 882.0 99.0 (860.0) (16.0) 585.0 9,844.0 1,325.0 12,625.0 (2,054.0) 978.0 (5,542.0) 429.0 2,269.0 (1,177.0) 189.0 (1,052.0) 189.0 (1,367.0) 108.0 (4.766.0) 2.180.0 1,029.0 252.0 (56.0) (3,976.0) 122.0 (1,477.0) 51.0 (2,765.0) (3,976.0) (1,477.0) (2.765.0) Coca 23,009.0 26.934.0 26,934.0 13.094.0 13,094.0 23,009.0 (24,850.0) (24,850.0) (28,796.0) (28,796.0) (12.866.0) (12,866.0) 1,012.0 (1,103.0) 647.0 (118.0) 702.0 (111.0) (6,845.0) (6,845.0) (7,047.0) (7,047.0) (7.252.0) (7,252.0) 30 Cash from Investing 31 32 Short Term Debt Issued 33 Long-Term Debt Issued 34 Total Debt Issued 35 Short Term Debt Repaid 36 Long-Term Debt Repaid 37 Total Debt Repaid 38 39 Issuance of Common Stock 40 Repurchase of Common Stock 41 42 Common Dividends Paid 43 Total Dividends Paid 44 45 Special Dividend Paid 46 Other Financing Activities 47 Cash from Financing 48 49 Foreign Exchange Rate Adj. 50 Net Change in Cash 51 52 Supplemental Items 53 Cash Interest Paid 54 Cash Taxes Paid 55 Levered Free Cash Flow 56 Unlevered Free Cash Flow 57 Change in Net Working Capital 58 Net Debt Issued 59 Filing Date 60 Restatement Type 61 Calculation Type 310.0 (227.0) (9,004.0) (353.0) (6,786.0) (8,070.0) (72.0) (2.581.0) 76.0 373.0 (159.0) 2.915.0 NA 921.0 2,126.0 6,651.4 7,242.6 (1,090.0) (1,841.0) Feb-25-2021 NC REP 935.0 1,268.0 6,886.0 7,784.1 (1,061.0) (1,862.0) Feb-25-2021 NA 8,497.6 9,495.8 (2,105.0) 228.0 Feb-10-2022 o P REP REP N Cash Flow 3 For the Fiscal Period Ending Pepsi 12 months Dec-28-2019 12 months Dec-26-2020 USD 12 months Dec-25-2021 4 Currency USD USD 7,314.0 2,597.0 7,120.0 2,784.0 7,618.0 2,989.0 91.0 81.0 90.0 2,678.0 2,874.0 3,080.0 166.0 152.0 135.0 20.0 34.0 237.0 (287.0) (650.0) (190.0) 735.0 5 6 Net Income 7 Depreciation & Amort. 8 Amort. of Goodwill and Intangibles 9 Depreciation & Amort., Total 10 11 Other Amortization 12 Asset Writedown & Restructuring Costs 13 Stock-Based Compensation 14 Other Operating Activities 15 Change in Acc. Receivable 16 Change In Inventories 17 Change in Acc. Payable 18 Change in Inc. Taxes 19 Change in Other Net Operating Assets 20 Cash from Ops. 21 22 Capital Expenditure 23 Sale of Property, Plant, and Equipment 24 Cash Acquisitions 25 Divestitures 26 Invest in Marketable & Equity Securt. 27 Net (Inc.) Dec. in Loans Originated/Sold 28 Other Investing Activities 29 Cash from Investing 30 264.0 472.0 (420.0) (516.0) 766.0 (159.0) (9.0) 301.0 (227.0) (651.0) (582.0) 1,762.0 30.0 (287.0) (87.0) 9,649.0 26.0 159.0 10,613.0 11,616.0 (4,232.0) 170.0 (2.717.0) 253.0 (4,240.0) 55.0 (6,372.0) 6.0 (1,108.0) (4,625.0) 166.0 (61.0) 169.0 1,077.0 97.0 5.0 (8.0) (6,437.0) 40.0 (11,619.0) (3,269.0) Cash from Investing (6,437.0) (11,619.0) (3,269.0) Pepsi 6.0 Short Term Debt Issued Long-Term Debt Issued Total Debt Issued Short Term Debt Repaid Long-Term Debt Repaid Total Debt Repaid 4,621.0 4,627.0 (5.0) (3,970.0) (3,975.0) 4,077.0 13,809.0 17,886.0 (3.663.0) (1,830.0) (5,493.0) 442.0 4,122.0 4,564.0 (397.0) (3,455.0) (3,852.0) 185.0 Issuance of Common Stock Repurchase of Common Stock 329.0 (3,114.0) 179.0 (2,096.0) (1980) Common Dividends Paid Total Dividends Paid (5,304.0) (5,304.0) (5,509.0) (5,509.0) (5,815.0) (5,815.0) Special Dividend Paid Other Financing Activities Cash from Financing (1,052.0) (8,489.0) (1,148.0) 3,819.0 (5,664.0) (10,780.0) 78.0 Foreign Exchange Rate Adj. Net Change in Cash (129.0) 2.684.0 (114.0) (2.547.0) (5.199.0) Supplemental Items Cash Interest Paid Cash Taxes Paid Levered Free Cash Flow Unlevered Free Cash Flow Change in Net Working Capital Net Debt Issued Filing Date Restatement Type Calculation Type 1,076.0 2,226.0 4,954.4 5,534.4 20.0 1,156.0 1,770.0 5,599.4 6,385.0 (490.0) 12,393.0 Feb-10-2022 1,184.0 1,933.0 5,096.6 6,221.0 11.0 712.0 652.0 Feb-10-2022 Feb-10-2022 NC NC O REP REP REP