This project is part of a comprehensive project that provides hands-on experience applying course concepts through the startup and operation of your own small business. Your team is part of the company ACCT 201 Industries, Inc., that produces and sells customizable bead bracelets called HawkletsTM. Your task in this project is to analyze the company's October performance. To receive full credit, your reports must be professional, typed, and include all supporting calculations. Submit the following via Blackboard: Contribution Margin Report with supporting calculations (use provided Excel template file on Blackboard) Performance Evaluation Report (submit as PDF) Extra Credit Opportunity: Use Excel formulas to complete the Contribution Margin Report template for 2 points of extra credit! REQUIRED: 1. Complete the actual column of the Contribution Margin Report, using the Actual data provided within the Data tab of the template to write your formulas. 2. Complete the flexible budget column of the Contribution Margin Report, using the Master Budget data provided within the Data tab of the template to write your formulas. 3. Complete the Contribution Margin Report by calculating the flexible budget variance for each line item in the report. Be sure to indicate whether each variance is favorable or unfavorable. 4. Select the largest direct material flexible budget variance and the direct labor flexible budget variance from those calculated in Requirement 3. Prepare a brief (two-page) performance evaluation report, including the following items for each of the two variances: Pitchfork dividing the flexible budget variance into price and efficiency variances; Visual aid (pie chart, bar graph, etc.) comparing the price, efficiency and total flexible budget variance; and Brief paragraph outlining possible causes for each price and efficiency variance (four total). Be specific; generic answers like "we spent more on raw materials" will not receive credit. NOTE: This project involves evaluating very small amounts. We recommend that you use four decimal places, rather than two, when reporting data. The template is already formatted to four decimal places. Master Budget Actual Results 4,838 48,380.0000 3,200 32,000.0000 $ No. of Hawklet Bracelets Sold Sales Revenue ($10 per Hawklet) Direct Materials: Elastic Cording Letter Beads Colorful Beads Direct Labor Variable Overhead ($0.0500 per Hawklet) Total Variable Costs Contribution Margin 116. 1120 234. 1592 84.6650 8,418.1200 241.9000 9,094.9562 39,285.0438 $ $ $ $ $ $ $ 57.6000 110.0800 134.4000 5,760.0000 160.0000 6,222.0800 25,777.9200 Fixed Overhead: Facility Rent Equipment Depreciation Indirect Labor 2,625.0000 14.0000 1,800.0000 4,439.0000 34,846.0438 $ $ $ $ $ 2,500.0000 14.0000 1,800.0000 4,314.0000 21,463.9200 Total Fixed Costs Operating Income Standard Price Standard Quantity Standard Costs for one Hawklet are as follows: Elastic Cording (in centimeters) Letter Beads (in beads) Colorful Beads (in beads) Direct Labor (in minutes) 0.0018 0.0043 0.0035 0.1500 10.0000 8.0000 12.0000 12.0000 Total Actual Quantity Actual Price Records indicate the company purchased and used the following quantities at the following prices: Elastic Cording (in centimeters) 0.0015 Letter Beads (in beads) 0.0044 Colorful Beads (in beads) 0.0025 Direct Labor (in minutes) 0.1582 77,408.0000 53,218.0000 33,866.0000 53,218.0000 ACCT 201 INDUSTRIES, INC. HAWKLETTM CONTRIBUTION MARGIN REPORT FOR THE MONTH ENDED OCTOBER 31, 2019 HINT: To provide supporting calculations, click on Formulas in the ribbon and then select Show Formulas. Flexible Budget Variance Actual Flexible Budget Amount FIU Actual Volume of Product Produced and Sold HINTS: Never do calculations on a calculator and then enter them into an Excel cell. Make Excel do the work for you using formulas! All of the BLUE boxes should be a formula. Sales Revenue Direct Materials: Elastic Cording Remember, to be able to copy a formula to the next row, but keep the right variable cost, use the $ to reference the cell (for example $D$5 will always refer to cell D5, while D$5 will always refer to row 5, but the column will change depending on where the formula is copied. Letter Beads Colorful Beads Direct Labor Variable Overhead Total Variable Costs Contribution Margin HINT: Use the ABS function when calculating the variance to return a positive value. HINT: Challenge yourself to use the IF function to write a formula to determine whether a variance is favorable or unfavorable! Fixed Overhead: Facility Rent Equipment Depreciation Indirect Labor Total Fixed Costs Operating Income HINT: Remember, if you are dealing with revenue or income, and ACTUAL is larger than the FLEXIBLE budget, that's good or favorable. If you are analyzing cost, and actual is larger than the flexible budget, that's bad or unfavorable. This project is part of a comprehensive project that provides hands-on experience applying course concepts through the startup and operation of your own small business. Your team is part of the company ACCT 201 Industries, Inc., that produces and sells customizable bead bracelets called HawkletsTM. Your task in this project is to analyze the company's October performance. To receive full credit, your reports must be professional, typed, and include all supporting calculations. Submit the following via Blackboard: Contribution Margin Report with supporting calculations (use provided Excel template file on Blackboard) Performance Evaluation Report (submit as PDF) Extra Credit Opportunity: Use Excel formulas to complete the Contribution Margin Report template for 2 points of extra credit! REQUIRED: 1. Complete the actual column of the Contribution Margin Report, using the Actual data provided within the Data tab of the template to write your formulas. 2. Complete the flexible budget column of the Contribution Margin Report, using the Master Budget data provided within the Data tab of the template to write your formulas. 3. Complete the Contribution Margin Report by calculating the flexible budget variance for each line item in the report. Be sure to indicate whether each variance is favorable or unfavorable. 4. Select the largest direct material flexible budget variance and the direct labor flexible budget variance from those calculated in Requirement 3. Prepare a brief (two-page) performance evaluation report, including the following items for each of the two variances: Pitchfork dividing the flexible budget variance into price and efficiency variances; Visual aid (pie chart, bar graph, etc.) comparing the price, efficiency and total flexible budget variance; and Brief paragraph outlining possible causes for each price and efficiency variance (four total). Be specific; generic answers like "we spent more on raw materials" will not receive credit. NOTE: This project involves evaluating very small amounts. We recommend that you use four decimal places, rather than two, when reporting data. The template is already formatted to four decimal places. Master Budget Actual Results 4,838 48,380.0000 3,200 32,000.0000 $ No. of Hawklet Bracelets Sold Sales Revenue ($10 per Hawklet) Direct Materials: Elastic Cording Letter Beads Colorful Beads Direct Labor Variable Overhead ($0.0500 per Hawklet) Total Variable Costs Contribution Margin 116. 1120 234. 1592 84.6650 8,418.1200 241.9000 9,094.9562 39,285.0438 $ $ $ $ $ $ $ 57.6000 110.0800 134.4000 5,760.0000 160.0000 6,222.0800 25,777.9200 Fixed Overhead: Facility Rent Equipment Depreciation Indirect Labor 2,625.0000 14.0000 1,800.0000 4,439.0000 34,846.0438 $ $ $ $ $ 2,500.0000 14.0000 1,800.0000 4,314.0000 21,463.9200 Total Fixed Costs Operating Income Standard Price Standard Quantity Standard Costs for one Hawklet are as follows: Elastic Cording (in centimeters) Letter Beads (in beads) Colorful Beads (in beads) Direct Labor (in minutes) 0.0018 0.0043 0.0035 0.1500 10.0000 8.0000 12.0000 12.0000 Total Actual Quantity Actual Price Records indicate the company purchased and used the following quantities at the following prices: Elastic Cording (in centimeters) 0.0015 Letter Beads (in beads) 0.0044 Colorful Beads (in beads) 0.0025 Direct Labor (in minutes) 0.1582 77,408.0000 53,218.0000 33,866.0000 53,218.0000 ACCT 201 INDUSTRIES, INC. HAWKLETTM CONTRIBUTION MARGIN REPORT FOR THE MONTH ENDED OCTOBER 31, 2019 HINT: To provide supporting calculations, click on Formulas in the ribbon and then select Show Formulas. Flexible Budget Variance Actual Flexible Budget Amount FIU Actual Volume of Product Produced and Sold HINTS: Never do calculations on a calculator and then enter them into an Excel cell. Make Excel do the work for you using formulas! All of the BLUE boxes should be a formula. Sales Revenue Direct Materials: Elastic Cording Remember, to be able to copy a formula to the next row, but keep the right variable cost, use the $ to reference the cell (for example $D$5 will always refer to cell D5, while D$5 will always refer to row 5, but the column will change depending on where the formula is copied. Letter Beads Colorful Beads Direct Labor Variable Overhead Total Variable Costs Contribution Margin HINT: Use the ABS function when calculating the variance to return a positive value. HINT: Challenge yourself to use the IF function to write a formula to determine whether a variance is favorable or unfavorable! Fixed Overhead: Facility Rent Equipment Depreciation Indirect Labor Total Fixed Costs Operating Income HINT: Remember, if you are dealing with revenue or income, and ACTUAL is larger than the FLEXIBLE budget, that's good or favorable. If you are analyzing cost, and actual is larger than the flexible budget, that's bad or unfavorable
