Is your company likely to need additional external financing in the next few years?
1)
Provide your financial planning model for the next 3-5 years
. Forecast
income statements and cash flow statements (complete the summary tab)
2)
Discuss all the assumptions
3)
Examine the implications of the net change in cash as we did in the lectures and from there
conclude
whether your company is likely to need additional external financing in the next few years.
CoComplete the excel sheet for forecasting
Summary Sheet The Kroger Co. Year 2022Year2023Year 2024Year 2025 Revenue142,960146,347148,056 EBITDA7,1127,2997,362 EBIT4,1814,2234,217 Capital Espenditure(3,864)(3,826)(3,933) Net Income (Excl. Excep)2,7742,8302,812 Depreciation & Amortization28242,9443,0663,101 Cash From Operations5,4155,7226,540 Effective Tax Rate %18.7722.9223.1123.18-------34.67 A29.33 A34.49 A32.91 A34.05 A33.78 A32.84 A33.90 A33.22 A23.67 A23.20 A18.77 A22.92 E+23.11 E+23.18 E+ Income Statement 2022 (actual)2023 (forecast)2024 (forecast)2025 (forecast)Methodology Revenue137,888142,960146,347148,056Forecast per CIQ Projected YoY growth rate of revenue 3.68%2.37%1.17% COGS106,555110,474113,092114,412Based on revenue growth rate SG&A 24,80025,71226,32126,945Based on revenue growth rate Others- 0- 0 EBIT4,3104,1814,2234,217Forecast per CIQ3,829 3,7093,829 Debt11,31210,88110,452Forecast per CIQ Interest expense (negative) (154.00)Based on latest total debt 20,440 with interest of?? Other non-operating income000 Income before tax2,8022,7742,8302,812Forecast per CIQ Income Taxes=Based on current (2022) corporate tax rate of 19% EarningsERROR:#VALUE! Cash Flow Statement Year 2022Year2023Year 2024Year 2025Methodology Earnings Depreciation & Amortization Increase in net working capital Other adjustments Cash flow from operations Capital expenditures (negative) Other cash from investing (negative) Cash flow from investing Dividends paid Net issuance of stock Net borrowing Other cash from financing Cash flow from financing Foreign exchange rate adj. Net change in cash Cash Flow Forecast The Kroger Co. (NYSE:KR) > Financials > Cash Flow In Millions of the reported currency, except per share items.Template:Standard Restatement:Latest Filings Period Type:Annual Order:Latest on Right Currency:Reported Currency Conversion:Historical Units:S&P Capital IQ (Default) Decimals:Capital IQ (Default) Source:Capital IQ & Proprietary Data Cash Flow - Actuals Forecasts For the Fiscal Period Ending12 months Feb-03-201812 months Feb-02-201912 months Feb-01-202012 months Jan-30-202112 months Jan-29-202212 months Jan 202312 months Jan 202312 months Jan 2023Methodology CurrencyUSDUSDUSDUSDUSDUSDUSDUSD Revenue (from Income Statement)123,280.00121,852.00122,286.00132,498.00137,888.00142,960.38146,346.69148,056.22For reference; from "Income Statement Forecast" tab Revenue Growth Rate YoY3.7%2.4%1.2%For reference; from "Income Statement Forecast" tab Net Income1,907.003,110.001,659.002,585.001,655.002,570.212,631.092,661.83From "Income Statement Forecast" tab Depreciation & Amort.2,436.002,465.003,289.003,373.003,429.002,927.882,997.243,032.25From "Income Statement Forecast" tab Amort. of Goodwill and Intangibles5980856759616363Based on revenue growth rate Depreciation & Amort., Total2,495.002,545.003,374.003,440.003,488.002,989.053,059.863,095.60 (Gain) Loss From Sale Of Assets-31-1,780.0078-59-44 (Gain) Loss On Sale Of Invest.--228-157-1,105.00821 Asset Writedown & Restructuring Costs181561207064 Stock-Based Compensation151154155185203 Other Operating Activities-1496-253158232 Change in Acc. Receivable61-208-36-90-61 Change In Inventories-23-354-351780 Change in Acc. Payable158213342330438 Change in Inc. Taxes-96289-1422416 Change in Other Net Operating Assets-1,241.00361-1251,270.00-702 Cash from Ops.3,413.004,164.004,664.006,815.006,190.00 Capital Expenditure-2,809.00-2,967.00-3,128.00-2,865.00-2,614.00 Sale of Property, Plant, and Equipment13885273165153 Cash Acquisitions-16-241--- Divestitures-2,169.00327-- Invest. in Marketable & Equity Securt.--157--- Net (Inc.) Dec. in Loans Originated/Sold----- Other Investing Activities-20-75-83-114-150 Cash from Investing-2,707.00-1,186.00-2,611.00-2,814.00-2,611.00 Short Term Debt Issued696-350-- Long-Term Debt Issued1,523.002,236.008131,049.0056 Total Debt Issued2,219.002,236.001,163.001,049.0056 Short Term Debt Repaid--1,321.00--1,150.00- Long-Term Debt Repaid-788-1,372.00-2,304.00-747-1,442.00 Total Debt Repaid-788-2,693.00-2,304.00-1,897.00-1,442.00 Issuance of Common Stock516555127172 Repurchase of Common Stock-1,633.00-2,010.00-465-1,324.00-1,647.00 Common Dividends Paid-443-437-486-534-589 Total Dividends Paid-443-437-486-534-589 Special Dividend Paid----- Other Financing Activities-87-57-46-1345 Cash from Financing-681-2,896.00-2,083.00-2,713.00-3,445.00 Net Change in Cash2582-301,288.00134 Supplemental Items Cash Interest Paid656614523564607 Cash Taxes Paid348600706659513 Levered Free Cash Flow269.61,893.602,141.104,188.904,831.30 Unlevered Free Cash Flow645.32,281.102,518.004,528.905,188.10 Change in Net Working Capital723-876-447-1,782.00-1,793.00 Net Debt Issued1,431.00-457-1,141.00-848-1,386.00 Filing DateApr-01-2020Mar-30-2021Mar-29-2022Mar-29-2022Mar-29-2022 Restatement TypeNCNCNCNCO Calculation TypeREPREPREPREPREP Income Statement Forecast The Kroger Co. (NYSE:KR) > Financials > Income Statement In Millions of the reported currency, except per share items.Template:Standard Restatement:Latest Filings Period Type:Annual Order:Latest on Right Currency:Reported Currency Conversion:Historical Units:S&P Capital IQ (Default) Decimals:Capital IQ (Default) Source:Capital IQ & Proprietary Data Income Statement Forecasts For the Fiscal Period Ending12 months Jan-28-2017Reclassified 12 months Feb-03-2018Reclassified 12 months Feb-02-2019Reclassified 12 months Feb-01-202012 months Jan-30-202112 months Jan-29-20222023 (forecast)2024 (forecast)2025 (forecast)Methodology CurrencyUSDUSDUSDUSDUSDUSDUSDUSDUSD Revenue115,337.00123,280.00121,852.00122,286.00132,498.00137,888.00142,960.38146,346.69148,056.22Forecast per CIQ Other Revenue--------- Total Revenue115,337.00123,280.00121,852.00122,286.00132,498.00137,888.00142,960.38146,346.69148,056.22 Revenue Growth Rate YoY6.9%-1.2%0.4%8.4%4.1%3.7%2.4%1.2%Projected YoY growth rate of revenue Average Annual Growth Rate from 20173.9% Cost Of Goods Sold88,785.0095,104.0094,351.0094,440.00100,709.00106,555.00110,474.76113,091.58114,412.64Based on revenue growth rate Gross Profit26,552.0028,176.0027,501.0027,846.0031,789.0031,333.0032,485.6233,255.1133,643.58 Selling General & Admin Exp.20,750.0023,290.0022,359.0022,525.0025,863.0024,800.0025,712.3026,321.3526,628.82Based on revenue growth rate R & D Exp.--------- Depreciation & Amort.2,340.002,436.002,465.002,649.002,747.002,824.002,927.882,997.243,032.25Based on revenue growth rate Other Operating Expense/(Income)--------- Other Operating Exp., Total23,090.0025,726.0024,824.0025,174.0028,610.0027,624.0028,640.1829,318.5829,661.07 Operating Income3,462.002,450.002,677.002,672.003,179.003,709.003,845.443,936.533,982.51 Interest Expense-522-601-620-603-544-571-592-606-613Based on revenue growth rate Interest and Invest. Income--------- Net Interest Exp.-522-601-620-603-544-571-592-606-613 Other Non-Operating Inc. (Exp.)--------- EBT Excl. Unusual Items2,940.001,849.002,057.002,069.002,635.003,138.003,253.443,330.503,369.40 Restructuring Charges--184--52-111-136---Unusual / one-off items without basis to repeating; exclude from forecast Impairment of Goodwill--110--19----- Gain (Loss) On Sale Of Invest.--2281571,105.00-821--- Gain (Loss) On Sale Of Assets--1,782.00176----- Asset Writedown-26-71-56-339-70-64--- Other Unusual Items---33-11-189-66--- EBT Incl. Unusual Items2,914.001,484.003,978.001,981.003,370.002,051.003,253.443,330.503,369.40 Income Tax Expense957-405900469782385683699708Based on current (2022) corporate tax rate of 21% Earnings from Cont. Ops.1,957.001,889.003,078.001,512.002,588.001,666.002,570.212,631.092,661.83 Earnings of Discontinued Ops.--------- Extraord. Item & Account. Change--------- Net Income to Company1,957.001,889.003,078.001,512.002,588.001,666.002,570.212,631.092,661.83 Minority Int. in Earnings181832147-3-11---One-off, immaterial item Net Income1,975.001,907.003,110.001,659.002,585.001,655.002,570.212,631.092,661.83 Supplemental Items OPAT2,505.002,855.001,777.002,203.002,397.003,324.003,162.223,237.123,274.94 Effective Tax Rate %32.84%NM22.62%23.67%23.20%18.77%21.00%21.00%21.00% Capital IQ Forecast The Kroger Co. (NYSE:KR) > Capital IQ Estimates > Consensus Currency: Reported CurrencyConversion: Today's Spot Rate Units: Capital IQ (Default)Decimals: CapitalIQ (Default) Consolidation: ConsolidatedAcctg. Standard: US GAAP Current Fiscal Year End: Jan-31-2023 | FQ1 2023 Earnings Release Date: Jun-16-2022 NYSE:KR (USD)MeanMedianHigh/LowStd. Dev.No. of Estimates.NYSE:KR - Recommendation: Hold (2.85) Target Price54.8155.575.00/38.008.1224/241 - Buy6 LT Growth12.69%12.88%20.00%/5.00%5.474/42 - Outperform1 Price VolatilityAbove Average (3.67)3 - Hold13 Industry RecommendationOutperform (2.50)4 - Underperform3 5 - Sell3 0 - No Opinion0 Market SummaryNYSE:KRNYSE:KR (US GAAP|USD) CurrencyUS DollarCurrent QuarterCurrent YearNTM Latest Price/Last Close Price52.15/52.15EPS Normalized1.263.83.8 52 Wk. High/Low62.78/36.77 Guidance-3.75/-/3.85- Potential Upside/Diff. from Target Price4.33 %/2.27Company Level (US GAAP|USD) Revenue42,873.43142,960.38142960.38 EBITDA2,300.577,111.67111.6 Fiscal Years NYSE:KR (USD)200020012002200320042005200620072008200920102011201220132014201520162017201820192020202120222023202420252026202720282029203020312032 EPS Normalized0.57 A0.67 A0.74 A0.82 A0.74 A0.58 A0.66 A0.77 A0.85 A0.96 A0.86 A0.88 A1.00 A1.32 A1.43 A1.76 A2.06 A2.12 A2.04 A2.11 A2.19 A3.47 A3.68 A3.80 E+3.98 E+4.15 E+4.42 E+4.51 E+4.78 E+5.18 E+5.39 E+5.72 E+6.08 E+ Final Est.0.57 E+0.68 E+0.74 E+0.83 E+0.73 E+0.56 E+0.64 E+0.74 E+0.85 E+0.95 E+0.83 E+0.88 E+1.00 E+1.24 E+1.39 E+1.71 E+2.05 E+2.10 E+2.04 E+2.16 E+2.20 E+3.32 E+3.52 E+---------- Median0.57 E+0.68 E+0.74 E+0.83 E+0.73 E+0.55 E+0.64 E+0.74 E+0.85 E+0.96 E+0.83 E+0.88 E+1.00 E+1.23 E+1.40 E+1.70 E+2.04 E+2.11 E+2.05 E+2.15 E+2.19 E+3.35 E+3.51 E+3.81 E+4.04 E+4.26 E+4.42 E+4.51 E+4.78 E+5.18 E+5.39 E+5.72 E+6.08 E+ High0.57 E+0.69 E+0.75 E+0.84 E+0.74 E+0.67 E+0.68 E+0.77 E+0.86 E+0.96 E+0.85 E+0.89 E+1.13 E+1.36 E+1.41 E+1.83 E+2.09 E+2.15 E+2.08 E+2.25 E+2.30 E+3.40 E+3.64 E+4.12 E+4.34 E+4.63 E+4.60 E+4.51 E+4.78 E+5.18 E+5.39 E+5.72 E+6.08 E+ Low0.57 E+0.67 E+0.74 E+0.82 E+0.70 E+0.53 E+0.63 E+0.72 E+0.82 E+0.94 E+0.82 E+0.86 E+0.96 E+1.20 E+1.38 E+1.67 E+2.01 E+2.05 E+2.00 E+2.08 E+2.16 E+2.75 E+3.45 E+3.40 E+3.50 E+3.38 E+4.23 E+4.51 E+4.78 E+5.18 E+5.39 E+5.72 E+6.08 E+ Std. Dev.-0.010.00.010.020.040.010.010.010.010.010.010.030.030.010.030.020.020.020.040.030.130.050.130.170.330.19------ No. of Estimates1/18/813/1314/144/1212/1216/1613/1313/1413/1317/1720/2020/2022/2219/1921/2227/2725/2625/2525/2520/2023/2424/2525/2524/249/92/21/11/11/11/11/11/1 Guidance Low----0.75 GL-0.62 GL0.71 GL0.82 GL0.94 GL0.80 GL-0.98 GL1.22 GL1.37 GL1.66 GL2.02 GL2.10 GL2.00 GL2.00 GL2.15 GL3.30 GL3.40 GL3.75 GL--------- Guidance-----------0.89 G--------------------- Guidance High----0.79 GH--0.72 GH0.84 GH0.96 GH0.85 GH-1.00 GH1.23 GH1.40 GH1.68 GH2.04 GH2.15 GH2.05 GH2.15 GH2.25 GH3.35 GH3.50 GH3.85 GH--------- Acctg. StandardUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAPUS GAAP EPS (GAAP)0.37 A0.52 A0.63 A0.78 A0.21 A( 0.09 A)0.66 A0.77 A0.85 A0.95 A0.06 A0.87 A0.51 A1.39 A1.45 A1.72 A2.06 A2.05 A2.09 A3.76 A2.04 A3.27 A2.17 A3.86 E+4.01 E+3.96 E+4.23 E+4