A B C D E F 17 Case Assumptions 18 Vessel Glass Growler Make it a float 19 Ounces 14 64 -6 20 Sales Price $2.00 $6.00 21 22 Soda standard cost per ounce $0.016122 23 24 Conversion Chart Ounce Gallon Keg 25 Ounce 1 0.0078125 0.0015625 26 Gallon 128 1 0.2 27 Cornelius Keg 640 5 1 28 29 Inventory Values 30 Unit: Cornelius Keg 6/18/2018 7/16/2018 8/13/2018 31 Black Cherry Soda 12.2 12.8 15.8 32 Ginger Ale 10.8 13.8 11.1 33 Orange Cream Soda 8.9 17.2 10.6 34 Root Beer 16.5 9.4 13.3 35 Vanilla Cream Soda 13.2 15.9 12.8 36 37 Produced 6/18/2018 7/16/2018 38 to to 30 Unit: Cornelius Keg 7/15/2018 8/12/2018 40 Black Cherry Soda 30 35 41 Ginger Ale 30 25 42 Orange Cream Soda 35 25 43 Root Beer 55 70 44 Vanilla Cream Soda 45 40 45A B C D E F G H 46 Sales Report Data Glass Sales Growler Sales Make-it-a-Float Sales 47 6/18/2018 7/16/2018 6/18/2018 7/16/2018 6/18/2018 7/16/2018 48 to to to to to to 40 7/15/2018 8/12/2018 7/15/2018 8/12/2018 7/15/2018 8/12/2018 50 Black Cherry Soda 826 857 71 85 972 1015 51 Ginger Ale 685 705 67 70 52 Orange Cream Soda 750 818 68 86 53 Root Beer 1784 1852 171 190 Vanilla Cream Soda 1005 1013 117 127 55 56 Weekly Waste Logs 6/18/2018 6/25/2018 7/2/2018 7/9/2018 7/16/2018 7/23/2018 7/30/2018 8/6/2018 57 to to to to to to to to 58 Department 6/24/2018 7/1/2018 7/8/2018 7/15/2018 7/22/2018 7/29/2018 8/5/2018 8/12/2018 59 Kitchen (gallons) 4.9 3 2.1 4.1 3.3 5 2.2 4.3 50 Truck 3.1 5.6 5.1 7.8 3.6 7.9 9.1 4.8 61 Total 8 8.6 7.2 11.9 6.9 12.9 11.3 9.1 52 63 Question 1: Calculate the ACTUAL usage in Cornelius kegs and gallons from 6/18 to 7/15 64 Beginning 65 Unit: Cornelius Keg Produced Ending Actual Actual Inventory Inventory Gallons 56 Black Cherry Soda 12.2 30 42.2 67 Ginger Ale 10.8 30 58 Orange Cream Soda 8.9 35 59 Root Beer 16.5 55 70 Vanilla Cream Soda 13.2 45 71 Total 61.6 195A B C D E F G H 82 Question 2: Calculate the ounces and gallons SOLD from 6/18 to 7/15 83 Glasses Growlers Total Total (Ounces) (Ounces) (Ounces) (Gallons) 84 Black Cherry Soda 11564 4544 16108 125.84375 85 Ginger Ale 9590 4288 13878 108.421875 86 Orange Cream Soda 10500 4352 14852 116.03125 87 Root Beer 24976 10944 35920 280.625 98 Vanilla Cream Soda 14070 7488 21558 168.421875 89 Make it a float -5832 0 -5832 -45.5625 90 Total 64868 31616 96484 753.78125 100 101 Question 3: Determine the amount of soda that is unaccounted for from 6/18 to 7/15 102 Category Gallons 103 Total Actual Use 104 Recorded Soda Waste 105 Sold 106 Theoretical Total (waste + sold) 107 Unaccounted for soda 108 Unaccounted for soda % (divide by theo total) 109 110 Category Gallons 111 Total Actual Use 112 Recorded Soda Waste 113 Sold 114 Theoretical Total (waste + sold) 115 Unaccounted for soda 116 Unaccounted for soda % (divide by theo total) 117_l A I B I c I o I E F s H I 11? \"3 Question 4: Determine the unaccounted for soda variance for each soda from 6113 to 7115 119 Actual So Id Variance Vanan ce % 120 Black Cherry.r Soda 121 Ginger Ale '22 Orange Cream Soda '23 Root Beer .124 Vanilla Cream Soda '132 Question 5: The root cause of the unaccounted for soda is unclear. Calculate the impact to Stacy's Sodas and Sliders for three scenarios from 6J1 8 to \"15. In scenario 1, '33 assume that all of the unaccounted for soda is due to product related costs. In scenario 2, assume that half the unaccounted for soda is due to product related costs and the other half is due to uncollected revenue. In scenario 3, assume that all the unaccounted soda is due to uncollected revenue. 13-1 Scenario 1 Scenario 2 Scenario 3 '35 Unaccounted for soda to unces] I351 Portion resulting in lost revenue {%) 0.00% 50.00% 100.00% 13? Portion resulting in lost revenue {oz} 133 Portion resulting in lost revenue {glasses} 139 Lost Revenue 140 Portion not resulting in lost revenue (9%) 100% 50% 0% 141 Portion not resulting in lost revenue (oz) '42 Soda standard cost per ounce 143 Cost of portion not resulting in lost revenue 14-1 Total cost of unaccounted for soda' \"5 \" This is the expected increase in prot iithe unaccounted for soda variance was brought to zero. In cases where revenue is missing, revenue would he recorded. In cases where . revenue would not likely[ would have been generated, costs are brought to zero. 15" 153 159