The Plated Elf Company produces meal kits. Each week, it ships thousands of meal kits.

Each meal kit includes a variety of foods (direct materials).

Company managers have the following investigation rules for direct materials price variances:

• Variances greater than 15% are a high priority for investigation.

• Variances between 5% and 15% should still be investigated.

• Variances less than 5% will not be investigated.

The controller prepared a report using Excel to calculate the direct material price variances and price variance percentages. A sample from that Excel report appears below.

Using the partial Excel report shown, answer the following questions.
1. What steps would be performed in Cell J2 to designate whether the variance percentage would be classified as High Priority, Investigate, or No Action?
2. How could the formula in Cell J2 be copied down to the rest of the rows in that column?
3. What Excel feature could be used to visually highlight cells in Column J?
4. Describe how the cells in Column J could be visually highlighted to show High Priority, Investigate, and No Action with separate colors automatically (rather than individually applying fill colors).

Transcribed Image Text:

B D F Actual Actual Standard Quantity Price Price 1 Item Item # (AQ) (AP) (SP) (AQ X AP) 2 Tilapia Fillets M-97935 187,500 $ 4.23 $ 3.50 $ 793,125 $ 3 Whole Peeled Tomatoes V-12008 27,343 $ 3.10 $ 3.00 $ 4 Mint V-19912 25,000 $ 14.14 $ 14.00 $ 5 Sliced Roasted Almonds N-49762 10,000 $ 9.40 $ 10.00 $ 84,763 $ 353,500 $ 94,000 $ 82,029 $ 2,734 (AQ X SP) DM Price Variance 656,250 $136,875 1 Price Variance Management % by Exception 20.9% High Priority 3.3% No Action 350,000 $ 3,500 1.0% No Action 6 Dried Currants F-81809 10,000 $ 6.20 $ 6.00 $ 62,000 $ 100,000 $ 60,000 $ 6,000 2,000 6.0% Investigate 7 Mustard Seeds S-86101 23,437 $ 7.28 $ 6.50 $ 170,621 $ 152,341 $18,281 3.3% No Action 12.0% Investigate 8 Chickpeas B-23019 234,375 $ 0.44 $ 0.40 $ 103,125 $ 93,750 $ 9,375 10.0% Investigate 9 Green Beans V-18659 93,750 $ 2.67 $ 2.25 $ 250,313 $ 210,938 $39,375 10 Garlic V-40513 6,250 $ 2.84 $ 2.87 $ 17,750 $ 11 Plain Nonfat Greek Yogurt D-89794 7,812 $ 1.66 $ 1.50 $ 12,968 $ 12 Ginger S-12113 4,062 $ 3.03 $ 2.47 $ 12.308 $ 13 Curry Powder S-26325 31,250 $ 1.84 $ 2.00 $ 57,500 $ 14 Olive Oil S-97150 3,906 $ 32.90 $ 35.00 $ 128,507 $ 15 Sea Salt S-53967 31,250 $ 7.68 $ 8.00 $ 240,000 $ 17,938 $ 11,718 $ 1,250 10,033 $ 62,500 $ 136,710 $ 250,000 $ 10,000 188 18.7% High Priority 1.0% No Action 10.7% Investigate 2,275 5,000 22.7% High Priority 8.0% Investigate 8,203 6.0% Investigate 4.0% No Action