A case study on Purchase Point Media Corporation
Mlinzi
y_of_Exam_500895_-_PPMC_Excel_Spreadsheet_1.xlsxStep 2
| Calculation of First Year Break Even Points | Calculation of First Year Break Even Points | ||||||||||||||||||
| Note | M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 | M10 | M11 | M12 | 1st | 1st | 2nd | 3rd | 4th | 1st | |
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Year | Qtr | Qtr | Qtr | Qtr | Year | ||
| Stores | 1,200 | 2,400 | 3,600 | 4,800 | 6,000 | 7,200 | 8,400 | 9,600 | 10,800 | 12,000 | 13,200 | 14,400 | 93,600 | 1,440,000 | 4,320,000 | 712,800 | 950 | 2,296,000 | |
| Multiply by 200 carts | 240,000 | 480,000 | 720,000 | 960,000 | 1,200,000 | 144,000 | 1,680,000 | 1,920,000 | 2,160,000 | 2,400,000 | 2,640,000 | 2,880,000 | 18,720,000 | 158,400 | 475,000 | 712,800 | 950,400 | 2,296,000 | |
| Total Carts | 240,000 | 480,000 | 720,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 | 3,120,000 | 20,880,000 | ||||||
| Multiply by Revenue per cart | 1,620,000.00 | 3,240,000.00 | 4,860,000.00 | 6,480,000.00 | 810,000.00 | 9,720,000.00 | 11,340,000.00 | 12,960,000.00 | 14,580,000.00 | 16,200,000.00 | 17,820,000.00 | 19,440,000.00 | 126,360,0 00 | 9,720,000.00 | 24,300,000.00 | 38,880,000.00 | 53,460,000.00 | 126,360,0 00 | |
| Total Revenues | 1 | 0 | 0 | 0 | 0 | 0 | |||||||||||||
| Variable Costs (VC) | |||||||||||||||||||
| Amortization (2 year S/L) Burcicki, Jim: While amortization in its normal sense would be considered a FC, it is considered a VC here because the number of carts is variable even though we are using an average of 200 carts as the basis. | 2 | 746,400 | 746,400 | 746,400 | 746,400 | 746,400 | 746,400 | 746,400 | 746,400 | 746,400 | 746,400 | 746,400 | 746,400 | 746,400 | 2,239,200 | 2,239,200 | 2,239,200 | 2,239,200 | 8,956,800 |
| Printing | 3 | 240,000 | 240,000 | 240,000 | 240,000 | 240,000 | 240,000 | 240,000 | 240,000 | 240,000 | 20,880,000 | 14,440,000 | 475,200 | 6,480,000 | 8,640,000 | 2,296,000 | |||
| Replacement (even distribution) | 4 | 18,990 | 18,990 | 18,990 | 18,990 | 18,990 | 18,990 | 18,900 | 18,900 | 18,900 | 18,900 | 18,900 | 18,900 | 227,880 | 569,499 | 569,499 | 569,499 | 569,499 | 22 |
| Cart Rental (10% Revenue) | 5 | 1,620 | 3,240 | 4,860 | 6,480 | 8,100 | 9,720 | 11,340 | 12,960 | 14,580 | 16,200 | 17,820 | 19,440 | 126,360 | 2,268 | 2,916 | 9,234 | ||
| Mktg. Sales & Comm. | 6 | 1,458,000 | 3,645,000 | 5,382,000 | 8,019,000 | 23,454,000 | 2,583,000 | 4,777,000 | 6,957,000 | 6,957,000 | 23,454,000 | ||||||||
| Grocery Store Operations | 7 | 240,000 | 240,000 | 240,000 | 480,000 | 480,000 | 480,000 | 720,000 | 720,000 | 720,000 | 2,160,000 | 2,520,000 | 2,880,000 | 3,240,000 | 3,600,000 | 12,240,000 | |||
| Total VC | 767,010 | 768,630 | 2,228,250 | 1,251,870 | 1,253,490 | 4,900,110 | 1,496,640 | 1,498,260 | 6,881,880 | 1,741,500 | 1,743,120 | 9,763,740 | 34,294,500 | 3,763,890 | 7,405,470 | 9,876,780 | 13,248,360 | 34,294,500 | |
| Contribution Margin (CM) | (767,010) | (768,630) | (2,228,250) | (1,251,870) | (1,253,490) | (4,900,110) | (1,496,640) | (1,498,260) | (6,881,880) | (1,741,500) | (1,743,120) | (9,763,740) | (34,294,500) | -3,763,890 | -7,405,470 | -9,876,780 | -13,248,360 | -34,294,500 | |
| CM per Unit/Cart | (3.20) | (1.60) | (3.09) | (1.04) | (0.87) | (2.92) | (0.78) | ERROR:#VALUE! | ERROR:#VALUE! | (0.66) | (0.61) | (3.13) | (1.64) | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | |
| Fixed Costs (FC) | |||||||||||||||||||
| Accounting & Audit | 8 | 24,000 | 24,000 | 52,000 | 26,000 | 26,000 | 52,000 | 26,000 | 26,000 | 26,000 | 26,000 | 26,000 | 32,000 Burcicki, Jim: Will cost 15k so I am assuming the expense is being accrued and therefore expensed in December as an adjusting entry. | 47,000 | 100,000 | 100,000 | 78,000 | 84,000 | 446,000 |
| Advertising (even distribution) | 9 | 13,125 | 13,125 | 131,250 | 13,125 | 13,125 | 131,250 | 13,125 | 13,125 | 131,250 | 13,125 | 13,125 | 131,250 | 132,825 | 132,825 | 132,825 | 132,825 | 531,300 | |
| Auto Lease | 10 | 6,000 | 6,000 | 6,000 | 6,000 | 6,000 | 6,000 | 6,000 | 6,000 | 24,000 | |||||||||
| Bank Charges | 11 | 500 | 500 | 500 | 500 | 2,000 | 500 | 500 | 500 | 500 | 2,000 | ||||||||
| Entertainment & Promotion | 12 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 120,000 | 30,000 | 30,000 | 30,000 | 30,000 | 120,000 |
| Insurance | 13 | 25,000 | 25,000 | 25,000 | 25,000 | 100,000 | 25,000 | 25,000 | 25,000 | 25,000 | 100,000 | ||||||||
| Legal | 14 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 120,000 | 30,000 | 30,000 | 30,000 | 30,000 | 120,000 |
| Management Fees | 15 | 24,167 | 24,167 | 24,167 | 24,167 | 24,167 | 24,167 | 24,167 | 24,167 | 24,167 | 24,167 | 24,167 | 24,167 | 290,000 | 72,500 | 72,500 | 72,500 | 72,500 | 290,000 |
| Office & Sundry | 16 | 15,000 | 9,000 | 12,000 | 15,000 | 51,000 | 15,000 | 9,000 | 12,000 | 15,000 | 51,000 | ||||||||
| Public Relations | 17 | 100,000 | 100,000 | 100,000 | 100,000 | 400,000 | 100,000 | 100,000 | 100,000 | 100,000 | 400,000 | ||||||||
| Rent | 18 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | 120,000 | 30,000 | 30,000 | 30,000 | 30,000 | 120,000 |
| Salaries & Benefits | 19 | 12,000 | 12,000 | 12,000 | 12,000 | 12,000 | 12,000 | 12,000 | 12,000 | 12,000 | 12,000 | 12,000 | 12,000 | 144,000 | 21,600 | 28,800 | 21,600 | 72,000 | 144,000 |
| Stationary & Printing | 20 |
Burcicki, Jim: The author expenses the 10k in January. However, it states that the 10k will be purchased during the first quarter - first three months - in sufficient quantities to last the entire year. This should be a prepaid and then expensed during the year. With no set amoutns, I assumed an even distribution throughout the year. | 10,000 | 10,000 | 10,000 | ||||||||||||||
| Telephone & Faxc | 21 | 3,500 | 3,500 | 3,500 | 3,500 | 3,500 | 3,500 | 3,500 | 3,500 | 3,500 | 3,500 | 3,500 | 3,500 | 42,000 | 10,500 | 10,500 | 10,500 | 10,500 | 42,000 |
| Travel & Accommodation | 22 | 30,000 | 60,000 | 90,000 | 135,000 | 315,000 | 30,000 | 60,000 | 90,000 | 135,000 | 315,000 | ||||||||
| Total FC | 106,792 | 106,792 | 439,417 | 108,792 | 108,792 | 453,417 | 108,792 | 108,792 | 460,417 | 108,792 | 108,792 | 514,417 | 2,734,004 | 653,001 | 671,001 | 678,001 | 732,001 | 2,734,004 | |
| Total Expenses (VC + FC) | 873,802 | 875,422 | 2,667,667 | 1,360,662 | 1,362,282 | 5,353,527 | 1,605,432 | 1,607,052 | 7,342,297 | 1,850,292 | 1,851,912 | 10,278,157 | 37,028,504 | 4,416,891 | 8,076,471 | 10,554,781 | 13,980,361 | 37,028,504 | |
| Net Operating Income | (873,802) | (875,422) | (2,667,667) | (1,360,662) | (1,362,282) | (5,353,527) | (1,605,432) | (1,607,052) | (7,342,297) | (1,850,292) | (1,851,912) | (10,278,157) | -37,028,504 | -4,416,891 | -8,076,471 | -10,554,781 | -13,980,361 | -37,028,504 | |
| Break Even Point in terms of carts | -1,664,582 Burcicki, Jim: The Formula Method - Managerial Accounting, 15th ed., Page 201 BE (Carts) = Total FC / (CM per Unit/Cart) | ERROR:#DIV/0! Burcicki, Jim: The Formula Method - Managerial Accounting, 15th ed., Page 201 BE (Carts) = Total FC / (CM per Unit/Cart) |
|||||||||||||||||
| Break Even Point in terms of stores | -7,462 Burcicki, Jim: The Equation Method - Managerial Accounting, 15th ed., Page 201 Break Even = Q Unit CM = CM / Total # of Stores Profit = Unit CM x Q - Fixed Expense | -183,040 Burcicki, Jim: The Equaion Method - Managerial Accounting, 15th ed., Page 201 Break Even = Q Unit CM = CM / Total # of Stores Profit = Unit CM x Q - Fixed Expense |
