Question

4. What is your weekly gross profit after raising the price of each cup size by 25 cents? We will check Insert the answer and the cell with the answer here the cell in your spread sheet. a. Hint: You have to determine the forecasted change in average weekly sales based on each cup size. New Cup Prices by Size Small-$2.75 Medium - $3.75 Large-$4.75 Customer Response to Coffee Price Change of +25 cents. (How the customer reacts to the price increase) Small- +4% (increase in demand) -10% ( decrease in demand) Medium- Large- +6% (increase in demand)

Answer 1

A с D 1 Qno.01 2 Sales 3 Week 1 4Week 2 5 Week 3 6 Week 4 7 Week 5 8 Week 6 Small Coffee Medium Coffee Large Coffee 2045 1689 922 2065 1751 901 2057 1008 868 1698 2032 901 2035 1723 865 2076 1802 903 9 a. Average sales 2,052 1,612 893 =ROUND(AVERAGE( ROUND(AVERAGE(C3 ROUND(AVERAGE(D |3:D8),0) 3.50 $ 5,642.00 $ В3:8),0) C8),0) 2.50 $ 5,130.00 $ 10 11 b. Price per cup 12 c.Average revenue= a x b 4.50 4,018.50 13 14 Q no.02 15 Size 16 Small 17 Medium 18 Large Total COGS Cost of each cup Cost of coffee 0.19 0.08 0.11 0.09 0.22 0.31 0.11 0.33 0.44 19 20 COGS of large cup of coffee $ 0.44 Cell D18 21 C

A с E 22 Q no.03 Small Coffee Medium Coffee Large Coffee 23 24 a. Average sales 25 b.Cost of Goods sold per cup S 893.00 2,052.00 0.19 $ 1,612.00 0.31 $ 0.44 c. Avergae cost per week (a 26 x b) 27 d. Average Revenue 28 e. Gross Profit (d-c) 389.88 $ 5,130.00 $ 4,740.12 $ 499.72 $ 392.92 5,642.00 $ 5,142.28 $ 4,018.50 3,625.58 $ 29 30 Q no.04 31 Change in average weekly sales Existing average sales Percentage Change +4% -10% New sales 32 2,134.00 ROUND(B34*1.04,0) 1,451.00 ROUND(B35*0.90,0) 947.00 ROUND(B36* 1.06,0) 33 Small 34 Medium 35 Large 2,052 1,612 +6% 893 36 37 Weekly gross profit after price change Small Coffee Medium Coffee 38 Large Coffee 39 a. New weekly sales 40 b.Cost of Goods sold per cup 2,134.00 947.00 1,451.00 0.31 $ 0.19 $ $ 0.44 c. Average cost per week (a 41 x b) 42 d. Revenue per cup 405.46 $ 2.75 $ 449.81 $ 3.75 $ 416.68 4.75 e. Average weekly Revenue 43 (а x d) 44 f. Gross Profit (e-c) 5,868.50 $ 5,463.04 $ 5,441.25 $ 4,498.25 4,081.57 4,991.44 $ $ 45 46 Cell no. C46 D46 В46 47

с E А 47 48 Q no.05 Medium Coffee Large Coffee 0.24 3/64*7.68 Small Coffee 49 50 a.New Coffee cost 0.12 0.36 - 1/64*7.68 -2/64*7.68 51 52 b. Cost of each cup 53 Total COGS 0.08 0.11 0.09 0.33 0.47 0.2 54 55 Computation of Gross Weekly Profit Small Coffee Medium Coffee Large Coffee 56 (Sales as per first 893.00 computation is taken here) 57 a. Average sales 58 b.Cost of Goods sold per cup $ 59 c. Avergae cost per week 2,052.00 1,612.00 0.20 $ 410.40 $ 0.33$ 531.96$ 0.47 419.71 (Revenue as per first 4,018.50 computation is taken here) 5,130.00 $ 4,719.60 $ 60 d. Average Revenue 61 e. Gross Profit (d-c) 5,642.00 $ 5,110.04 $ $ 3,598.79 62 Note (if to be recom puted with after price changes, replace the sales and revenue figures 63 with after price change sales and revenue figures) 65