12. Variance Analysis Excel Ass lome Insert Page Layout Formulas Data Review View Times New Roman 12 ab wrao lext Copy e, Format Paste Blyv -A Merge & Center v This assignment is intended to help you practice doing variance analysis. Your responses in the yellow cells will be graded for participation. You can carm at most 6.5 points on this assignment. A company uses a standard cost system higher volume products is as follows: for production costing and control. The standard cost sheet for one of its Direct materials (7.6 lbs.@$4.40) Direct Labor (11.6 hrs. $21.60) Variable overhead (11.6 [email protected]) Fixed overhead (11.6 hrs.@$3.40) S 33.44 250.56 104.40 Standard unit cost S 427.84 During the year, the company had the following activity related to production: a. Production of 81,663 units. b. A total of 552,000 pounds of direct materials was purchased at $5.00 per pound. c. There were 71,000 pounds of direct materials in beginning inventory (carried at $4.40 per pound). There was no ending inventory d. The company used 948,000 direct labor hours at a total cost of $2,004,000. e. Actual fixed overhead totalled $180,000. f. Actual variable overhead totalled $146,000. 4 The company preduces all of this product in a single plant. Normal activity is 83,000 units per year. Standard overhead rates are computed based on normal activity measured in standard direct labor hours. Required 1. Compute the direct materials price and usage variances. 2. Compute the direct labor rate and efficiency variances. 3. Compute overhead variances using a two-variance analysis. 4. Compute overhead varainces using a four-variance analysis. 5. Prepare all possible journal entries (assuming a four-variance analysis of ove Sheeti Step 1: Identify relevant data you will need to solve this problem. SP Unit Cost DM DL VOH FOH 7.6 11.6 11.6 11.6 Standard unit cost AP for DM & DL Direct material Direct labor Ab. /hr. Total Direct Materials Available Actual Direct Materials (in pounds) Beginning Balance Purchased Total Direct Materials Available Actual Direct Labor (in hours) Hours Used 9 Unit Information 2 3 Budgeted Production (in units) Actual Production (in units) Actual Total Price for Overhead Actual Fixed Overhead Actual Variag le Overhead L' Times New Roman12 wras Copy Paste Merg- Format Actual Total Price for Overhead Actual Fixed Overhead Actual Variagle Overbead 56 Total Overhead 58 Step 2: Convert relevant information into SP, SQ, AP, and AQ. 70 AQ. 72 73 74 DM DL VOH 75 FOH 8 Step 3: Use data from step 2 to compute variances for requirements 1-4. 79 1) Direct Materials: If the actual quantity had not changed, then variances for direct materials v computed using the tree below. 81 3 Accounts Payabe Materials AQ x SP Work in Process x SP AQ x AP 85 Price Variance Usage . Variance Total Variance For the Price Variance, we use AQ-552.000, which is the pounds of direct materials purchased. Variance we use AQ 623,000, which is the pounds of direct materials used in production. Sheett 12. Variance Analysis Excel Assigna Home Insert Page Layout Formulas Data Review View bwrap Text Gener Cut Copy Format L' Times New Roman v 12.AA-- Blu- -A Merge & Center $v Paste 90 2 3 4 For the Price Variance, we use AQ -552,000, which is the pounds of direct materials purchased. For Usage Variance we use AQ 623,000, which is the pounds of direct materials used in production. When evaluating whether the variance is favorable or unfavorable, we judge a variance as favorable when the number in the box on the right is bigeer than the number in the box on the left. AQ x AP AQ x SP Price Variance SQ x SP AQx SP Usage Variance 2) Direct Labor: Variances for direct labor could be computed using the tree below. Wages Payable Work in Process AQAPAQxSP SQ x SP Rate Variance Efficiency Variance Total Variance Sheetl 12. Variance Analysis Excel Assign Home Insert Page Layout Formulas Data Review View Cut D Copyv Format Times New Roman 12 v A A eb wrap Text Genes Paste B 1 yv -v v Av Merge & Center v $ v 3) Total Overhead (ie. Two-Variance Analysis): Variances for total overhead could be computed using the tree below. Amounts in each box in the top row of the tree need to be computed separately for VOH and FOH 28 9 and then added together. Actual TOH AQX AP Budgeted TOH Applied TOH x SP Budget Variance Volume Variance Total Variance Actual TOH Budgeted TOH Applied TOH 4) Variable and Fixed Overhead fi.e. Four-Variance Analysis): Variances for variable and fixed overhead could be computed using the trees below The first tree is for Variable Overhead. The sum of the Spending Variance and Efficiency Variance equal the Total Variance. The Total Variance is also equal to the difference between the Actual VOH and Applied VOH 5 Actual VOH Budgeted VOH AO x SP Applied VOH SQx SP Sheetl 12. Variance Analysis Excel Assign ome Insert Page Layout Formulas Data Review View x Cut D Copy r Fermat Times New Roman 12 A A abwrao Text Gene Paste Blyv -v -v Av Merge & Center $v 4) Variable and Fixed Overhead (i.e. Four-Variance Analysis): Variances for variable and fixed overhead could be computed using the trees below. The first tree is for Variable Overhead. The sum of the Spending Variance and Efficiency Variance equal the Total Variance. The Total Variance is also equal to the difference between the Actual VOH and Applied VOH. Actual VOH AQ x AP Budgeted VOH Applied VOH AQ SP x SP 40S Spending Variance Efficiency Variance Total Variance Actual VOH Budgeted VOH Applied VOH second tree is for Fixed Overhead. The sum of the Spending Variance and Volume Variance equal the Total Variance. The Total Variance is also equal to the difference between the Actual FOH and Applied FOH The Sheetl 12. Variance Analysis Excel Assignment (subm Home Insert Page Layout Formulas Data Review View L' PasteFormat Cut D Copy Times New Roman v 12 w A, A Wrap Text General Blu--'-wAv Merge & Center. $v%, NS 7 The second tree is for Fixed Overhead. The sum of the Spending Variance and Volume Variance equal the s Total Variance. The Total Variance is also equal to the difference between the Actual FOH and Applied FOH. Actual FOH Budgeted FOH AQ x AP AQx SP x SP Spending Variance Volume Variance Total Variance Actual FOH Budgeted FOH Applied FOH 4 Step 4: Use answers from step 3 to address requirement 5. Note: The red bold text in Step 3 shows you where the numbers in the journal entries came from A) Record the purchase of materials. Use the Direct Materials Variance tree from part I of step 3 to obtain the numbers for this journal entry. Mat ed at Actual Quantity x Standard Price, but Accounts Sheetf acBoo 12. Variance Analysis Excel Assignment Home Insert Page Layout FormulasData Review View Wrap Text 12 AA-v L' Poste Copy Times New Roman NS Step 4: Use answers from step 3 to address requirement s. Note: The red bold text in Step 3 shows you where the numbers in the journal entries came from. A) Record the purchase of materials. Use the Direct Materials Variance tree from part 1 of step 3 to obtain the umbers for this journal entry. Materials are capitalized at Actual Quantity x Standard Price, but Accounts Payables are recorded at Actual Quantity x Actual Price. Remember, unfavorable variances are debited and 08 9 favorable variances are credited. 12 13 Use the Direct Materials Variance tree from part 1 of step 3 to obtain the B) Record the requisition of materials. 16 numbers for this journal entry. Work in Process is recorded at Standard Quantity x Standard Price, but 15 17 Materials are recorded at Actual Quantity x Standard Price. Remember, unfavorable variances are debited and 18 favorable variances are credited. 19 21 23 C) Record labor being incurred. Use the Direct Labor Variance tree from part 2 of step 3 to obtain the numbers for this journal entry. Work in Process is recorded at Standard Quantity x Standard Price, but Wages Payable s are recorded at Actual Quantity x Actual Price. Remember, unfavorable variances are debited and favorable variances are credited. 227 231 MacBook A 12. Variance Analysis Excel Assigna Home Insert Page Layout Formulas Data Review View Cut L' Times New Roman 12 A AE Copy Merge& Center 32 D) Zero out all variances at year end. Assume the variances are immaterial and close them out to Cost of Goods Sold. The variances below are from parts A)-C) directly above. 36 38 39 40 12 E) Record overhead being incurred. Do this for both variable overhead and fixed overhead. The numbers for these journal entries come from the actual cells of the Variable Overhead and Fixed Overhead trees from part 4 of step 3. 47 50 52 F) Record overhead being applied. Do this for both variable overhead and fixed overhead. The numbers for these journal entries come from the applied cells of the Variable Overhead and Fixed Overhead trees from part 4 of step 3 57 60 Sheetl MacBook 12. Variance Analysis Excel Assig ome Insert Page Layout Formulas Data Review View cu b wrap Text Gen Times New Roman 12A A Format Blu- v_vAv G) Record the variable and fixed overhead variances. The numbers for these journal entries come from the Variable Overhead and Fixed Overhead trees from part 4 of step 3. Remember, unfavorable variances are 6 debited and favorable variances are credited. For this journal entry, the Variable Overbead Control account captures the Total Variance for Variable Overhead, and the Fixed Overhead Control account captures the Total Variance for Fixed Overhead. The Variable Overhead Control account serves as a couterbalance for the variable overhead variance line items. The Fixed Overhead Control account serves as a counterbalance for the fixed overhead variance line items. H) Zero out all variances at year end. Assume the variances are immaterial and close them out to Cost of Goods Sold. The variances below are from parts G) directly above. 82 85 86 90 91 92 93 94 Sheetf