If you’re an accountant, you know that quickly doing a t-account can sometimes help you plan your journal entries and save you some headaches later on. But sometimes it can be time-consuming and a bit cumbersome to go through the process of setting everything up in an Excel spreadsheet. That’s where my new, live t-account template can help you.
Simply go to this link and you’ll be taken to a page where you can start creating your t-accounts on the fly. All you need to do is first make sure you name the accounts along the top and then record the entries on the left-hand-side. The accounts will automatically update as you enter the data.
Here’s a quick demo of how the page works:
It supports 20 line items and five accounts. And if you make a mistake or want to make another set of t-accounts, you can just refresh the page to clear what you’ve entered.
If you like the live t-account template and find it useful, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download.
Unless you’re still stuck on old versions of Excel, you probably know that you can filter data using colors. And by doing so you can use the SUBTOTAL function in excel to tabulate those amounts. But in this post, I’m going to show you something a little different. By using a macro, I’ll highlight cells containing values and then sum them by color in Excel, without needing a subtotal or a filter. This free template will just use VBA to populate the totals.
How the template works
In this template, all you need to do is enter your data and then assign whatever formatting you want to use to identify a cell to the corresponding category. As long as a cell has the exact same formatting as the category, it will be included in that category’s calculation.
I got the idea when I was trying to quickly analyze expenses and didn’t want to go through a whole process of putting it into a complex budget template. Rather than setting up logic to classify whether an expense falls into one category or another, I thought color-coding could be another way you could quickly group expenses.
Here’s a quick video showing you the template in action from color-coding cells to calculating the totals:
Setting up the data
In the template, there’s one section dedicated to the raw data where you’ll enter your inputs:
You can input data up until column N, although I’ve left the column blank for a buffer. In this example, I’ve put in random numbers and grouped them based on a store value in column A. This can be all numbers, it doesn’t really matter, I just preferred to have some grouping.
Next to the data entry, I’ve got a list of categories set up in column O:
You can add as many categories as you like. How they’re color-coded here is how you’ll need your cells will need to look to ensure they’re in the correct categories. For instance, any cells that are highlighted in light blue will go into Category I. Whereas anything in a dark red will belong to Category E.
You may think this would be a painful process to try and color-code your data based on all these different categories. After all, what if you get the shade wrong or the font color is wrong. The solution’s really simple and you just need to use our trusty friend, the Format Painter. If you’re not familiar with it, this is what it looks like:
It’s on the left-hand-side of the Home tab where the copy buttons are. What you can do is select the category you want to be assigning data to, click once on the Format Painter and then click on the cell you want to highlight with that exact same formatting.
If you’ve got multiple cells that you want to apply the formatting to and don’t want to keep repeating this exercise, then Double-Click on the Format Painter. You can now continue selecting cells and the Format Painter will take care of the formatting for you. You won’t need to re-select it each time. Once you’re done with the formatting and want to stop applying it, click on the Format Painter again to stop.
I’ve color-coded some of my data based on the categories, and here’s how it looks:
Updating the calculations
On the right-hand-side of the page there’s a button that says Update. This will update the totals based on the color-coding. You’ll need to have macros enabled for this to work. Above the button you’ll see the total of all the values in columns and how much is unallocated.
Clicking on the update button will trigger the macro to run the calculations. After pressing the button, here’s what my categories look like and the totals corresponding to their color-coding:
You’ll notice I’ve also created visuals to see the relative size of each category using the REPT function. If you’re interested in learning how to use this function, check out this post.
Anytime you make changes to the color-coding, be sure to hit the Update button. I didn’t want the formulas to update every time there was a change on the sheet because that can sometimes slow a spreadsheet down, especially since it would involve recalculating all the totals.
The code
Here’s the VBA code itself on how the update button works:
Sub Oval1_Click()
Dim clvalue, clcolor As Double
Dim cl, colorrange As Range
Dim lstrow As Integer
lstrow = ActiveCell.SpecialCells(xlLastCell).Row
Set colorrange = Range("colorrange")
'clear data range
colorrange.Offset(0, 1).ClearContents
For Each cl In ActiveSheet.Range("A1:O" & lstrow)
If IsNumeric(cl) Then
clcolor = cl.Interior.Color
For Each lookupcl In colorrange
If lookupcl.Interior.Color = clcolor Then
lookupcl.Offset(0, 1) = lookupcl.Offset(0, 1) + cl.Value
GoTo nextcl:
End If
Next lookupcl
End If
nextcl:
Next cl
End Sub
There’s a named range called “colorrange” that it cycles through, and those are the categories in column O on the spreadsheet.
Using the template
This isn’t a terribly complex template to use. However, it can help if you want to quickly group items. It’s a unique way to classify expenses rather than just using drop-downs and complicated formulas. And it makes it easy to sum data by color in excel. The way I found it useful was to list your vendors or stores in column A. Then, arrange transactions by date (e.g. the first transaction is in column B, then C, and so on). But this can be used in a variety of different purposes to help classify data.
If you want to reset the calculations, just change the data back to the default formatting or something that doesn’t correspond to a category you already have, and then click update.
Download
The template is free to download and it’s available here.
If you liked this post on how to sum by color in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
If you earn income from multiple jobs, you know how important it is to keep track of how much you’re making. If you work too little, you may not be making as much as you were planning. And if you’re working a lot more, you could afford to give yourself a break.
Whether you’re self-employed or you supplement your income by driving for Skip the Dishes or Uber, there’s no shortage of ways to make money these days which doesn’t involve working a regular 9-to-5 job. The income tracking template I’ve created will allow you easily track your income from various sources and help you stay on track if you’re targeting a certain income figure for the month or year.
There are two tabs on this template: inputs, and calendar. The inputs are where you’ll go to enter in your income, so let’s start there.
On the Inputs tab you’ll enter the date you earned the income, the source, and how much you made. You can setup hourly or item rates. You can also just enter in a fixed income amount. Here’s an example of some sample inputs:
For jobs like Skip the Dishes or Uber where you don’t earn an hourly wage, you’ll need to enter a manual amount. If you sell items on eBay or Amazon where you might have an average price, you could potentially use a rate. And that’s where the rate schedule comes in handy:
This could also work if you work a part-time job or something where the rate is fairly steady. Whether it’s per hour or per item is irrelevant. The point is to try and make the input section as easy as possible and you don’t need to necessarily use the rate schedule if you don’t need it.
But if you enter a number under the hours/items section, the income earned formula will be looking for a rate to multiply that by — you’ll get an error if it can’t find one (unless you enter a manual total, which will override the calculation). So if you don’t have a rate for that source of income, don’t enter anything in the hours/items section and just use the manual input column.
Once all your data’s entered in, it’s time to head over to the Calendar tab.
A summary of all your income
For the week that I entered the weekly income for, here’s what the calendar shows:
You can visually see how much you’ve made each day and there is also a weekly total at the end. The key to everything calculating correctly is the value in the source column needs to match what you entered on the inputs tab.
There’s also a column further down titled Annual Run Rate which will tell you how much you’ll earn over the course of the year if you work a full year at your current weekly pace.
It’s simply a way to gauge whether you’re on track that week for your annual goal or not. That brings me to the next section: the setup.
Settings and goals for the income tracker template
If you scroll over to the right on the calendar tab, you’ll see an area where you can specify a number of different settings and goals. Only change the items that are highlighted in grey. Let’s go over each one:
Work weeks. This is how many weeks you plan to work during the year. If you are going to work every week then you can leave this at the default value of 52. If you’re going to take some weeks off, then deduct from that total. The purpose of this is for calculating the annual run rate.
Include partial month. If you strictly only want to include the days that fall in the month when calculating your weekly totals, then set this to ‘N’. If you set this to ‘Y’, then the first and last weeks of the month could include parts of the previous and upcoming months, depending on where the month ends and starts. The purpose of setting it to ‘Y’ would be so that every week is a full week. For instance, July 2020 began on a Wednesday. If you set partial months to ‘Y’, then your July calendar and weekly totals would include June 28-30. If you set partial months to ‘N’, then those days would not show up on the calendar and they wouldn’t be included in your weekly totals.
Monthly goal. This is the total income you want to earn on a monthly basis.
Annual goal. This is the total income you want to earn for the full year.
Below the setup options, you’ll also see a summary of your sales by month. You can enter the year if you’ve got multiple entered. But by default, I’ve set this to 2020.
Tracking your goals
Next to the calendar, you’ll also see charts showing you the progress that you’re making relative to your goals for the month and year:
The different gauges show different things. The first one is how close you are to reaching your monthly goal. The next one is how close you are to your annual run rate based on the monthly income you’ve earned thus far. And the third takes a tally of all of the income you’ve entered on the inputs page and compares it to your annual goal.
Download the template
This template is free to use and allows you to stay on top of all your income sources. The version is locked down to minimize data entry errors and does come with an ad. But as it is, it will allow you to enter in your data and the template is fully functional. You can download it here.
Currently, the template supports five income sources and you can adjust those in the free version. With the premium version, everything is unlocked and there is no ad. And you could add more income sources on the calendar tab if you’re comfortable adding rows and updating the formulas.
If you liked this post on the income tracker template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
A countdown timer can help you track how much time there’s left to do a task or until a deadline comes due. Below, I’ll show you how you can make a countdown timer in Excel that can track days, hours, minutes, and seconds. In order to make it work, we’ll need to use some VBA code, but it won’t be much. And if all else fails, you can just download my free template at the end of the post and repurpose it for your needs.
Let’s get right into it and start with the first step:
Calculating the difference in days,
To calculate the difference between two dates is easy, as all you’re doing is subtracting the current date and time from when you’re counting down to.
The start date is just going to be today, right this very second. And Excel has a convenient function just for that, called NOW. It doesn’t require any arguments and all you need to do is enter the following formula:
=NOW()
Entering the date and time you’re counting down to is a bit trickier. As long as you enter it correctly, then calculating the differences will be a breeze. However, this may involve a little bit of trial and error since it’ll depend on how your regional settings are setup. For the countdown date, I’m going to set it to the end of the year. Let’s say 11:00 PM on New Year’s Eve. Here’s how I input that into my spreadsheet:
2020-12-11 11:00 PM
The key things to remember here are that there should be a space between the time and the AM/PM indicator (if you use it) and there should be two spaces between the date and the time. Then, it’s just a matter of whether you’ve got the right order of date, month, and year. This is where you may need to do some testing on your end to ensure you’ve got the correct order.
Now that the dates are set up, we can calculate the difference in days. To do this, we can just calculate the difference and use the ROUNDDOWN function to ensure we aren’t adding partial days:
There are 222 days left until the end of the year. By using the NOW function, the formula will automatically update and tomorrow the days remaining will change to 221, and so on. If your output’s looking a little different, make sure to check the formatting and that it’s set to days.
Calculating the difference in hours, minutes, and seconds
There’s not a whole lot of complexity when it comes to calculating the difference in hours, minutes, or seconds. We’re still subtracting the current date from the deadline. The only difference is that now we’re just going to change the formatting. If I do a simple subtraction, I end up with a fraction, which isn’t really usable in its current format:
The trick here is to change the format of this cell so that it shows me hours, minutes, and seconds. And that’s an easy fix. If I just click on cell C10 and click CTRL+1, this will get me to the Format Cells menu. In here, I’ll want to select a Custom format so that the cells just shows hours, minutes ,and seconds:
Here’s what the countdown timer looks like after the format changes:
It’s important to include a date in the calculation even though we’re just doing a difference between hours, minutes, and seconds. Otherwise, the formula wouldn’t correctly calculate in all situations, such as when the deadline hour is earlier than our current hour.
Putting it all together
Now that all the calculations are entered in, now it’s just a matter of formatting the data. We can create a countdown clock that separates days remaining, from hours, minutes, and seconds remaining.
One cell can have the difference in days, while another will have the difference in hours, minutes, and seconds. This goes back to just modifying the formatting and applying a custom format. Here’s how mine looks:
Although we’ve gotten to this point, the challenge is that this countdown timer still doesn’t update on its own. Unless you want to click on the delete button all the time, the countdown isn’t going to move unless there’s something to trigger a calculation in Excel. That’s why we’re going to need to add a macro to help us do that, which bring us to the important last step of this process:
Adding a macro to refresh every second
We need a macro to update the file. Whether it’s every second, every five seconds, it’s up to you. While the countdown timer will update when someone enters data or does something in Excel, that’s not much of a countdown. This is where VBA can help us. If you’re not familiar with VBA, don’t worry, you can just follow the steps below and copy the code.
To get into VBA, click on ALT+F11. From the menu. Once you’re there, click on the Insert button on the menu and select Module:
Over to the right, you’ll see some blank space where you can enter in some code. Copy and paste the following there:
Sub RunTimer()
If Range("C10") <> 0 Then
Interval = Now + TimeValue("00:00:01")
Application.Calculate
Application.OnTime Interval, "RunTimer"
End If
End Sub
One thing you may to change is the reference I made to cell C10. Change that to where you have your countdown timer. As long as there’s a value in the cell, the macro will continue running. All it does is check if there’s a value there, and if there is, it updates the worksheet every second. And by doing that calculation, your countdown timer will update even if you’re not making any changes to the spreadsheet.
You can also change the interval which currently updates every second, as noted by the 00:00:01. You can change this to five seconds, 10 seconds, however often you want it to update.
But there still needs to be something that triggers the macro to start running. You can assign a button or shortcut key to do that.
However, in this example I’ll activate it when the sheet is selected. Inside VBA, you should see a list of worksheets. Double-click on the one that contains your countdown timer:
You’ll again see blank space to the right where you can enter code. And you’ll also see a couple of drop-downs near the top that you’ll want to look for. By default, the first one should say (General). Change this to Worksheet:
Next, change the other drop-down which will probably say SelectionChange. Change it to Activate. Then you should see something like this:
Copy the following code into there to call the macro we created above:
RunTimer
Now when you switch to another worksheet and come back to the current one you’ll notice your countdown timer is updating on its own. If you want it to stop it, just clear the cell that has the timer. Otherwise, the macro will continue running every second.
The Countdown Timer Template
If you’d rather just use a template, then you can download one that I’ve made here. You don’t have to worry about macros and instead you just need to enter the end time; the time that you’re counting down towards.
I’ve also got a start/stop button that you can toggle to get the countdown timer going and that will pause it:
You can move the button as well as the time your counting down to onto another sheet if you don’t want someone altering it. If you have any questions or comments about this template, please send me an email at [email protected]
If you liked this post on how to make a countdown timer in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
A word search can be a great way to pass the time and for kids, it can help them practice their spelling as well. With this free word search maker, you can easily create a random new word search in just seconds. With small, medium, and large sizes to accommodate different ages and skillsets, the template provides a lot of flexibility. You can download it here.
Let’s jump right into it and see how the template works:
How to create a word search using the template
There are three tabs in this template: WORDSEARCH, WORDSEARCH.MED, and WORDSEARCH.SM. They indicate their size and difficulty. If you want to create something fairly simple, then the small (SM) tab will work best and it can accommodate up to 10 words. The medium (MED) tab is a bit bigger and you can have up to 15 words. And the main tab will allow you to plot up to 20 words.
Below the actual word search, you’ll have a list of spaces where you can enter your words in, titled Word List:
Each list contains a pair of boxes. The small one off to the left is where you might tick off that a word has been found. The larger box on the right is where you will enter the actual word.
Further off to the right, you’ll see how many characters your words have taken up as well as a suggested limit:
If you’re over the suggested character limit then the macro may have trouble finding space for all your words. If that happens, you’ll get an error message saying so. However, you can still try and see if it’ll work. to create a new word search, click on the Randomize button shown above. This will plot your words randomly in every possible direction, up, right, left, down, and diagonally as well.
Once the words are plotted, then the remaining spaces will be filled in with random letters. However, not every space will be a random mix of any possible letter in the alphabet. Less common letters like Z, X, and Q won’t have the same odds of showing up as more common letters. This is done in order to make the word search more challenging.
When the word search is entirely populated you can just print it off. You won’t see where the words were plotted on the word search without actually searching for them yourself. So if you wanted to create a word search for yourself, that’s entirely possible as even the person who runs the macro won’t have any advantage of knowing where the words were placed.
If you liked this free word search maker template in excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
If you need to track employee sick days and vacation, then the time off tracker template can help you. This template can also help you decide whether to approve or deny time-off requests as well. Below, I’ll go over the main features of the template and how it works.
On the Summary tab, you’ll start with a list of all the employees you want to track. This will include their hourly wage, start date, their beginning balance for their vacation days, as well as their vacation rate. The time off tracker template will also track sick days as well.
You can enter the current year and the cutoff date if you want to see up to a certain point in time. The annual hours and vacation rate percentage will impact the annual vacation day accrual. Annual hours you’ll probably want to set to either 2,000 (50 weeks x 40 hours) or 2,080 (52 weeks x 40 hours).
For sick days, there is a section off to the right on the summary page where you can enter the number of sick days people are entitled to annually. You can also specify a maximum number of people that can be off at any one time. This is related to approving requests which I’ll cover further down.
You’ll also see a section for holidays and blackout days for when you don’t want people taking time off. These lists can be as long as you like.
Entering and requesting time off
Whether you want to check if a person can book time off during a certain period or if you want to actually book it, you’ll do this through the Request.Form tab. Here, you can select the employee, the type of request (vacation or sick), and how long they will be off for. This template assumes employees do not work weekends. If the request includes a weekend, it will automatically account for that. Here’s a sample request for someone looking to take Jan. 24 – Jan. 31 off.
The available days, hours, days requested and hours requested will all automatically fill in once you enter all your selections. You’ll notice the days requested equals six, which isn’t counting Saturday and Sunday. It also assumes these are full days off and hence multiplies the days off by an eight-hour workday to get to 48 hours requested off.
If you want to see if this request complies with your policy (blackout rules, maximum people off) you can click on the Check Availability button. Then you will see the following summary:
The lights come up green for having sufficient time available and there being enough coverage. But it comes up red because the request includes a blackout day. Over on the right-hand side, you’ll see the person’s most recent time-off requests. It will also show any people who are off during this time.
You can still proceed and click the Post Time-Off Request button either way. It will post the information into the Timeoff tab:
If I double-click on any of the red boxes to the right, it will delete an entry. There is no data that needs to be entered on this tab, this is simply for record-keeping. The Timeoff tab is used to populate other areas of the spreadsheet.
If I were to go back and try and book another entry on Jan. 31 for a different employee, it will now tell me that someone is off during this time:
Note: you won’t be prevented from posting vacation if a person already has vacation booked for the same date. But if you make a mistake you can clear the duplicate entry from the Timeoff tab. You can also visually see who is off during a given period on the Calendar tab:
Blackout days are highlighted in black, vacation is dark blue, weekends are light blue and sick days are in yellow. In the above example it shows the one employee who took a vacation day during the blackout period. The color code for vacation overrides the blackout formatting. From this, you can also see that two employees were off on Jan. 31.
Entering in partial-day requests
If an employee is taking a half-day or only a certain amount of hours off rather than a full day, you can select the Partial Day option from the drop-down in the Request.Form tab. A new field will appear, allowing you to enter the hours per day:
In this example, this employee is requesting 5 hours off on both Jan. 30 and Jan. 31. The employee is requesting a total of 10 hours off, or 1.25 days. If you need to do a partial request, you can’t mix and match with full-day requests, you’ll need to do them separately. Even if the hours are different, multiple requests will be needed.
Whether an employee takes a full or a partial day off, it’ll look the same on the Calendar tab.
Adjusting for rate changes
One of the more complex calculations this time off tracker template factors in is if an employee moves to new pay rate or if their vacation rate changes during the year. This is where the Ratechanges tab comes into play. You select the employee, the date that the change is effective as well as the date it ends – this can be left blank if there’s no further rate change. The purpose of the end date is if there are multiple changes for an employee during the year.
Then, you enter the new hourly wage and vacation rate. Enter bothnumbers. For instance, if an employee now accrues 6% vacation rather than 4%, you’ll want to enter the new vacation rate but you’ll also want to enter in their hourly wage as well, even if it remains the same. This is important so that the formula calculates correctly.
This calculation will spit out a change in daily accrual as well as a total adjustment for the year based on the cut-off date. This adjustment will populate on the Summary tab under the Vacation Days Adj (Rate Changes) field.
As with any complex calculations, always be sure to double-check these numbers against your own. Especially when it comes to multiple rate changes a year, it’s important to ensure the data is entered correctly and that the correct number of days are being accrued. Although I’ve tested the spreadsheet, it’s impossible to factor in every possible situation and so I cannot guarantee 100% accuracy in all situations.
If you’d like to test out the template, you can download the free version here which is limited to tracking five employees. The full version has no limits and there are no ads and the code is fully unlocked:
If you liked this post on the time off tracker template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
This bank reconciliation template is an update from an earlier file that was made three years ago. It offers many of the same features with some notable improvements, and I’ll go over both in this post. I’ll start off by highlighting the key features and how it can help improve the bank reconciliation process for you.
Matching transactions is easier than ever in this new bank reconciliation template
In the earlier version, the bank reconciliation template looked at the total of transactions for a day or that matched criteria and so it didn’t make match individual items. It can be a mixed bag since some people prefer one way of matching (e.g. multiple cash transactions on a book entry matching up to one large bank deposit amount versus having one deposit for each entry). This template tries to make both methods a bit easier.
The auto-matching feature takes care of the latter approach where each line is effectively given a unique id that will be used to match against other transactions. This is ideal for one-to-one matches where you don’t want to look at just the totals.
If you’ve used the earlier version of this file, you’ll know that in this template, you can set up categories and keys associated with them. For example, if a check transaction shows up on your accounting system as CK#1234 you can create a rule in this template to say anything with CK in the description is categorized as a check and that the numbers that follow the number sign form the key, or the unique identifier. You can create these rules in the Setup tab.
Here are a couple of examples as to how this looks:
The Category is just the name of the category and the Identifier is what Excel will be looking for in the item description to see if it falls into that category or not. For checks, I’ve used the use numbers after identifier to say that what comes after CK# is what should be the key or the criteria that the template will be looking for when auto-matching. If there is no criteria, you can leave this blank and it’ll simply look at the amount and the category. However, this can be less accurate depending on if you have duplicates and similar data in your bank and book downloads.
You can also cap the length of the key, which is what I did in the above example, setting the Length of Key to 4. What this does is say that only the first four numbers will be pulled after the identifier. You can leave this blank and everything will be included. There is also a section for Gap if you don’t want it to immediately start pulling numbers after it finds the key. For instance, if I used CK as the identifier rather than CK#, then I’d want to set the Gap field to 1, to ensure that it skips over the next character, which in this case would be the # sign. But if you want to immediately pull data after the identifier, you can leave the Gap blank.
Alternatively, you can also just use the date as your key but that will not be very precise. The template and auto-matching will only be as strong as the rules that you put into place.
Manually matching transaction is easy, too
Even if you can’t auto-match all your transactions, I’ve tried to make this template as easy as possible to bulk match transactions as well. While the auto-matching is designed to help one-to-one matches, it’s also possible to match multiple transactions to one. This can be done using the Reconciler, which can be accessed via the Ribbon:
In the previous version, these buttons were within the file. Now, they’re on the home tab within the Ribbon, making it easy to access from anywhere in the file. Select a transaction from either the Book or Bank tabs and click the Reconciler button and you’ll have an interface where you can easily match transactions side-by-side:
In the previous version, side-by-side matching was not possible in the Reconciler and this allows you to easily do your matching within this interface. If I select the first transaction, which is a wire transfer, it will show me all the possible wire transfers I can match it to:
However, the ability to match the transactions won’t appear until I have the credits and debits matching an equal amount on both sides, to prevent running into a situation where I’ve matched an unequal amount:
By default, there will be no warning to pop-up when you’re matching transactions. However, if you prefer there to be one, you can change this in the Setup tab where there’s an option to toggle the confirmation from ‘No’ to ‘Yes.’
With it set to off, you can continue going through and matching transactions to ensure that you don’t have to click boxes before moving on to the next item to match. As it’s set up, you can match multiple items to one amount. However, you can’t match multiple-to-multiple and if you want to match multiple to one, then select the one transaction that will be matched to multiple items when launching the Reconciler. In the above screenshot, any transactions on the left-hand-side can be matched to multiple transactions on the right-hand-side, but not vice versa.
However, there is a SWAP button at the top of the form, which is also new, which can allow you to easily switch between the two views.
On both the Book and Bank tabs, there is a column for Manual Override and if you want to match an item manually you just need to enter a value in here. And that’s what the Reconciler does when you’re matching transactions. This is also where the next key feature comes into play: auditing and correcting your matches.
Audit trail from the Reconciler makes it easy to see which transactions are matched to one another
In the transaction that I matched above, it posted this in the Manual Override section:
You’ll notice that it says Previous BOOK Row 6. What this tells me is that the transaction was reconciled on the Previous OS Items tab, which includes transactions carried over from the previous period. It also tells me the row it was on and that it came from the book side. If the entry were to say Current, then it would be from the current transactions and that it would just be from the Book tab rather than the Previous OS Items tab.
If I wanted to undo this match, I could just press delete and clear the data in the Manual Override column. However, if you do this, be sure to clear off the other entry or entries related to it. Otherwise, you can be out of balance if you only cleared out one side of what was matched.
Reconciling the month
Once you’re done with your reconciliation and you want to see a list of your outstanding items, you can click on the Reconcile Month button in the Ribbon. This will spit out the outstanding items and group them by category. This process is similar to how the older version of the file worked.
When you’re finished and ready to start a new month or period, you can click on the New Month button which will clear the Book and Bank tabs and move any outstanding items over to the Previous OS Items tab. You’ll now be able to start a new month or period.
You can also use the Clear Data tab if you just want to remove everything and start completely from scratch
Testing out the file
If you want to give this file a try, please download the bank reconciliation template for free here. You can test out all the functions. There is a limit of just 25 transactions on the Bank and Book tabs. If you want the full version of the product, including with the code unlocked, please visit the product page here.
If you liked this post on the new bank reconciliation template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
QuickBooks does a good job when it comes to recording sales and doing day-to-day accounting tasks. You may be content with the reports that come out of QuickBooks, too. But if you’re looking for some more in-depth analysis to do of your own or to make your own reports, you’re likely going to want to move that data into Excel. And, unfortunately, the QuickBooks export into Excel can be less than optimal.
With many spaces, subtotals, and a non-tabular format, it’s not a very practical output to use in Excel. If you want to run a pivot table and do some serious analysis in Excel, you first have to clean up the data before being able to use it, and that can be a very tedious and tiresome process.
For example, this is what your QuickBooks report might look like when you’re pulling a simple summary of your customer sales:
There are a lot of things that need to be adjusted for this report to be useable in Excel, including getting rid of the blank spaces and ensuring that the customer information is repeated in the first column, as opposed to just in the first line and in the last line’s total. From afar, it’s a bit of a painful process to have to go in and clean this up. And while it’s not impossible, it’s not going to be quick, either.
That’s where a macro can help you make the task much quicker and it will save you a lot of time if you have to go through these steps often. Click the button on the ribbon and your data will convert into a more table-friendly format! Here’s how it works:
Using the macro to fix the QuickBooks export
Before running the macro, you’ll need to specify the columns where your customer names and dates are:
Then, run the Covert Data button:
Downloading the file
The free version of the QuickBooks macro will allow you to run the conversion if it doesn’t go past 100 rows. However, if you decide to purchase the full version please ensure that the macro and file works as expected. There’s no guarantee the QuickBooks export hasn’t changed or won’t change in the future. If there are changes that need to be made to the macro, please feel free to contact me so that I can make the necessary adjustments. Whether you prefer the add-In or the actual Excel spreadsheet itself, both versions are available both here and in the paid version as well.
Here is the download link for the add-in as well as the Excel file. For the paid versions, please visit the product page.
If you have another program or software that you’d like a similar add-in for, I can help with that as well.
If you liked this post, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
There are many Excel calendars online but with this template, you can customize it to suit your needs and use the calendar for more than just one year.
At a minimum, you can use the template to help generate a 12-month calendar for a specific year. However, it can do a whole lot more than that. If you want to use it for a custom fiscal year, you can use a different start date. You can also use custom accounting periods based on weeks, whether it’s the 4-4-5, 4-5-4, or 5-4-4. It can help make a template that will fit all of those needs.
And on top of that, you can also highlight holidays and deadlines on the calendar to make it easy to keep track of all your important dates.
How the template works
There are two tabs on the calendar template: calendar and setup. The only area to enter data is on the setup tab, and that’s where you will select the details relating to how you want your calendar to display.
The first item that you’ll want to enter is the start of the fiscal year. If you just want a regular calendar, then you can leave this as January 1. However, you’ll want to enter the year that you want the calendar for as well. This is the only cell where you’ll specify both the year for the calendar and the first day of the calendar as well.
On the following field, you can toggle between whether your week starts on Sunday or Monday. This will just impact how the calendar looks and will have no effect on anything else in the template.
If you just need a regular 12-month calendar, that’s all you need. If, however, you’re looking for a more customized format that accommodates a varying week schedule, then you will want to enter an ‘X’ in the Custom Quarterly Format field. If you do not enter an ‘X’ here, then the following values will be ignored and won’t have a bearing on the calendar.
The next three fields relate to how many weeks each period has. If you have fiscal periods that follow the 4-4-5 pattern where the third month of each quarter is a 5-week month, then you would enter ‘4’ for period 1 and period 2, and ‘5’ for period 3.
Those are all the variables that will relate to the structure of the calendar itself. You can go a bit further, however, and enter in holidays and other important dates as well.
Entering holidays and other dates
In column G you can enter the holidays or non-working days that you observe. Any dates in this column will be highlighted in yellow on the calendar. Columns H and I serve the same purpose, and that’s to highlight any important dates or deadlines. In column H, the dates will be highlighted in red while the ones in column I will be in green.
There’s no restriction on what you can enter in these columns and these deadlines could be annual or monthly. You could even use various date functions to help create recurring deadlines here as well.
You can enter dates in for the entire column so they don’t even need to be for the current year. This is why you can reuse this template for future years, as you can just adjust the starting date for the year and update the holidays, and the template will automatically update based on what you have entered.
Download the 12-month excel calendar template
This template contains no macros and is free to use. You can download it here. It does have some ads and the calendar tab is locked to ensure nothing isn’t overwritten by accident. There is also an ad-free version available here that is completely unlocked. As always, I encourage you to always try the free version first to ensure it works how you expect it to and that it’s what you’re looking for.
Looking for a full-month calendar template?
This template is for a 12-month snapshot but if you’re looking for one that focuses on an individual month and that can allow you to enter tasks and deadlines, be sure to check out this template. It will give you even more options for managing your monthly tasks and deadlines.
And if you’re looking for a personal calendar that will help you manage different goals at once, then this goal tracker template could be what you’re looking for. But if you’re looking for something completely different, feel free to contact me and make a suggestion. I am always looking for new ideas and templates to add to the site.
If you liked this 12 Month Calendar Template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
A tax-free savings account (TFSA) is a very useful tool for Canadian investors to shield investment gains and dividend income from taxes. However, it can be challenging to keep track of the rules and just how much you’re able to contribute and what your TFSA limit is for the year. But that’s where this TFSA template will be able to help you.
What this template will help you do
The purpose of this template is to help you keep track of both the contributions you make to your TFSA as well as the withdrawals so that you know what your limit is in a given year. If you’ve got multiple TFSAs and they aren’t all at one financial institution, keeping track of all your transactions can be a challenge. That’s where a spreadsheet can come in very handy; having all your information all in one place can make it much easier to stay on top of your TFSAs.
By logging your transactions each time you make a withdrawal or contribution from one of your TFSAs, you can have a complete picture of your balance at any given time. There’s no limit to the number of transactions you can enter in the template, and this can be used for a running total — forever. And with no macros and a simple, easy-to-use interface, the goal of this template is to make the process as painless as possible.
Why it’s important to know your TFSA limit and track your balance
Probably the main reason that you’ll want to keep track of your TFSA balance is that if you end up overcontributing you can end up with a hefty penalty.
At 1% per month of the overcontributed balance in a given month, the penalty can grow very quickly depending on how much you’ve overcontributed by and for how long. The last thing you want to see is your TFSA incurring costs rather than growing your savings. It would be a bit counter-intuitive, to say the least. Any fees that are incurred in a TFSA are not tax-deductible and that’s why overcontributing to it is something you want to avoid.
One easy mistake that can cause problems for TFSA holders
While it may seem simple to track your balance, there’s one issue that can cause headaches for TFSA holders, and that’s when it comes to withdrawing funds. One of the advantages of a TFSA is that since the funds that are contributed are after-tax, you don’t incur any penalties for taking money out. Unlike with an RRSP, you don’t have to worry about a withholding tax. With a TFSA, you can freely move money in and out of your accounts as you need it.
The caveat, however, is that when you withdraw funds, the contribution room isn’t replenished until the beginning of the next calendar year. And so if your TFSA had been maxed out on July 1st and you had withdrawn $10,000, then that will free up contribution room –- but it won’t be until January 1st. Any withdrawals that are made, regardless of the time of the year, won’t free up space until the beginning of the next calendar year.
That’s where much of the complexity comes into play when it comes to TFSAs. While contributions will reduce your available contribution room immediately, withdrawals won’t make that room available until next year. That lag can create many problems for TFSA holders. That lag can give people the misleading impressing that they have contribution room since they recently took money out, and that’s where overcontributing can happen very easily.
Suppose your TFSA is maxed out (2019 cumulative balance is $63,500) and you pull all the funds out today and they re-contributed them immediately after. In this scenario, you’ve now overcontributed by the entire balance -– meaning you’ll get a 1% penalty on that entire amount, which would amount to $635. And that’s just for one month. Leave that overcontribution in your TFSA and those penalties will pile up quickly.
While that may not be a common scenario that will take place, it’s an extreme example that helps to demonstrate just how costly it can be to make a very simple mistake. That’s why simply tracking the balance and looking at contributions and withdrawals is not enough, TFSA holders need to factor in the lag that happens with withdrawals. It’s a small but important detail that can make a big difference in determining how much contribution room you have available.
Using the template to track your TFSA limit
The template itself is very simple to use and there’s only one area where you’ll need to enter data, and that’s in columns K:N. There, you’ll enter the date of your transaction, if it was a contribution or withdrawal, and the amount. The year field is the tax year and it will auto-populate once you enter the date. You can keep adding to the list of transactions as you need to and the table will continue expanding.
The one thing to remember is that withdrawals should be negative and contribution amounts will need to be positive.
Once you’ve entered your transactions, the summary in columns A:I will update on its own:
The one time you will need to update the above table is when there is a new year to be added. Since there’s no guarantee what a future year’s TFSA limit will be, you’ll need to manually add the year as well as the new contribution room. As you can see, in prior years, the TFSA contribution limits have fluctuated, normally ranging from $5,000 to $5,500, with 2015 being the exception with a limit of $10,000.
However, to add a year is as simple as entering the new information below the most recent line. The table will automatically expand and the formulas will auto-update as well. And then you’ll just need to enter the current year’s contribution limit (column B), and the cumulative limit will be calculated automatically.
Here’s a breakdown of all the columns in the template:
Column A: Year – manually entered.
Column B: Annual Limit – manually entered to reflect the current year’s contribution limit.
Column C: Cumulative limit – this is automatically calculated based on the data in column B.
Column D: Contributions – this is the total amount of the contributions made during the year, based on the transaction data.
Column E: Withdrawals – this is the total amount of the withdrawals made during the year, based on the transaction data.
Column F: Cumulative Contributions – this is the running total of all the contributions you have made over the years.
Column G: Prior-Year Withdrawals – these are the withdrawals that were made a year ago that will be added to the current year’s TFSA contribution room. For instance, you’ll notice that the transaction from earlier that was a withdrawal of $5,000 made during 2010 is not added back to the TFSA balance until 2011.
Column H: Cumulative Withdrawals Added Back – this is the running total of the amounts in column G.
Column I: Available Room – this is your available contribution room based on all the transactions that have been entered.
Going over your TFSA limit
If the available room, column I, goes negative and indicates that you have overcontributed to your TFSA, the amounts will be highlighted in red. That being said, just because it hasn’t highlighted in red doesn’t mean your safe and should use this as a guide and not an absolute indicator of whether you’re okay or not.
There are many reasons why you could see differences from your own calculations versus how much room the CRA says you have. If, for instance, you’ve incurred gains or losses in your TFSA your contribution room will be affected. If you grew your TFSA to six figures or more and then went to withdraw those funds, then your contribution room would be replenished by your withdrawal amount, meaning you’d have a lot more room to use. On the flip side, if you’ve incurred losses, you can’t recoup that contribution room and are stuck waiting for the next year’s contribution limit.
Ultimately, your TFSA contribution room could look a lot different if you haven’t been eligible for TFSA since its inception, or if you’ve had gains or losses impact your available room. That’s why it’s important to take steps to ensure your information is up to date.
Planning makes perfect
Even if you haven’t made any transactions during the year, what you can do is to enter transactions you expect, or plan to make. Especially if you’re expecting to withdraw funds, you’ll want to budget for that in this template to ensure that it won’t cause a problem for you. Doing some planning beforehand can help prevent problems down the road, and save some costly surprises.
Checking your data
One of the most important things that you can do is to verify that your data is correct. Columns D & E in the template can be the most useful in this case because these amounts should reflect all the contributions and withdrawals that you made during the year. If you can’t reconcile to these numbers, then you know you’ve got a problem
Note that if you’ve made an error and overcontributed too much and then made a withdrawal to correct it, this template would still not reset the balance until the following year. In those cases, you may want to leave out the overcontributed amounts as well as the subsequent withdrawal to correct it.
When in doubt, your best bet is to confirm with the CRA. If you have My Account setup for access online, what you can do is access your balance as of the beginning of the year. While it won’t have all the contributions and withdrawals that you have made since the start of the year, you will have information on your available room as of January 1. This will at least give you a number that you can use as a starting point and then after factoring in your transactions, you can determine what your up-to-date balance is.
Downloading the file
As always, if you want to go ahead and try the file out just keep in mind there are no guarantees that come with it and that when in doubt, you should always verify your information with the CRA especially when it comes to determining how much room you still have available.
The
template will not factor in penalties and, ultimately, it’ll depend on how up to
date your recordkeeping is.
The file is completely free of charge with no limitations. If you like it, please give this site a like on Facebook and also be sure to check out many other templates that are available for download. You can also follow us on Twitter and YouTube.
