Alpine Cupcakes - Audit Case with Data Analytics 2e
Data Analytics Module Part 1 Risk Assessment
Question # 4
Alpine Cupcakes Recipes Size Purchased in purchased (for 36 cupcakes) Carrot Cake Cupcakes (makes 36) Eggs Veg. Oil Sugar Vanilla Flour Baking soda Baking powder Salt Cinnamon Carrots Pecans 8.00 eggs 2.50 cups 4.00 cups 4.00 tsp 4.00 Cups 4.00 tsp 4.00 tsp 1.00 tsp 4.00 tsp 3.00 pound 2.00 cups dozen gallons pounds quart pounds pounds pounds pounds pounds pounds pounds 0.666666667 i.e., we need .6666 of a dozen to make 36 cupcakes 0.1575 1.764 0.0200 1.1 0.0400 0.0320 0.0130 0.0520 16.0000 0.63 Cr. Cheese Frosting/Carrot Cake (36) Butter 0.5 Cream cheese Confect. Sugar Vanilla Pecans 1.00 cup 16.00 oz 8.00 cup 2.00 tsp 2.00 cup pounds pounds pounds quart pounds 1.008 2.112 0.0100 0.63 Gluten free Chocolate Cupcakes (36) Rice flour Millet flour Cocoa Salt Baking soda Baking powder Xanthan gum Eggs Sugar Sour cream Milk Vanilla 2.25 cups 1.13 cups 0.75 cups 1.50 tsp 1.50 tsp 1.50 tbl 1.50 tsp 6.00 eggs 1.88 cups 1.00 cups 1.50 cups 3.00 tsp pounds pounds pounds pounds pounds pounds pounds dozen pounds pounds gallons quart 0.783 0.253125 0.14 0.0195 0.0150 0.0120 0.0014 0.5 0.826875 0.507 0.0945 0.0150 1 Gluten Free Chocolate Frosting (36) Butter Confect. Sugar Cocoa Vanilla Milk pounds pounds 2.00 cups 9.00 cups 2.50 cups 0.40 tsp 1.00 cups pounds quart gallons 2.376 0.48 0.0020 0.063 Red Velvet Cupcakes (36) Flour Cocoa Baking soda Salt Butter Sugar Eggs Sour cream Milk Food color Vanilla 3.75 cups 0.75 cups 1.50 tsp 0.75 tsp 1.50 cups 3.00 cups 6.00 eggs 1.50 cups 0.75 cups 1.50 oz 3.00 tsp pounds pounds pounds pounds pounds pounds dozen pounds gallons pints quart 1.03125 0.14 0.0150 0.0098 0.75 1.323 0.5 0.7605 0.04725 0.0945 0.0150 0.756 0.1875 Vanilla Cream Cheese Frosting/ red velvet cupcakes (36) Cream cheese Butter Sour cream Vanilla Confect. Sugar 12.00 oz 0.38 cup 3.00 tbl 3.00 tsp 24.00 oz pounds pounds pounds quart pounds 1.521 0.0150 6.336 Vanilla Cupcakes (36) Butter Sugar Flour Salt Eggs Vanilla Baking powder 2.00 cups 2.25 cups 4.50 cups 0.38 tsp 9.00 eggs 3.00 tsp 1.50 tsp pounds pounds pounds pounds dozen quart pounds 0.99225 1.2375 0.0049 0.75 0.0150 0.0195 Butter Cream Frosting (36) Butter Shortening Salt Vanilla Confec. Sugar Milk 1.50 cups 1.50 cups 0.38 tsp 4.50 tsp 15.00 cups 0.75 cups pounds pounds pounds quart pounds gallons 0.75 0.678 0.0049 0.0225 3.96 0.04725 Lemon Cupcakes (36) Flour Salt Butter Sugar Eggs Vanilla Lemon zest Milk Lemon juice Baking powder 3.60 cups 0.70 tsp 1.20 cups 2.40 cups 5.00 eggs 1.20 tsp 2.40 tbl 1.20 cups 3.00 tbl 1.50 tsp pounds pounds pounds pounds dozen quart pounds (lemon gallons gallon pounds 0.99 0.0091 0.6 1.0584 0.416666667 0.0060 0.0880 0.0756 0.012 0.0195 gallon Lemon Cream Icing (36) Cream Confec. Sugar Lemon juice 2.40 cups 0.90 cups 1.80 tbl pounds 0.1512 0.2376 0.0072 gallon Chocolate cupcakes (36) Flour Baking soda Baking powder Cocoa Salt Butter Sugar Eggs Vanilla Milk 3.00 cups 0.56 tsp 4.50 tsp 1.69 cups 0.28 tsp 6.75 tbl 3.38 cups 5.00 eggs 1.69 tsp 2.25 cups pounds pounds pounds pounds pounds pounds pounds dozen quart gallons 0.825 0.0056 0.0360 0.32 0.0037 0.20925 1.488375 0.416666667 0.0084 0.14175 Alpine Cupcakes Ingredient Price List, by Vendor Bakery Price List Almond milk Baking powder Baking soda Butter Carrots Cider vinegar Cinnamon Cocoa Coconut oil Confec. Sugar Cornstarch Cream Cream cheese Eggs Flour Food color Lemon juice Lemon zest Milk Millet flour Nutmeg Pecans Rice flour Salt Shortening Sour cream Sugar Vanilla Vanilla soymilk Veg. Oil Vegan butter Xanthan gum Pr. Per $4.00 $3.00 $1.40 $2.00 $2.00 $3.00 $9.00 $2.80 $20.00 $0.56 $4.00 $10.00 $5.00 $1.50 $0.30 $8.00 $10.00 $1.50 $3.00 $3.00 $22.00 $5.00 $1.20 $0.20 $0.55 $2.00 $0.50 $40.00 $4.00 $4.00 $3.00 $10.00 Unit gallon pound pound pound pound gallon pound pound gallon pound pound gallon pound dozen pound pint gallon pound gallon pound pound pound pound pound pound pound pound quart gallon Vendor # Vendor Name V3242 Mountain Dairy Company V2352 Milsap Foods V2352 Milsap Foods V3242 Mountain Dairy Company V4516 Coastal Farms V2352 Milsap Foods V2828 Boulder Spice Importers V2828 Boulder Spice Importers V2352 Milsap Foods V2352 Milsap Foods V2352 Milsap Foods V3242 Mountain Dairy Company V3242 Mountain Dairy Company | v3242 Mountain Dairy Company V2352 Milsap Foods V2828 Boulder Spice Importers V4516 Coastal Farms V4516 Coastal Farms V3242 Mountain Dairy Company V2352 Milsap Foods V2828 Boulder Spice Importers V4516 Coastal Farms V2352 Milsap Foods V2352 Milsap Foods V2352 Milsap Foods V3242 Mountain Dairy Company V2352 Milsap Foods V2828 Boulder Spice Importers V3242 Mountain Dairy Company V2352 Milsap Foods 14897 Joe's Organic Supplies 14897 Joe's Organic Supplies pound pound State Zip CO CO CO CORPORATE CUSTOMER LIST Customer Customer # since Name C1001 6/14/10 Alpine Cupcakes Storefront C1106 9/18/18 Bon Appetito Restaurants C1031 3/23/16 Boulder Tea house C1062 5/9/17 Broken Eggs Restaurant C1147 2/9/20 Brown's Tavem C1111 12/17/18 Bubba's Fish House C1071 7/20/17 Buckhead Restaurants C1057 2/16/17 Chavez Cantina C1016 9/23/15 Country Barrel Restaurants C1083 11/6/17 Denver Bakery Caf C1076 8/12/17 Denver Sirloin Restaurants C1096 5/22/18 Elkhom Coffee Shops C1052 11/27/16 Fontana Catering & Caf C1105 6/10/18 Granny's Caf C1089 5/7/18 High Country Coffee C1002 5/1/15 Julie's Wraps C1084 12/30/17 Little's Grill C1021 11/12/15 Luigi's Bistro C1044 9/27/16 Mile High Steakhouses C1086 2/8/18 Mountain Lion Restaurant C1114 3/29/19 Mountain Trout Fish House C1043 9/6/16 Nora's Caf C1053 12/16/16 Papa's Restaurant C1137 10/15/19 Pebbles Inn C1113 2/26/19 Scotty's Taverns C1122 5/13/19 Smokey's Barbeque Pit C1115 5/10/19 Southside Caf C1128 7/12/19 St. Francis Hotel and Spa C1036 6/27/16 Steinberg Delis C1138 11/13/19 The Breakfast Place C1045 10/11/16 The Sandwich Place C1027 2/13/16 UC Boulder Food Service C1011 8/3/15 UC Denver Food Service Address 1000 16th Street 4990 Washington 1700 13th St 2400 S. Parker Road 950 Lincoln St 5050 E 28th St 2700 Welton Street 2351 Champak Street 1400 Poplar Street 321 17th Street 320 E Colfax Avenue 400 S Broadway 1776 5th St 4902 E. Colfax Avenue 3900 W 32nd Street 1300 S Clarkston 3600 S Monaco Parkway 1500 Arapahoe St 1730 19th Street 37000 Boulder Canyon Dr 640 30th Street 1700 Federal Blvd 2300 S. Colorado Blvd 8000 Foothills Highway 600 Sherman St 7700 E Hampden Ave 670 S. Broadway 800 Walnut Blvd 1700 Broadway 8110 Powhaton Mile Road 1741 Broadway 1669 Euclid Ave 13001 E 17th Place City Denver Denver Boulder Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Boulder Boulder Denver Denver Boulder Denver Denver Boulder Boulder Denver Denver Denver Boulder Aurora 8 8899 ooooo 8888888888888 Phone Number 80202 (702) 555-5013 80202 (303) 555-9104 80302 (720) 555-3138 80231 (720) 555-1078 80203 (303) 555-2791 80207 (720) 555-4051 80205 (720) 555-2106 80222 (303) 555-9886 80220 (303) 555-5341 80202 (720) 555-1747 80203 (720) 555-6912 80209 (720) 555-1185 80204 (720) 555-4668 80220 (702) 555-0238 80212 (303) 555-3833 80210 (720) 555-8995 80237 (720) 555-8253 80202 (702) 555-5959 80202 (303) 555-5325 80302 (303) 555-2662 80303 (303) 555-2233 80204 (303) 555-1772 80222 (720) 555-5965 80302 (720) 555-2618 80203 (720) 555-3424 80113 (720) 555-7649 80305 (720) 555-7312 80302 (702) 555-5402 80290 (303) 555-4454 80249 (702) 555-9820 80210 (303) 555-6368 80309 (720) 555-2035 80045 (303) 555-4906 CO CO CO CO co co CO CO co A B C 5 MN Risk Assessment: Question 4 Analyze the risk associated with customers a) Create a visualization (i.e., chart, graph, table or some other visualization) showing the risk associated with customers. In otherwords, are there any customers that Alpine is particularly reliant upon so the loss of their business would constitute a risk to the continuation of Alpine's Business? Use Sales data from 2022. b) How would you advise Alpine Cupcakes after seeing your visualization about vulnerabilities associated with customers? Hint: Use data from "Sales Orders" worksheet and the "Customer List." You might need to use VLOOKUP to combine the data. Ask if it is better to use number of cupcakes sold? Dollar amount sold? Some other number? Once you have assembled your data, experiment with visualization techniques, tables? Pivot tables? Charts? Graphs? or some other technique? Alpine Cupcakes Recipes Size Purchased in purchased (for 36 cupcakes) Carrot Cake Cupcakes (makes 36) Eggs Veg. Oil Sugar Vanilla Flour Baking soda Baking powder Salt Cinnamon Carrots Pecans 8.00 eggs 2.50 cups 4.00 cups 4.00 tsp 4.00 Cups 4.00 tsp 4.00 tsp 1.00 tsp 4.00 tsp 3.00 pound 2.00 cups dozen gallons pounds quart pounds pounds pounds pounds pounds pounds pounds 0.666666667 i.e., we need .6666 of a dozen to make 36 cupcakes 0.1575 1.764 0.0200 1.1 0.0400 0.0320 0.0130 0.0520 16.0000 0.63 Cr. Cheese Frosting/Carrot Cake (36) Butter 0.5 Cream cheese Confect. Sugar Vanilla Pecans 1.00 cup 16.00 oz 8.00 cup 2.00 tsp 2.00 cup pounds pounds pounds quart pounds 1.008 2.112 0.0100 0.63 Gluten free Chocolate Cupcakes (36) Rice flour Millet flour Cocoa Salt Baking soda Baking powder Xanthan gum Eggs Sugar Sour cream Milk Vanilla 2.25 cups 1.13 cups 0.75 cups 1.50 tsp 1.50 tsp 1.50 tbl 1.50 tsp 6.00 eggs 1.88 cups 1.00 cups 1.50 cups 3.00 tsp pounds pounds pounds pounds pounds pounds pounds dozen pounds pounds gallons quart 0.783 0.253125 0.14 0.0195 0.0150 0.0120 0.0014 0.5 0.826875 0.507 0.0945 0.0150 1 Gluten Free Chocolate Frosting (36) Butter Confect. Sugar Cocoa Vanilla Milk pounds pounds 2.00 cups 9.00 cups 2.50 cups 0.40 tsp 1.00 cups pounds quart gallons 2.376 0.48 0.0020 0.063 Red Velvet Cupcakes (36) Flour Cocoa Baking soda Salt Butter Sugar Eggs Sour cream Milk Food color Vanilla 3.75 cups 0.75 cups 1.50 tsp 0.75 tsp 1.50 cups 3.00 cups 6.00 eggs 1.50 cups 0.75 cups 1.50 oz 3.00 tsp pounds pounds pounds pounds pounds pounds dozen pounds gallons pints quart 1.03125 0.14 0.0150 0.0098 0.75 1.323 0.5 0.7605 0.04725 0.0945 0.0150 0.756 0.1875 Vanilla Cream Cheese Frosting/ red velvet cupcakes (36) Cream cheese Butter Sour cream Vanilla Confect. Sugar 12.00 oz 0.38 cup 3.00 tbl 3.00 tsp 24.00 oz pounds pounds pounds quart pounds 1.521 0.0150 6.336 Vanilla Cupcakes (36) Butter Sugar Flour Salt Eggs Vanilla Baking powder 2.00 cups 2.25 cups 4.50 cups 0.38 tsp 9.00 eggs 3.00 tsp 1.50 tsp pounds pounds pounds pounds dozen quart pounds 0.99225 1.2375 0.0049 0.75 0.0150 0.0195 Butter Cream Frosting (36) Butter Shortening Salt Vanilla Confec. Sugar Milk 1.50 cups 1.50 cups 0.38 tsp 4.50 tsp 15.00 cups 0.75 cups pounds pounds pounds quart pounds gallons 0.75 0.678 0.0049 0.0225 3.96 0.04725 Lemon Cupcakes (36) Flour Salt Butter Sugar Eggs Vanilla Lemon zest Milk Lemon juice Baking powder 3.60 cups 0.70 tsp 1.20 cups 2.40 cups 5.00 eggs 1.20 tsp 2.40 tbl 1.20 cups 3.00 tbl 1.50 tsp pounds pounds pounds pounds dozen quart pounds (lemon gallons gallon pounds 0.99 0.0091 0.6 1.0584 0.416666667 0.0060 0.0880 0.0756 0.012 0.0195 gallon Lemon Cream Icing (36) Cream Confec. Sugar Lemon juice 2.40 cups 0.90 cups 1.80 tbl pounds 0.1512 0.2376 0.0072 gallon Chocolate cupcakes (36) Flour Baking soda Baking powder Cocoa Salt Butter Sugar Eggs Vanilla Milk 3.00 cups 0.56 tsp 4.50 tsp 1.69 cups 0.28 tsp 6.75 tbl 3.38 cups 5.00 eggs 1.69 tsp 2.25 cups pounds pounds pounds pounds pounds pounds pounds dozen quart gallons 0.825 0.0056 0.0360 0.32 0.0037 0.20925 1.488375 0.416666667 0.0084 0.14175 Alpine Cupcakes Ingredient Price List, by Vendor Bakery Price List Almond milk Baking powder Baking soda Butter Carrots Cider vinegar Cinnamon Cocoa Coconut oil Confec. Sugar Cornstarch Cream Cream cheese Eggs Flour Food color Lemon juice Lemon zest Milk Millet flour Nutmeg Pecans Rice flour Salt Shortening Sour cream Sugar Vanilla Vanilla soymilk Veg. Oil Vegan butter Xanthan gum Pr. Per $4.00 $3.00 $1.40 $2.00 $2.00 $3.00 $9.00 $2.80 $20.00 $0.56 $4.00 $10.00 $5.00 $1.50 $0.30 $8.00 $10.00 $1.50 $3.00 $3.00 $22.00 $5.00 $1.20 $0.20 $0.55 $2.00 $0.50 $40.00 $4.00 $4.00 $3.00 $10.00 Unit gallon pound pound pound pound gallon pound pound gallon pound pound gallon pound dozen pound pint gallon pound gallon pound pound pound pound pound pound pound pound quart gallon Vendor # Vendor Name V3242 Mountain Dairy Company V2352 Milsap Foods V2352 Milsap Foods V3242 Mountain Dairy Company V4516 Coastal Farms V2352 Milsap Foods V2828 Boulder Spice Importers V2828 Boulder Spice Importers V2352 Milsap Foods V2352 Milsap Foods V2352 Milsap Foods V3242 Mountain Dairy Company V3242 Mountain Dairy Company | v3242 Mountain Dairy Company V2352 Milsap Foods V2828 Boulder Spice Importers V4516 Coastal Farms V4516 Coastal Farms V3242 Mountain Dairy Company V2352 Milsap Foods V2828 Boulder Spice Importers V4516 Coastal Farms V2352 Milsap Foods V2352 Milsap Foods V2352 Milsap Foods V3242 Mountain Dairy Company V2352 Milsap Foods V2828 Boulder Spice Importers V3242 Mountain Dairy Company V2352 Milsap Foods 14897 Joe's Organic Supplies 14897 Joe's Organic Supplies pound pound State Zip CO CO CO CORPORATE CUSTOMER LIST Customer Customer # since Name C1001 6/14/10 Alpine Cupcakes Storefront C1106 9/18/18 Bon Appetito Restaurants C1031 3/23/16 Boulder Tea house C1062 5/9/17 Broken Eggs Restaurant C1147 2/9/20 Brown's Tavem C1111 12/17/18 Bubba's Fish House C1071 7/20/17 Buckhead Restaurants C1057 2/16/17 Chavez Cantina C1016 9/23/15 Country Barrel Restaurants C1083 11/6/17 Denver Bakery Caf C1076 8/12/17 Denver Sirloin Restaurants C1096 5/22/18 Elkhom Coffee Shops C1052 11/27/16 Fontana Catering & Caf C1105 6/10/18 Granny's Caf C1089 5/7/18 High Country Coffee C1002 5/1/15 Julie's Wraps C1084 12/30/17 Little's Grill C1021 11/12/15 Luigi's Bistro C1044 9/27/16 Mile High Steakhouses C1086 2/8/18 Mountain Lion Restaurant C1114 3/29/19 Mountain Trout Fish House C1043 9/6/16 Nora's Caf C1053 12/16/16 Papa's Restaurant C1137 10/15/19 Pebbles Inn C1113 2/26/19 Scotty's Taverns C1122 5/13/19 Smokey's Barbeque Pit C1115 5/10/19 Southside Caf C1128 7/12/19 St. Francis Hotel and Spa C1036 6/27/16 Steinberg Delis C1138 11/13/19 The Breakfast Place C1045 10/11/16 The Sandwich Place C1027 2/13/16 UC Boulder Food Service C1011 8/3/15 UC Denver Food Service Address 1000 16th Street 4990 Washington 1700 13th St 2400 S. Parker Road 950 Lincoln St 5050 E 28th St 2700 Welton Street 2351 Champak Street 1400 Poplar Street 321 17th Street 320 E Colfax Avenue 400 S Broadway 1776 5th St 4902 E. Colfax Avenue 3900 W 32nd Street 1300 S Clarkston 3600 S Monaco Parkway 1500 Arapahoe St 1730 19th Street 37000 Boulder Canyon Dr 640 30th Street 1700 Federal Blvd 2300 S. Colorado Blvd 8000 Foothills Highway 600 Sherman St 7700 E Hampden Ave 670 S. Broadway 800 Walnut Blvd 1700 Broadway 8110 Powhaton Mile Road 1741 Broadway 1669 Euclid Ave 13001 E 17th Place City Denver Denver Boulder Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Denver Boulder Boulder Denver Denver Boulder Denver Denver Boulder Boulder Denver Denver Denver Boulder Aurora 8 8899 ooooo 8888888888888 Phone Number 80202 (702) 555-5013 80202 (303) 555-9104 80302 (720) 555-3138 80231 (720) 555-1078 80203 (303) 555-2791 80207 (720) 555-4051 80205 (720) 555-2106 80222 (303) 555-9886 80220 (303) 555-5341 80202 (720) 555-1747 80203 (720) 555-6912 80209 (720) 555-1185 80204 (720) 555-4668 80220 (702) 555-0238 80212 (303) 555-3833 80210 (720) 555-8995 80237 (720) 555-8253 80202 (702) 555-5959 80202 (303) 555-5325 80302 (303) 555-2662 80303 (303) 555-2233 80204 (303) 555-1772 80222 (720) 555-5965 80302 (720) 555-2618 80203 (720) 555-3424 80113 (720) 555-7649 80305 (720) 555-7312 80302 (702) 555-5402 80290 (303) 555-4454 80249 (702) 555-9820 80210 (303) 555-6368 80309 (720) 555-2035 80045 (303) 555-4906 CO CO CO CO co co CO CO co A B C 5 MN Risk Assessment: Question 4 Analyze the risk associated with customers a) Create a visualization (i.e., chart, graph, table or some other visualization) showing the risk associated with customers. In otherwords, are there any customers that Alpine is particularly reliant upon so the loss of their business would constitute a risk to the continuation of Alpine's Business? Use Sales data from 2022. b) How would you advise Alpine Cupcakes after seeing your visualization about vulnerabilities associated with customers? Hint: Use data from "Sales Orders" worksheet and the "Customer List." You might need to use VLOOKUP to combine the data. Ask if it is better to use number of cupcakes sold? Dollar amount sold? Some other number? Once you have assembled your data, experiment with visualization techniques, tables? Pivot tables? Charts? Graphs? or some other technique
