1) Prepare a discounted cash flow analysis of Panera Bread based on the consensus forecast, with an exit valuation multiple of 16.8x EBITDA in 2021, and calculate the present value of one share. (50 points) please in excel format and show the calculations

Panera Bread Historical Financial Statements Historical Balance Sheet For Years Ending December 31 (in \$ millions) ASSETS Current Assets Cash \& Equivalents Accounts Receivable Other Receivables Investments Inventory Prepaid Expenses Other Current Assets Total Current Assets Fixed Assets Land Buildings \& Leasehold Improvements Equipment Fumiture \& Fixtures Vehicles Total Fixed Assets Less: Accumulated Depreciation Net Fixed Assets Other Assets Goodwill Other Intangible Assets Accumulated Amortization Intangible Assets, Net Other Assets Total Other Assets TOTAL ASSETS 2013 2014 2015 2016 \begin{tabular}{|c|c|c|c|} \hline & & & \\ \hline 125.2 & 196.5 & 241.9 & 105.5 \\ \hline 33.0 & 36.6 & 38.2 & 53.5 \\ \hline 51.6 & 70.1 & 77.6 & 59.4 \\ \hline 21.9 & 22.8 & 22.5 & 23.8 \\ \hline 43.1 & 51.6 & 59.5 & 69.2 \\ \hline 27.9 & 28.6 & 28.7 & \\ \hline 302.7 & 406.2 & 468.3 & 311.4 \\ \hline 29 & 2.1 & 1.6 & 56 \\ \hline 687.6 & 792.8 & 780.7 & 820.1 \\ \hline 392.4 & 478.5 & 531.0 & 618.2 \\ \hline 176.5 & 197.2 & 188.7 & 204.6 \\ \hline 1.259 .3 & 1,470.6 & 1.502 .0 & 1,648.5 \\ \hline(589.9) & (683.3) & (725.8) & (845.7) \\ \hline 669.4 & 787.3 & 776.2 & 802.8 \\ \hline 123.0 & 120.8 & 121.8 & 122.4 \\ \hline 79.8 & 70.9 & 63.9 & 54.8 \\ \hline 202.8 & 191.7 & \begin{tabular}{r} - \\ 185.7 \end{tabular} & 177.2 \\ \hline 6.0 & 5.7 & 10.6 & 10.2 \\ \hline 208.7 & 197.4 & 196.3 & 187.4 \\ \hline 1,180.9 & 1,390.9 & 1,440.8 & 1,301 \\ \hline \end{tabular} Historical Balance Sheet For Years Ending December 31 (in $ millions) 2013 2014 2015 2016 LIABILITIES \& EQUITY Current Liabilities Line of Credit Accounts Payable Other Payables Accrued Liabilities Notes Payable Other Current Liabilities Total Current Liabilities Long-Term Liabilities Long-Term Debt Capital Leases Notes Payable Other Liabilities Total Long-Term Liabilities Total Liabilities Stockholders' Equity Common Equity Additional Paid-in Capital Retained Earnings Treasury Stock Total Stockholders' Equity TOTAL LIABILITIES \& EQUITY Tangible Assets Tangible Net Worth Total Debt \begin{tabular}{rrrr} 17.5 & 19.5 & 19.8 & 22.5 \\ 285.8 & 333.2 & 359.5 & 408.6 \\ - & - & 17.2 & 17.2 \\ - & - & 2.9 & - \\ \hline 303.3 & 352.7 & 399.4 & 448.3 \\ & & & \\ - & 100.0 & 389.0 & 410.6 \\ - & - & - & - \\ \hline 177.6 & 202.0 & 154.7 & 154.3 \\ \hline 177.6 & 302.0 & 543.7 & 564.9 \\ \hline 481.0 & 654.7 & 943.2 & 1.013 .2 \end{tabular} \begin{tabular}{rrrr} 0.0 & 0.0 & 0.0 & 0.0 \\ 196.9 & 214.4 & 235.4 & 257.6 \\ 1,049.6 & 1,227.8 & 1,373.5 & 1.520 .0 \\ (546.6) & (706.1) & (1,111.6) & (1.488.8) \\ \hline 699.9 & 736.2 & 497.3 & 288.8 \\ \hline 1,180.9 & 1,390.9 & 1,440.5 & 1,302.0 \\ \hline \hline \end{tabular} Check \begin{tabular}{rrrr} 901.2 & 1.113 .3 & 1.156 .4 & 1,045.0 \\ 420.2 & 458.5 & 213.2 & 31.8 \\ - & 100.0 & 406.2 & 427.8 \end{tabular} Historical Income Statement For Years Ending December 31 (in $ millions) Total Revenues Cost of Sales Gross Profit General \& Administrative Expense Other Expense Depreciation \& Amortization Total Operating Expenses Operating Income Other Income / (Expense) Other Income (Expense) Interest Income (Expense) Total Other Income / (Expense) Earnings Before Taxes Income Tax Expense Net Income / (Loss) \begin{tabular}{rrrr} 2013 & 2014 & 2015 & 2016 \\ 2,385.0 & 2,529.2 & 2,681.6 & 2,795.4 \\ 1,837.6 & 1,982.4 & 2,135.5 & 2,205.4 \\ \hline 547.4 & 546.8 & 546.1 & 590.0 \\ 123.3 & 138.1 & 142.9 & 179.9 \\ 7.8 & 8.7 & 26.2 & 16.0 \\ 106.5 & 124.8 & 135.4 & 154.4 \\ \hline 237.7 & 271.6 & 304.5 & 350.2 \\ \hline 309.8 & 275.2 & 241.6 & 239.8 \end{tabular} \begin{tabular}{rrrr} 4.0 & 3.2 & (1.2) & (1.4) \\ (1.1) & (1.8) & (3.8) & (8.9) \\ \hline 3.0 & 1.4 & (5.0) & (10.3) \\ \hline 312.7 & 276.6 & 236.6 & 229.5 \\ 116.6 & 98.0 & 87.2 & 84.3 \\ \hline 196.2 & 178.6 & 149.3 & 145.2 \\ \hline \hline \end{tabular} Panera Bread Projected Cash Flows For Fiscal Years Ending 12/31 (in S millions) (Actual) (Forecast) (Projected) (Projected) (Projected) (Projected) 2016 2017 2018 2019 2020 2021 INCOME STATEMENT Total Revenues Growth YoY Cost of Sales Gross Profit Operating Expenses Depreciation \& Amortization Total Operating Expenses EBIT Adjustments EBIT (Adjusted) Other Income (Expense) Interest Expense (Income) Pre-Tax Income Income Tax Expense Net Income Depreciation \& Amortization EBITDA Growth YoY \begin{tabular}{rrrrrr} $2,795.4 & $2.846.1 & $3.083.1 & $3.374.4 & $3.886.9 & $4.220.0 \\ & 1.8% & 8.3% & 9.4% & 15.2% & 8.6% \\ $2,205.4 & $2.220.0 & $2.404.8 & $2.632.0 & $3.031.8 & $3.291.6 \\ 78.9% & 78.0% & 78.0% & 78.0% & 78.0% & 78.0% \\ \hline$590.0 & $626.1 & $678.3 & $742.4 & $855.1 & $928.4 \\ $195.8 & $196.0 & $199.9 & $203.9 & $208.0 & $212.2 \\ $154.4 & $155.0 & $155.0 & $155.0 & $155.0 & $155.0 \\ \hline$350.2 & $351.0 & $354.9 & $358.9 & $363.0 & $367.2 \\ 12.5% & 12.3% & 11.5% & 10.6% & 93% & 8.7% \\ \hline$239.8 & $275.1 & $32.4 & $383.4 & $492.1 & $561.2 \\ $0.0 & $0.0 & $0.0 & $0.0 & $0.0 & $0.0 \\ \hline$239.8 & $275.1 & $323.4 & $383.4 & $492.1 & $561.2 \\ 8.6% & 9.7% & 10.5% & 11.4% & 12.7% & 13.3% \\ ($1.4) & $0.0 & $0.0 & $0.0 & $0.0 & $0.0 \\ $8.9 & $12.8 & $13.7 & $14.8 & $16.2 & $18.7 \\ \hline$229.5 & $262.3 & $309.7 & $368.7 & $475.9 & $542.6 \\ \hline$84.3 & $94.43 & $111.49 & $132.71 & $171.33 & $195.33 \\ \hline 36.7% & 36.0% & 36.0% & 36.0% & 36.0% & 36.0% \\ \hline$145.2 & $167.9 & $198.2 & $235.9 & $304.6 & $347.3 \\ \hline \hline 154.4 & $155.0 & $155.0 & $155.0 & $155.0 & $155.0 \\ \hline$394.1 & $430.1 & $478.4 & $538.4 & $647.1 & $716.2 \\ \hline \hline \end{tabular} \begin{tabular}{rrrrrr} $2,795.4 & $2.846.1 & $3.083.1 & $3.374.4 & $3.886.9 & $4.220.0 \\ & 1.8% & 8.3% & 9.4% & 15.2% & 8.6% \\ $2,205.4 & $2.220.0 & $2.404.8 & $2.632.0 & $3.031.8 & $3.291.6 \\ 78.9% & 78.0% & 78.0% & 78.0% & 78.0% & 78.0% \\ \hline$590.0 & $626.1 & $678.3 & $742.4 & $855.1 & $928.4 \\ $195.8 & $196.0 & $199.9 & $203.9 & $208.0 & $212.2 \\ $154.4 & $155.0 & $155.0 & $155.0 & $155.0 & $155.0 \\ \hline$350.2 & $351.0 & $354.9 & $358.9 & $363.0 & $367.2 \\ 12.5% & 12.3% & 11.5% & 10.6% & 93% & 8.7% \\ \hline$239.8 & $275.1 & $32.4 & $383.4 & $492.1 & $561.2 \\ $0.0 & $0.0 & $0.0 & $0.0 & $0.0 & $0.0 \\ \hline$239.8 & $275.1 & $323.4 & $383.4 & $492.1 & $561.2 \\ 8.6% & 9.7% & 10.5% & 11.4% & 12.7% & 13.3% \\ ($1.4) & $0.0 & $0.0 & $0.0 & $0.0 & $0.0 \\ $8.9 & $12.8 & $13.7 & $14.8 & $16.2 & $18.7 \\ \hline$229.5 & $262.3 & $309.7 & $368.7 & $475.9 & $542.6 \\ \hline$84.3 & $94.43 & $111.49 & $132.71 & $171.33 & $195.33 \\ \hline 36.7% & 36.0% & 36.0% & 36.0% & 36.0% & 36.0% \\ \hline$145.2 & $167.9 & $198.2 & $235.9 & $304.6 & $347.3 \\ \hline \hline 154.4 & $155.0 & $155.0 & $155.0 & $155.0 & $155.0 \\ \hline$394.1 & $430.1 & $478.4 & $538.4 & $647.1 & $716.2 \\ \hline \hline \end{tabular} $200 $211 $200 $196 $196 $193