please see below additional sheet
in L6 calculate how many item codes require ordering
in L7 calculate how many item codes are over stocked
in L8 calculate the total cost of all inventory items currently in stock
job_4971 worksheet working
Question Section - C2 Instruction 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:G53 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. C6 In L7 calculate how many item codes are over stocked. Section D Di Use Excel's built in help to investigate the SUMPRODUCT function. In L8 calculate the total cost of all inventory items currently in stock. The following are to be completed in the JO8_4971 Worksheet This sheet shows the Job Card for the most recent job, but it is missing several calculations. The first cost section is for direct materials. Items used for the job are listed by item code. In F13:F18 use calculations to lookup the correct description for each item from the Inventory table. In 113:118 use calculations to lookup and calculate the total cost for each item. (Cost to the company not the client). In 121 calculate the total costs for direct materials. In H25:H31 use a formula to lookup the correct hourly rate for each department from the Information sheet. In 125:131 calculate the labour costs for each task. Question Section - C2 Instruction 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:G53 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. C6 In L7 calculate how many item codes are over stocked. Section D Di Use Excel's built in help to investigate the SUMPRODUCT function. In L8 calculate the total cost of all inventory items currently in stock. The following are to be completed in the JO8_4971 Worksheet This sheet shows the Job Card for the most recent job, but it is missing several calculations. The first cost section is for direct materials. Items used for the job are listed by item code. In F13:F18 use calculations to lookup the correct description for each item from the Inventory table. In 113:118 use calculations to lookup and calculate the total cost for each item. (Cost to the company not the client). In 121 calculate the total costs for direct materials. In H25:H31 use a formula to lookup the correct hourly rate for each department from the Information sheet. In 125:131 calculate the labour costs for each task
