70.2K

Verified Solution

Link Copied!

Reorder quantities are based on price as shown in the Reorder Quantities data (K11:L16), e.g. currently for items that cost from $100 up to (but not including) $300 we re-order 35. Create a calculation in I4 that will check if we need to reorder and if so return the correct reorder amount otherwise return 0. Ensure the formula is copied down for the whole table. Note stock levels and reorder quantities will change, you should have one consistent calculation for the column and the calculations should work regardless of values or sort order of the inventory data

D On Backorde Re-Order? Reorder Qty 28% Yes 25 5 % Markup Maximum Stock Level Minimum Stock Level No. Item Codes to re-order No. Item Codes Over-Stocked Total Inventory Value Yes Re-Order Qty 50 Yes Reorder Quantities: Item Cost $0 $100 $300 $500 $1,000 I Yes C 1 Inventory 2 3 Code Description In Stock 4 C1019 Connector Pack 5 M1021 MGX 8025 Chip Set 6 M1022 MGX 8066 Chip Set 7 R1001 Robot Motor Kit 8 R1002 Robot Kit 33A 9 R1003 Robot Kit 38A 10 R1004 Robot Kit 38G 11 R1005 Robot Kit SP3 12 R1006 Robot Kit 448 13 R1007 Robot Kit 555 14 R1023 Relays - Small Pack 15 R1024 Relays - Medium Pack 16 R1025 Relays - Large Pack 0 17 S1008 Servo Motors A21 (4 pack) 18 S1009 Servo Motors B21 (4 pack) 18 19 S1010 Servo Motors A21 (8 pack) 39 20 S1011 Servo Motors B21 (8 pack) | 0 21 s1012 SMC Pneumatic Part 3-44 SMC Pneumatic Part X-22 23 s1014 SMC Pneumatic Part X-33 24 s1015 SMC Pneumatic Part 3-47 25 S1016 SMC Pneumatic Part 3-48 26 s1017 SMC Pneumatic X 200mm 27 S1018 SMC Pneumatic X 250mm 28 S1020 SD Drive 29 C1219 emc Connector Pack emc MGX 8025 Chip Set | 20 31 M1222 emc MGX 8066 Chip Set I 32 R1201 emc Robot Motor Kit 33 R1202 emc Robot Kit 33A 34 R1203 emc Robot Kit 38A 35 R1204 emc Robot Kit 38G 13 36 R1205 emc Robot Kit SP3 18 37 R1206 emc Robot Kit 448 | 0 38 R1207 emc Robot Kit 555 21 39 R1223 emc Relays - Small Pack I 40 R1224 emc Relays - Medium Pack I 33 41 R1225 emc Relays - Large Pack 6 42 S1208 emc Servo Motors A21 (4 pack) 8 43 S1209 Teme Servo Motors B21 (4 nack) | ... Information Completed Jobs Stats 12 20 Cost Price (each $74.30 $133.60 $158.40 $1,416.20 $2,264.90 $2,876.50 $2,711.90 $3,263.50 $3,218.10 $3,488.20 $250.50 $310.30 $306.40 $905.70 $949.20 $1,925.00 $1,800.00 $724.40 $682.10 $851.00 $762.60 $790.70 $532.00 $609.70 $95.60 $66.20 $119.70 $154.10 $1,445.401 $2,521.80 I $2,431.90 $2,596.501 $2,836.50 $2,902.60 $3,064.40 $272.80) $325.101 $313.00 $973.10 $939 001 JOB_4971 Journals Retail Price Over-Stocked $95.101 $171.01 $202.75 Yes $1,812.74 $2,899.07 $3,681.92) $3,471.23 $4,177.28 $4,119.17 $4,464.90 $320.64. $397.18 $392.19 $1,159.30 $1,214.98 $2,464.00 Yes I $2,304.00 $927.23 $873.09 $1,089.28 $976.13 $1,012.10 $680.96 $780.42 $122.37 $84.74 $153.22 $197.25 $1,850.11 $3,227.90 $3,112.83 $3,323.52 $3,630.72 $3,715.33 $3,922.43 $349.18 $416.13 $400.64 $1,245.57 $1.201 921 + Yes Yes 8 Yes I 00 Yes Yes Inventory

Answer & Explanation Solved by verified expert

See Answer