* 20 FA Project Template Q Search Sheet Page Layout Formulas Data Review View + Share Home Insert X Cut Arial 10 -A- A AutoSum , General Wrap Text 49 H H Copy Fill Paste B I u A Merge & Center $ %) 4.0 .00 .00 0 Insert Delete Format Format Conditional Format Cell Formatting as Table Styles Clear Sort & Filter Check for Updates x Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. E45 4 x fx A B D E F F G H 1 J K L L M N O Templates Direct Materials Actual Input Quantity x Budgeted (Std) Price Actual Cost Example: $ 661,254 Flexible Budget (Applied) 7 Purchases Usage Price Variance Efficiency Variance NOTE: There is NO "otal" 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) Price Variance Efficiency Variance Flexible Budget (TOTAL) Variance 3 Problem and Requirements 4 The primary purpose of this assignment is to give you practice calculating variances. The 5 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 tool for analysis (as opposed to word processing software). 8 9 REQUIRED: 10 All yellow cells in this template (right) must be completed with formulas referencing other cells. 11 For example, do not just type the number $661,254 in cell F9. 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 or 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 file in this format: ACC3365_Calculations_[1st initial, last name) and 19 ACC3365_Letter_[1st initial, last name). Be certain to include your full name in the cell in the upper right corner 20 of the first line (above) in this file. 21 Assignment will not be accepted late other than due to emergency situations as described in the syllabus. 22 23 PROBLEM: Mascot Mfg. makes awnings for athletic team tailgating and uses a standard costing system. 24 The company recognizes (isolates) materials price variances at the time the materials are purchased. 25 They allocate (apply) OH based on # of DL hours. 26 Following are this year's cost and standards data; assumes 52 weeks per year. 27 28 Standards (for a planned level of production of 2,200 units per month 29 Direct Materials 30 Yards per awning 31 Price per yard S Direct Labor 33 Hours per awning 34 Rate per hour $ 35 Variable MOH standard rate per direct labor hour $ 36 Fixed MOH standard rate per direct labor hour $ 37 Total budgeted fixed MOH cost s 38 39 Actual cost and data from the current month: 40 Number of yards purchased 41 Cost of yards purchased $ 42 Number of yards used 43 Number of awnings produced 44 Actual direct labor hours 45 Actual direct labor cost $ 46 Actual variable MOH S 47 Actual fixed MOH S 48 49 Calculate these variances using the templates to the right. 50 1. Direct material variances Actual Cost Incurred Variable Manufacturing Overhead Actual Input Quantity x Budgeted (Std) Rate Flexible Budget (Applied) Allocated 22 13 32 Spending Variance Efficiency Varience 5 15 8 6 63,500 Flexible Budget (TOTAL) Variance Fixed Manufacturing Overhead Actual Cost Incurred Static FOH Budget Flexible Budget (Same as Static) Allocated 51,260 661,254 $ 12.90 per yd 47,500 2.200 10,850 168,175 $ 15.50 per hr 93,310 67,500 Spending Variance Production Volume Variance TOTAL Fixed OH Variance Sheet1 + Ready + 100% * 20 FA Project Template Q Search Sheet Page Layout Formulas Data Review View + Share Home Insert X Cut Arial 10 -A- A AutoSum , General Wrap Text 49 H H Copy Fill Paste B I u A Merge & Center $ %) 4.0 .00 .00 0 Insert Delete Format Format Conditional Format Cell Formatting as Table Styles Clear Sort & Filter Check for Updates x Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. E45 4 x fx A B D E F F G H 1 J K L L M N O Templates Direct Materials Actual Input Quantity x Budgeted (Std) Price Actual Cost Example: $ 661,254 Flexible Budget (Applied) 7 Purchases Usage Price Variance Efficiency Variance NOTE: There is NO "otal" 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) Price Variance Efficiency Variance Flexible Budget (TOTAL) Variance 3 Problem and Requirements 4 The primary purpose of this assignment is to give you practice calculating variances. The 5 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 tool for analysis (as opposed to word processing software). 8 9 REQUIRED: 10 All yellow cells in this template (right) must be completed with formulas referencing other cells. 11 For example, do not just type the number $661,254 in cell F9. 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 or 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 file in this format: ACC3365_Calculations_[1st initial, last name) and 19 ACC3365_Letter_[1st initial, last name). Be certain to include your full name in the cell in the upper right corner 20 of the first line (above) in this file. 21 Assignment will not be accepted late other than due to emergency situations as described in the syllabus. 22 23 PROBLEM: Mascot Mfg. makes awnings for athletic team tailgating and uses a standard costing system. 24 The company recognizes (isolates) materials price variances at the time the materials are purchased. 25 They allocate (apply) OH based on # of DL hours. 26 Following are this year's cost and standards data; assumes 52 weeks per year. 27 28 Standards (for a planned level of production of 2,200 units per month 29 Direct Materials 30 Yards per awning 31 Price per yard S Direct Labor 33 Hours per awning 34 Rate per hour $ 35 Variable MOH standard rate per direct labor hour $ 36 Fixed MOH standard rate per direct labor hour $ 37 Total budgeted fixed MOH cost s 38 39 Actual cost and data from the current month: 40 Number of yards purchased 41 Cost of yards purchased $ 42 Number of yards used 43 Number of awnings produced 44 Actual direct labor hours 45 Actual direct labor cost $ 46 Actual variable MOH S 47 Actual fixed MOH S 48 49 Calculate these variances using the templates to the right. 50 1. Direct material variances Actual Cost Incurred Variable Manufacturing Overhead Actual Input Quantity x Budgeted (Std) Rate Flexible Budget (Applied) Allocated 22 13 32 Spending Variance Efficiency Varience 5 15 8 6 63,500 Flexible Budget (TOTAL) Variance Fixed Manufacturing Overhead Actual Cost Incurred Static FOH Budget Flexible Budget (Same as Static) Allocated 51,260 661,254 $ 12.90 per yd 47,500 2.200 10,850 168,175 $ 15.50 per hr 93,310 67,500 Spending Variance Production Volume Variance TOTAL Fixed OH Variance Sheet1 + Ready + 100%