ACC 321 Variance Project Spring 2021 35 Points Part 1: Calculate and

ACC 321

Variance Project

Spring 2021

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 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. (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.

Actual Data

Month

Bottles Produced

Price Per Ounce

Ounces Used

January

126,000

0.2428

511,560

February

120,000

0.2431

486,960

March

112,000

0.2428

454,272

April

90,000

0.2525

374,400

May

73,000

0.2527

277,400

June

65,000

0.2688

263,770

July

58,000

0.243

235,480

August

62,000

0.243

251,596

September

75,000

0.2325

307,500

October

86,000

0.2323

354,320

November

102,000

0.2431

418,200

December

118,000

0.243

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

Month

Total Variance

% Deviation

Total Variance

Price Variance

% Deviation Price Variance

Usage Variance

% Deviation Usage Variance

January

$3,246.77 U

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.00 U

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 value the variance is unfavorable and when a ‘F’ follows the table value the variance is favorable.

For all calculations below, a positive value indicates an Unfavorable variance (Actual > Budget) and a negative value indicates a Favorable variance (Actual < Budget)

Total Variance = Total Materials Variance (Actual Materials Cost – Flexible Budget for Materials)

% Deviation Total Variance = The percentage that actual materials cost deviates from flexible budget [(Total Variance) / (Flexible Budget Materials Cost)]

Price Variance = The difference between actual price paid and the budgeted price for an ounce of materials used: (Actual Materials Price – Standard Price) * Actual Materials Used

% Deviation Price Variance = The percentage that the actual materials cost deviates from budget: [(Price Variance) / (Actual Materials Used * Standard Price)]

Usage Variance = The difference between the actual quantity of materials used and the standard quantity of materials allowed for the number of units produced [(Actual Materials Used – (Budgeted Usage per unit of output * Number of Units Produced)]

% Deviation Usage Variance = the percentage that materials usage deviated from budget: [(Usage Variance / (Budgeted Usage per unit of output * Number of Units Produced)]

When you were conducting investigations, you also learned that the company had a number of events that could have had implications for the variances. You also recall from your Cost Accounting class that some variances can’t be controlled while others can be controlled. You found your old book and used this table to help with your final recommendations:

Because the company had an eventful year as far as materials were concerned, you organized the chart of events so that you could determine how to address the various events.

Significant Events

Month

Events

For each of the events, you decided to indicate whether and how the event would impact the Materials Price or Materials Usage Variance by placing an ‘X’ in the appropriate bracketed area. If you determined the event would have affected the variance(s), you also noted whether corrective action could be taken. You knew this would be a great help when preparing your presentation. Finally, to keep it all straight, you added the last column to show which months were affected by the event. You need to be careful so that it is clear which event you are referring to when the month has multiple events!

Affect the Price Variance?

Affect Usage Variance?

Is There a Corrective Action?

Indicate the Months Affected

January

The company predicted that the price of materials would increase so the purchasing manager decided to buy several months of materials before the price increase.

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Yes]

[No]

February

Employees were paid their annual bonus in February.

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Yes]

[No]

March

A batch of supplies were received that were of slightly lower quality. The production department was able to use them slowly along with good supplies so that the company did not need to write off the inventory or make returns.

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Yes]

[No]

April

In April the clerk in accounting made an input error and as a result records showed that that more material than actual, was added to the production process.

Supplies are usually delivered by rail but there was a shutdown in the railroad and now supplies must come by truck for 2 months, starting in April, which is more expensive.

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Yes]

[No]

[Yes]

[No]

May

The input error from April reversed.

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Yes]

[No]

June

Our materials supplier, which was our only materials supplier, had a temporary outage that lasted through June and at the last minute we had to find a temporary (emergency) supplier. Since it was last minute, the supplier charged premium pricing and had to expedite shipping.

A major equipment purchase was planned for June but was delayed until August.

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Yes]

[No]

[Yes]

[No]

July

Many of our employees took vacation to enjoy summer activities.

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Yes]

[No]

August

Our supplier informed purchasing that they were going to halt production for maintenance and requested that our company take a two-month supply of materials now.

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Yes]

[No]

September

Union negotiations were in full swing and the production manager noted that employees seemed a bit disgruntled. He also noticed that they were taking more ‘personal’ breaks and seemed less conscientious about the quality of their work. The production manager also said that it seemed like there was more waste.

Material purchase price for the containers is based on a contract tied to the price of oil. Oil prices declined during the months of September and October.

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Yes]

[No]

[Yes]

[No]

October

OPEC is meeting this month to negotiate supply levels of oil for the following year.

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Yes]

[No]

November

Toward the end of November, due to the deteriorating efficiency of the production machinery it was decided to take a plant outage and accelerate the maintenance originally scheduled for March of the following year.

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Yes]

[No]

December

The plant started back up in early December and the maintenance performed in November appears to have resolved the efficiency issues.

[Favorable]

[Unfavorable]

[No Effect]

[Favorable]

[Unfavorable]

[No Effect]

[Yes]

[No]

To make sure you weren’t missing anything, you also kept track of your reasoning for determining that the event would not have affected either the materials price or usage variance.

Month

Events

You are completing the process and making sure your thoughts are in order. You have all of the events listed in the Events column but decided to fill in the [No Effect] area when the event would not have affected the price or the usage variance. You provided a third column to keep track of your logic when you determined that the event would not have affected the respective variance(s).

Affect the Price Variance?

Affect Usage Variance?

If you marked the ‘No Effect’ area, please explain why there is no effect

January

The company predicted that the price of materials would increase so the purchasing manager decided to buy several months of materials before the price increase.

[No Effect]

[No Effect]

February

Employees were paid their annual bonus in February.

[No Effect]

[No Effect]

March

A batch of supplies were received that were of slightly lower quality. The production department was able to use them slowly along with good supplies so that the company did not need to write off the inventory or make returns.

[No Effect]

[No Effect]

April

In April the clerk in accounting made an input error and as a result records showed that that more material than actual, was added to the production process.

Supplies are usually delivered by rail but there was a shutdown in the railroad and now supplies must come by truck for 2 months, starting in April, which is more expensive.

[No Effect]

[No Effect]

[No Effect]

[No Effect]

May

The input error from April reversed.

[No Effect]

[No Effect]

June

Our materials supplier, which was our only materials supplier, had a temporary outage that lasted through June and at the last minute we had to find a temporary (emergency) supplier. Since it was last minute, the supplier charged premium pricing and had to expedite shipping.

A major equipment purchase was planned for June but was delayed until August.

[No Effect]

[No Effect]

[No Effect]

[No Effect]

July

Many of our employees took vacation to enjoy summer activities.

[No Effect]

[No Effect]

August

Our supplier informed purchasing that they were going to halt production for maintenance and requested that our company take a two-month supply of materials now.

[No Effect]

[No Effect]

September

Union negotiations were in full swing and the production manager noted that employees seemed a bit disgruntled. He also noticed that they were taking more ‘personal’ breaks and seemed less conscientious about the quality of their work. The production manager also said that it seemed like there was more waste.

Material purchase price for the containers is based on a contract tied to the price of oil. Oil prices declined during the months of September and October.

[No Effect]

[No Effect]

[No Effect]

[No Effect]

October

OPEC is meeting this month to negotiate supply levels of oil for the following year.

[No Effect]

[No Effect]

November

Toward the end of November, due to the deteriorating efficiency of the production machinery it was decided to take a plant outage and accelerate the maintenance originally scheduled for March of the following year.

[No Effect]

[No Effect]

December

The plant started back up in early December and the maintenance performed in November appears to have resolved the efficiency issues.

[No Effect]

[No Effect]

Required:

Complete the tables above.

In the space provided below, briefly describe and number your recommendations. You may have noticed issues that are not directly related to the events included in the above tables.

You will need to present the information to management. You understand that management does not have a strong accounting background and doesn’t understand how variances are calculated. They are simply concerned about the pricing and usage of materials. You have less than 10 minutes to share your recommendations with management so, you have decided to:

Display the information graphically and limit the number of presentation slides to 3.

Provide a management-level overview of the variances to support your recommendations.

2

Part 2 Variance Analysis