ACC 321 Variance 35 Points Part 1: Calculate and present monthly material variances for a 12-month period using Excel (10 points) Part 2: Contextual information along with the variances will be provided and you will be asked to identity 'causes for the variances and any noticeable trends. As a component of this assignment, you will also be asked to organize the variance data in a format that will be understandable to upper management. (25 points) Part 2 - Identify Potential Causes for Materials Variances and Offer Solutions Background: Specialty Products produces items that are of interest to a more upscale market. Their products include scented soaps, shampoos, and balms. In response to recent events, the company now produces hand sanitizer with an infusion of essential oils. Although hand sanitizer is similar to some of their other products, this was a new venture and management did not have the requisite experience with pricing. In preparation for next year's budgeting process, management would like to evaluate last year's budgeted materials costs to estimate future costs more accurately and to identify actions the company can take to minimize unexpected costs. Management is also considering whether the frequency of the standard-setting process is appropriate and has turned to you for analysis and recommendations. After sifting through the accounting records and working with various individuals in purchasing and on the production floor, you came up with budgeted and actual materials information. Budgeted costs were based on the following: Standard Price for an ounce of hand sanitizer is 0.24 and Standard Quantity is 4 ounces for a bottle of sanitizer. It also appears that this standard combines all materials - the alcohol, aloe, essential oils, and the container. er movie Month January February March April May June uly August September October November December Actual Data Bottles Produced Price Per Ounce 126,000 0.2428 120,000 0.2431 112,000 0.2428 90,000 0.2525 73,000 0.2527 65,000 0.2688 58,000 0.243 62,000 0.243 75,000 0.2325 86,000 0.2323 102,000 0.2431 118,000 0.243 Ounces Used 511,560 486,960 454,272 374,400 277,400 263,770 235,480 251,596 307,500 354,320 418,200 462,560 You put the information together and calculated the variances and the percentage of their deviation(s) from flexible budget. You became a bit concerned because management does not have a high tolerance for variances. The general rule is that the usage and price variance should not deviate from budget by more than 1%. You included notes so that you could recall your calculations. Variances and Deviations from Flexible Budget % Total Deviation Month Price Deviation Usage Deviation Variance Total Variance Price Variance Usage Variance Variance Variance January $3,246.77U 2.6842% $1,432.37 U 1.1667% $1,814.40 U 1.5000% February $3,179.98 U 2.7604% $1,509.58 U 1.2917% $1,670,40 U 1.4500% March $2,777.24 U 2.5830% $1,271.96 U 1.1667% $1,505.28 U 1.4000% April $8,136.00 U 9.4167% $4,680.000 5.2083% $3,456.00 U 4.0000% May $18.98 U 0.0271% $3,522.98 U 5.2917% $3,504.00 F 5.0000% June $8,501.38 U 13.6240% $7,596.58 U 12.0000% $904.80 U 1.4500% July $1,541.64 U 2.7687% $706.44 U 1.2500% $835.20 U 1.5000% August $1,617.83 U 2.7181% $754.79 U 1.2500% $863.04 U 1.4500% September $506.25 F 0.7031% $2,306,25 F 3.1250% $1,800.00 U 2.5000% October $251.46 F 0.3046% $2,728.26 F 3.2083% $2,476.80 U 3.0000% November $3,744.42 U 3.8240% $1,296.42 U 1.2917% $2,448.00 U 2.5000% December $877.92 F 0.7750% $1,387.68 U 1.2500% $2,265,60 F 2.0000% Annual $31,128.60 U 2.9830% $19,124.28 U 1.8118% $12,004.32 U 1.1504% When a 'U' follows the table volue the variance is unfavorable and when a F follows the table value the variance is favorable. For all calculations below, a positive value indicates on Unfavorable variance (Actual > Budget) and a negative value indicates a favorable variance (Actual Budget) and a negative value indicates a favorable variance (Actual