need help converting these numbers over with the correct formula entrys for each blue box. help!!
C27 10 DEFG Actual Results Master Budget No. of Hawklet Bracelets Sold 4.783 3,200 Sales Revenue ($10 per Hawkle") $ 47,830.0000 $ 32,000.0000 Direct Materials: Elastic Cording $ 114.7920 $ 57.6000 Letter Beads 231.4972 $ 110.0800 Colorful Beads 83.7025 $ 134.4000 Direct Labor $ 8,322.4200 $ 5.760.0000 Variable Overhead ($0.0500 per Hawkler" $ 239.1500 $ 160.0000 Total Variable Costs $ 8.991.5617 $ 6,222.0800 Contribution Margin $ 38,838.4383 $ 25.777.9200 Fixed Overhead: Facility Rent $ 2,625.0000 $ 2.500.0000 Equipment Depreciation $ 14.0000 $ 14.0000 Indirect Labor $ 1.800.0000 $ 1.800.0000 Total Fixed Costs $ 4,439.0000 $ 4,314.0000 Operating Income $ 34,399.4383 $ 21.463.9200 Standard Standard Standard Costs for one Hawklet are as follows: Elastic Cording (in centimeters) $ 0.0018 1 0.0000 Letter Beads (in beads) $ 0.0043 8.0000 Colorful Beads in beads) $ 0.0035 12.0000 Direct Labor fin minutes) $ 0.1500 12.0000 Actual Price 'otal Actual Quantity Records indio ate the company purchased and used the following quantities at the following prices Elastic Cording in centimeters) $ 0.0015 76,528.0000 Data Contribution Margin Report O Type here to search of = = - - Clipboard Font Alignment C27 x for DEFGH Direct Materials: Elastic Cording $ 114.7920 $ 57.6000 Letter Beads $ 231 4972_ $ 110.0800 Colorful Beads $ 83.7025 $ 134.4000 Direct Labor $ 8.322.4200 $ 5,760.0000 Variable Overhead ($0.0500 per Hawklet) $ 239.1500 $ 160.0000 Total Variable Costs $ 8,991.5617 $ 6.222.0800 Contribution Margin $ 38,838.4383 $ 25,777.9200 Fixed Overhead Facility Rent $ 2,625.0000 $ 2.500.0000 Equipment Depreciation $ 14.0000 $ 14.0000 Indirect Labor $ 1.800.0000 $ 1.800.0000 Total Fixed Costs R $ 4,439,0000 $ 4,314.0000 Operating Income $ 34,399.4383 $ 21,463. 9200 Standard Standard Standard Costs for one Hawklet are as follows Elastic Cording (in centimeters) 0.0018 10.0000 Letter Beads lin beads) $ 0.0043_ 8.0000 Colorful Beads (in beads) $ 0.0035 12.0000 Direct Labor in minutes) $ 0.1500 1 2.0000 Actual Price total Actual Quantity Records indicate the company purchased and used the following quantities at the following prices Elastic Cording lin centimeters) 0.0015 76,528.0000 Letter Beads (in beads) .. $ 0.0044 52,613.0000 Colorful Beads (in beads) 0.0025 3 3 481.0000 Direct Labor (in minutes) 0.1582 52.613.0000 Data Contribution Margin Report 0 Type here to search o GMKLIMNO ACCT 201 INDUSTRIES, INC. HAWKLET'M CONTRIBUTION MARGIN REPORT FOR THE MONTH ENDED OCTOBER 31, 2019 HINT: To provide supporting caloustions, click on Formulas in the ribbon and then Actual select Show Formulas. Flexible Budget Flexible Budget Variance HINTS: Amount F/U Never do calodations on a calculator and Actual Volume of Product Produced and Sold 4,783.0000 then enter them into an Excel cell Make $ 3,200.0000 Excel do the work for you usingformulas! Sales Revenue All of the BLUE boxes should be a 47,830.0000 $ 32,000.0000 Formula Direct Materials Remember, to be able to copy a formula to Elastic Cording 114.7920 the next row, bu keep the right variable 57.8000 cost use the foreference the cell for Letter Beads 231.4972 110.0800 example 105 w ays refer to cel05, while 015 will always refer to row 5, but the Colorful Beads 83.7025 134.4000 column will change depending on where the formula is copied Direct Labor 8,322.4200 5.760.0000 HINT: Variable Overhead 239.1500 160.0000 Use the ABS function when calculating the variance to return a positive value Total Variable Costs 8,991.5617 6,222.0800 Contribution Margin 38,838.4383 25,7779200 Challenge yourself to use the IF function to write a formulato determine whether a Fixed Overhead variance is favorable or unfavorable! Facility Rent 2625.0000 $ 2.500.0000 Remember, If you are desing with teenue Equipment Depreciation 14.0000 14.0000 or income, and ACTUAL is larger than the FLEXOLE budget, that's good or Indirect Labor 1.800.0000 $ 1,8000000 favorable, you are anayeing cost and actualis larger than the flexible budget Data Contribution Margin Report eady 1 Type here to search HINT: HINT: o e M OO Numbers Styles G18 HA CD F G H I J K L M N O Teppo CCC on Formulas in the ribbon and then select Show Formulas Actual Flexible Budget Flexible Budget Variance Amount FIU $ 4,783.0000 47.830.0000 $ $ 3,200.0000 32,000 0000 HINTS: Never do caloulations on a calculator and then enter them into an Emcel cell Make Excel do the work for you using formulas! All of the BLUE boxes should be a formula Hemember, to be able to copy alormato the netow, but keep the light variable costure the storeference the color example 1015 will always refer to celos whle 015 will always refer to tow 5, but the column wil change depending on where the formula is copied Actual Volume of Product Produced and Sold Sales Revenue Direct Materials Elastic Cording Leter Beads Colorful Beads Direct Labor Variable Overhead Total Variable costs Contribution Margin Fixed Overhead Facility Rent Equipment Depreciation Indirect Labor Total Fixed Costs Operating Income 114 7920 231.4972 83.7025 8,322 4200 239 1500 8,991.5617 38,838.4383_ 57 6000 110.0800 134.4000 5,760.0000 160.0000 6,222 0800 25,777.9200 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 formato determine whether a variance is favorable or un lavorable HINT: Remember, you are dealing with tevenue of income, and ACTUAL is larger than the FLEXIBLE budget, that's good or favorable. you are analyzing coil and actualis larger than the flexible budget that's bad or unfavorable $ 2,625.0000 14.0000 1,800.0000 4,439.0000 34,399,4383 $ $ $ 2.500.0000 14.0000 1,800.0000 4,314.0000 21,463.9200 Data Contribution Margin Report Ready Type here to search o e * MOOI 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. 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. o e @ @ 00 W
