Alternative Analysis Case study
farjad
DATA.xlsxIntro
| Soft Drinks | ||||||||
| Mango Plus | Minessota Spruce | Limextreme Pop | Orangola Pop | Total sales | ||||
| Sales | $ 3,076,668 | $ 2,568,976 | $ 2,883,730 | $ 2,324,749 | $ 10,854,123 | $ 24,038,224 | ||
| COGS | $ 1,136,204 | $ 733,599 | $ 840,507 | $ 678,835 | $ 3,389,145 | |||
| Gross margin | $ 1,940,464 | $ 1,835,377 | $ 2,043,223 | $ 1,645,914 | $ 7,464,978 | |||
| Mango Plus | 13% | |||||||
| Minessota Spruce | 11% | |||||||
| Juices | Limextreme Pop | 12% | ||||||
| Caribbean Punch | Fruist Zest | Apple-Berry | Pear Supreme | Orangola Pop | 10% | |||
| Sales | $ 3,607,416 | $ 3,194,377 | $ 3,592,972 | $ 2,789,336 | $ 13,184,101 | Caribbean Punch | 15% | |
| COGS | $ 871,421 | $ 791,364 | $ 866,453 | $ 664,620 | $ 3,193,858 | Fruist Zest | 13% | |
| Gross margin | $ 2,735,995 | $ 2,403,013 | $ 2,726,519 | $ 2,124,716 | $ 9,990,243 | Apple-Berry | 15% | |
| Pear Supreme | 12% |
Brand contribution (Sales)
Mango Plus Minessota Spruce Limextreme Pop Orangola Pop Caribbean Punch Fruist Zest Apple-Berry Pear Supreme 0.12799065355244216 0.10687045765111433 0.11996435343975495 9.6710514054615684E-2 0.15006998853159867 0.13288739634009566 0.14946911219397904 0.1160375242363995
Water venture
| SETUP COSTS | Variable costs | ||||||||||||
| Cost of land | $ 1,840,000 | Bottle | 0.014 | ||||||||||
| Transactional costs | $ 65,000 | Bottling | 0.033 | ||||||||||
| Drilling costs | $ 48,000 | Other packages | 0.01 | ||||||||||
| Property cleanup | $ 40,000 | Shipping | 0.005 | ||||||||||
| Collection and tanks | $ 840,000 | Total | 0.062 | ||||||||||
| Legal | $ 25,000 | ||||||||||||
| $ 2,858,000 | |||||||||||||
| Inflation is 1% annually | |||||||||||||
| Year | Sales number | Sales price | Revenue | Variable costs | Total VC | Contribution margin | Sales personnel | Sales commission | Advertising | Store promo | Fix site personnel | Depreciation | Total costs |
| 1 | 50,000,000 | $ 0.16 | $ 8,000,000 | $ 0.06200 | $ 3,100,000 | $ 4,900,000 | $ 640,000 | $ 16,000 | $ 2,400,000 | $ 1,500,000 | $ 320,000 | 99000 | $ 4,975,000 |
| 2 | 54,000,000 | $ 0.1602 | $ 8,648,640.00 | $ 0.06206 | $ 3,351,348 | $ 5,297,292 | $ 640,640 | $ 17,297 | $ 2,424,000 | $ 1,515,000 | $ 320,320 | 99000 | $ 5,016,257 |
| 3 | 57,240,000 | $ 0.1603 | $ 9,176,725.96 | $ 0.06212 | $ 3,555,981 | $ 5,620,745 | $ 641,281 | $ 18,353 | $ 1,500,000 | $ 1,200,000 | $ 320,640 | 99000 | $ 3,779,274 |
| 4 | 60,102,000 | $ 0.1605 | $ 9,645,197.82 | $ 0.06219 | $ 3,737,514 | $ 5,907,684 | $ 721,441 | $ 19,290 | $ 1,446,780 | $ 1,212,000 | $ 361,081 | 99000 | $ 3,859,592 |
| 5 | 63,107,100 | $ 0.1606 | $ 10,137,585.17 | $ 0.06225 | $ 3,928,314 | $ 6,209,271 | $ 722,162 | $ 20,275 | $ 1,520,638 | $ 1,216,510 | $ 361,442 | 99000 | $ 3,940,027 |
| 6 | 65,000,313 | $ 0.1608 | $ 10,452,154.43 | $ 0.06231 | $ 4,050,210 | $ 6,401,945 | $ 722,884 | $ 20,904 | $ 1,567,823 | $ 1,254,259 | $ 361,804 | 99000 | $ 4,026,674 |
| 7 | 66,950,322 | $ 0.1610 | $ 10,776,484.79 | $ 0.06237 | $ 4,175,888 | $ 6,600,597 | $ 723,607 | $ 21,553 | $ 1,616,473 | $ 1,293,178 | $ 362,165 | 99000 | $ 4,115,976 |
| 8 | 68,958,832 | $ 0.1611 | $ 11,110,879.11 | $ 0.06244 | $ 4,305,466 | $ 6,805,413 | $ 724,331 | $ 22,222 | $ 1,666,632 | $ 1,333,305 | $ 362,528 | 99000 | $ 4,208,018 |
| 9 | 71,027,597 | $ 0.1613 | $ 11,455,649.69 | $ 0.06250 | $ 4,439,064 | $ 7,016,585 | $ 805,617 | $ 22,911 | $ 1,718,347 | $ 1,374,678 | $ 402,808 | 99000 | $ 4,423,362 |
| 10 | 73,158,425 | $ 0.1614 | $ 11,811,118.50 | $ 0.06256 | $ 4,576,808 | $ 7,234,310 | $ 806,422 | $ 23,622 | $ 1,771,668 | $ 1,417,334 | $ 403,211 | 99000 | $ 4,521,258 |
| $ 61,993,842 | |||||||||||||
| EBT | Income Tax | Net income | Depreciation | Op Cashflow | |||||||||
| 1 | -$ 75,000 | -22500 | -$ 52,500 | 99000 | $ 46,500 | ||||||||
| 2 | $ 281,035 | 84310.4160000002 | $ 196,724 | 99000 | $ 295,724 | ||||||||
| 3 | $ 1,841,470 | 552441.07128096 | $ 1,289,029 | 99000 | $ 1,388,029 | ||||||||
| 4 | $ 2,048,092 | 614427.538636318 | $ 1,433,664 | 99000 | $ 1,532,664 | ||||||||
| 5 | $ 2,269,243 | 680773.028290975 | $ 1,588,470 | 99000 | $ 1,687,470 | ||||||||
| 6 | $ 2,375,271 | 712581.197693941 | $ 1,662,689 | 99000 | $ 1,761,689 | ||||||||
| 7 | $ 2,484,620 | 745386.136784815 | $ 1,739,234 | 99000 | $ 1,838,234 | ||||||||
| 8 | $ 2,597,396 | 779218.785089205 | $ 1,818,177 | 99000 | $ 1,917,177 | ||||||||
| 9 | $ 2,593,224 | 777967.05571055 | $ 1,815,256 | 99000 | $ 1,914,256 | ||||||||
| 10 | $ 2,713,052 | 813915.667076048 | $ 1,899,137 | 99000 | $ 1,998,137 | ||||||||
| $ 14,379,882 |
Wonder brands
| Quantity | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | ||||
| Prune Wonder | 5,245,332 | 5,245,332 | 5,245,332 | 5,245,332 | 5,245,332 | 5,245,332 | ||||
| Ice Tea Wonder | 5,405,227 | 5,405,227 | 5,405,227 | 5,405,227 | 5,405,227 | 5,405,227 | NPV | |||
| Splite Wonder | 5,114,577 | 5,114,577 | 5,114,577 | 5,114,577 | 5,114,577 | 5,114,577 | Present value of cash flow | $ 2,925,000 | ||
| Sports Wonder | 5,263,767 | 5,263,767 | 5,263,767 | 5,263,767 | 5,263,767 | 5,263,767 | Transaction cost | $ 25,000 | ||
| Total | 21,028,903 | 21,028,903 | 21,028,903 | 21,028,903 | 21,028,903 | 21,028,903 | Cost of Purchase | $ 2,800,000 | ||
| Selling price (227 ml) | $ 0.16 | $ 0.16 | $ 0.16 | $ 0.16 | $ 0.17 | $ 0.17 | ||||
| Selling price (500 ml) | $ 35.00 | $ 35.00 | $ 35.00 | $ 35.00 | $ 37.00 | $ 37.00 | ||||
| Revenue | $ 7,411,000 | $ 7,422,000 | $ 7,426,000 | $ 7,427,000 | $ 7,891,000 | $ 7,891,000 | ||||
| VC | $ 3,705,500 | $ 3,711,000 | $ 3,713,000 | $ 3,713,500 | $ 3,945,500 | $ 3,945,500 | ||||
| Contibution margin | $ 3,705,500 | $ 3,711,000 | $ 3,713,000 | $ 3,713,500 | $ 3,945,500 | $ 3,945,500 | ||||
| Expenses | $ 2,618,000 | $ 3,206,000 | $ 3,228,000 | $ 3,250,000 | $ 3,301,000 | $ 3,323,000 | ||||
| Income before tax | $ 1,087,500 | $ 505,000 | $ 485,000 | $ 463,500 | $ 644,500 | $ 622,500 | ||||
| Taxes | $ 326,250.00 | $ 151,500.00 | $ 145,500.00 | $ 139,050.00 | $ 193,350.00 | $ 186,750.00 | ||||
| Net income | $ 761,250.00 | $ 353,500.00 | $ 339,500.00 | $ 324,450.00 | $ 451,150.00 | $ 435,750.00 | ||||
| Net income margin % | 10% | 5% | 5% | 4% | 6% | 6% | ||||
| $ 761,250 | $ 294,583 | $ 235,764 | $ 187,760 | $ 217,568 | $ 175,118 |
