Question: 1. Add back all formulas to the current and proposed scenarios to create a fully functioning spreadsheet. 2. Model the annualized dollar impact on the
| 1. Add back all formulas to the current and proposed scenarios to create a fully functioning spreadsheet. | ||||||||||||
| 2. Model the annualized dollar impact on the water portfolio assuming no increase in units sold. | ||||||||||||
| 3. How much additional discount per unit is needed from Smartwater to breakeven | ||||||||||||
| Item Desc | Promo Desc | Item Cost | Discount % | Discount $ | Net Cost | Retail | Profit | GM% | Units Sold | Low C-Store EDLP | ||
| Current | ||||||||||||
| Smart Water 23.7 | REG | $ 1.26 | 4.76% | $ 0.06 | $ 1.20 | $ 2.29 | $ 1.09 | 48% | 26,647 | $ 1.99 | ||
| Smart Water 33.8 | REG | $ 1.44 | 0.00% | $ - | $ 1.44 | $ 2.59 | $ 1.15 | 44% | 37,106 | $ 2.29 | ||
| Lifewtr 23.7 | REG | $ 1.25 | 19.95% | $ 0.25 | $ 1.00 | $ 1.89 | $ 0.89 | 47% | 35,854 | $ 1.69 | ||
| Lifewtr 33.8 | REG | $ 1.56 | 29.46% | $ 0.46 | $ 1.10 | $ 2.29 | $ 1.19 | 52% | 18,634 | $ 2.39 | ||
| Voss 16.9 | REG | $ 1.08 | 8.34% | $ 0.09 | $ 0.99 | $ 1.79 | $ 0.80 | 45% | 8,710 | $ - | ||
| Voss 28.7 | REG | $ 1.69 | 20.12% | $ 0.34 | $ 1.35 | $ 2.59 | $ 1.24 | 48% | 10,729 | $ - | ||
| Fiji 16.9 | REG | $ 1.04 | 0.00% | $ - | $ 1.04 | $ 1.99 | $ 0.95 | 48% | 15,107 | $ - | ||
| Fiji 33.8 | REG | $ 1.66 | 0.00% | $ - | $ 1.66 | $ 2.99 | $ 1.33 | 45% | 2,492 | $ 2.99 | ||
| Proposed lower Smart Water retails | ||||||||||||
| Smart Water 23.7 | REG | $ 1.26 | 4.76% | $ 0.06 | $ 1.20 | $ 1.99 | $ 0.79 | 40% | 26,647 | $ 1.99 | ||
| Smart Water 33.8 | REG | $ 1.44 | 0.00% | $ - | $ 1.44 | $ 2.29 | $ 0.85 | 37% | 37,106 | $ 2.29 | ||
| Lifewtr 23.7 | REG | $ 1.25 | 19.95% | $ 0.25 | $ 1.00 | $ 1.89 | $ 0.89 | 47% | 35,854 | $ 1.69 | ||
| Lifewtr 33.8 | REG | $ 1.56 | 29.46% | $ 0.46 | $ 1.10 | $ 2.29 | $ 1.19 | 52% | 18,634 | $ 2.39 | ||
| Voss 16.9 | REG | $ 1.08 | 8.34% | $ 0.09 | $ 0.99 | $ 1.79 | $ 0.80 | 45% | 8,710 | $ - | ||
| Voss 28.7 | REG | $ 1.69 | 20.12% | $ 0.34 | $ 1.35 | $ 2.59 | $ 1.24 | 48% | 10,729 | $ - | ||
| Fiji 16.9 | REG | $ 1.04 | 0.00% | $ - | $ 1.04 | $ 1.99 | $ 0.95 | 48% | 15,107 | $ - | ||
| Fiji 33.8 | REG | $ 1.66 | 0.00% | $ - | $ 1.66 | $ 2.99 | $ 1.33 | 45% | 2,492 | $ 2.99 |
Step by Step Solution
★★★★★
3.53 Rating (191 Votes )
There are 3 Steps involved in it
1 Expert Approved Answer
Step: 1 Unlock
Question Has Been Solved by an Expert!
Get step-by-step solutions from verified subject matter experts
Step: 2 Unlock
Step: 3 Unlock
Study Help