Problem and Requirements The primary purpose of this assignment is to give you practice calculating variances. The variances you need to calculate are described under the "REQUIRED heading below. The secondary purpose of this assignment is to improve your proficiency in using Excel as a tool for analysis (as opposed to word processing software). Actual Templates Direct Materials Actual Input Quantity x Budgeted (Std) Price Purchases Usage Flexible Budget (Applied) Cost Example: $ 810,000 Requirement #1: Calculate Calculate All yellow cells in this template (right) must be completed with formulas referencing other cells. For example, do not just type the number $810,000 in cell F9. Instead, you must enter the formula in the cell to calculate this number. Indicate whether variances are favorable (FAV) or unfavorable (UNF) in the "green" cells beside each amount. Some formulas will reference the problem, other formulas will use cells in the template. Variances should not have a minus sign or parentheses Price Variance Efficiency Variance NOTE: There is NO Total variance because Price & Efficiency Variances are measured at different times for DM Actual Cost Direct Labor Actual Input Quantity x Budgeted (Std) Rate Flexible Budget (Applied) PROBLEM: Mascot Mfg. makes costumes for athletic team mascots and uses a standard costing system The company recognizes isolates) materials price variances at the time the materials are purchased. They allocate (apply) OH based on # of DL hours. Following are this year's cost and standards data, assumes 52 weeks per year. Price Variance Efficiency Variance 12.50 yards 26.00 average Flexible Budget (TOTAL) Variance Standards (for a planned level of production of 2,200 units per month) Direct Materials Yards per costume Price per yard Direct Labor: D.L. Hours per costume D.L. Rate per hour Variable MOH standard rate per direct labor hour Fixed MOH standard rate per direct labor hour Total Budgeted Fixed MOH (for 1 month) 62.00 hours $ 44.50 average $ 4.35 per D.L. hour $ 9.47 per D.L. hour $ 1,250,000 Actual Cost Incurred Variable Manufacturing Overhead Actual Input Quantity x Budgeted (Std) Rate Flexible Budget (Applied) Allocated Spending Variance Efficiency Varience $28.42 per yd Flexible Budget (TOTAL) Variance Actual cost and data from the current month Actual number of yards of materials purchased Actual cost of materials purchased Actual number of yards used in production Actual number of costumes produced Actual direct labor hours Actual direct labor cost Actual variable MOH (in total) Actual fixed MOH (in total) 28,500 $ 810,000 27,350 2,300 133,500 $ 5,500,000 $ 665,000 $ 1,488,000 Fixed Manufacturing Overhead $ 41.20 per hr Actual Cost Incurred Flexible Budget (Same as Static) Static FOH Budget Allocated Requirement #2 Write a one page letter summarizing: 1. What the variances mean, and 2. What conclusion do you draw from the variances? Spending Variance Production Volume Variance TOTAL Fixed OH Variance