Create an Efficient Restaurant Bill Format in Excel for Product Quality

Restaurant bill format in excel for Product quality

DSR or daily sales report is used to maintain or track daily weekly monthly and yearly sales even DSR can be used as a tool through which we can analyze data and can take steps to grow sales and transactions in today's video we will see how we can create a fully automated Dynamic DSR along with dashboard hi my name is Vijay and you are watching office let's start the video without any delays this is a daily sales report which I have created in advance this sales report will include dining takeaway and delivery sales along with tickets and APC or apt when we will come to the right hand side here will be Consolidated sales on Extreme left hand side we have got a dashboard from where we can see performance summary for the whole month in dashboard we have got month year start date end date total days total weekends means total Saturday and Sunday highest sale for the particular day in the month lowest sale for the particular day in the month if Target will be achieved it will be showing yes and will be highlighted in green if the sales Target will not be achieved it will be showing no and will be highlighted in Red Sales Target till date average sales Diamond sales takeaway sales and delivery sales and after that total sales last year sales and growth means over here we will compare last year sales with current sales if the current sales will be more than last year sales it will be showing in green and if it will be less it will be showing in red we have got total 14 worksheets in which 12 or 4 months one is Consolidated means the last sheet will be consolidated and the first one will be home tab from Home tab we can jump to any worksheet as per our requirement in case I want to check the sales of October I'll have to click on October and I'll be diverted to October sheet to get back to home sheet we will have to click over here on home and we are on for Sheet now in case I want to check the sales of Consolidated sheet I'll have to click over here and we are on Consolidated sheet or Consolidated sales report let's start creating a new sheet now I have opened a new worksheet we will start from here G3 this will be our serial number I'll type 1 and we'll drag it till 31st or 31. we will select from V2 till e we will highlight this this will be for our dashboard we will click on C3 and over here we will type 1. we will select this and we'll change the font color we'll make it bold we will select the whole sheet and we'll change the font style month after that we will type here start date end date total days total Saturday total Sunday highest sale for the day we will select two rows and we'll merge it will make the column size bigger and we wrap the text over here we will type lowest sale for the day we will select two rows and we'll merge it we'll wrap the text Target achieved sales till date average sales dining sales take away sales delivery sales total sales last year sales growth in case you want to include last year tickets or transactions you can do it now I'll click on the top and we'll take all the things in extreme left we'll make this smaller we'll select this and we'll give it a border will give thick border we'll make the column size bigger and will give borders we will merge this and we'll give it a border we'll make this smaller we'll change color of month year start date and end date we'll change the font color will make this bigger we'll select from ple and will merge it and over here we will type dashboard we'll take this in mid and will increase the font size will change the font style we'll take this in right side we will go in insert and we will insert a shape over here we'll double click on the shape and we'll type home we'll go to home we'll take this in center and embed we'll increase the font size and we'll change the font style we'll change the font color we'll right click on the shape we'll go to format shape will go in Center and we'll give it a shadow will give shadow to text also we will resize this and we'll place it over here we will make the font smaller we'll make this bigger we will change the background of the shape we'll take this little bit closer now we will start creating the report form Act so here is the serial number after that eight day dining sales dining tickets APC or apt APC is average per check and apt is average per ticket we will copy this and we'll paste over here we'll manage the cell size this will be takeaway sales take away tickets we will copy this Ctrl C Ctrl V this will be our delivery sales delivery tickets we will manage the cell size now we will select this and we'll create borders we will select this and we'll merge it together we'll put a color over here and we'll change the font color daily sales report bracket open dine in take away delivery bracket close we will increase the font size we will make it bold it will select the heads and we'll take this in Center we will change color of all the channels to differentiate it we will change color of this also you rectify the spelling of take will come to the right hand side this will be Target sales total sales total tickets APC slash apt Target versus achieved means Target sales versus achieved sales and over here percentage will manage the sale size we will select this and we'll take all the things in center and in mid we'll select the cells on the top and will merge it together we'll put a pillow over here this will be consolidated will increase the font size will change the font style you will select this and will give it a border now we will select all the heads and we'll change the color we will manage the cell size we'll select this and will give it a border will come to right hand side we'll select consolidated and we change the color will give it some light color will change the font color we'll take this in left we'll go to view show and will remove the grid lines so our blank format is ready now we will click over here we'll go to data data validation from the drop down we will select list and in Source we will type name of all the months from Jan to December and we'll do okay so our drop down what created we will select Jam we'll take this in Center in the same way we will create drop down for EO also we'll go in data data validation list and from here we will type our ears and we'll do okay so the drop down list for here got created we will select two zero two three we'll take this in Center start date the formula for start date will be plus or equal to date value bracket open double inverted comma 1 double e noted comma close and we will select the month and we will select the year we'll close the bracket and we'll hit enter we'll take this in Center we'll right click the mouse we'll go to format cells we'll go to date format and we'll select a desired format okay end date the formula for end date will be plus or equal to e Oman bracket Open start date comma 0 bracket close and we will hit enter we'll take this in Center we'll right click the mouse format cells date and will select a desired date format okay now whenever we will change the month start date and end date will change automatically Feb so here first web 28th Feb March 1st March and 31st March we will keep it Jan now we will insert formula over here date we will insert first date over here Plus or equal to we will select start date and we'll hit enter we'll take this in left side formula for second date will be totally different Plus or equal to if bracket open start date is smaller than end date we will press function f o and dollars will be inserted means cell will be locked comma start date plus 1 comma double inverted comma e o m double inverted comma close bracket close and we will hit enter we'll select this we'll right click the mouse format cells date and we'll select off desired date format okay so what's the logic behind inserting eom the months in which the days will be less than 31 it will be showing eom suppose the last date of February is 28th so after 28th it will be showing eom means end of the month now we will click on the second date and we'll drag it till 31st we got our dates over here it's showing till 31st Jan when I'll change the month I'll do it February so after 28th of February it's showing eom when it will be leap year after 29th it will be showing eom means end of the month we will do it 23 and month as January formula for day will be plus or equal to text bracket open we will select the date comma double inverted comma DDD double inverted comma close bracket close and we will hit enter now we will drag the still end we will keep it selected we will go to conditional formatting highlight cell rules equal to over here we will type Saturday we'll leave the red color as selected and we'll do ok so all the Saturdays got highlighted in red we'll go to conditional formatting once again highlight cell rule equal to this time we will type Sunday we'll click on the drop down and we'll select green color okay so Saturdays got highlighted in red and Sundays got highlighted in green whenever the month will change the highlights will change automatically it got changed before inserting the formulas we will take the figures from our example sheet so I have taken all the figures from my example sheet and all the figures are random now we will insert formula for APC or apt and the formula will be plus or equal to sales divided by tickets and we'll hit enter we'll take this incentive and we'll drag till end we'll remove the decimals we'll leave this selected we'll copy this Ctrl C or command C and we'll paste it over here so formula got inserted will paste in delivery also so all the formulas got inserted let's delete some sales figure from here you will notice one thing if the sales are blank it's showing some error so to remove this error we will insert a formula we'll undo this we'll go to the formula we'll sleep behind the formula and we'll insert criteria of if if bracket open sales is equal to 0 comma double inverted comma twice comma we will close the bracket and we'll hit enter now we will drag this till end I'll delete few sales and tickets and now we can't see any error over here I'll undo this will copy all the formulas from 4 to 31st Ctrl C or command C and will paste in take away as well as delivery so all the formulas got inserted we will come in right hand side Target sales we will insert some random figures as twenty thousand we'll take this incentive we will drag this till end we'll make this bold total sales so what will be the total sales dining Sales Plus take away Sales Plus delivery Sales Plus dining Sales Plus take away Sales Plus delivery sales and will hit enter we will insert a criteria over here we'll go to formula bar we'll take this in bracket and will insert the criteria of if if bracket open dialing sales is equal to 0 comma double inverted comma twice comma we will come to the end we'll close the bracket and we'll hit enter the basic reason behind inserting the criteria of if in case there will be no sales it will be showing blank now we will drag this till end total tickets Plus dining tickets plus takeaway tickets plus delivery tickets we'll take this in bracket and we'll insert the criteria of if if bracket open sales is equal to double inverted comma twice means if the sales will be blank comma we want the cell as blank comma we will come to the end we'll close the bracket and we'll hit enter we'll drag this till end APC or apt Plus or equal to bracket open sales divided by tickets bracket close over here also we will insert criteria of if if bracket open tickets is equal to double inverted comma twice means blank comma double inverted comma twice comma we will come to the end we'll close the bracket and we'll hit enter we'll remove the decimals we'll take this in Center now we will drag the still end Target versus achieved the formula will be plus or equal to achieved sales minus Target over here we will insert criteria of if error if error bracket open we will come to the end comma double inverted comma twice we will close the bracket and we'll hit enter we'll take this incentive and will drag till end percentage we will select this will go to home and from here means from Center we will select percentage and the formula will be plus or equal to sales achieved divided by sales Target will hit enter we'll take this in Center over here also we will insert criteria of if if bracket open sales Target is equal to 0 comma double inverted comma twice comma we will come to the end we'll close the bracket and we'll hit enter now we will drag the still end so most of the formulas got inserted over here we will come to the left hand side and we'll go to our dashboard total days the formula will be Plus or equal to count bracket open we will select from first to last date we'll close the bracket and we'll hit enter we'll select this and we'll take all the things in Center currently it's showing 31 when the month will change means when we selected February it's showing 28. when we'll select merge it's showing 31. we will select Jan total Saturday formula will be plus or equal to count if bracket open we will select the range comma double inverted comma s a t means Saturday double inverted comma close bracket close and we will hit enter total Sunday we will insert the same formula of countif Plus or equal to countif bracket open we will select this comma double inverted comma Sunday s-u and doubly noted comma close bracket close and we will hit enter so in the month of January there were four Saturday and five Sunday when we will change the month February for Saturday and for Sunday we will keep it as Jan highest sale for the day plus or equal to Max bracket open we will go to right hand side we'll select the range we'll close the bracket and will hit enter so the total sale in a particular day for the whole month it was this figure means 1 lakh thirty five thousand one twenty lowest sale for the day in the whole month the formula will be plus or equal to Min means minimum bracket open we will select the sales we'll close the bracket and will hit enter so the lowest sale in the whole month for a particular day was 10 520. now we will insert rest of the formulas later on let's insert the formula over here for the totals yeah I'll come down we'll make this bigger we'll delete one row from here delete I'll merge this this will be our total will increase the font size we'll put some color over here dining Sales Plus or equal to sum bracket open we will select this will close the bracket and we'll hit enter we'll select this we'll take all the things in center and in bit we'll change the color will make it bold and will increase the font size now we will drag this till end total of APC is not required so I'll delete this from all three channels means from dynin delivery and take away will come to the right hand side we'll copy this and we'll paste over here means we want sum of Target sales now we will drag this till end we'll delete total of APC we'll change color of this now we will insert our formulas over here sales till date formula will be plus or equal to we will come to right hand side and we will select total of the sales and we'll hit enter average sales the formula will be plus or equal to average bracket open we will come to right hand side we'll select total sales from first to last date of the month we'll close the bracket and we'll hit enter we'll remove the decimal places dining sales the formula will be plus or equal to we will select total of dining sales enter take away Sales Plus or equal to we will take total of take away sales means this figure enter delivery Sales Plus or equal to we will take total of delivery sales enter total sales it will be plus or equal to sum bracket open we will select all three channels we'll close the bracket and we'll hit enter last year sales we will have to insert it manually I'll change the cell color and font color I'll take a random figure 75 000 or 7 lakh 50 000 enter growth the formula will be plus or equal to present minus passed and we will hit enter we will insert percentage over here and the formula will be present minus passed divided by past into hundred so we will do one thing we will select percentage from here and we'll insert our formula Plus or equal to present beta we will take this in bracket is equal to bracket open present minus fast bracket close divided by past we have selected percentage over here so we are not going to multiply by 100 so the formula will be present minus passed divided by past enter so 6 percent there was a loss we will go in conditional formatting highlight cell rule greater than 0. so when it will be greater than 0 we want green color we'll do okay we will go to conditional formatting once again highlight cell rule less than zero and we want red color we'll do okay so it got highlighted in red we'll select this conditional formatting highlight cell rule greater than 0 we want green color in positive values okay we'll go to conditional formatting once again highlight cell rule less than 0 we want negative values in red we'll do okay we'll put border over here currently the values are a negative so it's showing red I'll change the figure over here when the current year sales increased it got highlighted in green we'll undo this over here we have to insert formula for Target achieved when the target will be achieved it will be showing yes when the target will not be achieved it will be showing no and it will be highlighted in green and red and the formula will be Plus or equal to if bracket open will come to right hand side total sales is greater than sales Target this is current your sales Target or current month sales Target comma double inverted comma yes double inverted comma close when the target will be achieved it should be showing yes and when the target will not be achieved it should be showing no so we will do comma double inverted comma no double inverted comma close bracket close and we will hit enter one thing to mention over here don't be confused this is for current year or current month and this is for last month or last year we will do one thing we will click over here on yes we'll go to conditional formatting highlight cell rule equal to we will type yes over here and we'll select green color okay we will go to conditional formatting once again highlight cell rule equal to this time we are going to type no we'll select red color and we'll do ok so when the target will not be achieved it will be showing no I'll change some figure from here earlier it was showing yes now it's showing no because sales Target was not achieved sales Target was for 6 lakh 20 000 and the achievement was 3 lakh 31 000. we will undo this so the DSR report for one month got created we will rename this as Jan we will insert another sheet and this will be our home if you will click over here and we'll type months over here also we will type months we will select this and we'll paste it over here we'll make this bigger we will make this bigger we'll select this and we'll change the color we'll do the same thing for this also we change the font color we will make this bigger we'll take this in center and in mid will increase the font size we'll change the font style we'll give it a border will go in insert illustrations shape and we select a desired shape we'll make this bigger and we'll make it smaller we'll change the color we'll make it red and font as yellow we'll click on the shape we'll right click the mouse format shape will go in center and will give it a shadow we'll click on text options we'll go in center and will give shadow to text also this will be Chan will take this in center and in mid will change the font style you will make it bigger we'll press Ctrl and we'll drag it down we'll drag one more time and we'll make seven Jan Feb March April May June we'll close this we'll press Ctrl and we'll drag it now we will rename it this will be Jan this will be Feb March April we will press Ctrl and will select all the shapes controls your command C and will paste it over here we'll take this up this will be August September October November and December we'll delete this this will be our consolidated we'll increase this will take it down and we'll change the color we'll take this closure we'll go to view show and will remove the grid lines now we will come in our four sheet over here we have got button of home we will click over here we'll right click the mouse we'll go to hyperlink this document and we will select home we'll do okay now whenever we will click on home we will be diverted to the main page I'll click over here on Jan we'll right click the mouse hyperlink and from here I'll select Jam we'll do okay now whenever I'll click on Jan I'll be diverted to the sheet of January and whenever I'll click on home I'll be diverted to the sheet of home I'll take this closer I'll take this home in the beginning so our months will be over here I'll click on the worksheet name we'll right click the mouse move or copy move to end create a copy and we will create such copies for all the months till December copy move to end create a copy let's rename this it will be Feb March April and will rename till December we will insert one more sheet for Consolidated this will be consolidated let's go to home we'll click on February we'll right click the mouse hyperlink and we select save from here okay merge right click the mouse hyperlink will select March okay and in the same way we will connect all the sheets April right click the mouse hyperlink select apparent okay right click the mouse hyperlink and select May okay and will connect Consolidated also so all the sheets got interconnected we will change the month this will be December this will be November October September August July June May April March and February we'll click on home we'll click on September and we got diverted to the sheet of September home Consolidated and we are in the seat of Consolidated we'll change this we'll remove daily and we'll type consolidated month is not required so we will delete this we'll delete date and day from here start date and end date is not required so we will delete this we'll delete this will delete this also now we will connect all the sheets so we will have to delete this will delete Target sales also we'll click over here in dining cells Plus or equal to will go in Jan and we'll select the sale of the first day Plus Feb we Select First Day sales March and in the same way we will connect all the sheets together and we'll hit enter now we will drag this till end we'll delete APC or apt formula our formulas are sitting over here so we will drag it up in all the three channels now we will select this and we'll drag till end we will connect Target sales also Plus or equal to Jan we will select the target cells Plus Feb and in the same way we will connect all the sheets and we'll hit enter now we will drag this till end so all the formulas got inserted we will update our formulas over here total days Plus or equal to we'll go to Jan will select total days Plus Feb will select total days from here and we'll select from March plus and will connect all the seeds together and we'll hit enter we'll copy the formula of days controls your command C and will paste it over here now we will drag this down so all the formulas got inserted and our DSR means daily sales report is ready now I'll click on home and in this way you can create your DSR or daily sales report for your company I'll provide link of this file in my description box in case you want to download you can I'm sure you must have loved the video please don't forget to like share and subscribe see you soon with a new topic till then bye