Material and Labor Variances:

Background Information: Original Question & Solution (with excel formulas):

Enter the input values or formulas to compute input values. Enter formulas where indicated on the worksheet. Formulas 8-10 should enter a F is variance is favorable or U if variance is unfavorable.

Answer:

The question/part I need help with:

Apparently, this is the correct answer below, but I need help seeing what formulas and calculations were used in excel. Thanks.

Apparently, this is the correct answer, but I need help seeing what formulas and calculations were used in excel. Thanks.

PROBLEM DATA McGrade Industries manufactures a product whose standard direct materials and direct labor unit costs are as follows: Direct materials: 5 pounds at $16 per pound $80 Direct labor: 6 hours at $8 per hour 48 Actual data for October: Actual production: 7,600 units Direct materials used in production: 39,500 pounds costing $628,050 Direct labor: 44,000 hours costing $354,200 A B D E $15.90 $16.00 39,500 38,000 $8.05 $8.00 1 2 PRIMEVAR 3 Material and Labor Variances 4 5 Data Section 6 7 ariance input 8 laterial per pound (ounce, etc.) 9 of mat. per pound (ounce, etc.) 10 11 y variance input 12 of materials used 13 city of mat. used for actual output 14 15 ce input 16 bor per hour of labor per hour 18 19 variance input 20 of hours incurred 21 ity of hours for actual output 22 23 Answer Section 24 25 26 riance ($3,950) 27 variance 24,000 28 29 ariance 30 31 ce $2,200 32 variance (12,800) 33 34 nce 35 36 nd labor variances 37 38 44,000 45,600 For U C $20,050 U U F (10,600) F $9,450 U D B PRIMEVAR Material and Labor Variances =628050/39500 16 39500 =76005 =354200/44000 =8 A 2 3 4 5 Data Section 6 7 Materials price variance input 8 Actual cost of material per pound (ounce, etc.) 9 Standard cost of mat. per pound (ounce, etc.) 10 11 Materials quantity variance input 12 Actual quantity of materials used 13 Standard quantity of mat. used for actual output 14 15 Labor rate variance input 16 Actual cost of labor per hour 17 Standard cost of labor per hour 18 19 Labor efficiency variance input 20 Actual quantity of hours incurred 21 Standard quantity of hours for actual output 22 23 Answer Section 24 25 26 Material price variance 27 Material quantity variance 28 29 Total materials variance 30 31 Labor rate variance 32 Labor efficiency variance 33 34 Total labor variance 35 36 Total materials and labor variances 37 38 44000 =7600*6 =(D8-D9)*D12 =(D12-D13)*D9 For U =IF(B26>0,"U","F") =IF(B27>0,"U","F") =B26+B27 =IF(C29>0,"U", "F") =(D16-D17) D20 =(D20-D21)-D17 =IF(B31>0," =IF(B32>0," U",IF(B31=0," U",IF(B32=0," =B31+B32 =IF(C34>0," U",IF(C34=0," =C29+C34 =IF(C36>0," U",IF(C36=0," WHAT-IF ANALYSIS 4. The worksheet you have developed will handle most simple variance analysis problems. Try the problem below for Pscheidl, Inc.: Cost per Unit Standard Actual $12.50 $12.60 Direct materials: Standard: 1.25 pounds at $10 per pound Actual: 1.20 pounds at $10.50 per pound Direct labor: Standard: 3.5 hours at $6.00 per hour Actual: 4 hours at $5.85 per hour 21.00 23.40 Actual production for October was 11,500 units. Compute the direct materials and direct labor variances for Pscheidl, Inc. Be careful when entering your input because this problem presents the information in a different format from the McGrade Industries' data. Save the file as PRIMEVAR4. Print the worksheet when done. Using the worksheet with the formulas already entered, change the data to reflect information about the new product for Company P. Once these changes have been made for the new product, the materials and variances worksheet should look like this: Data Materials price variance input Actual cost of material per pound (ounce, etc.) Standard cost of mat. per pound ounce, et.) $10.50 $10.00 Materials quantity variance input Actual quantity of materials used Standard qantity of mat. used for actual output 13,800 14,375 $5.85 $6.00 Laborrate variance input Actual cost of laborper hour Standard cost of labor per har Laboreficiency variance input Actual quantity of hours incumed Standard qantity of hours for actual output 46,000 40.250 Answer Foru Material price Kariance Material quantity wariance $6,900 ($5,750) c Total materials variance $1,150 U Laborrate variance Labor efficiency variance ($6,900) 34,500 c Total labor mariance 27,600 U Total materials and tabor variances $28,750 U