3 Problem and Requirements 4 The primary purpose of this assignment is to give you practice calculating variances. The s variances you need to calculate are described under the 'REQUIRED"heading below 6 The secondary purpose of this assignment is to improve your proficiency in using Excel as a fool 7 for analysis (as opposed to word processing software) 8 19 REQUIRED 10 All yellow cells in this template (right) must be completed with formulas referencing other cells 71 For example, do not just type the number $840,000 in cell F. Instead, you must enter the formula 12 in the cell to calculate this number indicate whether variances are favorable (FAV) or unfavorable (UNF) 13 in the green cells beside each amount Some formulas will reference the problem other formulas will 14. Use cells in the template. Variances should not have a minus sign of parentheses 15 16 SUBMISSION: You must submit your Excel file (1) with the yellow and green cells completed and 17 (2) submit a written report in Word according to instructions included in the Canvas assignment You must 18 name the tile in this format ace 3365 Excel (1st initial & last name) and acc 3365-Word (1st initial & last 19 name) Be certain to include your full name in the cell in the upper right comer of the first line (above) in this file 20 Assignment will not be accopted late other than due to emergency situations as described in the syllabus 21 22. PROBLEM: Mascot Mly makes costumes for athletic team mascots and uses a standard costing system, 23 The company recognizes (isolates) materials price variances at the time the materials are purchased 24 They allocate (apply oH based on # of DL hours 25 Following are this year's cost and standards data, assumes 52 weeks per year 26 27 Actual cost and data from the current month: 28 Actual number of yards of materials purchased 29.500 29 Actual cost of materials purchased $ 840,000 S 28 47 peryd 30 Actual number of yards used in production 27350 31 Actual number of costumes produced 2.400 32 Actual direct labor hours 131.500 33 Actual direct labor cost $5 500 000 542.59 per hr 34 Actual variable MOH in total) 5 665,000 35 Actual ud MOH On total) 51,488,000 36 37 Standards (for a planned level of production of 2.200 units per month 38 Direct Materials 39 Yards per costume 1250 yards 40 Price per yard 25 50 average 41 Direct Labor 42 DL Hours per costume 6200 hours 43 DL Rate per hour 5 450 average 44 Variable MOH standard rate per direct labor hour 450 per hour 45 Fixed MOH Standard rate per direct labor houn 9.16 per DL hout 46 Total Budgeled Fred MOH (for 1 month) $1.250.000 S 47 48 Requirement #1: Using Excel functionality, calculate 49 these variances using the templates to the right. 50 1. Direct material variances 51 2. Direct labor variances 52 3. Variable MOH variances 53 4. Fixed MOH variances 54 55 Requirement #2 Write a one page letter summarizing: 56 1. What the variances mean, and 57 2. What conclusion do you draw from the variances? 58 Instructions in the Assignment on Canvas 59 60 Templates Direct Materials Actual Input Quantity x Budgoted (Std) Price Purchases Usage Actual Cost Example: $ 840,000 Flexible Budget (Applied) UNE FAV Elliency Variance Price Variance NOTE: There is NO Total variance because Price & Emiciency Variances are measured at different times Actual Direct Labor Actual Input Quantity xBudgeted (Std) Rate Flexible Budget (Applied) Cost FAV FAV Price Variance Eficiency Varance Flexible Budget (TOTAL) Variance Variable Manufacturing Overhead Actual Actual Input Quantity Costincurred x Budgeted (Std) Rate Flexible Budget (Applied) Allocated Spending Variance Efficiency Vanence Flexible Budget (TOTAL) Varance Fixed Manufacturing Overhead Actual Cost Incurred Static FOH Budget Flexible Budget (Same as Static) Allocated Spending Variance Production Volume Variance TOTAL Fixed OH Variance