Using Figure G.2, enter $6,000 into cell E8. In that spreadsheet, any new supplier (with a ‘Y’ in column J) should have its total purchases in column D equal to its change from the previous month in column E. Analytics manager Vicky Luo wishes to set up Conditional Formatting in Excel to identify any new suppliers whose purchases do not meet that condition. What formula should go into the box Format values where this formula is true:?

Figure G.2

Transcribed Image Text:

A B 1 Thompson Industries Monthly Supplier Report - April 2 3 4 5 Supplier Name 6 Maverick Supplies 7 Cowboy Pete's Lumber 8 ABC Metals 9 Perkins Plastics 10 Everything Brass 11 Jones Aluminum & Copper 12 Rick's Wrought Iron 13 Penelope's Paper Products 14 Anthony's Adhesives 15 Columbus Manufacturing 16 Smart Electronics, Inc. 17 Watson Woodworking 18 Fred's Foam Molding 19 Impenetrable Insulation, Inc. 20 Suzy's Shipping Supplies Supplier Supplier Category Number 6071 6894 5054 5528 4413 5858 9733 4718 9944 1343 4785 2730 2949 1443 8039 Total (P,S) Purchases P P S P P P S P S P D S P P $39,117 $25,554 $13,051 $27,886 $29,300 $16,862 $29,078 $25,716 $22,750 $20,103 $30,062 $32,589 $47,886 $24,971 $23,851 E Change from Previous Month $5,000 $400 $13,051 $32,564 ($4,658) $16,862 ($3,121) $8,795 $1,268 $469 $30,062 $471 $47,886 ($6,895) $1,269 F Quality On-time Rating (1-5) Percentage 100% 90% 0% 100% 1000% 50% 60% 100% 90% 100% 5 5 3 4 5 4 1 3 4 4 5 5 5 4 5 33% 25% 100% 15% 50% H No. of Percentage Rejected Returns Shipments 0% 0 0% 75% 3% 0% 5% 80% 12% 0% 50% 1% 0.50% 4% 0% 0 2 0 0 1 6 0 0 0 0 0 0 0 0 New Supplier (Y, N) N N Y N N Y N N N N Y N Y N N