Hey! I have included two files you will need. The first titled TBBC is part 1 of the project. The second file is an example of our lecture 6.
- Starting from your Excel file from part 1 of the project, add another worksheet for historical analysis & forecast assumptions. Similar to our lecture 6, you will calculate revenue growth, % or revenue, efficiency measures (Inventory days, etc.) for some income statement and balance sheet items. Then you will make assumptions for these ratios. Below this table, use texts (words) to describe your historical analysis results and explain why you make such assumptions for the next five years of these ratios.
- Based on your forecast assumptions, project the next five years of the income statement and balance sheet. Finally, project the statement of cash flow.
Thank you.
Class 6 - Objectives Class # 6 Financial Statement Forecasting Use Home Depot to analyze historical statement and to forecast over the next 5 years. Financial Analysis Class # 6 Financial Analysis Forecast Tools The Home Depot, Inc. and Subsidiaries for the Years 2011 through 2018 (FYE February 3) HistoricalForecast 20112012201320142015201620172018 Income Statement Revenue Annual Growth Raten/a4%6.19%6%6%6%6%6% Annual Absolute Value Increasen/a$ 2,398$ 4,359 Gross Margin34%34%35%35%35%35%35%35% SG&A % of Revenue23%23%22%22%22%22%22%22% SG&A Annual Growth Raten/a1%3% Annual Absolute Value Increasen/a$ 179$ 480 EBITDA Margin11%12%12% Depreciation: % of revenue2%2%2%2%2%2%2%2% Income tax40% Balance Sheeet Inventory: Days in Inventory (DII) Michael Christie: (Inventory/COGS)*365 8782808080808080 Account Receivable: Average Collection (DSO) Michael Christie: (AR/Revenue)*365 66777777 Gross Fixed Assets: % of revenue56%55%51%51%51%51%51%51% Other Current Assets: % of Revenue1.8%1.4%1.0%1.0%1.0%1.0%1.0%1.0% Account Payable: Days Payable Outstanding (DPO) Michael Christie: (AP/(COGS/365) 3938404040404040 Accrued Salaries & Benefits % of Revenue1.9%1.9%1.9%1.9%1.9%1.9%1.9%1.9% Sales Tax Payable % of Revenue0.5%0.6%0.6%0.6%0.6%0.6%0.6%0.6% Deferred Revenue % of Revenue1.7%1.6%1.7%1.7%1.7%1.7%1.7%1.7% Income Tax Payable % of Revenue0.0%0.0%0.0%0.0%0.0%0.0%0.0%0.0% Other Current Accrued Expenes & Other Liabilities2.2%2.2%2.1%2.1%2.1%2.1%2.1%2.1% Net Working Capital Current Assets (less cash)$ 12,934$ 12,533$ 12,878$ 13,601$ 14,417$ 15,282$ 16,199$ 17,171 Less: Current Liabilities10,1229,37611,46211,95812,59613,27213,98914,749 Net Woring Capital$ 2,812$ 3,157$ 1,416$ 1,643$ 1,821$ 2,010$ 2,210$ 2,422 % of revenue4%4%2%2%2%2%2%2% &"Calibri,Italic"&8&D &T P&L The Home Depot, Inc. and Subsidiaries Income Statement for the Historical Years 2011 through 2013, and Forecast Years 2014 through 2018 (FYE February 3, $ in millions) HistoricalForecast 20112012201320142015201620172018 Net Revenue$ 67,997$ 70,395$ 74,754$ 79,239$ 83,994$ 89,033$ 94,375$ 100,038 Cost of Revenue44,69346,13348,91251,50654,59657,87261,34465,025 Gross Profit23,30424,26225,84227,73429,39831,16233,03135,013 Operating Expense Selling, General and Administrative15,84916,02816,50817,43318,47919,58720,76322,008 Depreciation and Amortization1,6161,5731,5681,5851,6801,7811,8882,001 Total Operating Expense17,46517,60118,07619,01720,15821,36822,65024,009 Earnings Before Interest & Taxes (EBIT)5,8396,6617,7668,7169,2399,79410,38111,004 Other Operating Expense (Income) Interest and Investment (Income)(15)(13)(20)(20) Michael Christie: Assume remains constant (20)(20)(20)(20) Interest Expense530606632632 Michael Christie: Assume remains constant 632632632632 Other51-(67)(67) Michael Christie: Assume remains constant (67)(67)(67)(67) Total Other Operating Expense (Income)566593545545545545545545 Earnings Before Taxes (EBT)5,2736,0687,2218,1718,6949,2499,83610,459 Income Tax1,9352,1852,6863,2693,4783,6993,9354,184 Net Income$ 3,338$ 3,883$ 4,535$ 4,903$ 5,217$ 5,549$ 5,902$ 6,275 EBITDA$ 7,455$ 8,234$ 9,334$ 10,301$ 10,919$ 11,574$ 12,269$ 13,005 % of revenue11%12%12%13%13%13%13%13% &"Calibri,Regular"&D &T BS The Home Depot, Inc. and Subsidiaries Balance Sheet for the Historical Years 2011 through 2013, and Forecast Years 2014 through 2018 (FYE February 3, $ in millions) HistoricalForecast 20112012201320142015201620172018 Assets Current Assets Cash$ 545$ 1,987$ 2,505$ 5,112$ 7,674$ 10,513$ 13,646$ 17,091 Short-term Investments---- Michael Christie: Assume Remains constant ---- Net Receivables1,0851,2451,3951,520 Michael Christie: = (AR Days/365)*Revenue 1,6111,7071,8101,919 Inventory10,62510,32510,71011,289 Michael Christie: =(Inventory Days/365)*COGS 11,96612,68413,44514,252 Other Current Assets1,2249637737928408909441,000 Total Current Assets13,47914,52015,38318,71322,09125,79529,84534,262 Long Term Assets Property Plant & Equipment (PPE), gross38,38538,97538,49140,41242,83745,40748,13151,019 Accumulated Depreciation of PPE(13,325)(14,527)(14,422)(16,007)(17,687)(19,467)(21,355)(23,356) PPE, net25,06024,44824,06924,40525,15025,94026,77727,664 Long Term Investments---- Michael Christie: Assume Long Term Assets remain constant.---- Note Receivables139135140140 Michael Christie: Assume Long Term Assets remain constant.140140140140 Goodwill1,1871,1201,1701,170 Michael Christie: Assume Long Term Assets remain constant.1,1701,1701,1701,170 Other Assets260295333333 Michael Christie: Assume Long Term Assets remain constant.333333333333 Total Long Term Assets26,64625,99825,71226,04826,79327,58328,42029,307 Total Assets$ 40,125$ 40,518$ 41,095$ 44,761$ 48,884$ 53,378$ 58,265$ 63,568 Liabilities Current Liabilities Current Portion of Long-Term Debt$ 1,042$ 30$ 1,3211,321 Michael Christie: Assume Current Portion of Long term Debt remain constant.1,3211,3211,3211,321 Accounts Payable4,7174,8565,3765,644 Michael Christie: =(AP Days/365)*COGS 5,9836,3426,7237,126 Accrued Salaries and Benefits1,2901,3721,4141,5061,5961,6921,7931,901 Sales Tax Payable368391472475504534566600 Deferred Revenue1,1771,1471,2701,3471,4281,5141,6041,701 Income Tax Payable132322$ -$ -$ -$ -$ - Other Current Accrued Expenses and Other Liabilities1,5151,5571,5871,6641,7641,8701,9822,101 Total Current Liabilities10,1229,37611,46211,95812,59613,27213,98914,749 Long Term Liabilities Long Term Debt8,70710,7589,4759,475 Michael Christie: Assume Remains constant Michael Christie: Assume Remains constant Michael Christie: = (AR Days/365)*Revenue Michael Christie: =(Inventory Days/365)*COGS 9,4759,4759,4759,475 Deferred Income Taxes272340319319319319319319 Other Deferred Revenue and Long-Term Liabilities 2,1352,1462,0512,0512,0512,0512,0512,051 Total Long Term Liabilities11,11413,24411,84511,84511,84511,84511,84511,845 Total Liabilities21,23622,62023,30723,80324,44125,11725,83426,594 Equity Common Stock86878888 Michael Christie: Assume Common Stock, Paid-In-Capital, & Capital in Excessss of Par remain constant. Michael Christie: Assume Long Term Assets remain constant. Michael Christie: Assume Long Term Assets remain constant.88888888 Paid-In Capital6,5566,9667,9487,9487,9487,9487,9487,948 Capital Stock in excess of par-------- Current Period Net Income$ 3,338$ 3,883$ 4,535$ 4,903$ 5,217$ 5,549$ 5,902$ 6,275 Plus: Prior Period Retained Earnings13,22614,99517,24620,04923,22026,70430,52234,691 Less: Current Period Dividends(1,569)(1,632)(1,732)(1,732)(1,732)(1,732)(1,732)(1,732) Current Period Retained Earnings14,99517,24620,04923,22026,70430,52234,69139,235 Treasury Stock(3,193)(6,694)(10,694)(10,694) Michael Christie: Assume Treasury Stock remain constant. Michael Christie: Assume Long Term Assets remain constant.(10,694)(10,694)(10,694)(10,694) Other445293397397 Michael Christie: Assume Other remain constant. Michael Christie: Assume Long Term Assets remain constant.397397397397 Total Equity18,88917,89817,78820,95924,44328,26132,43036,974 Total Liabilities and Equity$ 40,125$ 40,518$ 41,095$ 44,761$ 48,884$ 53,378$ 58,265$ 63,568 check-------- Cash Flow The Home Depot, Inc. and Subsidiaries Statement of Cash Flows for the Historical Years 2011 through 2013, and Forecast Years 2014 through 2018 (FYE February 3, $ in millions) HistoricalForecast 20112012201320142015201620172018 Net Income$ 3,883$ 4,535$ 4,903$ 5,217$ 5,549$ 5,902$ 6,275 Cash Flow from Operations Depreciation1,5731,5681,5851,6801,7811,8882,001 Change in Short-term Investments------- Change in Net Receivables(160)(150)(125)(91)(97)(102)(109) Change in Note Receivable4(5)----- Change in Inventory300(385)(579)(677)(718)(761)(807) Change in Other Current Assets261190(19)(48)(50)(53)(57) Change in Accounts Payable139520268339359381403 Change in Accrued Salaries and Benefits8242929096101108 Change in Sales Tax Payable2381329303234 Change in Deferred Revenue(19)287781869196 Change in Income Tax Payable78(22)(22)---- Change in Other Current Liabilities423077100106112119 Total Cash Flow from Operations6,2066,4326,2606,7197,1417,5898,064 Cash Flow from Investing Capital Expenditures (CAPX)(590)(1,312)(1,921)(2,425)(2,570)(2,724)(2,888) Investments------- Goodwill67(50)----- Other Assets(35)(38)----- Total Cash Flow from Investing(558)(1,400)(1,921)(2,425)(2,570)(2,724)(2,888) Cash Flow from Financing Repurchase of Common Stock(3,501)(4,000)----- Sale of Stock411983----- Net Borrowing1,0398----- Other(152)104----- Cash Dividends(1,632)(1,732)(1,732)(1,732)(1,732)(1,732)(1,732) Total Cash Flow from Financing(3,835)(4,637)(1,732)(1,732)(1,732)(1,732)(1,732) Total Change in Cash1,8133952,6072,5622,8393,1333,445 Effect of Exchange Rate Changes on Cash- Michael Christie: Given (371)123----- Beginning Period Cash5451,9872,5055,1127,67410,51313,646 Ending Period Cash $ 545$ 1,987$ 2,505$ 5,112$ 7,674$ 10,513$ 13,646$ 17,091 &D &T