Collaborate on Monthly Bills Template for Small Businesses with Ease Using airSlate SignNow

Monthly bills template for small businesses

welcome to this video tutorial on how to design a business budget spreadsheet a business budget spreadsheet is a tool that helps you plan and track your income and expenses and monitor your financial performance in this video you will learn how to create a spreadsheet template with the basic categories and formulas for a business budget and then enter your projected and annual income and expenses for each month then calculate the variance between your budget and actual results and analyze your budget data using charts and graphs and finally adjust your budget as needed to meet your business goals by the end of this video you will have a clear and comprehensive business budget spreadsheet that you can use for your own business or share with others in the course of this tutorial we will use the following Excel functions and formulas we use sumifs conditional formatting data validation drop down boxes progress bars and charting techniques as usual we are providing a free copy of this business budget spreadsheet all we ask is that you subscribe to this Channel and give this video a big thumbs up and then simply follow the link to the Mr spreadsheet website in the description section below I hope you enjoy watching okay let's get started on the design of our small business budgeting spreadsheet open up a new workbook and on sheet1 navigate to cell C11 and then in the range C11 through to G11 enter in the headings of date analysis description budget forward slash actual and value these are our headings for the data table Now navigate to cell D2 and then the range D2 through to M2 enter in the various income and expenditure headings as shown on screen now select the range D2 through to L2 right click your mouse and select copy from the options available and then navigate to cell i12 right click your mouse again and from the paste options choose transpose this places the contents of the range D2 through to L2 into a vertical list starting in i12 and extending down to I20 from the page layout ribbon uncheck the viewgrid lines box now highlight the range C11 through 2g28 from the home ribbon click on the borders tool and then select all borders this is the table range that will contain our underlying budget totals together with the facility to include subsequent actual values let's populate line one of this table select cell C12 and enter January 23. in the analysis field we want to select one of the analysis types in the range i12 through to I20 we can use the data validation tool to introduce a drop down box in cell D12 so with D12 active select the data validation tool from the data ribbon in the allow field select list and in the source field highlight the range i12 and drag this down to I20 the Excel coordinates are now appear in the source field click ok to save and close cell D12 now contains a drop down box selector to the right of the cell from this drop down box choose sales enter in your optional description in cell E12 in F12 we want to enter either budget for our forecast sales or actual to be able to enter in subsequent sales values we can use the data validation tool once again to achieve this so with cell F12 active select the data validation tool from the data ribbon select list in the allow field and then type in budget comma actual with no spaces in the source field click ok to save and close now from the drop down box in cell F12 select budget and then enter in a value of 12 000 in cell g12 highlight the range C12 through to g12 and copy and drag down the contents to row 28. now select the range C13 through to G13 and delete the contents all the values disappear but importantly the underlying formulas for analysis and budget and actuals remain now adjust your formatting so that you can easily view your work that's great let's enter in data for the rest of this table please copy in the values as shown on screen or indeed enter your own values you can pause the video whilst you complete this okay we now have the basic data set of our budgets and also some data representing subsequent actual transactions we will now use various techniques to display this data in a meaningful way so in the next section we will use the sum ifs command to populate our summary section in Row 3 all the way through to row 7. we will also analyze the data in a vertical format and employ data progress bars to emphasize the extent to which we have utilized our budget and then finally we will enter a chart to help us visualize the progress of budget values and actual values in cell C3 enter in the word budget Now navigate to cell D3 in this cell we want to extract some data from the tables below specifically we want to extract all instances where the analysis is sales and where the sale is a budget value we will use the sum ifs command to achieve this but before we do this let's name the various ranges that are some ifs command we'll use highlight the range D12 through 2d28 and with this range active enter the text analysis range with no spaces into the name box at the top left of your worksheet and hit the enter key this is a shortcut way to creating a named range highlight the range F12 through to F28 and enter b or a range again with no spaces and then hit the enter key finally select the range g12 through 2g28 and in the name box enter value range with no spaces hit the enter key to complete the process now return to cell D3 and enter in the following formula equals some IFS Open brackets value range comma analysis range comma d dollar sign 2 comma b or a range comma dollar sign C dollar sign three and close the brackets this formula says from the value range find all instances where the analysis range value is equal to the contents of cell D2 which in this instance is sales and then from the b or a range choose only those items that match the content to cell C3 which in this instance is the budget the dollar signs in the coordinates of D2 and C3 either completely lock or partially lock the relative positions in this formula we will see this working in the next section once we complete the formula the value of 12 000 is returned we can now copy this sumifs command from cell D3 all the way across to cell L3 so with D3 active copy and drag the formula across to cell L3 quickly check your work to ensure the correct values have been picked up from the budget table below now select cell M3 and enter in the formula equals plus D3 minus sum Open brackets the range E3 through to L3 and close the brackets formula picks up the sales value in cell D3 and then deducts the expense values in the range E3 through to L3 cell C5 enter in the text income and then in D5 enter in the command equals sumifs Open brackets value range comma analysis range comma D dollar two comma b or a range comma and then the word actual in inverted commas and close the brackets this formula is very similar to the one just described for cell D3 but instead of picking up the final component by reference to the value in cell C5 I've simply used the word actual in inverted commas as the last component this formula should return the value of 5000. now in cell C6 enter in the word expenses navigates to cell E6 and entering the formula equals sumifs Open brackets value range comma analysis range comma e dollar two comma b or a range comma the word actual inverted commas and close the brackets and now with E6 still active copy and drag this formula across to cell L6 you can check your balances to ensure that you have entered your formula correctly let's tidy up this section by entering a few totals and applying a suitable format in cell C7 entering the word balance in cell D7 enter in the formula equals plus D3 minus D5 in cell E7 the formula is equals plus E3 minus E6 and with E7 active copy and drag this formula across to cell L7 locate cell M5 and enter in the formula equals plus D5 in cell M6 the formula becomes equals sum Open brackets the range E6 through to L6 and close the brackets and in cell M7 the formula becomes equals plus D7 minus sum Open brackets the range E7 through to L7 and close the brackets check your totals for accuracy and then apply the formats shown or indeed choose a format style more to your liking in this final section we will present the data in a vertical format we will apply some progress bars and finally create a chart to highlight the progress in relation to our budgets and our actual progress in relation to the budgets in the range j11 through to M11 enter in the headings of budget actual balance and percentage in cell J12 we want to pick up the value in cell D3 above so simply enter the formula plus D3 into cell J12 apply this same simple process to populate each cell in the range J12 through to K20 just pick up the relevant total from the top few rows hopefully your work will match that on screen now in cell l12 enter in the formula plus J12 minus K12 and then copy and drag the formula down to cell l20 the value in M12 is plus l12 divided by J12 and formatted as a percentage now copy and drag this down to cell M20 in the range J12 through 2k20 and from the home ribbon pick up the conditional formatting tool now choose the data bars option and then from the gradient fill palette select the Red Data bar we now have progress bars in each of the cells reflecting the progress of actual values against budget values we need some totals so in cell j21 enter in the formula plus J12 minus sum Open brackets j13 through to j20 and close the brackets in cell k21 the formula becomes plus K12 minus sum Open brackets the range k 13 through 2K 20 and close the brackets and in cell l21 the formula is plus j21 minus k21 now apply the formatting for this section as shown on screen or apply formatting to match your previous work in cell i23 enter in the word sales and in cell I24 enter in expenses in cell j23 enter in the formula plus J12 to pick up the budgeted sales value from the table above in cell k23 the formula is plus K12 select cell j24 and enter in the formula equals sum Open brackets the range j13 through 2j20 and close the brackets in cell K24 the formula becomes equals sum Open brackets the range k 13 through 2K 20 and close the brackets check that your values are correct and then highlight the range i23 through 2K 24 and from the insert ribbon select the recommended charge tool now select the clustered column option and click ok your graph now appears on screen we will make a few changes to the graph and then our work is complete with the graph still active click on the chart title and then hit the delete key now highlight the data series text box and once again delete this let's change the graph's color scheme select the blue sales column right-click your mouse and from The Fill color palette select a mid green now click on the orange data bar and use the same process to select a light green color now drag and resize the entire chart such that it occupies the area as shown on screen and actually hides the small data table below finally enter in the words budget and actual in sales j28 and k28 and use the cell positioning tools to align the text below the graph components above finally apply formatting as shown on screen and that's it we have now completed our budgeting template it is quite likely that you will want to expand the various tables and use your own data sets you can easily do this by using the techniques shown we do hope that you enjoyed watching this video we do hope that our spreadsheet presentation was a value to you and that there was lots of useful content if you would like a copy of this free template then all we ask is that you subscribe to our Channel and give this video a big thumbs up alternatively you can visit us on one of our channels at either Facebook Instagram or Twitter if you enjoyed this video then why not watch your other videos based on this same theme you could view our how to create a business expenses spreadsheet or you can learn how to create reports from your expense analysis by viewing our year-end accounts created in Excel tutorial