Question

1 Approved Answer

Posted on Jul 31, 2024

1. Put in all formulas to the current and proposed scenarios to create a fully functioning spreadsheet. 2. What is the total profit of proposed

image text in transcribed

1. Put in all formulas to the current and proposed scenarios to create a fully functioning spreadsheet. 2. What is the total profit of proposed vs current. 3. How much additional discount per unit is needed from the 2 Smartwater items to breakeven on Profit. Item Net Item Desc Discount % Discount $ Retail Profit GM% Units Sold Low C- Store EDLP Cost Cost Current Smart Water 23.7 Smart Water 33.8 Lifewtr 23.7 Lifewtr 33.8 Voss 16.9 Voss 28.7 Fiji 16.9 Fiji 33.8 $ 1.26 $ 1.44 $ 1.25 $ 1.56 $ 1.08 $ 1.69 $ 1.04 $ 1.66 4.76% $ 0.00% $ 19.95% $ 29.46% $ 8.34% $ 20.12% $ 0.00% $ 0.00% $ 0.06 $ 1.20 $ 2.29 $1.09 $ 1.44 $2.59 $1.15 0.25 $ 1.00 $ 1.89 $0.89 0.46 $ 1.10 $ 2.29 $1.19 0.09 $ 0.99 $ 1.79 $0.80 0.34 $ 1.35 $2.59 $1.24 $ 1.04 $1.99 $0.95 $ 1.66 $2.99 $1.33 48% 44% 47% 52% 45% 48% 48% 45% 26,647 $ 1.99 37,106 $ 2.29 35,854 $ 1.69 18,634 $ 2.39 8,710 $ 10,729 $ 15,107 $ 2,492 $ 2.99 Proposed lower Smart Water retails Smart Water 23.7 $ 1.26 4.76% $ Smart Water 33.8 $ 1.44 0.00% $ Lifewtr 23.7 $ 1.25 19.95% $ Lifewtr 33.8 $ 1.56 29.46% $ Voss 16.9 $ 1.08 8.34% $ Voss 28.7 $ 1.69 20.12% $ Fiji 16.9 $ 1.04 0.00% $ Fiji 33.8 $ 1.66 0.00% $ 0.06 $ 1.20 $1.99 $0.79 $ 1.44 $ 2.29 $0.85 0.25 $ 1.00 $ 1.89 $0.89 0.46 $ 1.10 $ 2.29 $1.19 0.09 $ 0.99 $ 1.79 $0.80 0.34 $ 1.35 $2.59 $1.24 $ 1.04 $1.99 $0.95 $ 1.66 $2.99 $1.33 40% 37% 47% 52% 45% 48% 48% 45% Low C- Units Store Sold EDLP 26,647 $ 1.99 37,106 $ 2.29 35,854 $ 1.69 18,634 $ 2.39 8,710 $ 10,729 $ 15,107$ 2,492 $ 2.99 - 1. Put in all formulas to the current and proposed scenarios to create a fully functioning spreadsheet. 2. What is the total profit of proposed vs current. 3. How much additional discount per unit is needed from the 2 Smartwater items to breakeven on Profit. Item Net Item Desc Discount % Discount $ Retail Profit GM% Units Sold Low C- Store EDLP Cost Cost Current Smart Water 23.7 Smart Water 33.8 Lifewtr 23.7 Lifewtr 33.8 Voss 16.9 Voss 28.7 Fiji 16.9 Fiji 33.8 $ 1.26 $ 1.44 $ 1.25 $ 1.56 $ 1.08 $ 1.69 $ 1.04 $ 1.66 4.76% $ 0.00% $ 19.95% $ 29.46% $ 8.34% $ 20.12% $ 0.00% $ 0.00% $ 0.06 $ 1.20 $ 2.29 $1.09 $ 1.44 $2.59 $1.15 0.25 $ 1.00 $ 1.89 $0.89 0.46 $ 1.10 $ 2.29 $1.19 0.09 $ 0.99 $ 1.79 $0.80 0.34 $ 1.35 $2.59 $1.24 $ 1.04 $1.99 $0.95 $ 1.66 $2.99 $1.33 48% 44% 47% 52% 45% 48% 48% 45% 26,647 $ 1.99 37,106 $ 2.29 35,854 $ 1.69 18,634 $ 2.39 8,710 $ 10,729 $ 15,107 $ 2,492 $ 2.99 Proposed lower Smart Water retails Smart Water 23.7 $ 1.26 4.76% $ Smart Water 33.8 $ 1.44 0.00% $ Lifewtr 23.7 $ 1.25 19.95% $ Lifewtr 33.8 $ 1.56 29.46% $ Voss 16.9 $ 1.08 8.34% $ Voss 28.7 $ 1.69 20.12% $ Fiji 16.9 $ 1.04 0.00% $ Fiji 33.8 $ 1.66 0.00% $ 0.06 $ 1.20 $1.99 $0.79 $ 1.44 $ 2.29 $0.85 0.25 $ 1.00 $ 1.89 $0.89 0.46 $ 1.10 $ 2.29 $1.19 0.09 $ 0.99 $ 1.79 $0.80 0.34 $ 1.35 $2.59 $1.24 $ 1.04 $1.99 $0.95 $ 1.66 $2.99 $1.33 40% 37% 47% 52% 45% 48% 48% 45% Low C- Units Store Sold EDLP 26,647 $ 1.99 37,106 $ 2.29 35,854 $ 1.69 18,634 $ 2.39 8,710 $ 10,729 $ 15,107$ 2,492 $ 2.99