Formula for flexible budget variance

Flexible Budgeting And Variance Analysis

Pr. 22(7)-2A
Problem 22(7)-2A
Name: 0
Section: # N-box Incorrects due to blanks COUNTIF(B15:AT24," ")
74
Score: 0% # N-box +B-box corrects COUNTIF(B15:AT24," ")
0
Key Code: [Key code here] Total SUM(AD13:AD15)
Instructions 74
Answers are entered in the cells with gray backgrounds. Percentage =(AD16-AD13-AD14)/AD16
Cells with non-gray backgrounds are protected and cannot be edited. 0%
An asterisk (*) will appear to the right of an incorrect entry. The essay answer will not be graded. Notes:
If number-entry box is blank (this would be an incorrect answer for N-boxes), error check returns two spaces, " "
If number-entry or blank-entry box is incorrect, returns "*"
1. a. If number-entry or blank-entry box is correct, returns single space, " "
Direct Materials Variance Cocoa Sugar Total Use data verification to set data entry to whole number >= 0, and use drop-downs for lables and names, so that students can't enter a space in a box and have it counted as correct.
Price variance: Conditional formatting might be used but wasn't here, to hide some of the error check return symbols. If A1 = "~*", then font = red, if something else, then font = background color.
Actual price
Standard price
Variance
Actual quantity (lbs.)
Direct materials price variance¾(favorable) unfavorable
Quantity variance:
Actual quantity used (lbs.)
Standard quantity used (lbs.)
Mark Sears: Complete the supporting calculation below and enter the amount here.
Mark Sears: Complete the supporting calculation below and enter the amount here.
Variance (lbs.)
Standard price
Direct materials quantity variance¾(favorable) unfavorable
Total direct materials cost variance¾(favorable) unfavorable
Total direct materials cost variance:
Actual cost
Mark Sears: Enter a formula of actual quantity x actual price.
Mark Sears: Enter a formula of actual quantity x actual price.
Standard cost
Mark Sears: Enter a formula of standard quantity x standard price.
Mark Sears: Enter a formula of standard quantity x standard price.
Total direct materials cost variance¾(favorable) unfavorable
Supporting calculations:
Standard quantity used of cocoa: Dark Chocolate Light Chocolate Total
Cases produced
Standard amount per case (lbs.)
Total
Standard quantity used of sugar:
Cases produced
Standard amount per case (lbs.)
Total
b.
Direct Labor Variance Dark Chocolate Light Chocolate Total
Rate variance:
Actual rate
Standard rate
Variance
Actual time
Direct labor rate variance¾(favorable) unfavorable
Time variance
Actual tiime
Standard time
Mark Sears: Enter a formula of standard time per case x actual cases produced.
Mark Sears: Enter a formula of standard time per case x actual cases produced.
Variance
Standard rate
Direct labor time variance¾(favorable) unfavorable
Total direct labor cost variance¾(favorable) unfavorable
Total direct labor cost variance:
Actual cost
Mark Sears: Enter a formula of actual hours x actual rate.
Mark Sears: Enter a formula of actual hours x actual rate.
Standard cost
Mark Sears: Enter a formula of standard hours x standard rate.
Mark Sears: Enter a formula of standard hours x standard rate.
Total direct labor cost variance¾(favorable) unfavorable
2.
[Key essay answer here]
Sol
Problem 22(7)-2A
Name: Solution
Section:
Score: ON
Instructions
Answers are entered in the cells with gray backgrounds.
Cells with non-gray backgrounds are protected and cannot be edited.
An asterisk (*) will appear to the right of an incorrect entry. The essay answer will not be graded.
1. a.
Direct Materials Variance Cocoa Sugar Total
Price variance:
Actual price $ 7.33 $ 1.35
Standard price 7.25 1 2/5
Variance $ 0.08 $ (0.05)
Actual quantity (lbs.) 140,300 188,000
Direct materials price variance¾(favorable) unfavorable $ 11,224 $ (9,400) $ 1,824
Quantity variance:
Actual quantity used (lbs.) 140,300 188,000
Standard quantity used (lbs.) 140,000
Mark Sears: Complete the supporting calculation below and enter the amount here. 190,000
Mark Sears: Complete the supporting calculation below and enter the amount here.
Variance (lbs.) 300 (2,000)
Standard price $7.25 $1.40
Direct materials quantity variance¾(favorable) unfavorable $ 2,175 $ (2,800) (625)
Total direct materials cost variance¾(favorable) unfavorable $ 1,199
Total direct materials cost variance:
Actual cost $ 1,028,399
Mark Sears: Enter a formula of actual quantity x actual price. $ 253,800
Mark Sears: Enter a formula of actual quantity x actual price.
Standard cost 1,015,000
Mark Sears: Enter a formula of standard quantity x standard price. 266,000
Mark Sears: Enter a formula of standard quantity x standard price.
Total direct materials cost variance¾(favorable) unfavorable $ 13,399 $ (12,200) $ 1,199
Supporting calculations:
Standard quantity used of cocoa: Dark Chocolate Light Chocolate Total
Cases produced 5,000 10,000
Standard amount per case (lbs.) 12 8
Total 60,000 80,000 140,000
Standard quantity used of sugar:
Cases produced 5,000 10,000
Standard amount per case (lbs.) 10 14
Total 50,000 140,000 190,000
b.
Direct Labor Variance Dark Chocolate Light Chocolate Total
Rate variance:
Actual rate $ 15.25 $ 15.80
Standard rate 15.50 15.50
Variance $ (0.25) $ 0.30
Actual time 2,360 6,120
Direct labor rate variance¾(favorable) unfavorable $ (590) $ 1,836 $ 1,246
Time variance
Actual tiime 2,360 6,120
Standard time 2,500
Mark Sears: Enter a formula of standard time per case x actual cases produced. 6,000
Mark Sears: Enter a formula of standard time per case x actual cases produced.
Variance (140) 120
Standard rate $15.50 $15.50
Direct labor time variance¾(favorable) unfavorable $ (2,170) $ 1,860 (310)
Total direct labor cost variance¾(favorable) unfavorable $ 936
Total direct labor cost variance:
Actual cost $ 35,990
Mark Sears: Enter a formula of actual hours x actual rate. $ 96,696
Mark Sears: Enter a formula of actual hours x actual rate.
Standard cost 38,750
Mark Sears: Enter a formula of standard hours x standard rate. 93,000
Mark Sears: Enter a formula of standard hours x standard rate.
Total direct labor cost variance¾(favorable) unfavorable $ (2,760) $ 3,696 $ 936
2.
The variance analyses should be based on the standard amounts at actual volumes. The budget must flex with the volume changes. If the actual volume is different from the planned volume, as it was in this case, then the budget used for performance evaluation should reflect the amount of direct materials and direct labor that will be required for the actual production. In this way, spending from volume changes can be separated from efficiency and price variances.