Risk Assessment: Question 2 Analyze the risk associated with the cost of ingredients a) Create a visualization to show how a 25% and 50% increase in the cost of sugar would affect the price of each cupcake. b) If the number of sales of cupcakes and their sales price ($2.4/dozen) remained the same, how would a 25% or 50% increase in the price of sugar affect the gross profit margin on cupcakes (Cupcake revenue less cost of cupcakes) and the gross profit Margin Percent (Gross Profit margin/ cupcake Revenue). For this exercise, use the number of cupcakes sold in 2022. Also, assume we are looking at only the cost of ingredients when computing the gross margin and gross margin percent. c) Is there a price point past which you believe an increase in the price of sugar would become a risk to Alpine Cupcakes? Show evidence to support your conclusion. The example below shows one way you might do this. Look at the formulas in the dark blue boxes, by changing the number in the green box of the "Price Change Key," it changes the price of sugar in the visualization box as well as the price of the cupcake. Try it. In the green box, put.25 for a 25% increase in the price of sugar. Hint: Often when doing analysis in excel, you need to set up a visualization where you can change a number in a "key" to see an effect elsewhere on a sheet. This exercise gives you an opportunity to practice this skill by referencing numbers in a different cell. Total w/price Orig. Price Vanilla Vanilla Cupcakes Butter Cream Frosting change baking Ingredient eggs sugar salt vanilla flour powder butter Unit measured as dozen pounds pounds quart pounds pounds pounds Amount of unit used per 36 cupcakes 0.7500 0.9923 0.0049 0.0150| 1.2375 0.0195 1.0000 price/unit $1.50 $0.75 $0.20 $40.00 $0.30 $3.00 $2.00 Ingredient cost per /36 cupcakes $1.13 $0.74 $0.00 $0.60 $0.37 $0.06 $2.00 Cost /cupcake vanilla quart 0.0225 $40.00 $0.90 Differenc Price change Key % change (50% Item Price increase = .5) sugar $0.50 0.5 Confect. Sugar $0.56 0.5 shorteni ng pounds 0.6780 0.55 $0.37 salt butter pounds pounds 0.0049 0.7500 $0.20 $2.00 $0.00 $1.50 confec. Sugar pounds 3.9600 $0.84 $3.33 milk gallons 0.0473 $3.00 $0.14 New Price $0.75 $0.84 $11.14 $0.31 $0.27 $0.04 Risk Assessment: Question 2 Analyze the risk associated with the cost of ingredients a) Create a visualization to show how a 25% and 50% increase in the cost of sugar would affect the price of each cupcake. b) If the number of sales of cupcakes and their sales price ($2.4/dozen) remained the same, how would a 25% or 50% increase in the price of sugar affect the gross profit margin on cupcakes (Cupcake revenue less cost of cupcakes) and the gross profit Margin Percent (Gross Profit margin/ cupcake Revenue). For this exercise, use the number of cupcakes sold in 2022. Also, assume we are looking at only the cost of ingredients when computing the gross margin and gross margin percent. c) Is there a price point past which you believe an increase in the price of sugar would become a risk to Alpine Cupcakes? Show evidence to support your conclusion. The example below shows one way you might do this. Look at the formulas in the dark blue boxes, by changing the number in the green box of the "Price Change Key," it changes the price of sugar in the visualization box as well as the price of the cupcake. Try it. In the green box, put.25 for a 25% increase in the price of sugar. Hint: Often when doing analysis in excel, you need to set up a visualization where you can change a number in a "key" to see an effect elsewhere on a sheet. This exercise gives you an opportunity to practice this skill by referencing numbers in a different cell. Total w/price Orig. Price Vanilla Vanilla Cupcakes Butter Cream Frosting change baking Ingredient eggs sugar salt vanilla flour powder butter Unit measured as dozen pounds pounds quart pounds pounds pounds Amount of unit used per 36 cupcakes 0.7500 0.9923 0.0049 0.0150| 1.2375 0.0195 1.0000 price/unit $1.50 $0.75 $0.20 $40.00 $0.30 $3.00 $2.00 Ingredient cost per /36 cupcakes $1.13 $0.74 $0.00 $0.60 $0.37 $0.06 $2.00 Cost /cupcake vanilla quart 0.0225 $40.00 $0.90 Differenc Price change Key % change (50% Item Price increase = .5) sugar $0.50 0.5 Confect. Sugar $0.56 0.5 shorteni ng pounds 0.6780 0.55 $0.37 salt butter pounds pounds 0.0049 0.7500 $0.20 $2.00 $0.00 $1.50 confec. Sugar pounds 3.9600 $0.84 $3.33 milk gallons 0.0473 $3.00 $0.14 New Price $0.75 $0.84 $11.14 $0.31 $0.27 $0.04
