Collaborate on Create Invoice in Google Sheets for Procurement with Ease Using airSlate SignNow

Create invoice in google sheets for Procurement

Hello friends welcome to the new tech Navigators my name is PK in this video we will explain you how to create a procurement procurement Trier in Google seats as you are seeing my screen I have created this Dynamic procurement trigger with the dashboard in Google seat so the first seat is the dashboard sheet and here we are showing the analysis of the tracker like how many quantity we have ordered and how many we have received what is the remaining quantity this is the total cost then how many orders are delayed in percentage then this is the order status so how many delivered partially how many completed and how many are pending then we are showing here vender wise remaining quantity so this is these are the vend the name then these are the remaining quantity now this is the item by cost or the product byse cost on the x-axis you can see the product name and then we are showing here total cost now here we are showing the purchase date by cost so you can see on the AIS we have the purchase date then on this line chart we have shown the cost now I will show you the data of this so this is the dat data seat and here you need to input the data once you enter the data the dashboard will be refreshed automatically this is totally Dynamic now let's come to the data seat and I will show you how you can enter the data so you can simply double you can simply enter the item name here let's say I'm putting here uh laptop okay then you can select the vender name so let's say I'm selecting this one then purchase date just double click here and select a date number of quantity you want to order let's say I'm saying 20 and if it is all pending then you can keep it as it is and if you have already ordered it and you have received some of the quantity so out of 20 let's say I'm saying I have received 15 just enter so the remaining quantity is now five and the unit price you can enter here so let's say I'm entering here 300 then the this is the total cost is automatically calculated these these green highlighted light green highlighted columns are the formulas and they will be calculated automatically now expected delivery date just double click and you can select any date here so it will give whether it is delayed or on time okay now this is the input data and on the basis of the that input data we have created this dashboard now I will show you step by step how we can create this in Google seat so let's start without delay I'm going to add a new blank sprad sheeet now so I'll go to the file new and I'll click on sprit sheet so this is the blank sprit sheet for now what we will do we'll just copy this data whatever we have used here to save the time so let's copy this whole data and we will paste as a value so here we can paste as value press shift control and V okay so we have whole data Now quickly we will format this select and align this Center and middle reduce the font size up to 8 select the header increase the font size around N9 make it bold and change the background color let's say I'm taking this color and take the font as white increase the column WID slightly okay now on the top you can input some title of this so select this range and just go to the merge cells increase the column width and take some dark background let's say I'm taking this one font take white make this Bold and the font name I'm taking areial LED let's say here I'm putting input data or you can put whatever you want just go to the horizontal alignment make it Center vertical one also increase the font size around 20 and increase the column width also reduce the column BD for the second column now go to the view so and uncheck the grid lines so this table is ready now if you want to put some border you can do that so just select whole data and then go to the borders take the light green color let's say this one and just click all borders so we have the borders here now for the Bandon name we can convert it into a drop- down so select this just right click and create the drop down so the drop down is automatically created just click on done now for the date we can convert this into a calendar so if you double click here it should be a calendar but it is not so what you can do just select this range and then first thing make sure you have a proper number format so go to the number and take this number format then right click and go to the dropdown and in the criteria just select the is valid date click on done now if you if you double click anywhere in this column that calendar will be popped up okay now let's go to the this expected delivery date also and we will do the same thing here so we can just apply this formatting also you can just select till the bottom and just click on pent format and now come to this column here just click so same formatting is applied if I double click okay so we will repeat this activity for the expected delivery date also just select this till the bottom and go to the number format number and take this number format then right click go to the drop- down and select here is valid date done close this if you double click anywhere the calendar will be popped up all right now we will identify the column wherein we have to put the formula so first we will put the formula and the remaining quantity just delete this and fill light green color or some light gray color whatever you want and then here also just delete and take light gray color and then we will do this here in the order status and or delivery status just delete all right now we will input the formula here so first the remaining quantity will be order quantity minus receive quantity okay but we will put this formula using the array function so just use the array formula and then take this quantity Colon D minus this quantity column e and just close and hit the enter so we are getting the values automatically but it has taken at bottom also so what you can do you can just input one more formula here just before this use if order quantity just select and cut this and use here if the order quantity this one column D equal to blank then blank else take this one just hit the enter you can see we have the value still here only now the total cost total cost will be f * G the total cost will be e mtip g received quantity multiply unit price so we can use this formula for now just copy and paste here copy and paste here and just change this one which is E4 colon e multiply G4 colum G and just hit the enter so we are getting here total cost you can see okay now the order status so order status whether it is completed or it is partial or it is pending okay so we will check if the remaining quantity is zero then it is pending then it is completed if it is less than order if it is greater than zero and less than order quantity then it will be pending and if it is equal to the order quantity that [Music] so we will put here the logic if remaining quantity is equal to Z that mean then it will be completed another if remaining quantity is less than than order quantity then it will be partial else it will be pending just hit the enter so you can see if I'll change the receive quantity as 100 then it will be completed and if it is zero then it is pending okay now let's put the array formula here and here in the array formula we will use if D4 column D is equal to blank then blank else we will use this one and here again we will use f here and just take F here and just take D here okay just hit the enter so we are getting all information Auto automatically now for the delivery status we can use if it is pending then only we will check if it if it is not completed then only we will check okay so if remaining quantity is greater than zero then use another if that is today is less than the expected delivery date this one then it is delayed else it is on time close the bracket and here it will be blank just close the bracket now just fill this down now again we will use the array formula here so let's use the array formula we can copy it from here also this still here just copy and just paste that here and just hit the enter now we will change few CH few things colum F colum J and just hit the enter so we are getting the information you can see so our data is ready now quickly we will create the dash board just rename this as data insert a seet as dashboard this one on the top you can just select few rows and columns and mge them and just fill some dark color font white and let's say I'm putting here dashboard Center Center area LED around 20 go to the view so and check the grid lines like here we have taken the order quantity receive quantity remaining quantity and total cost so we will take that here also so make this slightly smaller and we can increase we can make this one also smaller or you can select together column A then D then this one then this one and make them smaller like this now select this these two b c e f h i and K L and increase this okay now like here we have created this order quantity we'll create the same one here also so let's select these two and merge and let's write here Order quantity align this Center middle and increase the font size take this area rounded and make this bolt slightly bigger and take here some dark color let's say I'm taking this color and font White NOW select this range and use merge cells and fill here some lighter color let's say this one now we will create the similar boxes here also and we can increase the font size for this one let's take areial rounded and font around take 30 so if you write any number here it will look like this so you can align this Center and middle and font you can take white if you want or you can keep it black also now just copy this and paste that here then here then here so the second one we will take received quantity then we will take here remaining quantity then we will take here total cost okay for the order quantity let's delete this all and you can change the color for each box also so let's say for the second one I want to take this one and here we can take the same theme color this one for the third one I want to take let's say this one and here we can take this all right so we have changed the colors now the order quantity we will take simple sum from the data seat and let's take the sum of column D and just just hit the enter so this is order quantity then use the sum here for the received quantity this one and just hit the enter for the remaining quantity take the sum of remaining quantity just hit the enter total cost take the sum of this total cost and just hit the enter okay so we have all information here you can just take the currency for this one okay now we will increase this one let's add a new seat and just rename this seat as support and here we will create the delay percentage so let's take here count of delayed and how many is on time and what is the delay percentage okay so to get the count of delay just use the count function come to the data and in the last we have delete so just select this comma come to the support seat and select this one enter and fill this down okay for the delayed percentage you can take the count of delayed divide by sum of delay and on time so this is the percentage you can convert this into the percentage now select these two go to the insert and insert a chart now change this chart as a gauge chart this one we'll do some customization so go to the customize and go to the Gaze the maximum we will put one which is 100% let's put some range also for minimum put zero and for Max put here 4.4 so up to 40% it will be red and uh here you can take 4 till 8 up to 80% 40 to 80% will be am then 8 till 1 it will be green okay so just make it slightly smaller and you can copy this chart then come to the dashboard and you can paste that here make it more smaller and you can increase the row height here now let's keep this here all right so now we will quickly create the other charts like we have created here the order status wise count first chart so we can just insert another chart here this A blank chart now we will add the range first so just click here and add another range and just add the range here so we'll go to the data and this is order status wise which is this column so we will take from here till the end click on okay and click on aggregate use row number three as header so we can see it is looking like this just select double click here and change the chart type as the pie chart so take this one if you want to make it 3D you can make it 3D otherwise you can keep it as it is you can make the 3D from the chart Style then come to the legend and say none then come to the pi chart and here the SCE label value and just make this is smaller let's keep this here you can change the colors also and uh show the labels at bottom so the completed double click here on this and take the green color for the completed double click on the amember so this is partial let's keep the same color and pending is also good to go so we can keep this color now the next one we will create Bender wise remaining quantity come to the data and we will select here Bender so this is the vender select till the end now remaining quantity this one and select till the end so we have selected we can make the copy of this chart and just paste add and uh just make it bigger just double click and we will change the this range so first range we will take column B so in place of I just take the V and here also V add another range and we will take this remaining quantity this one so you can just click and select till the end click on okay now just change this chart and take this bar chart you can change the color if you want and you can remove the legion so go to the customize and remove the legion none and just click on the grid lines and uncheck this major GD grid lines from here then if you want to change the color just change the color from here some green theme and add the data labels also okay or if you if you want to make it 3D you can make it 3D also so this chart is also done you can add the title also just double click and add the chart title so this is vendor wise remaining quantity okay and just form it slightly so make it Center and reduce the font size 14 and take the black color and just double click here also We'll add the chart title from here and this will be order status wise order count and align this Center take the black color and font take around 14 all right so this is done now make a copy of this so you can take this one and paste that here make this bigger now the next one we will create item byse cost or total cost just double click and we will change the range so just change the range the first we will take the item bu so item is available on column A so in place of this just take this one then the total cost we can just come here and we can go to the total cost this is the total cost column Ed just select till the end click on okay all right now we will change the chart type so we can take here this column chart so this is ready you can make it slightly bigger and everything is appearing properly just change the chart title this is item buse total cost now the last chart which we have created that is the line chart and this is purchase date wise total cost so we can make the copy of this one and we can paste that here just double click and uh we will take the range here the second range is okay but first one should be the purchase date so let's see the purchase date range that is on column C okay so let's click on the chart again and in place of a we can see the column C so take here C and C and just hit the enter okay so now xaxis we will take the purchase date and here we will take the total cost all right and uh we can change the chart type to the line and go to the customize and we will make some changes so we can go to the series and here the point size take around seven and rest is okay and you can take the line style as the smooth just change this as purchase date wise total cost all right friends so we have added all the elements available in the dashboard so that is how you can create the this procurement trer with the dashboard in Google seat friends I hope you have enjoyed this video If you like this video please hit the like button and please subscribe my channel to get the regular update of my videos thank you so much for the watching