ACC 3211 Variance Project 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 identify '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. (20 points) Part 1 - Calculate and Present Direct Material Variances 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 this analysis. 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. Materials Price Variances are calculated when the materials are used in production as opposed to when purchased. Month January February March April May June July 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 Required: Using the information you gathered and in Excel, calculate for all months the (1) Materials Price Variance (2) Materials Usage Variance (3) Total Materials Variance and (4) the percentage over or under flexible budget for each of the monthly variances (Variance/Flexible Budget Amount for that item). Also show a final row to display the annual variance. (Hint: you can sum the monthly variances, but not the percent deviation from flexible budget.) Clearly label your output so that any reader can identify the following components without a secret decoder ring: 1. Flexible Budget for Materials, total dollar amount 2. Materials Price Variance in dollars, whether the variance is Favorable or Unfavorable, the % deviation from flexible budget [variance / (standard price * actual quantity used)) 3. Materials Usage Variance in dollars, whether the variance is Favorable or Unfavorable, the % deviation from flexible budget [variance / (standard quantity allowed * standard price)] 4. Total Materials Variance in dollars, whether the variance is Favorable or Unfavorable, the % deviation from flexible budget (variance / flexible budget amount]
