The Plated Gnome 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.

Requirements:

1. Using the Excel worksheet provided in MyLab Accounting, enter a formula in Cell J2 to display High Priority, Investigate, or No Action based on the Price variance % in Column I and the investigation rules listed in the exercise.
2. Copy the formula in Cell J2 down to the rest of the rows in that column.
3. Use Conditional Formatting in Column J to show cells containing High Priority with Light Red Fill with Dark Red Text and cells containing Investigate with Yellow Fill with Dark Yellow Text.

Transcribed Image Text:

B C D F G H Actual Actual Standard Quantity Price Price Price 1 Item Item # (AQ) (AP) 2 Boneless, Skinless Chicken Breasts M-95598 23,437 $ 5.49 $ (SP) 4.50 $ (AQ x AP) (AQ X SP) 128,669 $ DM Price Variance Variance 105,467 $23,203 Management % by Exception 22.0% 3 Carrots V-31883 11,718 $ 2.21 $ 1.89 $ 25,897 $ 22,147 $ 3,750 16.9% 4 Gochujang S-60803 1,953 $ 30.60 $ 30.00 $ 59,762 $ 58,590 $ 1,172 2.0% 5 Honey S-54278 976 $ 9.04 $ 8.00 $ 8,823 $ 7,808 $ 1,015 13.0% 6 Gal Lan V-52741 11,718 $ 1.00 $ 0.80 $ 11,718 $ 9,374 $ 2,344 25.0% 7 Jasmine Rice V-16711 7,812 $ 0.76 $ 0.75 $ 5,937 $ 5,859 $ 78 1.3% 8 Garlic V-16443 390 $ 2.95 $ 2.87 $ 1,151 $ 1,119 $ 31 2.8% 9 Radishes S-73047 5,859 $ 2.52 $ 2.48 $ 14,765 $ 14,530 $ 234 1.6% 10 Rice Vinegar S-92325 976 $ 33.30 $ 30.00 $ 32,501 $ 29,280 $ 3,221 11.0% 11 Olive Oil S-43466 1,953 $ 36.40 $ 35.00 $ 71,089 $ 68,355 $ 2,734 4.0% 12 Sea Salt S-13480 1,953 $ 8.20 $ 8.00 $ 16,015 $ 15,624 $ 391 2.5% 13 Cracked Black Pepper S-11765 976 $ 15.47 $ 17.00 $ 15,099 $ 16,592 $ 1,493 9.0% 14 Wheat Roll B-47474 7,812 $ 2.10 $ 2.00 $ 16,405 $ 15,624 $ 781 5.0% 15 Green Beans V-55326 11,718 $ 2.98 $ 2.25 $ 34,920 $ 26,366 $ 8,554 32.4%