Show your work in Excel
1. Add back all formulas to the current and proposed scenarios to create a fully functioning spreadsheet. (3 minutes).
2. Model the annualized dollar impact on the water portfolio assuming no increase in units sold. (3 minutes).
3. How much additional discount per unit is needed from Smartwater to breakeven (3 minutes).
										Movement		Competitor
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