need to help finish this case calculation in excel.

This spreadsheet supports STUDENT analysis of the case "Panera Bread Company" (UVAF-1575). This spreadsheet was prepared by Marc Lipson, Associate Professor of Business Administration. Copyright 2009 by the University of Virginia Darden School Foundation, Charlottesville, VA. All rights reserved. For customer service inquiries, send an e-mail tosales@dardenbusinesspublishing.com. No part of this publication may be reproduced, stored in a retrieval system, posted to the Internet, or transmitted in any form or by any meanselectronic, mechanical, photocopying, recording, or otherwisewithout the permission of the Darden School Foundation. Rev. Jun. 21, 2012 Exhibit 1 PANERA BREAD COMPANY Historic Income Statements (n thousands of dollars) 2003 2004 2005 2006 602 741 877 1,027 363,702 479,139 640,275 828,971 210,822 54,967 18,304 31,502 315,595 288,706 65,627 25,298 38,735 418,366 399,760 75,036 33,011 50,240 558,047 542,916 85,618 44,166 63,502 736,202 Operating Profit (EBIT) 48,107 60,773 82,228 92,769 Interest Expense Pretax Profit Tax Net Income 48 48,059 17,629 30,430 18 60,755 22,175 38,580 50 82,178 29,995 52,183 92 92,677 33,827 58,850 Number of Bakery Cafs(a) Revenue Costs of Goods Sold Bakery Caf Dough Sold to Franchisees Depreciation General and Administrative (b) (a) Including both company-owned and franchised bakery cafs (b) Includes pre-opening expenses and other expenses Data source: Panera Bread Company annual reports, 2003-06. Exhibit 2 PANERA BREAD COMPANY Historic Balance Sheets (in thousands of dollars) Historic Balance Sheets: 2003 2004 2005 2006 51,421 12,394 4,350 3,887 72,052 58,054 17,256 5,398 3,905 84,613 60,651 25,158 7,358 9,607 102,774 72,122 30,919 8,714 15,863 127,618 Property, Plant and Equipment Goodwill and Other Assets 146,362 38,421 201,725 38,334 268,809 66,084 345,977 69,014 Total Assets 256,835 324,672 437,667 542,609 Accounts Payable Acrued Expenses and Deferred Revenue Current Liabilities 8,072 37,571 45,643 5,840 49,865 55,705 4,422 82,443 86,865 5,800 103,810 109,610 Deferred Rent and Other Liabilities Total Liabilities 13,616 59,259 27,604 83,309 33,824 120,689 35,333 144,943 197,576 256,835 241,363 324,672 316,978 437,667 397,666 542,609 Cash and Short Term Investments Accounts Receivable Inventory Prepaid Expenses and Deferred Taxes Current Assets Equity Data source: Panera Bread Company annual reports, 2003-06. Exhibit 1 PANERA BREAD COMPANY 2007 Operating Forecast(a) (in thousands of dollars) Number of Bakery-Cafes(b) Revenue Costs of Goods Sold Bakery Caf Dough Sold to Franchisees Depreciation General and Administrative (c) 1,230 1,050,000 738,000 86,000 60,000 78,000 962,000 Operating Profit (EBIT) 88,000 Interest Expense Pretax Profit Tax Net Income 150 87,850 31,500 56,350 Current Assets Property, Plant, and Equipment Goodwill and Other Assets Total Assets 150,000 430,000 110,000 690,000 Current Liabilities Deferred Rent and Other Liabilities Total Liabilities 130,000 45,000 175,000 (a) Case writer estimate based on history and third-quarter results. (b) Including both company-owned and franchised bakery cafs (c) Includes pre-opening expenses and other expenses Panera Stock Price and Volume 6/1/2006 6/2/2006 6/5/2006 6/6/2006 6/7/2006 6/8/2006 6/9/2006 6/12/2006 6/13/2006 6/14/2006 6/15/2006 6/16/2006 6/19/2006 6/20/2006 6/21/2006 6/22/2006 6/23/2006 6/26/2006 6/27/2006 6/28/2006 6/29/2006 6/30/2006 7/3/2006 7/4/2006 7/5/2006 7/6/2006 7/7/2006 7/10/2006 7/11/2006 7/12/2006 7/13/2006 7/14/2006 7/17/2006 7/18/2006 7/19/2006 7/20/2006 7/21/2006 7/24/2006 7/25/2006 7/26/2006 7/27/2006 7/28/2006 7/31/2006 8/1/2006 8/2/2006 8/3/2006 8/4/2006 8/7/2006 8/8/2006 8/9/2006 8/10/2006 8/11/2006 8/14/2006 8/15/2006 8/16/2006 8/17/2006 8/18/2006 8/21/2006 8/22/2006 8/23/2006 8/24/2006 8/25/2006 8/28/2006 8/29/2006 8/30/2006 8/31/2006 9/1/2006 9/4/2006 9/5/2006 9/6/2006 9/7/2006 9/8/2006 9/11/2006 9/12/2006 9/13/2006 9/14/2006 9/15/2006 9/18/2006 9/19/2006 9/20/2006 9/21/2006 9/22/2006 9/25/2006 9/26/2006 9/27/2006 9/28/2006 9/29/2006 10/2/2006 10/3/2006 10/4/2006 10/5/2006 10/6/2006 10/9/2006 10/10/2006 10/11/2006 10/12/2006 10/13/2006 10/16/2006 Panera Stock Volume 65.53 277.1 65.95 358.8 64.62 432.6 64.62 745.8 62.47 1373.1 62.62 794.2 63.04 549 63.18 714.9 62.14 584.1 61.7 427.5 63.83 428.8 63.99 264.9 63.41 555.2 63.42 192.4 64.29 369.2 66.57 1245.8 66.73 396.9 67.5 491.9 67.03 509.5 65.58 748.5 66.87 382.8 67.24 351.7 66.94 86.2 66.94 0 66.37 328.3 65.5 417.9 64.15 359.6 64.83 312.2 64.44 529.1 63.83 476.9 61.25 938.2 62.1 924.9 60.71 1229.8 59.41 861.3 60.01 729.7 58.25 1124.5 57.69 770.5 59.56 906 59.27 754.8 51.93 6267.6 51.53 1469 52.59 1053.8 52.31 660.6 51.45 630.9 51.54 684.5 51.56 673.5 51.5 659.8 50.61 465.2 50.1 467 46.85 1755.4 48.08 1258.8 47.95 426.1 48.26 413.2 49.81 962.2 51.96 890.7 53.46 916.1 52.4 641.8 51.39 572.7 51.62 532.9 51.15 531.5 50.12 856.1 49.16 578.7 50.29 701 51.9 862.2 53.6 901.5 51.9 1038.7 52.88 491.3 52.88 0 54.75 1403.2 52.03 1591.5 51.52 1200.3 52.72 573.6 53.35 748.2 57.67 2127.9 56.89 857.3 57.46 690.3 58.9 724 58.22 926.8 58.09 651.8 60.6 946 60.08 887 59.03 633.9 60.11 757.9 60.11 504 59.57 508.9 59.1 655.7 58.25 407.7 58.17 642.5 58.35 774.9 64.75 3400.1 65.68 1166 64.41 730.9 66.02 595.9 65.19 941.8 64.22 813.9 67.2 1398.8 66.8 849.3 67.03 769 Panera Stock Price 80 70 60 50 40 30 20 10 0 6/1/2006 9/1/2006 12/1/2006 3/1/2007 6/1/2007 9/1/2007 10/17/2006 10/18/2006 10/19/2006 10/20/2006 10/23/2006 10/24/2006 10/25/2006 10/26/2006 10/27/2006 10/30/2006 10/31/2006 11/1/2006 11/2/2006 11/3/2006 11/6/2006 11/7/2006 11/8/2006 11/9/2006 11/10/2006 11/13/2006 11/14/2006 11/15/2006 11/16/2006 11/17/2006 11/20/2006 11/21/2006 11/22/2006 11/23/2006 11/24/2006 11/27/2006 11/28/2006 11/29/2006 11/30/2006 12/1/2006 12/4/2006 12/5/2006 12/6/2006 12/7/2006 12/8/2006 12/11/2006 12/12/2006 12/13/2006 12/14/2006 12/15/2006 12/18/2006 12/19/2006 12/20/2006 12/21/2006 12/22/2006 12/25/2006 12/26/2006 12/27/2006 12/28/2006 12/29/2006 1/1/2007 1/2/2007 1/3/2007 1/4/2007 1/5/2007 1/8/2007 1/9/2007 1/10/2007 1/11/2007 1/12/2007 1/15/2007 1/16/2007 1/17/2007 1/18/2007 1/19/2007 1/22/2007 1/23/2007 1/24/2007 1/25/2007 1/26/2007 1/29/2007 1/30/2007 1/31/2007 2/1/2007 2/2/2007 2/5/2007 2/6/2007 2/7/2007 2/8/2007 2/9/2007 2/12/2007 2/13/2007 2/14/2007 2/15/2007 2/16/2007 2/19/2007 2/20/2007 2/21/2007 2/22/2007 2/23/2007 2/26/2007 2/27/2007 2/28/2007 3/1/2007 3/2/2007 3/5/2007 3/6/2007 3/7/2007 66.47 66.9 65.58 64.79 66.01 68.7 64.41 64.33 62.03 61.98 61.8 61.27 60.66 59.15 60.83 60.64 61.47 59.99 60.47 59.52 60.58 61.97 61.82 61.58 61.26 60.25 60.87 60.87 60.97 59 58.08 57.6 57.45 56.9 58.68 58.79 56.94 56.7 56.89 56.74 55.63 55.7 55.68 55.97 55.9 55.52 56.08 55.77 55.82 55.82 55.47 56.1 56.47 55.91 55.91 55.91 56.01 56.75 55.95 56.15 57.27 56.79 58.4 57.92 57.92 58.54 57.63 56.38 55.67 55.2 54.87 55.11 54.5 53.85 53.15 54.58 58.96 57.93 58.34 57.93 58.55 58.57 58.12 57.4101 57.2 58.4 58.92 59.47 59.91 59.91 61.11 61.97 62.2 62.63 62.23 60 61.23 60.07 59.1 58.13 57.84 58.48 571.3 651.1 725.5 620 801.8 1196 4101.2 874.8 1089.2 1411.6 923.5 1157.2 974.1 1138.8 813.7 435.4 584.9 529 499.4 758.6 581.3 649.2 508.5 576.1 453.1 1248 568.3 0 115.9 661.7 968.6 1158.9 685.2 1330.7 632.9 786 3162 1556.3 1074.5 689 870 777.1 487.4 682 518.6 505.9 452.9 602.9 272.7 0 370.7 365.2 682.4 429.4 0 0 842.6 847.9 658.2 554.8 818.5 585.9 732.9 567.5 0 394.6 637.1 604.4 613 622.9 376.8 446.8 620.6 935.5 1184.3 1146.6 2966.4 1180.9 443.3 359.5 340.6 839.6 752.1 1294 1149.1 845.5 512.4 592.2 1113.5 0 1330.1 980.9 919.3 667.7 705.1 1076.3 1187.7 693.9 533.8 746.4 1021.5 1541.1 3/8/2007 3/9/2007 3/12/2007 3/13/2007 3/14/2007 3/15/2007 3/16/2007 3/19/2007 3/20/2007 3/21/2007 3/22/2007 3/23/2007 3/26/2007 3/27/2007 3/28/2007 3/29/2007 3/30/2007 4/2/2007 4/3/2007 4/4/2007 4/5/2007 4/6/2007 4/9/2007 4/10/2007 4/11/2007 4/12/2007 4/13/2007 4/16/2007 4/17/2007 4/18/2007 4/19/2007 4/20/2007 4/23/2007 4/24/2007 4/25/2007 4/26/2007 4/27/2007 4/30/2007 5/1/2007 5/2/2007 5/3/2007 5/4/2007 5/7/2007 5/8/2007 5/9/2007 5/10/2007 5/11/2007 5/14/2007 5/15/2007 5/16/2007 5/17/2007 5/18/2007 5/21/2007 5/22/2007 5/23/2007 5/24/2007 5/25/2007 5/28/2007 5/29/2007 5/30/2007 5/31/2007 6/1/2007 6/4/2007 6/5/2007 6/6/2007 6/7/2007 6/8/2007 6/11/2007 6/12/2007 6/13/2007 6/14/2007 6/15/2007 6/18/2007 6/19/2007 6/20/2007 6/21/2007 6/22/2007 6/25/2007 6/26/2007 6/27/2007 6/28/2007 6/29/2007 7/2/2007 7/3/2007 7/4/2007 7/5/2007 7/6/2007 7/9/2007 7/10/2007 7/11/2007 7/12/2007 7/13/2007 7/16/2007 7/17/2007 7/18/2007 7/19/2007 7/20/2007 7/23/2007 7/24/2007 7/25/2007 7/26/2007 7/27/2007 59.29 59.95 60.57 59.46 59.84 59.74 59.1 59.32 59.35 60.42 61.16 61.02 60.77 59.95 58.78 58.76 59.06 59.09 60.22 57.75 57.34 57.34 56.39 56.38 55.73 55.64 56.03 56.23 55.79 56.05 55.83 56.14 56.25 55.7 59.13 57.45 56.8 55.69 56.1 56.94 56.24 56.24 55.95 56.24 56.3 55.52 56.05 55.65 54.94 55.43 54.99 55.15 55.59 56.62 56.92 56.97 56.67 56.67 56 56.6 56.37 56.73 57.59 58.32 50.28 48.78 49.01 47.85 47.64 48.33 48.29 47.98 47.82 47.15 47.04 46.41 46.69 46.35 46.29 46.65 46.02 46.06 46.02 47.55 47.55 47.68 47.9 47.23 47.48 46.7 46.87 46.49 47.26 46.83 46.37 46.45 46.1 46.9 45.9799 41.81 40.58 39.79 1123.8 728.5 444.8 493.1 1008.3 381.1 430.4 425.3 378.9 464.3 404.4 231.1 231.1 340.6 710.6 392.8 336.2 438.7 842.8 2747.4 985 0 927.3 920.8 894.7 826.1 724.8 835.3 594.9 469.8 299.7 614 590.9 933.9 2897.3 1478 820.8 510.7 787.1 903.4 448.8 389.1 554.8 429.9 379.6 338.9 292.6 466.7 648.5 484 348.8 842.2 505.8 522.5 590.4 597.8 417.9 0 555.9 260.7 256.6 380 445.2 849.5 5000 2703.2 1847.2 1554.6 858.8 864.5 715.7 626.5 708 677.2 719.2 1347.1 1218.7 963.3 751.2 755.5 469.8 570.8 686.5 825.5 0 1537.5 632.7 828.6 663.7 689.7 648.6 553.2 819.1 451 396.1 395.7 542.2 769.4 1043.5 2989.1 1740.8 1365.4 7/30/2007 7/31/2007 8/1/2007 8/2/2007 8/3/2007 8/6/2007 8/7/2007 8/8/2007 8/9/2007 8/10/2007 8/13/2007 8/14/2007 8/15/2007 8/16/2007 8/17/2007 8/20/2007 8/21/2007 8/22/2007 8/23/2007 8/24/2007 8/27/2007 8/28/2007 8/29/2007 8/30/2007 8/31/2007 9/3/2007 9/4/2007 9/5/2007 9/6/2007 9/7/2007 9/10/2007 9/11/2007 9/12/2007 9/13/2007 9/14/2007 9/17/2007 9/18/2007 9/19/2007 9/20/2007 9/21/2007 9/24/2007 9/25/2007 9/26/2007 9/27/2007 9/28/2007 10/1/2007 10/2/2007 10/3/2007 10/4/2007 10/5/2007 10/8/2007 10/9/2007 10/10/2007 10/11/2007 10/12/2007 10/15/2007 10/16/2007 10/17/2007 10/18/2007 10/19/2007 10/22/2007 10/23/2007 10/24/2007 10/25/2007 10/26/2007 10/29/2007 10/30/2007 10/31/2007 11/1/2007 11/2/2007 11/5/2007 11/6/2007 11/7/2007 11/8/2007 11/9/2007 11/12/2007 11/13/2007 11/14/2007 11/15/2007 11/16/2007 11/19/2007 11/20/2007 11/21/2007 11/22/2007 11/23/2007 11/26/2007 11/27/2007 11/28/2007 11/29/2007 11/30/2007 41.38 40.64 39.8 41 41.58 43.04 45.56 47.19 46.37 43.31 44.1 44.36 43.54 43.91 45.23 45 44.65 45.04 44.39 44.59 43.69 42.8 43.21 42.96 43.74 43.74 43.59 43.24 44.46 44.13 43.1 43.4 43.08 43.93 44.53 43.87 44.39 44.48 43.26 43.28 43 40.59 41.36 41.56 40.8 42.19 43.12 46.57 47.27 48.11 47.62 48.19 48.95 48.66 47.77 47.57 48.12 48.57 47.82 46.85 47.2 48.81 43.61 41.66 41.05 41.01 40.73 40.99 39.28 38.21 37.89 37.25 37.19 37.4799 36.62 36.96 37.85 37.14 37.11 37.01 35.91 34.64 34.27 34.27 35.09 35.29 35.51 38.01 39.47 40.07 1548.2 605.6 1508.9 807.8 1170.3 1069.2 1362.2 2828 2177 1881.6 910.5 1162.6 844.6 1158.9 1219.8 764.9 363.8 473.6 567.7 518 420.3 639.9 400 591.5 447.8 0 677.5 627.6 650.5 607.4 548.6 302.6 373.9 334.8 262.7 308.8 639.4 733.3 437.1 339.4 410.9 1049.7 845.1 428 537.6 1079.8 786.2 2384 1306.2 657.1 410.4 378.5 1009.4 904.5 536.2 714.3 483.7 620.7 710.9 947.1 770.6 910.7 2796.6 1437 955.7 951.2 1011.1 809.6 840.5 1001.6 752.5 939.9 675.2 637 527 599.4 486.1 532.6 388.3 617.6 494 681.3 857.5 0 505.7 795.8 664.2 1259.9 784.8 1270.7 Exhibit 4 PANERA BREAD COMPANY Data on Comparable Firm Capital Structure (numbers in thousands except for prices) Estimates for Year-End 2007 Revenue EBIT LT Debt 11/30/2007 Price Shares Quick Service Restaurants McDonald's Corp Wendy's Group Inc. Burger King Holdings Inc. Domino's Pizza, Inc. Jack in the box Inc. 22,786,600 1,263,717 2,234,000 1,462,870 2,513,431 3,879,000 19,900 290,000 193,910 216,996 8,174,500 739,333 943,000 1,720,083 433,303 56.32 8.10 25.90 13.86 29.95 1,165,300 28,884 135,000 59,665 59,736 Casual Dining Darden Restaurants Inc. Ruby Tuesday Inc. PF Chang's China Bistro Inc. The Cheesecake Factory Inc. California Pizza Kitchen Inc. 5,567,100 1,410,227 1,084,193 1,511,577 632,884 574,400 154,855 53,312 110,803 21,517 491,600 514,338 191,195 175,000 0 38.25 13.11 25.59 23.29 15.91 141,400 53,240 24,152 69,152 28,358 Fast Casual Chipotle Mexican Grill, Inc. Starbucks Corp. Buffalo Wild Wings Inc. 1,085,782 9,411,497 329,652 113,706 1,053,945 28,518 0 550,000 12,585 133.15 23.39 28.91 32,805 727,600 17,657 Data sources: Investex, Onesource, Yahoo! Finance, and individual firm 10-K filings. Multiperiod Forecast Historic 2003 Revenue 2004 2005 Forecast 2006 2007 2008 363,702 479,139 640,275 828,971 1,050,000 Costs of Goods Sold Bakery Caf 210,822 288,706 399,760 542,916 Dough Sold to Franchisee 54,967 65,627 75,036 85,618 Depreciation 18,304 25,298 33,011 44,166 General and Administrati 31,502 38,735 50,240 63,502 315,595 418,366 558,047 736,202 738,000 86,000 60,000 78,000 962,000 Operating Profit 48,107 60,773 82,228 92,769 88,000 Interest Expense Pretax Profit Tax Net Income 48 48,059 17,629 30,430 18 60,755 22,175 38,580 50 82,178 29,995 52,183 92 92,677 33,827 58,850 150 87,850 31,500 56,350 Current Assets 72,052 84,613 102,774 127,618 Property, Plant, and Equip 146,362 201,725 268,809 345,977 Goodwill and Other Assets 38,421 38,334 66,084 69,014 Total Assets 256,835 324,672 437,667 542,609 150,000 430,000 110,000 690,000 Current Liabilities 45,643 55,705 86,865 Deferred Rent and Other Lia 13,616 27,604 33,824 Total Liabilities 59,259 83,309 120,689 Debt Equity 197,576 241,363 316,978 Total Liabilities and Equity 256,835 324,672 437,667 130,000 45,000 175,000 109,610 35,333 144,943 397,666 542,609 Historic Percentage of Sal 2003 2004 2005 2006 Bakery Caf 57.97% 60.26% 62.44% 65.49% Dough Sold to Franchisee 15.11% 13.70% 11.72% 10.33% Depreciation 5.03% 5.28% 5.16% 5.33% General and Administrativ 8.66% 8.08% 7.85% 7.66% 87% 87% 87% 89% Current Assets Property, Plant, and Equi Goodwill and Other Current Liabilities Deferred Rent and Other Tax Rate 19.81% 17.66% 16.05% 15.39% 40.24% 42.10% 41.98% 41.74% 10.56% 8.00% 10.32% 8.33% 12.55% 11.63% 13.57% 13.22% 3.74% 5.76% 5.28% 4.26% 36.68% 36.50% 36.50% 36.50% 2007 Average 70.29% 63.29% 8.19% 11.81% 5.71% 5.30% 7.43% 7.94% 92% 88% 14.29% 40.95% 10.48% 12.38% 4.29% 35.86% 16.64% 41.40% 9.54% 12.67% 4.67% 36.41% 2009 2010 2011 2012 WILMINGTON UNIVERSITY COURSE TITLE: Advanced Financial Analysis COURSE NUMBER: MBA 7294 FACULTY MEMBER: John Bish Week #1 Case Study - Panera Bread Company Case Study Questions: 1. 2. 3. 4. 5. 6. 7. Complete the financing portion of Panera Bread Company's 2007 forecast financial statements a. Include a chart of your financial assumptions. Develop a 5 year Financial Forecast (both Balance Sheet and Income Statement) Describe three possible financial forecasting processes. Discuss the benefits and limitations of each three methods. Describe why you chose the approach you used in this case study. Provide an assessment of the earning quality of Panera Bread in Year 5 of the projected financial statements. Determine the amount of Free Cash Flow Panera has in Year 5 of the projected financial statements. Discuss the importance of Free Cash Flow, and it's relationship to overall accounting earnings. Develop a table of relevant financial ratios for 2007 and Forecast Year 5; discuss the ratios, their change of the forecast period, and the overall performance of Panera Bread in Forecast Year 5. Given the need for external sources of capital, compare and contrast the advantages and disadvantages of external equity, a long-term note payable, and a short-term line of credit. Case Study Assumptions: 1. 2. 3. 4. A 5-year financial forecast worksheet has been provided to you on Blackboard. Assume all borrowing are a type of debt, no additional equity will be utilized to raise capital The share repurchase program DOES occur in 2008; and interest expense is equal to 6% of outstanding debt Sales growth is 25% for the first two years; then 5% thereafter. Case Study Analysis Papers grading rubric Grading Criteria Completed Panera Bread Company's 2007 forecast financial statements. Describe three possible financial forecasting processes. Discuss the benefits and limitations of each method. Describe why you chose the approach you used in this case study. Develop a 5 year Financial Forecast (Balance Sheet and Income Statement) Provide an assessment of the earning quality of Panera Bread in 2012 of the projected financial statements. Determine the amount of Free Cash Flow in 2012; discuss the importance of Free Cash Flow and its relationship to overall accounting earnings. Analysis of financial ratios; including baseline ratios, their change of the forecast periods, and the overall performance in 2012. Given the need for external sources of capital, compare and contrast the advantages and disadvantages of external equity, a long-term note payable, and a short-term line of credit. Proper spelling, punctuation, and APA Formatting Total Maximum Points 5 10 25 15 15 10 10 10 100