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/cupcake) 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.
Vanilla	Vanilla Cupcakes							Butter Cream Frosting						Total w/price change	Orig. Price	Difference	Price change Key
Ingredient	eggs	sugar	salt	vanilla	flour	baking powder	butter	vanilla	salt	butter	shortening	confec. Sugar	milk				Item	Price	% change (50% increase = .5)	New Price
Unit measured as	dozen	pounds	pounds	quart	pounds	pounds	pounds	quart	pounds	pounds	pounds	pounds	gallons				sugar	$0.50		$0.50
Amount of unit used per 36 cupcakes	0.7500	0.9923	0.0049	0.0150	1.2375	0.0195	1.0000	0.0225	0.0049	0.7500	0.6780	3.9600	0.0473				Confect. Sugar	$0.56		$0.56
price/unit	$1.50	$0.50	$0.20	$40.00	$0.30	$3.00	$2.00	$40.00	$0.20	$2.00	0.55	$0.56	$3.00
Ingredient cost per /36 cupcakes	$1.13	$0.50	$0.00	$0.60	$0.37	$0.06	$2.00	$0.90	$0.00	$1.50	$0.37	$2.22	$0.14	$9.79
Cost /cupcake														$0.27	$0.27	$0.00