Discover the Best Weekly Billing Format for Inventory Management

Weekly billing format for Inventory

hi everyone today we going to learn how to control stock in Excel by using fifo method it's as very easy to maintain stock using Excel end of this video I'm sure you will get expert in stock control using Excel let's get start by preparing Excel table for stock control I'm going to categorize stock control in three groups let me create header for stock control thanks for your patience I'm almost complete in creating header for stock control table let me resize the columns first category is to insert details about stock in to look different I'm putting color by categories I color this category in gold accent second category is to insert details about stock out now I color orange for this category the third category is to shows balance quantity stock I putting blue color for this now the table is ready to record stock start key and first purchase first of May purchased 11 shares from ABC company in this Excel we going to record Stocks by unit meaning to record 11 shares in 11 rows let me show to how to record start with key and date first of May for the column year and month I'm going to formularize start with equal sign type month Open Bracket select cell C4 close bracket and enter the formula for year almost the same start with equal sign type year Open Bracket select cell C4 close bracket and enter key and supplier details and purchase invoice number inventory is chair quantity is one unit and the price for one unit the reason we key in stock by each unit is to make easier to record when the is stock out you will get clear when I complete the table let me record all the 11 units of chairs by copy and paste to 10 more rows using the same method I'm going to record two more purchases 15th of May bought five units of sofa and seven units of cabinets now let's see how to record stock out by fifo Method firstly I select all the header and set filter 2nd of May sold three units of chairs to record this first we filter inventory to chair then record the three unit of stock out meanwhile we set formula for balance quantity quantity n minus quantity out the formula is equal sign select cell G4 symbol minus select L4 and enter formula for balance amount also the same unit price and minus amount out the formula is equal sign select cell H4 symbol minus select M4 and enter 20th of May sold two units of sofa and five units of cabinets let me complete record for stock out please take note amount record under stock out should key in amount purchase which is cost price do not key in selling price stock in and out already recorded before I move to another sheet let me rename this sheet as Master list this second sheet is to show the balance Stocks by unit and value I'm renaming the new sheet as inventory balance and creating header for the stock summary listing listing Stock's name in this table we need to insert formula to the column balance quantity and amount the formula is equal sign type sumifs Open Bracket in master list select column o comma select column f comma quotation mark type chair quotation mark close bracket and enter the balance chairs is eight let's start insert formula for amount as well the formula is equal sign type sumifs Open Bracket select column p in master list comma select column f comma quotation mark type chair quotation mark close bracket and enter the total value for the balance stock of chair is 160 dollar let we check whether the balance unit and value show in this list is correct filter the inventory to chairs select column o can you see the sum value is 8. so it's correct now select column P the sum shows as 160 meaning the summary table shows correctly the formula is ready to copy to other cells however we need to change the stock name respectively such as the name of chair change to sofa repeat the same to other cells table have no stock balance as of now let me add one purchase for table now can see have balanced stock for table if the stock sold out then again the balance becomes zero it looks everything working correctly this is how to control stock balance in Excel using fifo method thanks for watching Please Subscribe and like the channel for more useful tutorials