Hey! I have included two files you will need. The first titled TBBC is part 2 of the project. The second file is an example of our lecture 8.
- Starting from your Excel file from part 2 of the project, add another worksheet for Discounted Cash Flow (DCF) valuation. Similar to our lecture 8, you will calculate free cash flow to the firm for the next five years and the terminal value of the firm at the end of the fifth year. Then you will discount these cash flows to present and find out the business enterprise value as well as the equity value per share. Finally, based on your valuation, make investment advice for this stock.
- To discount the cash flows, the discount rate will be the weighted average cost of capital. For simplicity, you don't have to estimate the WACC yourself. You can use the industry average cost of capital from this database (http://people.stern.nyu.edu/adamodar/New_Home_Page/datafile/wacc.htm)Links to an external site.). (No need to use the build-up method from lecture)
- You need to also explain the steps and the assumptions so someone out of our class can understand. Please write on the DCF worksheet below the calculations. Don't create another worksheet.
Thank you!
Financial Analysis The Boston Beer Company Inc. Income Statement for the Years 2018 through 2020 (FYE February 3) HistoricalForecast 20182019202020212022202320242025 Income Statement Revenue Annual Growth Raten/a26%39.33%39%39%39%39%39% Annual Absolute Value Increasen/a$ 271,613$ 522,705$ 728,318$ 1,014,766$ 1,413,873$ 1,969,949$ 2,744,730 Gross Margin48%46%44%44%44%44%44%44% SG&A % of Revenue37%35%31%31%31%31%31%31% SG&A Annual Growth Raten/a18%22%40%39%39%39%39% Annual Absolute Value Increasen/a$ 72,892$ 100,991$ 229,596$ 314,577$ 438,301$ 610,684$ 850,866 EBITDA Margin11%11%13%13%13%13%13%13% Depreciation: % of revenue0%0%0%0%0%0%0%0% Income tax20%24%21%21%21%21%21%21% Balance Sheeet Inventory: Days in Inventory (DII) Michael Christie: (Inventory/COGS)*365 4754464646464646 Account Receivable: Average Collection (DSO) Michael Christie: (AR/Revenue)*365 1215151515151515 Gross Fixed Assets: % of revenue37%41%34%34%34%34%34%34% Other Current Assets: % of Revenue1.2%0.9%1.6%1.6%1.6%1.6%1.6%1.6% Account Payable: Days Payable Outstanding (DPO) Michael Christie: (AP/(COGS/365) 3239434040404040 Accrued Salaries & Benefits % of Revenue6.9%7.5%7.0%7.0%7.0%7.0%7.0%7.0% Sales Tax Payable % of Revenue0.0%0.0%0.0%0.0%0.0%0.0%0.0%0.0% Deferred Revenue % of Revenue0.0%0.0%0.0%0.0%0.0%0.0%0.0%0.0% 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 Liabilities0.0%0.4%0.4%0.4%0.4%0.4%0.4%0.4% Net Working Capital Current Assets (less cash)$ 123,172$ 181,978$ 249,891$ 329,409$ 458,965$ 639,476$ 890,982$ 1,241,405 Less: Current Liabilities120,514180,649259,423349,272486,641678,036944,7081,316,262 Net Woring Capital$ 2,658$ 1,329$ (9,532)$ (19,863)$ (27,676)$ (38,561)$ (53,727)$ (74,857) % of revenue0%0%-1%-1%-1%-1%-1%-1% &"Calibri,Italic"&8&D &T In the current forecast we are forecasting the revenue, financial statements, and cash flow statement for the next 5 years from 2021 based on the data available from 2018 to 2020 for forecasting from 2021 to 2025. In assuming the income statement, we assume the revenue growth of 39% from 2021 to 2025 which is the stable growth rate as the case was in 2020. The increase in absolute value in 2021 to 2025 shall be 728,318 in 2021, 1,014,766 in 2022, 1,413,873 in 2023, 1,969,949 in 2024 and 2,744,730 in 2025. The increase in gross margin is 44% which is same gross margin from 2021 to 2025. The selling, general and administrative exp would increase on an average by 31%, which was same as 2020 and the assumption in forecast remains the same. The EBITDA margin has remained stable at 13% which had increased from 11% in 2019 to 13% in 2020 and henceforth. The average inventory days are 46 days which was the same as the forecast for 2021 till 2025. The average collection days were 15 days which was based on the realistic data of 2020. % of revenue of gross fixed assets shall be 34% which shall remain stable which is same as 2020. The ratio of other current assets with % of revenue shall be 1.6%. which shall remain same as 2020. The accounts payable days of outstanding shall be equivalent to 40 days, however for 2020 shall be 43 days. The accrued salaries and benefits shall be 7% which is same as 2020 so assumed to be remaining constant. The total accrued liabilities and expenses shall be 0.4% which remaining stable as per 2020.The net working capital requirement shall be -1% of the total revenue. P&L The Boston Beer Company Inc. Income Statement for the Historical Years 2018 through 2020, and Forecast Years 2021 through 2025 (FYE February 3, $ in millions) HistoricalForecast 20182019202020212022202320242025 Net Revenue$ 1,057,495$ 1,329,108$ 1,851,813$ 2,580,131$ 3,594,897$ 5,008,769$ 6,978,718$ 9,723,448 Cost of Revenue545,252714,9421,037,3611,444,8732,013,1422,804,9113,908,0825,445,131 Gross Profit512,243614,166814,4521,135,2581,581,7552,203,8593,070,6364,278,317 Operating Expense Selling, General and Administrative396,362469,254570,245799,8411,114,4181,552,7192,163,4033,014,269 Depreciation and Amortization----- Total Operating Expense396,362469,254570,245799,8411,114,4181,552,7192,163,4033,014,269 Earnings Before Interest & Taxes (EBIT)115,881144,912244,207335,417467,337651,140907,2331,264,048 Other Operating Expense (Income) Interest and Investment (Income)(887)(1,189)222222 Michael Christie: Assume remains constant 222222222222 Interest Expense1,292647(199)(199) Michael Christie: Assume remains constant (199)(199)(199)(199) Other---- Michael Christie: Assume remains constant ---- Total Other Operating Expense (Income)405(542)232323232323 Earnings Before Taxes (EBT)116,286144,370244,230335,440467,360651,163907,2561,264,071 Income Tax23,62334,32952,27070,44298,146136,744190,524265,455 Net Income$ 92,663$ 110,041$ 191,960$ 264,998$ 369,214$ 514,419$ 716,733$ 998,616 EBITDA$ 115,881$ 144,912$ 244,207$ 335,417$ 467,337$ 651,140$ 907,233$ 1,264,048 % of revenue11%11%13%13%13%13%13%13% &"Calibri,Regular"&D &T BS The Boston Beer Company Inc. Balance Sheet for the Historical Years 2018 through 2020, and Forecast Years 2021 through 2025 (FYE February 3, $ in millions) HistoricalForecast 20182019202020212022202320242025 Assets Current Assets Cash$ 108,399$ 36,670$ 163,282$ 346,886$ 378,915$ 423,525$ 485,663$ 572,225 Short-term Investments---- Michael Christie: Assume Remains constant ---- Net Receivables34,07354,40478,358106,033 Michael Christie: = (AR Days/365)*Revenue 147,735205,840286,797399,594 Inventory70,249106,038130,910182,094 Michael Christie: =(Inventory Days/365)*COGS 253,711353,496492,525686,236 Income Tax Receivable5,7149,45910,393 Other Current Assets13,13612,07730,23041,28257,51880,140111,659155,575 Total Current Assets231,571218,648413,173676,294837,8791,063,0001,376,6451,813,630 Long Term Assets Property Plant & Equipment (PPE), gross389,789541,068623,083877,2451,222,2651,702,9822,372,7643,305,972 Accumulated Depreciation of PPE----- PPE, net389,789541,068623,083877,2451,222,2651,702,9822,372,7643,305,972 Long Term Investments---- Michael Christie: Assume Long Term Assets remain constant.---- Note Receivables- Michael Christie: Assume Long Term Assets remain constant.---- Operating right-of-use assets53,75858,483 Intangible assets2,099104,272103,930 Goodwill3,683112,529112,529112,529 Michael Christie: Assume Long Term Assets remain constant.112,529112,529112,529112,529 Other Assets12,70923,78267,62767,627 Michael Christie: Assume Long Term Assets remain constant.67,62767,62767,62767,627 Total Long Term Assets408,280835,409965,6521,057,4011,402,4211,883,1382,552,9203,486,128 Total Assets$ 639,851$ 1,054,057$ 1,378,825$ 1,733,695$ 2,240,300$ 2,946,138$ 3,929,565$ 5,299,758 Liabilities Current Liabilities Current Portion of Long-Term Debt- Michael Christie: Assume Current Portion of Long term Debt remain constant.---- Accounts Payable47,10276,374121,647158,342 Michael Christie: =(AP Days/365)*COGS Michael Christie: Assume Remains constant 220,618307,387428,283596,727 Accrued Expenses and other current liabilities73,41299,107129,544180,609251,643350,614488,510680,641 Sales Tax Payable----- Deferred Revenue----- Income Tax Payable$ -$ -$ -$ -$ - Other Current Accrued Expenses and Other Liabilities-5,1688,23210,32114,38020,03527,91538,894 Total Current Liabilities120,514180,649259,423349,272486,641678,036944,7081,316,262 Long Term Liabilities Long Term Debt- Michael Christie: Assume Remains constant Michael Christie: = (AR Days/365)*Revenue Michael Christie: =(Inventory Days/365)*COGS ---- Deferred Income Taxes49,16975,01092,66592,66592,66592,66592,66592,665 Non Current Operating lease liabilities53,94059,171 Other Deferred Revenue and Long-Term Liabilities 9,8518,82210,59910,59910,59910,59910,59910,599 Total Long Term Liabilities59,020137,772162,435103,264103,264103,264103,264103,264 Total Liabilities179,534318,421421,858452,536589,905781,3001,047,9721,419,526 Equity Common Stock115121122122 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. Michael Christie: Assume Long Term Assets remain constant. Michael Christie: Assume Long Term Assets remain constant.122122122122 Paid-In Capital405,711571,784599,737599,737599,737599,737599,737599,737 Capital Stock in excess of par-------- Current Period Net Income$ 92,663$ 110,041$ 191,960$ 264,998$ 369,214$ 514,419$ 716,733$ 998,616 Plus: Prior Period Retained Earnings52,10555,688165,400357,360622,358991,5721,505,9902,222,723 Less: Other(89,080)(329) Less: Current Period Dividends-------- Current Period Retained Earnings55,688165,400357,360622,358991,5721,505,9902,222,7233,221,339 Treasury Stock---- Michael Christie: Assume Treasury Stock remain constant.---- Accumulated other comprehensive loss, net of tax(1,197)(1,669)(252)(252) Michael Christie: Assume Other remain constant.(252)(252)(252)(252) Total Equity460,317735,636956,9671,221,9651,591,1792,105,5972,822,3303,820,946 Total Liabilities and Equity$ 639,851$ 1,054,057$ 1,378,825$ 1,674,501$ 2,181,083$ 2,886,898$ 3,870,302$ 5,240,472 check---59,19459,21759,24059,26359,286 Cash Flow The Boston Beer Company Inc. Statement of Cash Flows for the Historical Years 2018 through 2020, and Forecast Years 2021 through 2025 (FYE February 3, $ in millions) HistoricalForecast 20182019202020212022202320242025 Net Income$ 92,663$ 110,041$ 191,960$ 264,998$ 369,214$ 514,419$ 716,733$ 998,616 Cash Flow from Operations Taxes2362334329522707044298146136744190524265455 Interest exp405(542)232323232323 Change in Short-term Investments------- Change in Net Receivables(324)(20,331)(23,954)(27,675)(41,703)(58,104)(80,957)(112,797) Change in Note Receivable-------- Change in Inventory(19,598)(35,789)(24,872)(51,184)(71,617)(99,785)(139,030)(193,710) Change in Other Current Assets(2,441)1,059(18,153)(11,052)(16,236)(22,622)(31,519)(43,916) Change in Accounts Payable8,96129,27245,27336,69562,27686,769120,896168,444 Change in Accrued expenses and other current liabilities9,79525,69530,43751,06571,03498,971137,896192,131 Change in Operating lease-59,1088,295----- Change in Other long term liabilities327(1,029)1,777----- Change in Income Tax Payable----- Change in Other Current Liabilities2,0894,0595,6557,88010,979 Total Cash Flow from Operations113,411201,813263,056335,402475,195662,071922,4451,285,225 (-) Taxes(7,371)(12,233)(35,549)(60,049)(98,146)(136,744)(190,524)(265,455) Cash Flow from Operations106,040189,580227,507275,352377,049525,327731,9211,019,770 Cash Flow from Investing Capital Expenditures (CAPX)(5,509)(151,279)(82,015)(254,162)(345,020)(480,717)(669,783)(933,208) Investments(53,758)(4,725)58,483---- Invesment in intangible assets(2,099)(102,173)342103,930---- Goodwill(108,846)------ Other Assets604(11,073)(43,845)----- Total Cash Flow from Investing(7,004)(427,129)(130,243)(91,749)(345,020)(480,717)(669,783)(933,208) Cash Flow from Financing Issue of stock33,120166,079------ Sale of Stock27,954----- Net Borrowing------- Other(88,989)(801)1,417----- Interest paid/Income(405)542(23) Cash Dividends------- Total Cash Flow from Financing(56,274)165,82029,348----- Total Change in Cash42,762(71,729)126,612183,60432,02944,61062,13986,562 Effect of Exchange Rate Changes on Cash- Michael Christie: Given ----- Beginning Period Cash65,637108,39936,670163,282346