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 decided to begin manufacturing the plastic containers used for its products. This was a new venture, and the management did not have the requisite experience with pricing. Further, there was some hesitation to undertake this project because crude oil underlies the pricing for some of the raw materials used to produce the containers and the company has limited experience for determining expected $$waste$$ for raw materials. Production workers are required to undergo training before beginning work. Part of this is a cost containment measure to reduce excessive waste as well as to enhance safety. 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.

Specialty Products uses a participative budgeting process and hence, when setting standards for the containers, the purchasing manager had significant input. Specialty Products also evaluates management personnel using a balanced scorecard for which one of the financial measures is whether the manager met the established standards.

Upper management is evaluating last year$$s performance and as part of that evaluation also considering whether the standards are appropriate. They have turned to you for 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 one unit of raw material is $0\mathrm{.}10$ and Standard Quantity is $4$ units for a container. Materials Price Variances are calculated when the materials are used in production as opposed to when purchased.

Actual Data

Month Containers Produced Actual Price Per Unit of Material Units of Material Used

January $85,0000\mathrm{.}0975345,270$

February $68,0000\mathrm{.}0972277,030$

March $59,0000\mathrm{.}0969244,025$

April $55,0000\mathrm{.}09875214,500$

May $62,5000\mathrm{.}09715254,000$

June $57,0000\mathrm{.}0989231,375$

July $60,5000\mathrm{.}09685246,780$

August $72,0000\mathrm{.}0973292,160$

September $66,0000\mathrm{.}095269,675$

October $62,0000\mathrm{.}0952251,285$

November $68,0000\mathrm{.}0973275,985$

December $81,0000\mathrm{.}0972329,670$

$$

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.$)$

You will need to calculate the following items as you complete the Excel Spreadsheet Columns identified the Requirements section:

$1.$ Flexible Budget for Materials, total dollar amount.

$2.$ Actual Cost for Materials, total dollar amount.

$3.$ Materials Price Variance $($a$)$ in dollars and indicate whether the variance is Favorable or Unfavorable, and $($b$)$ the $\%$ deviation from flexible budget

$[|$materials price variance$|/($standard price $*$ actual quantity used$)]$

$4.$ Materials Usage Variance $($a$)$ in dollars and indicate whether the variance is Favorable or Unfavorable, and $($b$)$ the $\%$ deviation from flexible budget

$[|$materials usage variance$|/($standard quantity allowed $*$ standard price$)]$

$5.$ Total Materials Variance $($a$)$ in dollars and indicate whether the variance is Favorable or Unfavorable, and $($b$)$ the $\%$ deviation from flexible budget

$[|$total materials variance$|/$ flexible budget for materials in dollars$]$