B C D E F G H Functions: IF; Formula: Multiply, Add, Subtract; Cell Referencing E6.13 - Using Excel to Calculate Variances Student Work Area PROBLEM Required: Provide input into cells shaded in yellow in this template. Use The following information is available for Coady's Chocolates: mathematical formulas with cell references to the Problem or work area as indicated. Use the IF function to denote if each variance is Favorable or Actual production, boxes 3,400 Unfavorable Budgeted production, boxes 3.000 Direct materials standards Pounds of chocolate per box a Calculate the direct materials price and quantity variances. Cost per pound S 6.00 Actual direct materials AQ x AP AQ x SP Pounds of chocolate purchased 9,000 Pounds of chocolate used 9,500 Direct Materials Price Cost per pound $ 6.10 Variance 18 19 AO x SP SQ x SP 20 Direct Materials Quantity Variance b. What might have caused the variances you calculated? Coady's Chocolates appears to have purchased a chocolate at a than the standard specified. The chocolate appears to have been processed the standard quality, requiring to complete the production run. 46 471 For nula: Divide, Multiply, Subtract; Cell Referencing IN E8.13- Using Excel to Allocate Constrained Resources Student Work Area PROBLEM Required: Provide input into cells shaded in yellow in this template. Use mathematical 5 Umbrella Co. is considering the introduction of three new products. Per unit sales and cost formulas with cell references to the Problem or work area as indicated. 6 information are as follows: B C Sales price 3.00 S 5.00 S 16,00 How many of each product should Umbrella Co. produce and sell to maximize its profit? 10 Variable costs 1.20 S 3.40 10.00 11 Fixed costs 0.50 1.00 S 3.50 12 Labor hours per unit 0.75 1.25 2.00 A B C 13 Monthly demand in units 800 650 250 Contribution margin per unit 14 15 The company has only limited direct labor hours available to commit to production of any new Contribution margin per labor hour 16 products. 17 Preference ranking 18 Labor hours available 1,500 Allocation Units to Hours Hours 19 20 Ranking Product Produce Used Available 1,500 #1 #2 #3 29 30 31 32 33 34