Streamline Your Billing with Our Invoice Google Sheets Template for Inventory

Effortlessly manage your invoices and inventory with airSlate SignNow's intuitive solution. Save time and reduce costs while enhancing your document workflow!

Award-winning eSignature solution

Send my document for signature

Get your document eSigned by multiple recipients.
Send my document for signature

Sign my own document

Add your eSignature
to a document in a few clicks.
Sign my own document

Move your business forward with the airSlate SignNow eSignature solution

Add your legally binding signature

Create your signature in seconds on any desktop computer or mobile device, even while offline. Type, draw, or upload an image of your signature.

Integrate via API

Deliver a seamless eSignature experience from any website, CRM, or custom app — anywhere and anytime.

Send conditional documents

Organize multiple documents in groups and automatically route them for recipients in a role-based order.

Share documents via an invite link

Collect signatures faster by sharing your documents with multiple recipients via a link — no need to add recipient email addresses.

Save time with reusable templates

Create unlimited templates of your most-used documents. Make your templates easy to complete by adding customizable fillable fields.

Improve team collaboration

Create teams within airSlate SignNow to securely collaborate on documents and templates. Send the approved version to every signer.

See airSlate SignNow eSignatures in action

Create secure and intuitive eSignature workflows on any device, track the status of documents right in your account, build online fillable forms – all within a single solution.

Try airSlate SignNow with a sample document

Complete a sample document online. Experience airSlate SignNow's intuitive interface and easy-to-use tools
in action. Open a sample document to add a signature, date, text, upload attachments, and test other useful functionality.

sample
Checkboxes and radio buttons
sample
Request an attachment
sample
Set up data validation

airSlate SignNow solutions for better efficiency

Keep contracts protected
Enhance your document security and keep contracts safe from unauthorized access with dual-factor authentication options. Ask your recipients to prove their identity before opening a contract to invoice google sheets template for inventory.
Stay mobile while eSigning
Install the airSlate SignNow app on your iOS or Android device and close deals from anywhere, 24/7. Work with forms and contracts even offline and invoice google sheets template for inventory later when your internet connection is restored.
Integrate eSignatures into your business apps
Incorporate airSlate SignNow into your business applications to quickly invoice google sheets template for inventory without switching between windows and tabs. Benefit from airSlate SignNow integrations to save time and effort while eSigning forms in just a few clicks.
Generate fillable forms with smart fields
Update any document with fillable fields, make them required or optional, or add conditions for them to appear. Make sure signers complete your form correctly by assigning roles to fields.
Close deals and get paid promptly
Collect documents from clients and partners in minutes instead of weeks. Ask your signers to invoice google sheets template for inventory and include a charge request field to your sample to automatically collect payments during the contract signing.
Collect signatures
24x
faster
Reduce costs by
$30
per document
Save up to
40h
per employee / month

Our user reviews speak for themselves

illustrations persone
Kodi-Marie Evans
Director of NetSuite Operations at Xerox
airSlate SignNow provides us with the flexibility needed to get the right signatures on the right documents, in the right formats, based on our integration with NetSuite.
illustrations reviews slider
illustrations persone
Samantha Jo
Enterprise Client Partner at Yelp
airSlate SignNow has made life easier for me. It has been huge to have the ability to sign contracts on-the-go! It is now less stressful to get things done efficiently and promptly.
illustrations reviews slider
illustrations persone
Megan Bond
Digital marketing management at Electrolux
This software has added to our business value. I have got rid of the repetitive tasks. I am capable of creating the mobile native web forms. Now I can easily make payment contracts through a fair channel and their management is very easy.
illustrations reviews slider
walmart logo
exonMobil logo
apple logo
comcast logo
facebook logo
FedEx logo
be ready to get more

Why choose airSlate SignNow

  • Free 7-day trial. Choose the plan you need and try it risk-free.
  • Honest pricing for full-featured plans. airSlate SignNow offers subscription plans with no overages or hidden fees at renewal.
  • Enterprise-grade security. airSlate SignNow helps you comply with global security standards.
illustrations signature

Using an invoice google sheets template for inventory

Managing your inventory efficiently can signNowly impact your business productivity and profitability. One effective tool for this is utilizing an invoice Google Sheets template for inventory. This guide will walk you through the steps of leveraging airSlate SignNow for document signing, making the process seamless and efficient.

Steps to use the invoice Google Sheets template for inventory

  1. Open the airSlate SignNow website in your preferred web browser.
  2. Create an account for a free trial or log into your existing account.
  3. Select the document that you wish to eSign or send out for signatures.
  4. If you plan to use this document frequently, convert it into a reusable template.
  5. Open the document to make necessary edits, such as adding fillable fields or essential information.
  6. Complete your document by signing it and including signature fields for your recipients.
  7. Click on 'Continue' to configure and send an electronic signature invitation.

airSlate SignNow offers a powerful solution for businesses looking to streamline their document signing process. With its diverse features, it ensures a high return on investment without unnecessary costs.

Its user-friendly interface and scalability make it perfect for small to mid-sized businesses. Plus, you can rest easy with transparent pricing and round-the-clock support for all paid plans. Start today and simplify your eSigning process!

How it works

Upload a document
Edit & sign it from anywhere
Save your changes and share

airSlate SignNow features that users love

Speed up your paper-based processes with an easy-to-use eSignature solution.

Edit PDFs
online
Generate templates of your most used documents for signing and completion.
Create a signing link
Share a document via a link without the need to add recipient emails.
Assign roles to signers
Organize complex signing workflows by adding multiple signers and assigning roles.
Create a document template
Create teams to collaborate on documents and templates in real time.
Add Signature fields
Get accurate signatures exactly where you need them using signature fields.
Archive documents in bulk
Save time by archiving multiple documents at once.
be ready to get more

Get legally-binding signatures now!

FAQs

Here is a list of the most common customer questions. If you can’t find an answer to your question, please don’t hesitate to reach out to us.

Need help? Contact support

What active users are saying — invoice google sheets template for inventory

Get access to airSlate SignNow’s reviews, our customers’ advice, and their stories. Hear from real users and what they say about features for generating and signing docs.

I couldn't conduct my business without contracts and...
5
Dani P

I couldn't conduct my business without contracts and this makes the hassle of downloading, printing, scanning, and reuploading docs virtually seamless. I don't have to worry about whether or not my clients have printers or scanners and I don't have to pay the ridiculous drop box fees. Sign now is amazing!!

Read full review
airSlate SignNow
5
Jennifer

My overall experience with this software has been a tremendous help with important documents and even simple task so that I don't have leave the house and waste time and gas to have to go sign the documents in person. I think it is a great software and very convenient.

airSlate SignNow has been a awesome software for electric signatures. This has been a useful tool and has been great and definitely helps time management for important documents. I've used this software for important documents for my college courses for billing documents and even to sign for credit cards or other simple task such as documents for my daughters schooling.

Read full review
Easy to use
5
Anonymous

Overall, I would say my experience with airSlate SignNow has been positive and I will continue to use this software.

What I like most about airSlate SignNow is how easy it is to use to sign documents. I do not have to print my documents, sign them, and then rescan them in.

Read full review

Related searches to Streamline your billing with our invoice Google Sheets template for inventory

Simple invoice google sheets template for inventory
Google Sheets inventory template
Google Sheets inventory template free
Invoice google sheets template for inventory free download
Free invoice google sheets template for inventory
Free inventory spreadsheet template
Invoice google sheets template for inventory excel
Invoice google sheets template for inventory download
video background

Invoice google sheets template for Inventory

welcome back to this new video tutorial in Google seeds and here in this tutorial we are going to create a stock inout balance tracking template with reorder planner and first of all I will briefly explain the features of the template or the operating guideline of the template after that we will head into creating these from the step byst step process so here we have two seeds one is the summary and another one is the tracker sheet and here in this summary sheet the overview of the stock or sales or purchase will be given in this uh First Column the serial number is kept the second column contains of the item name then the unit of measurement after that the unit price then opening stock this opening stock means the quantity of the stock or items at the beginning of of the period for example if you are going to make this template for the year 2023 the stock that you have in your warehouse at the beginning of the 2023 or first of January 2023 will be given here and this we need to put manually if it is for the monthly basis then the stock at the beginning of the month should be kept here the next one is the purchase column and this purchase column gets values from this tracker sheet by using the formula that you you can see over here then the sales column is also formula based the sales is also automatically captured from the tracker sheet the next one is the balance stock by subtracting the sales from opening stock plus purchase we will get the balance then the Safety stock is calculated here and the Safety stock is also formula based and here the formula used for calculating Safety stock is the maximum daily requirement multiplied with maximum lead time and then minus average daily requirement multiplied with the average lead time there are other approaches of calculating Safety stock as well but this is one of the most commonly used uh Safety stock calculation formula that's why I have used this formula over here the next one is the goods in Transit this is also captured from this tracker sheet Goods in transit means the the product that you order to your supplier and there is always a gap between the order date and actual physical product receiving date so that is called the goods in transit and here for example the average uh git of 7 days is taken and the maximum imum Goods in transit period of 10 days is taken over here for example and finally the K colum is will suggest us for reorder requirement or not when the stock goes below the appropriate level then this reorder required suggestion will be given like here you can see the Apple has only seven unit left 7 kg of the apple is left that's why it is giving us reorder required there is no any quantity in the goods in transit that's why we need to provide the order to our supplier because we need Safety stock of 287 unit for Apple this Safety stock will be used for the transit period like if I ordered this Apple today I will only get the product in my warehouse after 7 days so to supply the demand that will create in 7 Days period we need the product immediately here that's why as soon as we see this reorder required message here in this section we have to place the order and when we place order to the supplier then this message will be removed from here like if I go here in the tracker sheet and place the order of Apple here in the stock order section for example if you double click on it you will get the calendar date picker from which you can pick up the date dat and here I'm going to order for apple and the unit of measurement here will automatically appear the order quantity I am going to decide by an analyzing my stock label here I have only 7 unit left my safety stock is 287 so here I can place order of 300 kg so this we can decide by looking into these two values here and here I will go in the tracker sheet and put the order of 300 kg here and when I put the 300 kg then here the formula is missed that's why it is not updated if I drag the formula down here the kg will automatically update as I select the values here and the 300 un kg is already placed the order now if I go to the summary sheet here you can see the reorder requirement suggestion is already removed from here because we already have the product in transit 300 kg that appeared here so in this way this is a dynamic and when we keep on tracking our sales record here in this uh stock outter sales then it will automatically reflect currently we have the sold quantity of 93 unit of Apple but if I go into the tracker and update upate a sales of Apple here let me quickly update one date here then choose Apple from here and the kg will automatically appear and here I'm putting the quantity of suppose that in that day Apple was sold 40 kg now when I click when I put 40 here if I go to the summary seet here you can see again the Apple's uh stock has uh the sales has increased but here currently it it has gone to negative that's why here let me deduct because because practically negative stock is not possible that's why here I'm only giving 12 unit of apple and if I go here now you can see it is still on negative that's why here I'm going to choose another product here like the grapes I'm taking and currently grapes size the sales of 184 and if I go here then change the Apple to Grape suppose that here you can see now the grapes stock has S size become 196 and the balance stock has become 14 unit so in this way the values that we put here in the tracker will have automatic impact here in the summary and then the thing here you need to uh consider is when you receive this ordered product after few days in your Warehouse then you have to make a entry here in the stock in section like this product I ordered today after 7 days I will get the product in my warehouse after getting the product in the in the warehouse then here we have to go then put here the date of receip the product we have to choose from here and the quantity received is 3 300 kg here after you get this ordered product here in the warehouse then you have to make this as received or you have to mark this as received because this Transit Goods is now no more in transit it already has received in the warehouse that's why this we need to do here this is most important because if we missed to do this then it will impact on our uh inventory this will double count that's why it with this we must do now if you go to the summary then you will not get you will not get any reorder requirement because the stock is already in in the warehouse it is now 307 kg so in this way the system is fully Dynamic here now we will head into creating this template from the step by- step [Music] process now we are in a completely blank Google sheet document and here start uh creating the template first of all give the heading of the template that is stock [Music] inout balance tracker with automatic reorder suggestion this is the title of our template so you can increase the font size by going here in this button make this bold by clicking on the B and then here let's uh rename this as the summary seat by Double C clicking on the seat name type here summary then just duplicate this seat by right clicking then click on the duplicate and the duplicated seeds name will be the tracker so double click on it then type here tracker so this two seat we require here and go to summary sheet and here create the uh column headers like serial number first then item name after that it is the unit of [Music] measurement then it will come here the price Pro unit after that the opening stuck then the receip or it is also known as stock in the next one is the sales or it is stock out here after that we will get the balance by using formula balance uh stock then we need to calculate here the uh safety Stu safety Stu after that we need the git meaning Goods in transit and finally we will see here the status of the stock so these are the column headers we require here after that just select them all then use this text r W ing option by going here click on WP text after that the text will fit in the column then I will select all this by uh and do some formatting here by going here then I'm taking this Center alignment and from here I'm taking the middle alignment then for the title I'll take the left alignment from here so after doing this formatting just select your column head headers then apply the background color by going here here I'm taking this color then I'm making the text bold now after making this then here we will put the product name so here let me quickly fill out some products here or the items here now I have created the list of the item their uh unit of measurement and price per unit here the then the opening stock we have to keep manually for example this is a system for uh 2023 year 2023 so here I'm putting year 2023 and here on the opening inventory the stock in your warehouse at the beginning of the period or 1 of January 2023 will come here which I am giving some random values so these are my opening inventory and all the other column here will be calculated by using the formula here in the balance stock I am using formula of this opening stock plus the received quantity minus the sold quantity this will give me the balance and here the auto fill is suggested just click on this tick mark now it get automatically failed and here in Safety stock git startup of invoice these will have the link with the tracker sheet so let's go to the tracker sheet and create here the uh tracker sheet and here first off all give the date then here appear item name in the next it will come the U and in the next it will come the quantity and this will be for the stock out so here insert a row and give the header that is stock out or it is also known as the sales similarly do this for other receive and Order also but before doing that just select these four rows four CS then go here in the Marge and center of option merge sales now take it to the center position by choosing the center alignment then just copy this contrl C create a copy here in the F column contrl V now decrease the wi of the E colum similarly cop create a copy of this here contrl V now just decrease the width of the J column and here it will be stock in so change this to stock in and it is also known as the purchase so here make it purchase and in this stock in this we have to make it stock ordered stock ordered and in the stock ordered we need to add a column that is the status of receip so here add a column that is status now here just select these five cells and Marge these together now here you can give the uh color highlight for the headers by going here then you can make it bold do the same for these two also now after giving the color for the titles then here just drag this till the row number five here to fix this uh title part of the report just go here and take this to the row number five by dragging this now when you go down or when you scroll down the title will remain constant here after this here let's estimate how many records you will have for stock out in a year so if you have suppose that 1,000 records then here go to the row number 1,000 and here just uh below 1,000 here type one one one and one this will be this is required here just to apply formula later when applying formula it will be easier for us to select the row number number till 1,000 that's why I have given one here and in the stock in section Also let's estimate how many records you will have for year here I'm assuming there will be 300 records in a year in for stock in that's why I'm taking the row number 300 here and this you can change ing to your uh volume of transaction if you have more transaction then you can take it more here I'm just taking this for example so here I'm giving one one and this will be useful when using the formulas here in the cell that's why here I'm putting the values one this you will understand later what is the logic behind putting one here after doing this now we need to link the item name here in this item name column so to link the item name simply select on the sale then press shift control down that will take you to the end of the Thousand row and here go on step up and here we need to do the validation here so go to data menu then go to data validation and here go to the add rule option in the criteria choose this uh drop down from arrange option then here in this ribbon or in this bar we have to select our items so go to the summary sheet and then select the item name from here in this way now here here I'm doing this again go to this then click on this select data range option and here now select the range from this item name range of the summary sheet and while taking this you can include some more row here because in future some if some products or items gets added then in that time this will automatically get the added value as well in the drop down that's why select some more row here then click okay now here you will see when you click on done button you will see the drop down created in the Tracker seat here you can see the the drop down high is created in stock out and here we need to select from the first row so I'm again doing this because it is not selected from first row then I'll go here in delete rule then I'll do this again by add rule then go to this drop down from range then I will put the uh range here by selecting clicking on it then go to summary then I will take these item names then click Okay now click on the done button then close this data validation now if you go to the tracker here you can see the validation is added by clicking on this you can choose the product name from here so do the same thing for this stock in and stock order of stock order here in the item name create the validation by following the same method Now to create a date uh peer calendar here in the date section we need to again use the data validation so to do that first of all select from the beginning to the end by control shift down then again go to data then go to data validation and here in this time we have to click on ADD Rule and here we have to choose is valid date option from the criteria is valid date now click on the done button then close this and after that when you double click on the sale then you will get a date picker calendar as you see here and repeat the same step for the date in stock in and stock ordered now you can see when you double click on the date column you will get the calendar here after that here we need to display the unit of measurement and here in this case the unit of measurement is uh same for all the items so here you can use the Bela formula to get the unit of measurement automatically when the product is selected here so the formula here will be is equal to B look up open the bracket the sarce key will be this item name that will given here in the drop down comma the range will be the summary seeds item and U so select these two till the row number 14 here then fix this reference by pressing F ke in the keyboard then give a comma and in the index it will be two comma and for each SED we have to give zero for exact match then close the bracket then press enter now you will get na a because nothing is selected here when you select product the kg will be given and here to remove the na a error just combine the if error formula before this by double clicking then combine if error bracket open go to the end of the formula give a comma and type double quotation to display blank now close bracket press enter now you will get blank when nothing is selected then you can drag this same formula till the row number th000 here so copy the formula down just uh here click here then contrl C then here select control shift down then enter now you'll you'll get all the formula copied down and you have to repeat the same step here for this stock in and stock ordered and here for that you can also directly select till row number 300 just uh select from here or you can also use the same formula here so let's apply the same formula let me quickly uh do this by pausing the video I will apply this formula here now the formula is applied here in this unit of measurement which will be automatically calculated when we add product here suppose that if I add product here you can see the kg will be autog given now here in the status of stock ordered we need to create a validation for receipt so just select it from the beginning till the end then go to data menu then go to data validation and here go to add rule option and here choose this drop down and in the option one one right here received and delete the option two from here then click on the done button then close this validation rules now here you will be able to select received when the products gets received in your Warehouse so this will we will do later after creating these uh data headings here and applying these validations now here let me fill out some stock out or sales record by double tapping then I'll choose the first date suppose that in the first of 2023 uh in in first of September 2023 the apple apple is sold and the quantity sold is suppose 24 unit similarly on the same date another transaction of Apple happened and this time the sales is 12 unit and here I'm also going to take one more record in second of September there is one new sales for orange quantity is 36 unit for example similarly in 4th of September there is a sales of pineapple and it is 24 so in this case when the product is repeating here in the same day at that time we need to make this or we need to combine this because in the Safety stock calculation formula there we have the formula that is I'm here giving the formula by going to this insert note the formula is maximum daily requirement multiplied with maximum lead time minus the average daily requirement multiplied with the average lead time this will be the formula and here to identify maximum daily requirement we need to combine the Same Day sales here because in the same day there is two records and when you add these two this is the sales of 36 unit in this day that's why to identify the maximum of the day here at the last of this template we will uh create some helper columns so here just combine the date and item date and item just beside it create a unique entries here so here use the here type the unique date and item and just beside it we will display the item for that so here let's say item and in this date and items use the simple formula is equal to then go to the stock out section then click on the date type the and symbol in keyboard then click on your item name then press enter now you will get the combination of date and item here and then you need to drag this till the last uh row here or till the 1,000 row so simply copy this contrl C then go click on it press control shift down it will take you to the Thousand row here contrl V now the same formula will be copied down till row number 1,000 here it is displaying nothing because nothing is given in the cell and here you can see till here it is created the combination of date and item here we need to generate a unique list from this so here press is equal to then type unique open the bracket and in the range select this from the beginning till the end in this way close bracket press enter now you will get the unique item list here because here if you notice if you see here the apple is repeating two times for the same day here you can see this two values is repeated that's why only one is taken from here in the unique data and item and here in the item we will now display the item associated with this combination of data an item so to extract the item only from this use this formula is equal to then we need to take the Apple from the right that's why the formula here will be the right formula right Open Bracket then here in the string click on this then give a comma and in the number of character we need to substract this 1 2 3 four five characters from the whole length of the Char characters so for that here use the lane Formula First Lan Open Bracket click on this close bracket minus then five because we have five digits here in the date now close this then press enter now you will get the product that is associated with this combination of date and item now click on this to autofill and here the value error is given because this cell does not contain anything that's why the value error has given here when this sale uh contains some value then this will be automatically given suppose that if I go here and do a new entry in this in 4th of September I'll create here a new entry for the product that is mango in this case if I go here a new entry will be created and the Val value error has removed from here and we need to do this till the 1,000 row here and this uniform function will automatically populate but for this we need to go till the uh last row so just copy this formula contrl C then and select from this sell to the last by control shift down then control B then the formula is now copied down till the last row and if you want to remove the value error you can combine the if error here so go to the formula double click go to the beginning then type if error open the bracket go to the end give a comma type double quotation close bracket and press enter now simply copy this contrl C then select from the second row till th row control B now the if error will remove the value error that was displayed now after creating this helper column now we need to go to the summary sheet go to the summary sheet and here we need to use formula to capture the receipt and uh sales here from the tracker and for this it is better to uh name the range for this item name and the quantity because after when we use formula that that time it will be easy so select the item name from the beginning till the end here by control shift down and give a name of this but after selecting just go here in the name box and give a name of this age item underscore name underscore out this is the item name of stock out so item name out give the name of item name out then press enter and here in the quantity of stock out just select from the beginning till the end then give a name of it is quantity underscore out press enter similarly for stock in section select the item name from the beginning till the end then here given name is item underscore name uncore in then hit enter and for the quantity of the stock in section give a name is just select it then go here then put it quantity underscore in now press enter similarly for the stock ordered select the item name range from the beginning till the end then give a name is item underscore name underscore ordered then press enter similarly for the quantity of the stock ordered give a name is uh go to the name box then type here quantity underscore ordered then press enter and finally select the status column from the beginning till the end then give a name of it as status so after giving name to the range now it will be easier for us to apply formula here in the summary sheet so let's go to the summary sheet it and here in the uh received section or Stu in section we need to get the uh values that is purchased so here the formula will be the sum if formula so here is equal to press is equal to then type sum if open the bracket and here in the range of the Su if formula we are going to get the stock in quantity based on the product name or item name that's why here in the range we have to go to the we have to give the name that we defined for the item of the uh stock in so here just type item name in this was the name given for the items that is given here in the stock in section so click on this then give a comma and here in the criteria we have to select this item name then give a comma and in the S range we have we have to pass the quantity of in so select this quantity in close bracket now press enter now we will get the formula here just double click on it then it will get it will get the receips and here in here for stock out or sales use the same formula again is equal to Su if open the bracket in this range we have to give the item name range of the out item so select this item name out name that we created give a comma in the criteria select the product name that is given here in give comma and in the sum range pass the quantity out so click on quantity out then close the bracket and press enter and you will get the sales or out values here click on autofill now here you can see the sales is automatically captured from the tracker sheet similarly for the goods in transit also we have to use the Su if formula and here in this case we have to use the sum ifs because we will have two condition here for goods in transit the first condition is this item name should match with this stock ordered item this is the first condition and second condition is the is the value in the status column should be blank so by using these conditions we will use the some if formula here so press is equal to then type some ifs open the bracket and in the sum range we have to pass the quantity of ordered so click on this quantity ordered give a comma in the criteria range one we have to use the item range of the order section so here give item name ordered in a qu criteria range one then give a comma Now in this criteria one select the product name from here then give a comma and in the criteria range to we have to give the status range from the tracker sheet so here type status status this was the name we created then give a comma and in this criteria range two we have to give double quotation to display blank so type double quotation here then close the bracket and press enter now it will create the git value here or it will display the git value now just drag the formula down and if you go to the tracker sheet now and here if you put a order just for example I am double clicking selecting the first date then I'll go to the item name choose the product here that is Apple for example then quantity order is suppose that 50 kg in this case when you go to the summary sheet here you can see the git is automatically captured and when we make this or when we make it received or when we mark it as received then the git will not be calculated if you go here now the git is given at zero so we have used the sum if formula to capture the goods in transit value here in the summary sheet now after uh generating this Formula First of all let's do some formatting here by selecting them till row number 15 give a border by going here in the all border option and in the row number 15 let's create the value of of inventory by merging these sales by going here in the merge and center option and write here value of inventory then select this line give a color highlight and here use this formula to get the value of inventory is equal to sum product formula sum product then in the range one or array one select the price range give a comma and in the array two select the opening stock then close the bracket and fix the price range by clicking over here then press F key in the keyboard now press enter then click on it go to the plus button and drag this till the um Safety stock till the git stock or Goods in transit now the value of the inventory will be calculated here in this row number 15 make this bold now the value has successfully calculated here and now the in the Safety stock we have to use formula so let's start using formula here in the Safety stock column to calculate the Safety stock here we need to know our average lead time and maximum lead time so here suppose lead time is uh 7 days for average and 10 days for maximum this is our lead time it takes on an average 7 days to receive the product in warehouse and sometimes it also goes up to 10 days so these two we need to know here after knowing these two um lead time values here then just go to the tracker sheet and here in we need to add a more column in the helper here we need to display the quantity sold in this specific date and product or item combination so for that here we have to use a formula is equal to then some if open the bracket here in this range of the criteria select this date and item range that we created here by joining date and item select it from the beginning till the row number th then fix this by pressing F key in the keyboard now give a comma in the Criterion select the unique combination of date and item on this sale then give a comma and in the Su range give the out quantity range by typing here out quantity out now close the bracket and press enter now you will get the result here you can double click on it [Music] to fill it down or if this doesn't work simply copy control C then select this sale then go till the down then control V2 f the formula now after that here you can see the value is now calculated here you can see in this same date there was two records if you go here in the stock out here you can see on the same date there was two record of Apple 24 + 12 unit so this 36 unit should be uh added here in this so here you can see the 36 is added in this quantity of Apple so this will now identify the maximum sales of Apple here in a single day so to identify maximum sales we have worked this year now go to the to calculate the Safety stock here we need to multiply the maximum daily requirement with the maximum lead time then subract average daily requirement with the average lead time so here to identify maximum daily requirement for this product use the Maxs formula so press is equal to then type Maxs formula open the bracket and here in the range of the max ifs we have to select the quantity range that we calculated here so select this quantity range from the beginning Till the End by dragging down control shift down key now we have to fix this range so press F key in the keyboard that will fix then give a comma and here in the criteria range one we have to select the item name that is given here so select this from the beginning to the end then Al also fix this by pressing F4 key then give a comma and in the criteria one or Criterion one we have to go to the summary then click on the item name that is Apple here then close the bracket and press enter now it will get 36 is a maximum daily requirement or maximum daily sales of Apple for now when we update value here in the stock out section then it will automatically update the maximum daily requirement if in single day more than this quantity will sold later then the maximum will be captured here now we need to multiply this with the maximum lead time days that is 10 here so go in the formula then multiplied by or multiplied with 10 now close the bracket go to the beginning of the max ifs and give a opening bracket here now we need to substract the average daily requirement into average lead time so here give the minus symbol then again open a bracket then here give the average if formula now average if formula average if then open the bracket in the criteria range of the average if we have to go to the tracker sheet and select the item range from here click on the item range and it is given from u5 to u1000 so here you can change this to u5 two type the colon symbol then Type U 1,000 then fix this by pressing F4 key then give a comma and in the Criterion of the average if go to the summary sheet and select the first product name first item name then give a comma and in the average range again go to the tracker sheet and select the quantity range from here click on the quantity then here change this to B5 to b1000 then fix this by pressing F in the keyboard now close the bracket then we need to multiply this by 7 multiplied with seven now close one more bracket then press enter now you will get the Safety stock for Apple and you can now drag the same formula for other products here till the pineapple and here there are two product given is error because we I haven't filed any information for the papaya and kiwi that's why divided by zero error is given if you add a new record for papaya here in the stock out or for kiwi then you will get the Safety stock for example here I am giving a date of 5th of September and I am choosing the product kiwi here and suppose that the quantity sold is 24 kg in this case if you go to summary here you can see the Safety stock requirement for kwi is given as 72 and the Safety stock calculation will be more accurate when we add some records here because currently we have the sales of only single day that's why the Safety stock may not be accurate here when we have very few data but once we update a data for 1 month then the Safety stock will be more accurate rate because in one month the pattern of sales can be understood so after calculating the Safety stock here I'm also adding a record for papaya as well so that the divided by zero error will remove so here I'm taking this date choosing the product papaya and taking the quantity 28 kg now if I go to summary here you can see for papaya also the Safety stock is calculated now here we have to display the status of stock whether we require reorder or not and for this we have to use a formula and the formula is is equal to then we have to compare the balance stock with the Safety stock we have to compare the balance stock plus the goods in transit with the Safety stock to sense the requirement of reorder so the formula here will be the if formula so press is equal to then type if open the bracket The Logical expression is if the balance stock plus the goods in transit here we need to close bracket for this open a bracket here so if the balance stock plus the goods in transit is less than so type the less than symbol here is less than the Safety stock requirement click on Safety stock then give a comma Now this formula will work as a logical expression and if the balance plus the git is less than Safety stock in that time the value if true will be re order required double quotation close double quotation open then reorder required double quotation close now give a comma and in the value if false we have to type double quotation twice to display nothing now close the bracket and press enter now here autofill is suggested click on this tick mark now here you can see when the stock label is down reorder required suggestion is given and here to highlight the reorder required you can select the sales then go to the format then go to conditional formatting and here you have to go to the custom formula option go to custom formula is and here in the value or formula or here you can choose the another option as well here you can go to this text contains option and in the text contains write re order required and if the reorder required text is given here we can click on it and then choose this yellow color now click on done button then close this and when there is reorder required it will be highlighted with the yellow color now here you can see the stock balancy stock is 62 unit but we need 108 unit in the Safety stock for orange that's why it is given as reorders required and when you see the reorder required you have to go here in the tracker seet then go to this stock order item and you have to place the order to your supplier so here just go in this select the date of order then select the item that is orange here then to identify the uh quantity order suggestion you have to go here in the summary sheet and here you have to check what is the stock level we have 36 unit only and we need 108 unit in Safety stock so so in this case we can plan for 100 unit of orange 100 kg so give here 100 kg order now when you put order here if you go to the summary the re order requirement is already removed because we already have the product in the transit now and when we receive this product in the warehouse in the stock in section here I am going to make a entry I order the product in 6th of September and suppose that it received on 13th of September the product received is orang and the quantity is 100 kg when you enter this order here you have to make it is received now because this has already shifted to this section that's why we have to make it received after making this received then this quantity will have no impact anywhere in this system now if you go to the summary here you can see there is no more transit for orange and the reorder required suggestion is also removed so in this way the system is working and here if I go to the uh tracker sheet and make a entry of some product for example currently I have 164 unit of Apple in stock if I go to tracker sheet and then go to this date and make a entry of Apple again this time if I increase the quantity sold of Apple here the Safety stock requirement will also increase currently the Safety stock requirement is 234 234 unit and if I go here and make this sales of 50 unit here in this case if you go to summary here you can see the Safety stock requirement for apple is given is 199 it is now increased because the maximum daily requirement is increased here that's why the Safety stock requirement is also increased in the summary sheet and here for Apple also it is suggesting for reorder required because the Safety stock required requirement is 199 but we have only 114 unit in the balance that why that's why it is suggesting for reorder required and if you go here in this order sheet and put the order for Apple suppose that in 8th of September if you put order for apple and the quantity is suppose 100 unit or 100 kg now the reorder suggestion will be removed from here so in this way the system is now fully Dynamic we have completed creating all the formula here and uh after creating this template now start now you can start working here every time a sales happens here you need to keep record of the sales by simply selecting the date product and quantity when you receive your order product in the warehouse you have to trans transfer it from this stock order section to the stock in section and after transferring this here in the stock in section you have to make this is received this is very important here otherwise the order suggestion will throw you a wrong message if you forget to do this here now we have completed making this template and this much for today's video thank you very much for watching if you like the video I request you to like and also share this video to your family and friends having interest in learning Microsoft Excel thank you very much for watching this video I request you to subscribe the channel and also hit notification Bell icon to get regular video updates in future once again thank you very much for watching this video we will again meet on next video

Show more
be ready to get more

Get legally-binding signatures now!