Exam 500895 -PPMC - Senior Capstone - Excel Spreadsheet

Calculation of First Year Break Even PointsNote M1 M2 M3 M4 Jan Feb Mar Apr Stores 1 1,200 1,200 1,200 1,200 Multiply by 200 carts 3 240,000 240,000 240,000 240,000 Total Carts 3 240,000 480,000 720,000 960,000 Multiply by Revenue per cart 1 6.75 6.75 6.75 6.75 Total Revenues 1 1,620,000 3,240,000 4,860,000 6,480,000 Variable Costs (VC) Amortization (2 year S/L) 2 746,400 746,400 746,400 746,400 Printing 3 26,400 52,800 79,200 132,000 Replacement (even distribution) 4 75,960 75,960 75,960 75,960 Cart Rental (10% Revenue) 5 162,000 324,000 486,000 648,000 Mktg. Sales & Comm. 6 861,000 861,000 861,000 1,590,000 Grocery Store Operations 7 840,000 840,000 840,000 960,000 Total VC 2,711,760 2,900,160 3,088,560 4,152,360 Contribution Margin (CM) (1,091,760) 339,840 1,771,440 2,327,640 CM per Unit/Cart (4.55) 0.71 2.46 2.42 Fixed Costs (FC) Accounting & Audit 8 6,000 6,000 6,000 6,000 Advertising (even distribution) 9 39,375 39,375 39,375 39,375 Auto Lease 10 2,000 2,000 2,000 2,000 Bank Charges 11 167 167 167 167 Entertainment & Promotion 12 10,000 10,000 10,000 10,000 Insurance 13 8,333 8,333 8,333 8,333 Legal 14 10,000 10,000 10,000 10,000 Management Fees 15 24,167 24,167 24,167 24,167 Office & Sundry 16 5,000 5,000 5,000 3,000 Public Relations 17 33,333 33,333 33,333 33,333 Rent 18 10,000 10,000 10,000 10,000 Salaries & Benefits 19 24,000 24,000 24,000 24,000 Stationary & Printing 20 833 833 833 833 Telephone & Fax 21 3,500 3,500 3,500 3,500 Travel & Accommodation 22 10,000 10,000 10,000 20,000 Total FC 186,708 186,708 186,708 194,708 Total Expenses (VC + FC) 2,898,468 3,086,868 3,275,268 4,347,068 Net Operating Income (1,278,468) 153,132 1,584,732 2,132,932 Break Even Point in terms of carts Break Even Point in terms of stores  M5 M6 M7 M8 M9 M10 M11 M12 1st May Jun Jul Aug Sep Oct Nov Dec Year 1,200 1,200 1,200 1,200 1,200 1,200 1,200 1,200 14,400 240,000 240,000 240,000 240,000 240,000 240,000 240,000 240,000 2,880,000 1,200,000 1,440,000 1,680,000 1,920,000 2,160,000 2,400,000 2,640,000 2,880,000 2,880,000 6.75 6.75 6.75 6.75 6.75 6.75 6.75 6.75 6.75 8,100,000 9,720,000 11,340,000 12,960,000 14,580,000 16,200,000 17,820,000 19,440,000 126,360,000 746,400 746,400 746,400 746,400 746,400 746,400 746,400 746,400 8,956,800 158,400 184,800 211,200 237,600 264,000 290,400 316,800 343,200 2,296,800 75,960 75,960 75,960 75,960 75,960 75,960 75,960 75,960 911,520 810,000 972,000 1,134,000 1,296,000 1,458,000 1,620,000 1,782,000 1,944,000 12,636,000 1,590,000 1,590,000 2,319,000 2,319,000 2,319,000 3,048,000 3,048,000 3,048,000 23,454,000 960,000 960,000 1,080,000 1,080,000 1,080,000 1,200,000 1,200,000 1,200,000 12,240,000 4,340,760 4,529,160 5,566,560 5,754,960 5,943,360 6,980,760 7,169,160 7,357,560 60,495,120 3,759,240 5,190,840 5,773,440 7,205,040 8,636,640 9,219,240 10,650,840 12,082,440 65,864,880 3.13 3.60 3.44 3.75 4.00 3.84 4.03 4.20 22.87 6,000 6,000 6,000 6,000 6,000 8,000 8,000 8,000 78,000 39,375 39,375 39,375 39,375 39,375 39,375 39,375 39,375 472,500 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 24,000 167 167 167 167 167 167 167 167 2,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 120,000 8,333 8,333 8,333 8,333 8,333 8,333 8,333 8,333 100,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 120,000  24,167 24,167 24,167 24,167 24,167 24,167 24,167 24,167 290,000 3,000 3,000 4,000 4,000 4,000 5,000 5,000 5,000 51,000 33,333 33,333 33,333 33,333 33,333 33,333 33,333 33,333 400,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 120,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 288,000 833 833 833 833 833 833 833 833 10,000 3,500 3,500 3,500 3,500 3,500 3,500 3,500 3,500 42,000 20,000 20,000 30,000 30,000 30,000 45,000 45,000 45,000 315,000 194,708 194,708 205,708 205,708 205,708 223,708 223,708 223,708 2,432,500 4,535,468 4,723,868 5,772,268 5,960,668 6,149,068 7,204,468 7,392,868 7,581,268 62,927,620 3,564,532 4,996,132 5,567,732 6,999,332 8,430,932 8,995,532 10,427,132 11,858,732 63,432,380 106,363 532 Calculation of First Year Break Even Points 1st 2nd 3rd 4th 1st Qtr Qtr Qtr Qtr Year 3,600 7,200 10,800 14,400 14,400 720,000 1,440,000 2,160,000 2,880,000 2,880,000 720,000 1,440,000 2,160,000 2,880,000 2,880,000 9,720,000 24,300,000 38,880,000 53,460,000 126,360,000 9,720,000 24,300,000 38,880,000 53,460,000 126,360,000 2,239,200 2,239,200 2,239,200 2,239,200 8,956,800 158,400 475,200 712,800 950,400 2,296,800 227,880 227,880 227,880 227,880 911,520 972,000 2,430,000 3,888,000 5,346,000 12,636,000 2,583,000 4,770,000 6,957,000 9,144,000 23,454,000 2,520,000 2,880,000 3,240,000 3,600,000 12,240,000 8,700,480 13,022,280 17,264,880 21,507,480 60,495,120 1,019,520 11,277,720 21,615,120 31,952,520 65,864,880 1.42 7.83 10.01 11.09 22.87 18,000 18,000 18,000 24,000 78,000 118,125 118,125 118,125 118,125 472,500 6,000 6,000 6,000 6,000 24,000 500 500 500 500 2,000 30,000 30,000 30,000 30,000 120,000 25,000 25,000 25,000 25,000 100,000 30,000 30,000 30,000 30,000 120,000  72,500 72,500 72,500 72,500 290,000 15,000 9,000 12,000 15,000 51,000 100,000 100,000 100,000 100,000 400,000 30,000 30,000 30,000 30,000 120,000 72,000 72,000 72,000 72,000 288,000 2,500 2,500 2,500 2,500 10,000 10,500 10,500 10,500 10,500 42,000 30,000 60,000 90,000 135,000 315,000 560,125 584,125 617,125 671,125 2,432,500 9,260,605 13,606,405 17,882,005 22,178,605 62,927,620 459,395 10,693,595 20,997,995 31,281,395 63,432,380 106,363 532  Stock # of Firm Ticker Stores Name SWY 2,200 Safeway SVU 2,012 Supervalu Private 1,300 Aldi ADRNY 1,214 Ahold-Delhaize Private 1,148 Publix KR 782 Kroger Private 690 Bi-Lo WFM 466 Whole Foods Market Private 465 Trader Joe's Private 340 HEB GAPTQ 300 Great Atlantic & Pacific Tea Co Private 235 Hy-Vee Private 210 Save Mar IMKTA 200 Ingles SPTN 150 Nash Finch RNDY 148 Roundy's Private 100 Winco Foods Private 90 Wegman's Total 12,050 Total # of Stores 24100