Question

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.
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.25	$0.75
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				sugar	$0.50	0.5	$1.00
price/unit	$1.50	$0.75	$0.20	$40.00	$0.30	$3.00	$2.00	$40.00	$0.20	$2.00	0.55	$0.81	$3.00				Confect. Sugar	$0.56	0.25	$0.81
Ingredient cost per /36 cupcakes	$1.13	$0.74	$0.00	$0.60	$0.37	$0.06	$2.00	$0.90	$0.00	$1.50	$0.37	$3.21	$0.14	$11.02				$0.56	0.5	$1.06
Cost /cupcake														$0.31	$0.27	$0.04

Answer 1

	Increase	KEY		VANILA CUPCAKES								BUTTER CREAM FROSTING
	0%	1		Ingredient	eggs	sugar	salt	vanilla	flour	baking powder	butter	vanilla	salt	butter	shortening	confec. Sugar	milk
				Unit measured as	dozen	pounds	pounds	quart	pounds	pounds	pounds	quart	pounds	pounds	pounds	pounds	gallons
				Amount of unit used per 36 cupcakes	0.75	0.9923	0.0049	0.015	1.2375	0.0195	1	0.0225	0.0049	0.75	0.678	3.96	0.0473
				price/unit	$1.50	$0.75	$0.20	$40.00	$0.30	$3.00	$2.00	$40.00	$0.20	$2.00	0.55	$0.81	$3.00
				Ingredient cost per /36 cupcakes	$1.13	$0.74	$0.00	$0.60	$0.37	$0.06	$2.00	$0.90	$0.00	$1.50	$0.37	$3.21	$0.14
				Cost per Cupcake	$0.03	$0.02	$0.00	$0.02	$0.01	$0.00	$0.06	$0.03	$0.00	$0.04	$0.01	$0.09	$0.00
				Cost per Vanila Cupcake	$0.14
				Cost per Butter Cream Frosting	$0.17