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.
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.
Whether you’re doing a forecast or looking back at how your sales were over a period of time, it’s important to ensure that you’re comparing apples to apples. While monthly and yearly numbers won’t have too much noise, once you’re trying to do a daily or weekly sales analysis, that’s when things can get a little challenging.
Below, I’ll show you how you can do a weekly sales analysis where you’re comparing the same days of the week against one another. This will give you an accurate picture of your year-over-year performance.
Step one: determine which day of the week you want to start on
This is a simple step and you’re probably going to go with either Sunday or Monday. But it’s an important one to consider because when you’re looking at weekly sales numbers, you want to be consistent. And while you can refer to the week number when comparing one week to a previous year, saying week 32 is not going to be as useful as saying the week starting August 5 or ending August 11.
In my example, I’m going to use Monday as my starting point to ensure that I’m not breaking up the weekend (the default in Excel is Sunday). To make it easy to compare a week, it will be helpful to create a header for the days of the week so it looks like a calendar.
Step two: entering the first date of the weekday you selected
The first Monday of 2020, wasn’t until Jan. 6 this year, which would be the second week of the year if we start on Mondays. The previous Monday was Dec. 30, which was technically week 53. Weeks 1 and 53 are often abbreviated. For now, just accept that there’s no Monday in Week 1 of 2020. I’ll show you how we can get around this problem further down.
For now, Jan. 6 will be our starting point which we’ll call Week 2. Now, that we have our starting point, we can build out what our subsequent weeks will look like.
For example, if I want to find out the start date for week 40, what I can do is simply use the following formula:
First, I multiply 7 by the difference in weeks. Then, add that to the first Monday value. In this example, it tells me the 40th Monday of the year is Sep 28, 2020. That’s why setting up the first Monday values is important to ensure that it’s easy to get the remaining dates.
This is the easier approach to take. However, later on I’ll show you a way where you don’t have to enter in the first Monday of the year.
Step three: filling in the remaining dates of the week for your sales analysis
Getting the starting date of the week is the toughest part. From there, all you have to add is just add 1 to each subsequent day:
Just adding 1 to the previous date will increment to the next day. No special formulas needed here.
Step four: getting the prior-year date
To get the previous year’s data you can follow the same approach as in step two. However, I’ll use this as an opportunity to show you another way that you can get the data. One that won’t require you to pull out the calendar.
First, we need to know what day of the week Jan. 1, 2019 fell on. To do this, we can just use the following formula:
=WEEKDAY(“Jan 1, 2019”,2)
The reason I put the number 2 as the second argument is because my week is starting on a Monday. If I set it to 1 or left it blank, the default would be Sunday. This is important because if Monday is my first day of the week then it’s day value is 1 and Sunday is 7. Had I used Sunday, then Sunday would have a value of 1 and Monday would be 2. This is why it’s important to know which day of the week you want your week to start on.
In 2019, Jan. 1 fell on a Tuesday, and the formula above gave me the result of 2. (Monday is 1, so Tuesday would be 2). The reason I need to know the weekday is because I need to adjust the date to find out when that week actually started. I use the following formula to do that:
What this formula does is subtracts Jan 1, 2019 from the number of days it is above day 1. It then moves the date back. I can simplify this formula by entering Jan 1. 2019 in cell A1. Then my formula looks like this:
=A1-(WEEKDAY(A1,2)-1))
I no longer need to use the DATEVALUE function and now it’s a bit easier to use. There’s also less chance of an error when entering the date. Now, when I want to find out the first day of the week, I can multiply 7 times the week number and add to this calculation:
=(A1-(WEEKDAY(A1,2)-1))+(7*(B1-1))
B1 is the week number. In this example, if I were to enter Jan 1. 2019 for cell A1, that would give me a result of Dec 31. 2018 for the start of Week 1. Excel also considers this to be the week that contains Week 53 and Week 1. This is where you can get around this issue. By calling this Week 1 of the current year and including December’s days into this week, it will ensure you don’t have the Week 53 problem. It may not look great to call the previous year’s dates part of the new year but it avoids having to manually make adjustments for this period.
Using the updated formula, I can change the Jan. 1 date to reflect 2019 and use week 40 to update my comparables for the weekly sales analysis:
From here, it’s just a matter of now using a SUMIF function on your data to pull the sales for each one of these dates and you’ve got your comparable sales numbers. With 2020 being a leap year, you can see that the dates have moved up two days from the prior year. Without the date adjustment, you could have ended up comparing a Sunday (Oct 4, 2020) against a Friday (Oct 4, 2020).
If you liked this post on how to do a weekly sales analysis, 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.
If you need to make an invoice and don’t want to spend money on some overpriced software, you can do so easily in Excel with this template. Not only can you customize it to how you want it to look and feel and produce a professional-looking invoice, but you can also track items, set prices based on customers and even have taxes calculated based on location codes.
Ultimately, it’s up to you how complex or simple you want it to be. Here’s how the template works:
The invoice itself
Whether you want to add a logo, change the colors or add some information to the headers, you can have a lot of control over how your invoice looks. The key thing to remember here, however, is not to add or remove any columns or rows. If you need extra space, stretch out the rows or columns, but don’t add any new ones.
And if you need to move the invoice date and number fields, be sure to move them, not delete them or copy and paste. They’re named ranges and so it is important that they remain intact for the code to still work.
Setting up the invoice template
Before you get started and using the template, what you’ll want to do is to set up some items, customers, locations and rates. Unless you really want to start from scratch every time, which I wouldn’t recommend.
First up, start with the Locations tab. If you’re only selling to one part of the world, then just set up a generic location but you can add as many different ones as you need. This is key for ensuring that the correct tax amount is being calculated per customer.
Next up, go to the Customer tab where you’ll have a list of your different customers, including their addresses and location codes. It’s important to add the location codes first because on the customer tabs the locations are drop-down selections that are derived from the locations tab, this ensures that you only select from a location code that has already been created. This is important to ensure that you aren’t mapping to a location that hasn’t been set up, otherwise, you’ll get an error.
Set up all the customers you need. Then next, move over to the Rate tab. Here, you’ll want to set up your customers from columns F onward. On this tab, you will also create your different items and differentiate between products and services. You can create as many items as you want. There is a rate field (column D) when you specify your default rate.
In the columns for your different customers, you can specify a special rate per customer. For example, in the above example, ITEM-1 has a default price of $50. However, if Company A is selected as a customer, a rate of $25 will be applied. If Company B or C is selected, the default rate of $50 will apply since no special pricing has been made available for those customers.
Putting it all together – creating an invoice using the template
With all that set up, now you can go back to the invoice tab to create your own invoice.
First, start with selecting your customer under the Bill To section. Enter the invoice date and invoice number.
Then, it’s a matter of selecting the items in column D, the date in column E and Quantity in column F (if it’s a service item, the quantity won’t matter, a value of 1 will be assumed). The remaining fields should auto-populate. You can edit everything that is in yellow. Anything not in yellow means that it you should NOT modify it (Note: the actual template will not show this highlighting):
Once everything is good to go, you can click on the Generate Invoice button.
This will do two things:
Create a PDF of the invoice you just created and save it to the location specified, and
It will also add the invoice to the Invoice.List tab. This creates a ledger for you to track all the invoices that have been created. If an invoice number is already on there, it will not allow you to create a duplicate. It’s important that you do not delete the invoice number field if you’re changing the template around.
The Invoice.List tab will log all the relevant data from the invoice. This includes the number, date, when it was saved and by who, which folder, and even individual item sales.
Disclaimer
The goal of this template is to allow you to generate invoices as accurately as possible. It also helps you track all your invoices. However, it’s by no means a perfect solution as you could conceivably alter the data in the Invoice.List tab after the fact. What I’d recommend is password protecting the file or hiding the tab if it will be used by multiple users.
So if you choose to use this file, it’s just important to keep that in mind. By adding too many controls and preventing people from deleting or correcting items, it may end up being too much of a hassle. This template isn’t a substitute for accounting software and it’s intended to create and track invoices.
Please note it’s up to you to ensure that your invoice is accurate and correct. You should always double-check an invoice before sending it out.
Download
The invoice template is free to use although there is a limitation of 3 items per invoice. It will also have an ad in the ribbon. The full version is available here. It will remove the limitations, advertising, and the code for the VBA will be unlocked as well.
If you like this Blank Invoice Template, please give the site a like on Facebook. Also be sure to check out our templates section. You can also follow us on Twitter and YouTube.
Whether you’re managing multiple deadlines, have a lot of tasks, or just have an odd schedule, this task manager template can help you manage all of that. Visually you can see what’s coming up and what still needs to be done. It’s a good way to organize and manage all your responsibilities.
How the Task Manager Template works
I’ve tried apps to track deadlines and tasks and none of them have ever done what I wanted them to do. And since I spend a lot of time in Excel, I thought I’d try to make a task manager template that can be a one-stop shop for managing all of that.
The task manager has four main tabs to it that I’ll go over in detail:
Calendar
Recurring Deadlines
Tasks
Team Calendar
Calendar
In its simplest form, you can use this tab to generate a calendar for whichever month and year you want just by changing the cell values. The calendar will highlight the current day as well as any holidays that you have specified.
It’s pretty simple, but once you start adding tasks and deadlines, it’ll look a whole lot different. I’ll refer back to the calendar as I go as it’ll change as I make updates to the other tabs.
Recurring Deadlines
There are multiple sections in this tab and it really acts as a setup tab, but the deadlines are definitely key.
In the left-hand-side of the page is the month-end schedule. Now, if you’re not an accountant and don’t have to deal with month end, you can probably skip this. However, for accountants that deal with a close process and have deadlines, you can change the month-end date.
For example, I’m going to change the month-end date for January 2019 to February 12:
If I go back to the Calendar tab, even though I’ve selected January, the calendar will continue until February 12:
You can enter the month-end values for each month so that way each month will cut off where you want it to. If you don’t need a custom end to the month, then the default values will suffice and you they will just end when the month does.
On the right side of the page there is a section for holidays. This is where you can put the non-working days in your part of the world. What you could also do is put any vacation days or time off that you have planned.
I’ve left a description of the holiday next to it but this is not necessary.
Now, if I go to the middle section of the tab, that’s where I see the recurring deadlines. There are five key fields here: description, occurrence, type of occurrence, day, and next date.
In the description field you’ll want to put in the name of the deadline. If you’ve got multiple deadlines on the same day you’ll want to combine them into one as they will only take up one line on the calendar anyway.
The occurrence and type of occurrence fields will go hand in hand. For example, if your deadline is the first business day of the month, then you’ll put a 1 in the occurrence field and Business Day in the type of occurrence.
If your deadline is the first Monday of the month, then enter a 1 in occurrence, Day of Week in the type of occurrence, and enter Monday in the day field (this is the only time you’ll need to use this field).
If your deadline is always the 1st calendar day of the month, then enter 1 for occurrence and Calendar Day in the type of occurrence field.
There’s an option to just enter a fixed date as well. If you always have a deadline on January 30th of every year, then enter the date in the occurrence field and Fixed Date on the occurrence type. This is helpful if you’ve got property taxes or annual deadlines that you can easily forget about. Of course, you’ll need to remember to update your deadlines.
Lastly, there is an occurrence type for Business Days Before Period End. Before your month end (whether custom or calendar), you can work backwards to compute your deadlines. Let’s say one business day before your cut off date you have to get a report submitted. In that case, you can enter a 1 for the occurrence and select Business Days Before Period End for the type.
Using the above scenarios, this is what my deadlines look like so far:
The Next Date field automatically gets populated based on what you’ve entered in the three prior fields.
These deadlines now show up on my calendar:
The deadlines are highlighted in red (except where there’s a holiday, as on Jan 1). The description of the deadline also shows in the first line for that particular day as well.
Tasks
On the tasks tab, you’ll be able to see your deadlines, to do list, and tasks. In the first section, you see the deadlines which we’ve already entered thus far:
You can add any notes to this section as well as update the status of the deadline. If you haven’t completed deadlines that are in the past, the Days Until Deadline visual will show a zero. However, if I update the status to ‘Completed’ for all the tasks in the past, then my countdown will update to show many days until my next deadline:
This now gives me an accurate countdown as to how many days away I’m showing until the next deadline.
The To Do List won’t show up on the calendar and is just a way for you to track items you’re working on now. It will also show another countdown and is similar to how the recurring deadlines work in this section:
The last section on this tab is the tasks section.
This section is important since this is what will show up on your calendar. The deadline column will automatically be the first item on the list. And so you have a limit of three tasks that will show up on your calendar (two if there is a deadline there).
For the first three days of the year, I’m going to put the same three tasks:
This is how the calendar now looks:
Because the first two days of the year each had deadlines, only Task 1 and Task 2 made it onto those days. On the third day, all three tasks showed up.
Team Calendar
If you’re working on projects or teams, you might find it helpful to have a glimpse as to when people are off.
On the left side of the page, you fill in the person’s name and the range of dates that they are off. You can enter in multiple ranges for the same person so the names can repeat.
Next to the actual dates, you’ll want to enter each person’s name that you wish to track (which I’d presume is everyone that is on the left side that you have entries for).
The date will automatically start at today and you’ll see the next 90 days of availability. Any holidays and weekends will be highlighted in light blue. Any time off outside of that will be highlighted in dark blue as in the above example.
Download the Task Manager Template
Free Version – Limit of 10 task and recurring deadlines, contains ads and sheets are locked.
Full Version – 30 recurring deadlines and to-do-list items. No ads and no sheets are locked.
There are many different options for formatting data in a spreadsheet. And there are even more available if you use a custom number format in Excel. That flexibility is important because it can be a bit frustrating if, for example, you want negative numbers to show up with a dollar sign as you have to use the currency format in that situation — which does not look very polished:
The positive and negative amounts look okay but I’d like to see a bit more spacing. But the bigger issue for me is the $0.00 formatting which can create a lot of noise if you’re looking at financials with lots of zeroes over the place (although I have a solution for this). It can divert your attention away from what you want to see — the cells that have non-zero values.
Creating a Custom Number Format
Although it may not be available by default, there is certainly a way to get a whole lot closer to the formatting that I want, and I’ll show you how. To start, you want to select the accounting format and then flip over to the Custom format (to do this right-click and select Format Cells). You’ll notice this is what the string looks like in the Type field:
This is what the accounting format looks like. The formatting is broken out into four main parts: positive, negative, zero, and text.
The string that appears until the first semi-colon is how the number will look when it is positive. Until the next semi-colon is the negative formatting, followed by if the value is zero and the last one is text.
Here is what the positive amount looks like in the accounting format:
_($* #,##0.00);(
The negative formatting looks very similar:
_($* (#,##0.00);
The main difference you’ll notice is the extra bracket “(” that is in the negative format. That is what puts the negative amounts in parentheses. Now, if I want to make this highlighted in red, all I would need to do is add [Red] right after the semicolon that indicates the end of the positive format:
($* #,##0.00);[Red]($* (#,##0.00);($* “-“??);(@_)
Upon doing that change, my number now comes up in red:
These are all the color options you can use:
Black
Blue
Cyan
Green
Magenta
Red
White
Yellow
There’s not any added customization you can do to these colors. And as you can imagine, many of these colors will be an eyesore on the default white background, and I’m not sure why you would even need to use the default black value. Blue, magenta, and red are the only ones that are easy to read and that won’t make you want to change the background color.
More Customization Options
For more complete customization, you’re better off looking at how to use conditional formatting.
If you need to make other tweaks to number formats what you can do is select the format and then switch over to the Custom section. Then you’ll see what that format looks like and you can test out what adjustments you’d like. Whether it’s adjusting the spacing or how the format looks like with a zero value, these are changes you can easily make and see what works through some trial and error.
If you’re interested in looking how to format dates, check out this post.
If you liked this How to Create a Custom Number Format 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.
