Video instructions and help with filling out and completing How Form 5495 Expiration

Instructions and Help about How Form 5495 Expiration

Music hello this is retail how in this tutorial I will show you how to make aging item list suppose you already have the inventory list as the one you saw here if you do not have one yet you can get it by export from the POS system or you create by your own if you already had one let get started first you need to create the table for the data to do so you need to select all of the data then press ctrl T or command T for MacBook then click okay now you get the table for the inventory list next you need to create the table name for later use to create just type any name in the text box then hit enter now you need a new sheet for aging item list to do so first select some data from the table then copy them next click on the new sheet button and paste in the sheet to get nice table you need to resize the self to do so simply just select the columns then drag them to the right click on the small arrow at the top left corner then double click on the line row and double click on the line column after you got a nice table next you need to add some more columns to the table to do so just type the header name in the cell see the video after you finish adding the header select all the data then click on ctrl T or command T and click OK to create a table on column UPC you need to change to the text format to avoid getting error when adding the data next you need to delete some data from the table just select the data then hit delete in the first row of the description column you need to add the function into the cell the function that will be used is the vlookup function the lookup value in here is the UPC number larae is the inventory list table the column index number is the column number of the inventory list table the range lookup option choose walls to get exact match of the UPC number after you add the function the value of the description that relate to the UPC will show up to get rid of the n/a error you need to add the if function to the vlookup function you the function is used to avoid the n/a error when there is no value in the UPC column apartment and the column cost you need to add the same vlookup function to add the function simply just copy the function from column description and paste in the column Department but you need to change the column index number you in the column cost just do the same as column department and change the column index number in the column quantity no function needed use it only when touting the data in the column total cost the formula that will be used is equal to the quantity time the cost in the column expiry date just change the format to date format in the column day remaining the formula that will be used as equal to expiry date - - day date then you need to change the format to general to get rid of the - number show in the list when there is no value in the column expiry date you need to add via function you in the column day remaining you need to add conditional adding to the cell to add click on conditional formatting then choose the rule the conditional formatting rule that you are using now is if the value is less than zero color the cell red if the value is equals to zero color to sell green you if the value is between 1 and 15 color to sell yellow the conditional formatting allows you to automatically apply formatting such as colors to one or more cells based on the cell value if you want to clear the conditional formatting rule simply just select the cell then click on conditional formatting unclear rules choose option that you want next you need to name the column day remaining table for later use to make it easier to see how many items already expired and how many will expire soon you need to add more counting box on top of the table in this counting box you will use counted function to count the data from the table day remaining table earlier you already named the day remaining table so now just type the name that you already created the criteria is less than zero now that you see two items are less than zero with the red color to count how many item expired today do the same as the already expired box but you need to change criteria criteria for the expire today is zero one item expired today with the green color from the expire soon is slightly different from those two this one you use a counted function but with CS cheerio range 1 is the day remaining table use the same as range and the criteria 1 is less than 16 two is the same as the criteria range one criteria two is greater than zero as you can see in the list there is only one items is between 1 and 15 the sheet name you might need to change it this name you can use month or week as you want now you need to delete the data before using the table remember do not delete the columns that contain the function in the formula now it is time to start adding items to the list but you might need to use the barcode scanner to scan the items if you do not have the barcode scanner to use you can just type the UPC number into