can you please help me to work out these instructions and what formulas and which steps to use in excel would be greatly appreciated Section C C1 C2 C3 C4 The following are to be completed in the Inventory Worksheet Go to the Inventory Sheet This sheet contains a list of all the items the company keep in stock to build the robotics Each part is identified by a unique code Convert the Range A3 153 to a Table Name the table Inventory Name the following ranges A4 A53 Code E4 E53 Cost Price (and any other ranges you feel may prove useful) In F3 F53 calculate the retail price (paid by customer) using the Cost Price and the Markup in L3 (Note the markup may be changed and your calculation should still produce the correct answer ) L4 indicates the maximum stock level In G3 653 create a formula to check if the quantity in stock is over the maximum stock in L4 (this level can be adjusted) If it is over the stock level put Yes in the cell, otherwise leave it empty (do not put No or a space) Formula should copy down, check it is working correctly L5 indicates the minimum stock level while column D indicates if an item is on back order (already been reordered) If a stock item is below the minimum and not on Backorder (is blank) we need to reorder it In H3 53 create a formula to check if the item needs to be re ordered If it does put Yes in the cell, otherwise leave it empty (do not put No or a space) Formula should copy down, check it is working correctly 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 14 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 In L6 calculate how many item codes require reordering In L7 calculate how many item codes are over stocked Use Excel's built in help to investigate the SUMPRODUCT function In L8 calculate the total cost of all inventory items currently in stock 1 Inventory In Stock On Backorder Over Stocked Re Order Reorder Qty Ma roup Maximum Stock Level Minimum Stock Level No Item Codes to re order No Item Codles Over Stocked Total Inventory Value 0 0 Yes Reorder Quantities Item Cost Re Order ty 11 24 2 26 $0 50 Yes $100 $300 YES 35 20 101 Yes 0 $1 000 5 Yes 3 Code 4 C1019 5 M1021 6 M1022 7 R1001 8 R1002 9 R1003 10 R1004 11 R1005 12 R1006 13 R1007 14 R1023 15 R1024 16 R1025 17 51008 18 $1009 19 S1010 20 $1011 21 S1012 22 51013 23 51014 24 S1015 25 $1016 26 51017 27 S1018 29 S1020 29 C1219 30 M1221 31 M1222 32 R1201 33 R1202 34 R1203 35 R1204 36 R1205 37 R 1206 38 R1207 39 R1223 40 R1224 41 R 1225 42 S120B 43 S1209 44 S1210 45 S1211 46 S1212 47 S1213 48 51214 49 51215 50 51216 51 51217 52 S1218 53 S1220 Description Connector Pack MGX 8025 Chip Set MGX 8066 Chip Set Robot Motor Kit Robot Kit 33A Rabot Kit 3BA Robot Kit 3BG Robot Kit SP3 Robot Kit 446 Robot Kit 555 Relays Small Pack Relays Medium Pack Relays Large Pack Servo Motors A21 (4 pack) Servo Motors 21 (4 pack) Servo Motors A21 (8 pack) Servo Motors B 21 (8 pack SMC Pneumatic Part3 44 SMC Pneumatic Part X 22 SMC Pneumatic Port X 33 SMC Pneumatic Part 3 47 SMC Pneumatic Port 3 48 SMC Pneumatic X 200mm SMC Pneumatic X 250mm SD Drive emc Connector Pack em MGX B025 Chip Set lemc MGX 8066 Chip Set leme Robot Motor Ki lamc Robot Kit 33A lemc Robot Kit 3A emc Robot Kit 3BG emc Robot Kit SP3 lemc Robot Kit 448 lemc Robot Kit 555 Leme Relays Small Pack amc Relays Medium Pack emc Relays Large Pack emc Servo Motors A21 (4 pack Jemc Servo Motors B21 (4 pack Jemc Servo Motors A21 (8 pack leme Servo Motors 21 (8 pack Jem SMC Pneumatic Part 3 44 emc SMC Pneumatic Part X 22 lemc SMC Pneumatic Part X 33 emc SMC Pneumatic Part 3 47 emc SMC Pneumatic Part 3 48 em SMC Pneumatic X 200mm em SMC Pneumatic X 250mm Jame SD Drive Cost Price (each) $70 90 $129 90 $151 20 $1,387,00 $2 171 50 T $2 798 10 $2,711 901 $2, 745 00 $3 028 80 I $3,292 60 $272 80 $298 50 $313 00 $915 30 1 $1 061 40 $1 662 50 $1 980 00 $605 80 S776 40 $764 40 $717 201 $734,80 $537 60 $723 60 $89 901 $71 60 $132 301 $158 40 $1,372 40 $2 428 40 $2 745 90 $3 000 40 $3,080 50 $3,186 60 $2 934 00 $225 70 $319 10 $342 301 $867 10 $990 00 $1 715 00 $1,860 00 5717 80 $798 201 $819 50 $762 60 $814 70 $571 20 $710 2011 $99 40 Retail Price $90 75 $166 14 $193 54 $1,775 36 $2 779 52 $3 581 57 $3,471 231 $3,513,601 $3 876 86 $4,214 53 $349 18 $382 08 $400 64 $1 171 58 $1,358 59 $2,128 00 $2534,401 $775 42 $993 79 $978,43 $918 02 $940 54 $688 131 $926,211 $115 07 $91 65 $169,34 $202 75 $1756 67 $3 108 35 $3 514 621 $3 840 51 $3 943 04 $4,078 85 $3 755 52 $288 90 $499 45 $439 141 $1,109 891 $1 267 20 $2 195 20 $2,380 801 $918 78 $1 021 70l $1 048 96 $976 13 $1,042 82 $731 141 $909 06 $127 23 JUNONO YES Section C C1 C2 C3 C4 The following are to be completed in the Inventory Worksheet Go to the Inventory Sheet This sheet contains a list of all the items the company keep in stock to build the robotics Each part is identified by a unique code Convert the Range A3 153 to a Table Name the table Inventory Name the following ranges A4 A53 Code E4 E53 Cost Price (and any other ranges you feel may prove useful) In F3 F53 calculate the retail price (paid by customer) using the Cost Price and the Markup in L3 (Note the markup may be changed and your calculation should still produce the correct answer ) L4 indicates the maximum stock level In G3 653 create a formula to check if the quantity in stock is over the maximum stock in L4 (this level can be adjusted) If it is over the stock level put Yes in the cell, otherwise leave it empty (do not put No or a space) Formula should copy down, check it is working correctly L5 indicates the minimum stock level while column D indicates if an item is on back order (already been reordered) If a stock item is below the minimum and not on Backorder (is blank) we need to reorder it In H3 53 create a formula to check if the item needs to be re ordered If it does put Yes in the cell, otherwise leave it empty (do not put No or a space) Formula should copy down, check it is working correctly 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 14 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 In L6 calculate how many item codes require reordering In L7 calculate how many item codes are over stocked Use Excel's built in help to investigate the SUMPRODUCT function In L8 calculate the total cost of all inventory items currently in stock 1 Inventory In Stock On Backorder Over Stocked Re Order Reorder Qty Ma roup Maximum Stock Level Minimum Stock Level No Item Codes to re order No Item Codles Over Stocked Total Inventory Value 0 0 Yes Reorder Quantities Item Cost Re Order ty 11 24 2 26 $0 50 Yes $100 $300 YES 35 20 101 Yes 0 $1 000 5 Yes 3 Code 4 C1019 5 M1021 6 M1022 7 R1001 8 R1002 9 R1003 10 R1004 11 R1005 12 R1006 13 R1007 14 R1023 15 R1024 16 R1025 17 51008 18 $1009 19 S1010 20 $1011 21 S1012 22 51013 23 51014 24 S1015 25 $1016 26 51017 27 S1018 29 S1020 29 C1219 30 M1221 31 M1222 32 R1201 33 R1202 34 R1203 35 R1204 36 R1205 37 R 1206 38 R1207 39 R1223 40 R1224 41 R 1225 42 S120B 43 S1209 44 S1210 45 S1211 46 S1212 47 S1213 48 51214 49 51215 50 51216 51 51217 52 S1218 53 S1220 Description Connector Pack MGX 8025 Chip Set MGX 8066 Chip Set Robot Motor Kit Robot Kit 33A Rabot Kit 3BA Robot Kit 3BG Robot Kit SP3 Robot Kit 446 Robot Kit 555 Relays Small Pack Relays Medium Pack Relays Large Pack Servo Motors A21 (4 pack) Servo Motors 21 (4 pack) Servo Motors A21 (8 pack) Servo Motors B 21 (8 pack SMC Pneumatic Part3 44 SMC Pneumatic Part X 22 SMC Pneumatic Port X 33 SMC Pneumatic Part 3 47 SMC Pneumatic Port 3 48 SMC Pneumatic X 200mm SMC Pneumatic X 250mm SD Drive emc Connector Pack em MGX B025 Chip Set lemc MGX 8066 Chip Set leme Robot Motor Ki lamc Robot Kit 33A lemc Robot Kit 3A emc Robot Kit 3BG emc Robot Kit SP3 lemc Robot Kit 448 lemc Robot Kit 555 Leme Relays Small Pack amc Relays Medium Pack emc Relays Large Pack emc Servo Motors A21 (4 pack Jemc Servo Motors B21 (4 pack Jemc Servo Motors A21 (8 pack leme Servo Motors 21 (8 pack Jem SMC Pneumatic Part 3 44 emc SMC Pneumatic Part X 22 lemc SMC Pneumatic Part X 33 emc SMC Pneumatic Part 3 47 emc SMC Pneumatic Part 3 48 em SMC Pneumatic X 200mm em SMC Pneumatic X 250mm Jame SD Drive Cost Price (each) $70 90 $129 90 $151 20 $1,387,00 $2 171 50 T $2 798 10 $2,711 901 $2, 745 00 $3 028 80 I $3,292 60 $272 80 $298 50 $313 00 $915 30 1 $1 061 40 $1 662 50 $1 980 00 $605 80 S776 40 $764 40 $717 201 $734,80 $537 60 $723 60 $89 901 $71 60 $132 301 $158 40 $1,372 40 $2 428 40 $2 745 90 $3 000 40 $3,080 50 $3,186 60 $2 934 00 $225 70 $319 10 $342 301 $867 10 $990 00 $1 715 00 $1,860 00 5717 80 $798 201 $819 50 $762 60 $814 70 $571 20 $710 2011 $99 40 Retail Price $90 75 $166 14 $193 54 $1,775 36 $2 779 52 $3 581 57 $3,471 231 $3,513,601 $3 876 86 $4,214 53 $349 18 $382 08 $400 64 $1 171 58 $1,358 59 $2,128 00 $2534,401 $775 42 $993 79 $978,43 $918 02 $940 54 $688 131 $926,211 $115 07 $91 65 $169,34 $202 75 $1756 67 $3 108 35 $3 514 621 $3 840 51 $3 943 04 $4,078 85 $3 755 52 $288 90 $499 45 $439 141 $1,109 891 $1 267 20 $2 195 20 $2,380 801 $918 78 $1 021 70l $1 048 96 $976 13 $1,042 82 $731 141 $909 06 $127 23 JUNONO YES

Question

can you please help me to work out these instructions and what formulas and which steps to use in excel would be greatly appreciated  Section C C1 C2 C3 C4 The following are to be completed in the Inventory Worksheet Go to the Inventory Sheet  This sheet contains a list of all the items the company keep in stock to build the robotics  Each part is identified by a unique code  Convert the Range A3 153 to a Table  Name the table Inventory  Name the following ranges  A4 A53 Code E4 E53 Cost Price (and any other ranges you feel may prove useful) In F3 F53 calculate the retail price (paid by customer) using the Cost Price and the   Markup in L3  (Note the markup may be changed and your calculation should still produce the correct answer ) L4 indicates the maximum stock level  In G3 653 create a formula to check if the quantity in stock is over the maximum stock in L4 (this level can be adjusted)  If it is over the stock level put Yes in the cell, otherwise leave it empty (do not put No or a space)  Formula should copy down, check it is working correctly  L5 indicates the minimum stock level while column D indicates if an item is on back order (already been reordered)  If a stock item is below the minimum and not on Backorder (is blank) we need to reorder it  In H3 53 create a formula to check if the item needs to be re ordered  If it does put Yes in the cell, otherwise leave it empty (do not put No or a space)  Formula should copy down, check it is working correctly  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 14 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  In L6 calculate how many item codes require reordering  In L7 calculate how many item codes are over stocked  Use Excel's built in help to investigate the SUMPRODUCT function  In L8 calculate the total cost of all inventory items currently in stock  1 Inventory In Stock On Backorder Over Stocked Re Order  Reorder Qty   Ma roup Maximum Stock Level Minimum Stock Level No  Item Codes to re order No  Item Codles Over Stocked Total Inventory Value 0 0 Yes Reorder Quantities  Item Cost   Re Order ty 11 24 2 26 $0 50 Yes $100 $300 YES 35 20 101 Yes 0 $1 000 5 Yes 3 Code 4 C1019 5 M1021 6 M1022 7 R1001 8 R1002 9 R1003 10 R1004 11 R1005 12 R1006 13 R1007 14 R1023 15 R1024 16 R1025 17 51008 18 $1009 19 S1010 20 $1011 21 S1012 22 51013 23 51014 24 S1015 25 $1016 26 51017 27 S1018 29 S1020 29 C1219 30 M1221 31 M1222 32 R1201 33 R1202 34 R1203 35 R1204 36 R1205 37 R 1206 38 R1207 39 R1223 40 R1224 41 R 1225 42 S120B 43 S1209 44 S1210 45 S1211 46 S1212 47 S1213 48 51214 49 51215 50 51216 51 51217 52 S1218 53 S1220 Description Connector Pack MGX 8025 Chip Set MGX 8066 Chip Set Robot Motor Kit Robot Kit 33A Rabot Kit 3BA Robot Kit 3BG Robot Kit SP3 Robot Kit 446 Robot Kit 555 Relays   Small Pack Relays   Medium Pack Relays  Large Pack Servo Motors A21 (4 pack) Servo Motors 21 (4 pack) Servo Motors A21 (8 pack) Servo Motors B 21 (8 pack SMC Pneumatic Part3 44 SMC Pneumatic Part X 22 SMC Pneumatic Port X 33 SMC Pneumatic Part 3 47 SMC Pneumatic Port 3 48 SMC Pneumatic X 200mm SMC Pneumatic X 250mm SD Drive emc Connector Pack em MGX B025 Chip Set lemc MGX 8066 Chip Set leme Robot Motor Ki lamc Robot Kit 33A lemc Robot Kit 3A emc Robot Kit 3BG emc Robot Kit SP3 lemc Robot Kit 448 lemc Robot Kit 555 Leme Relays   Small Pack amc Relays   Medium Pack emc Relays   Large Pack emc Servo Motors A21 (4 pack Jemc Servo Motors B21 (4 pack Jemc Servo Motors A21 (8 pack leme Servo Motors 21 (8 pack  Jem SMC Pneumatic Part 3 44 emc SMC Pneumatic Part X 22 lemc SMC Pneumatic Part X 33 emc SMC Pneumatic Part 3 47 emc SMC Pneumatic Part 3 48 em SMC Pneumatic X 200mm em SMC Pneumatic X 250mm Jame SD Drive Cost Price (each) $70 90 $129 90 $151 20 $1,387,00 $2 171 50 T $2 798 10 $2,711 901 $2, 745 00 $3 028 80 I $3,292 60 $272 80 $298 50 $313 00 $915 30 1 $1 061 40 $1 662 50 $1 980 00 $605 80 S776 40 $764 40 $717 201 $734,80 $537 60 $723 60 $89 901 $71 60 $132 301 $158 40 $1,372 40 $2 428 40 $2 745 90 $3 000 40 $3,080 50 $3,186 60 $2 934 00 $225 70 $319 10 $342 301 $867 10 $990 00 $1 715 00 $1,860 00 5717 80 $798 201 $819 50 $762 60 $814 70 $571 20 $710 2011 $99 40 Retail Price $90 75 $166 14 $193 54 $1,775 36 $2 779 52 $3 581 57 $3,471 231 $3,513,601 $3 876 86 $4,214 53 $349 18 $382 08 $400 64 $1 171 58 $1,358 59 $2,128 00 $2534,401 $775 42 $993 79 $978,43 $918 02 $940 54 $688 131 $926,211 $115 07 $91 65 $169,34 $202 75 $1756 67 $3 108 35 $3 514 621 $3 840 51 $3 943 04 $4,078 85 $3 755 52 $288 90 $499 45 $439 141 $1,109 891 $1 267 20 $2 195 20 $2,380 801 $918 78 $1 021 70l $1 048 96 $976 13 $1,042 82 $731 141 $909 06 $127 23 JUNONO YES Section C C1 C2 C3 C4 The following are to be completed in the Inventory Worksheet Go to the Inventory Sheet  This sheet contains a list of all the items the company keep in stock to build the robotics  Each part is identified by a unique code  Convert the Range A3 153 to a Table  Name the table Inventory  Name the following ranges  A4 A53 Code E4 E53 Cost Price (and any other ranges you feel may prove useful) In F3 F53 calculate the retail price (paid by customer) using the Cost Price and the   Markup in L3  (Note the markup may be changed and your calculation should still produce the correct answer ) L4 indicates the maximum stock level  In G3 653 create a formula to check if the quantity in stock is over the maximum stock in L4 (this level can be adjusted)  If it is over the stock level put Yes in the cell, otherwise leave it empty (do not put No or a space)  Formula should copy down, check it is working correctly  L5 indicates the minimum stock level while column D indicates if an item is on back order (already been reordered)  If a stock item is below the minimum and not on Backorder (is blank) we need to reorder it  In H3 53 create a formula to check if the item needs to be re ordered  If it does put Yes in the cell, otherwise leave it empty (do not put No or a space)  Formula should copy down, check it is working correctly  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 14 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  In L6 calculate how many item codes require reordering  In L7 calculate how many item codes are over stocked  Use Excel's built in help to investigate the SUMPRODUCT function  In L8 calculate the total cost of all inventory items currently in stock  1 Inventory In Stock On Backorder Over Stocked Re Order  Reorder Qty   Ma roup Maximum Stock Level Minimum Stock Level No  Item Codes to re order No  Item Codles Over Stocked Total Inventory Value 0 0 Yes Reorder Quantities  Item Cost   Re Order ty 11 24 2 26 $0 50 Yes $100 $300 YES 35 20 101 Yes 0 $1 000 5 Yes 3 Code 4 C1019 5 M1021 6 M1022 7 R1001 8 R1002 9 R1003 10 R1004 11 R1005 12 R1006 13 R1007 14 R1023 15 R1024 16 R1025 17 51008 18 $1009 19 S1010 20 $1011 21 S1012 22 51013 23 51014 24 S1015 25 $1016 26 51017 27 S1018 29 S1020 29 C1219 30 M1221 31 M1222 32 R1201 33 R1202 34 R1203 35 R1204 36 R1205 37 R 1206 38 R1207 39 R1223 40 R1224 41 R 1225 42 S120B 43 S1209 44 S1210 45 S1211 46 S1212 47 S1213 48 51214 49 51215 50 51216 51 51217 52 S1218 53 S1220 Description Connector Pack MGX 8025 Chip Set MGX 8066 Chip Set Robot Motor Kit Robot Kit 33A Rabot Kit 3BA Robot Kit 3BG Robot Kit SP3 Robot Kit 446 Robot Kit 555 Relays   Small Pack Relays   Medium Pack Relays  Large Pack Servo Motors A21 (4 pack) Servo Motors 21 (4 pack) Servo Motors A21 (8 pack) Servo Motors B 21 (8 pack SMC Pneumatic Part3 44 SMC Pneumatic Part X 22 SMC Pneumatic Port X 33 SMC Pneumatic Part 3 47 SMC Pneumatic Port 3 48 SMC Pneumatic X 200mm SMC Pneumatic X 250mm SD Drive emc Connector Pack em MGX B025 Chip Set lemc MGX 8066 Chip Set leme Robot Motor Ki lamc Robot Kit 33A lemc Robot Kit 3A emc Robot Kit 3BG emc Robot Kit SP3 lemc Robot Kit 448 lemc Robot Kit 555 Leme Relays   Small Pack amc Relays   Medium Pack emc Relays   Large Pack emc Servo Motors A21 (4 pack Jemc Servo Motors B21 (4 pack Jemc Servo Motors A21 (8 pack leme Servo Motors 21 (8 pack  Jem SMC Pneumatic Part 3 44 emc SMC Pneumatic Part X 22 lemc SMC Pneumatic Part X 33 emc SMC Pneumatic Part 3 47 emc SMC Pneumatic Part 3 48 em SMC Pneumatic X 200mm em SMC Pneumatic X 250mm Jame SD Drive Cost Price (each) $70 90 $129 90 $151 20 $1,387,00 $2 171 50 T $2 798 10 $2,711 901 $2, 745 00 $3 028 80 I $3,292 60 $272 80 $298 50 $313 00 $915 30 1 $1 061 40 $1 662 50 $1 980 00 $605 80 S776 40 $764 40 $717 201 $734,80 $537 60 $723 60 $89 901 $71 60 $132 301 $158 40 $1,372 40 $2 428 40 $2 745 90 $3 000 40 $3,080 50 $3,186 60 $2 934 00 $225 70 $319 10 $342 301 $867 10 $990 00 $1 715 00 $1,860 00 5717 80 $798 201 $819 50 $762 60 $814 70 $571 20 $710 2011 $99 40 Retail Price $90 75 $166 14 $193 54 $1,775 36 $2 779 52 $3 581 57 $3,471 231 $3,513,601 $3 876 86 $4,214 53 $349 18 $382 08 $400 64 $1 171 58 $1,358 59 $2,128 00 $2534,401 $775 42 $993 79 $978,43 $918 02 $940 54 $688 131 $926,211 $115 07 $91 65 $169,34 $202 75 $1756 67 $3 108 35 $3 514 621 $3 840 51 $3 943 04 $4,078 85 $3 755 52 $288 90 $499 45 $439 141 $1,109 891 $1 267 20 $2 195 20 $2,380 801 $918 78 $1 021 70l $1 048 96 $976 13 $1,042 82 $731 141 $909 06 $127 23 JUNONO YES

Accepted Answer

The Answer is in the image, click to view ...

Question

can you please help me to work out these instructions and what formulas and which steps to use in excel would be greatly appreciated? Section

Step by Step Solution

Step: 1

Get Instant Access to Expert-Tailored Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

The Quest For A Science Of AccountingAn Anthology Of The Research Of Robert R. Sterling

Students also viewed these Accounting questions

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question