H2Etaxes1

How to Calculate Taxes in Excel

Want to estimate how much you might owe in taxes next year? If you are self-employed or have other income besides what you get from an employer, then you may find it useful to plan ahead of time and determine how much you might owe to ensure that you are putting aside enough money for taxes. It’s not a fun process but it can save some headaches later on. The good news is that Excel can make that process easy. Below, I’ll show you how you can calculate and estimate your taxes in Excel. And if you’d just prefer to download the file that I have created, scroll to the bottom of this page.

Determining your marginal tax rate

To estimate your taxable income and marginal tax rate, the first thing you’ll need is a table for the tax brackets. For this, I will use the schedule for federal income tax brackets 2021 found here.

I can’t simply copy the table into Excel as I will need to format it a little differently (the values contain text and won’t be helpful if I need to do a lookup). The table needs to be organized by income threshold rather than tax rate. This is how I have set it up in Excel:

Income tax brackets set up in Excel.

To make this table easier to reference to, I am going to create named ranges for these tax brackets plus the income I am going to enter in. This will make it easier to follow along.

If I want to look up the incremental tax bracket for a given level of income, I can accomplish this using a VLOOKUP formula. This is the formula I would use to accomplish that:

=VLOOKUP(Income,TaxBrackets,2)

What it is doing is taking the income number, and looking up the tax bracket table, and pulling in the second column (the tax rate). The VLOOKUP formula doesn’t look for an exact match (as I have left the last argument empty) and it will pull the closest number without going over. This is where it’s important to put in the numbers that the tax bracket start at, rather than a range. Using this formula, it correctly tells me that income of $100,000 would be at the 24% tax bracket as it does not yet reach the minimum amount for the next bracket — $164,926:

Determining the marginal tax rate in Excel.

That tells me the correct tax bracket but I still need to calculate the taxes that are due at each level, which I will cover in the next section.

Determine how much you owe at each tax bracket

For the first tax bracket, I will need to determine if the income level reaches the second tax bracket. If it does and the income is at least $9,951, then I can multiply that by the tax rate of 10% as that would be the maximum that can be taxed at the first bracket — 9,951 x 10%. If the income is not at least $9,951, then I just multiply the total income by the tax rate. Here is what the formula looks like using named ranges:

=IF(Income>=IncomeLevel2,IncomeLevel2-IncomeLevel1,Income)*TaxRate1

For the second tax bracket calculation, I can follow similar logic. I will multiply the difference between the start of the third and second income levels. Here’s how that calculation looks:

=IF(Income>=IncomeLevel3,IncomeLevel3-IncomeLevel2,MAX(0,Income-IncomeLevel2))*TaxRate2

I also use the MAX function just in case there is a negative number (where the income doesn’t even reach the next level). The same logic can now be applied for all of the remaining tax brackets except for the last one. Like the first one, it needs to be calculated differently. In that case, I just need to know if the income is above that threshold. And if it is, I take the difference between it and the total income, and multiply it by the highest rate:

=IF(Income>=IncomeLevel7,Income-IncomeLevel7,0)*TaxRate7

If the income isn’t above the last level, then I put a 0 and multiply that by the tax rate. Now, when I’m all finished, I can sum up the tax owing at each level and come to a total tax number that would be due based on a given income number:

Tax owing calculation in Excel.

At this stage, you could now decide to deduct how much you may have already paid in taxes and any deductions or credits that you are entitled to.

But I’m not going to go any deeper here because there are too many different variations from one country and jurisdiction to the next when it comes to taxes. However, this should at least give you a good starting point for doing the rest of your estimation, however detailed you want it to be. But by at least estimating the taxes owing and deducting how much you have already paid, you should have a good idea of how much you might owe come tax time, under a worst-case scenario.

If you’d like to just download the file that I created when making this post, you can do so here.


If you liked this post on How to Calculate Taxes 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.

H2Etax

How to Calculate Tax Included in an Invoice

If you are creating an invoice and need to account for taxes, usually you just need to multiply the subtotal by the percentage due for taxes. However, it gets trickier when the tax amount is already included within the invoice total and you need to work out what the amount relating to tax is. This is important if you need to determine how much in taxes you need to claim on an expense or how much you need to collect if you’re the seller. Below, I’ll go over a sample invoice calculation to show how can determine the tax amount whether it is included in the total or not.

Calculating taxes on an invoice

Let’s start with the basic calculation. This is how you might normally determine the taxes on an invoice and the total invoice value:

Sample invoice calculation including taxes.

The calculation is straightforward as what you do is just take the subtotal, multiply that by the tax rate, and add that back to the subtotal. Another way is to just take the subtotal and multiply it by a factor of 1 + the tax rate. In this case, it would $100 x 1.10. But let’s pretend we don’t know the subtotal and just know that the invoice total is $110.00 and the tax rate is 10%. In order to calculate the pre-tax amount, we need to do the steps in the opposite order. To prove this out, let’s use a bit of algebra:

$100 + ($100 x 10%) = $110

This can be simplified as follows:

$100 (1 + 10%) = $110

Now let’s solve for $100 which I will assign a variable of ‘y’ to:

y (1 + 10%) = $110

To solve for y, all we need to do is move the factor of 1 + the tax rate and divide $110 by that:

y = $110/(1 + 10%)

Taking $110 and dividing by 1.1 will give us a value of $100. And so what our end result comes out to is essentially this:

invoice total / (1 + tax rate) = pre-tax amount

To calculate the tax, all that’s needed then is to take the total and subtract the pre-tax amount.

Now that the logic is set up, let’s convert this into an Excel formula:

Invoice calculation when the tax amount is included.

Similar to how multiplying by a factor of the pre-tax amount by 1.1 (when the tax rate is 10%) would get you to the invoice total, dividing the total by 1.1 would get you to the amount before taxes. If the tax rate were 5%, then you would use 1.05, etc.


If you liked this post on How to Calculate the Tax Amount When it Is Included in the Total, 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.

amort

2 Excel Functions That Can Eliminate the Need for an Amortization Table

An amortization table is a useful tool when you need to calculate interest payments, principal payments, and to track the balance that’s owed on a loan. However, you don’t have to create a full schedule to get these values and below I’ll show you two functions that can get you that information quickly and easily. First, let’s start with what a typical amortization schedule looks like.

Creating the amortization schedule

When you set up an amortization schedule, you’ll track the balances, interest, and principal payments. It often looks something like this:

Amortization table in excel.

You could use the table to determine what the balance is at the end of period 10 or to add up all the interest payments up until that point. However, there’s another way to arrive at those totals, and that’s using two functions that are available in the newest version of Excel: CUMIPMT and CUMPRINC.

Using the functions

In the amortization schedule, we can see that the ending balance of the $100,000 loan by the end of period 10 is $85,016.67. We can use the CUMPRINC function get to that total as well. The function takes on the following arguments:

Cumprinc arguments.

To calculate the cumulative principal payments, I’ll enter the formula with the following arguments:

=CUMPRINC(0.05/12,60,100000,1,10,0)

This gives me a total of -$14,983.36. When added to $100,000, it nets out to a balance of $85,016.64 — within just a few cents of the amount on the amortization table. The function gives you the flexibility to specify which periods you want to extract and so you aren’t limited in just tabulating the totals for the first 10 periods or starting from the beginning. You can start from period 13, or the second year, and so on.

If you want to calculate the total interest payments, then that’s where you can use the CUMIPMT function. It has the same arguments as the CUMPRINC calculation, so the formula will look very similar to what’s above:

=CUMIPMT(0.05/12,60,100000,1,10,0)

This tells me that the cumulative interest payments during the first 10 payment periods is $3,887.87. This matches what I would get by adding the interest payments in my amortization table over the same period, this time to the penny.

Should you use these functions instead of an amortization table?

On older versions of Excel, you won’t have access to these functions but if you’re using Microsoft 365 or Excel 2019, then these functions are available and can potentially serve as replacements for an amortization table. Now, if you need the table for audit purposes it may not be possible for you to do without an amortization table completely. But if you’re only generating the table just to determine how much you’ve spent on interest or what your balance will be at some point in the future, then these functions can certainly replace doing a full-blown amortization table.


If you liked this post on 2 Excel functions that can eliminate the need for an amortization table, 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.

IRR

How to Calculate Internal Rate of Return (IRR) in Excel

Last week, I covered how to calculate discounted cash flow. In this post, I’ll build off that worksheet and show you how you can calculate the internal rate of return (IRR) in Excel. IRR tells you the return that you’re making on an investment or project, and at what discount rate the net present value of all the cash flows will be zero. In these scenarios, there’s typically an outlay of cash, usually at the beginning.

In my previous example, I only looked at cash flows coming in. This time, I’ll look at a scenario where you pay money out at the beginning and generate cash flow in future periods. A common example is paying to upgrade a piece of equipment and then generating cost savings from it for x number of years. Knowing the IRR can tell you if you’re making enough of a return off of the investment and whether you should move forward with it. Using IRR can also be helpful when you’re comparing multiple options to see which one is the best one.

Setting up the spreadsheet

This step is about the same as when setting up the discounted cash flow template. You’ll need to enter the different years, the cash you expect to come in or out, and then calculate back what the present value is today.

Here’s what the file looks like setting in a scenario where you pay $100,000 upfront and then generate $10,000 in cash flow for 25 years. At a 5% discount rate, in this example the present value of all that cash flow is a positive $40,939.45:

Discounted cash flow calculation using an interest rate of 5%.

Calculating the IRR

The problem here is the discount rate can be difficult to determine, and that can have a significant impact on your overall returns. And so rather than worry about what your discount rate should be, you only need to determine the IRR — which is to say at what point would your present value be worth $0? If you need a higher return than the IRR the project would be a no-go but if you’re okay with anything up to and including the IRR, then the project or investment would be passable. What it comes down to is the lower the IRR is, the worse the investment is

There are a couple of different ways to calculate IRR in Excel. One way is through a formula called XIRR. It only has two required arguments — dates and cash flow. This is why in this example I entered dates for my cash flows rather than just numbering the years. This makes it easier for me to use the XIRR formula. In my spreadsheet, I enter the following formula:

=XIRR(D6:D31,C6:C31)

Column D contains my cash flow and column C contains the dates. Doing this, Excel tells me the IRR is 9.687% for this specific project. But if I work backwards and calculate the net present value, it doesn’t get me right to 0:

It certainly gets close to 0 and it’s probably close enough that it can help you make a decision about your investment. However, there’s another way to calculate IRR and that’s using Excel’s What-If Analysis. On the Data tab, there’s a drop-down for this option in the Forecast section:

What-if analysis on the forecast tab in Excel.

Depending on which version of Excel you’re using, it may show a bit differently, but what you’re ultimately looking for is Goal Seek.

Selecting goal seek from the What-If Analysis drop-down.

Goal Seek is an accelerated way of doing trial-and-error. Excel’s doing it for you much quicker than you could ever do it by yourself. For IRR, it’s the best solution.

Here’s how it works. You’ll need to enter the cell that you want to get to a certain value, what value that is, and which cell Excel should be changing values in. In my spreadsheet, E2 is where my net present value formula is, and I want that to equal 0. In cell B2 is my discount rate, which is what I want Excel to be changing. Here are what my inputs look like:

Setting the inputs in goal seek.

Then, once I click on OK, Excel goes to work. After a few seconds you should see Excel show you that the target value and the current value are a match (e.g. they’re both 0), meaning it’s done its job successfully:

Goal seek after completion.

Now, if I look at my template, I see a different discount rate and my total present value is netting out to 0:

Discounted cash flow template after using goal seek to calculate the internal rate of return.

As you can see, this is much more accurate than Excel’s XIRR function. You can repeat these steps and make this table for other projects that you can assess side-by-side.

If you’d like to test this out, try downloading the discounted cash flow spreadsheet from my last post and then just using Goal Seek or the XIRR function to determine your IRR. You can remove unnecessary columns from the sheet and then duplicate the table, and then you’ve got a template where you can assess multiple investments against one another.


If you liked this post on how to calculate IRR 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.

DCF

How to Calculate Discounted Cash Flow in Excel

Do you need to calculate the present value of future cash flows or assess two options that will impact your cash flow over many years? Excel’s a great place to do that and below I’ll show you how you can easily set up a template to calculate discounted cash flow that you can adjust for changes in the discount rate and cash flow. And if you don’t want to create your own template, you can download mine at the bottom of this post.

In this example, I’ll compare a lump sum lottery win versus a scenario where you receive an annual amount for 25 years. Step one is knowing to calculate present value, which is what I’ll cover next:

Calculating the preset value

To calculate the present value of future cash flow, you need to know what discount rate to use. What you can use is the rate that you can earn on a typical investment. For instance, if you invest in stocks and assume you can make 5% per year, on average, then you might want to use that as your discount rate. If you want to be more conservative, you could use a rate of 2%. Below, you’ll see how the discount rate can play a big impact in your calculations.

That’s because when calculating today’s present value, you have to use the discount rate to bring the future value back to what it would be worth today. For example, suppose you were to receive a $10,000 payment a year from now, and your discount rate was 5%. An easy way to calculate this is as follows:

Calculating discounted cash flows one year out.

You might see other formulas on the web involving fractions to calculate present value but just using a negative power does the trick. This calculation yields a result of $9,523.81. Because you’re not getting the payment today, the value of that money is worth less than the full amount. Consider that if you were to receive $10,000 today and invest it and earn 5%, then a year from now it would be worth $10,500 — more than if you were to receive the $10,000 in a year.

Now, suppose you used a discount rate of just 2%. In that scenario, the $10,000 payment a year from now would be worth $9,803.92 today. Since the discount rate is lower, there’s less of a cost associated with waiting for your payment. If the discount rate was 0%, then there would be no incentive for you to invest your money since a year from now it would still be worth the same value it is today. That’s why when interest rates fall and get closer to zero, people will be less inclined to keep their money at the bank and there’s more demand for gold — since that can be a better way to store wealth at that point.

Creating a template to calculate discounted cash flow in Excel

Now that we’ve gone over how to calculate discounted cash flow in Excel, we can set up the template. All that’s really necessary here is to map out the payment schedule, including how much cash you’ll receive every year. Here’s an example scenario of receiving $100,000 for 25 years:

Receiving 100,000 every year for 25 years.

All the payments don’t have to be the same, but for the lottery example, I’m going to keep them that way. What I can do is create another column that will tell me the present value of each one of those payments. To do that, I’ll use a formula that takes the cash flow value, multiples it by the discount rate (I’ll use 5%) raised to a negative power (the year). Here’s how that looks:

Discounted cash flow template.

I created a discount rate named range so that it’s easy to reference the percentage and to change it. The only thing left here is to calculate the total of all these payments, to arrive at the present value of all of them:

Present value calculation of all the payments over 25 years.

The total present value of the payments comes in at just over $1.4 million. Even though the total of all the payments over 25 years is $2.5 million, we’re losing a lot of that value because of the time value of money, at a rate of 5% per year.

However, let’s prove this out, and to do that let’s look at the future value of all these payments. Let’s assume that these funds will be reinvested and earning a rate of 5% every year. Here’s how much we’d have by the end of year 25:

Calculating future value of payments over 25 years.

In this situation, we’re benefitting from compounding and earning 5% on each year’s ending balance, which includes the prior-year return. By the end of year 25, if we were to invest all of these $100,000 payments at a rate of 5%, we’d have a future ending value of $4,772,709.88.

Now, remember, the equivalent of these annual payments is a present value of $1,409,394.46. Let’s assume that rather than receiving annual payments of $100,000, we simply receive a lump sum payment of this and invest it and also earn 5% every year. Here’s how that will look like:

Lump sum payment earning 5% every year.

The ending value after 25 years is the same, $4,772,709.88. This tells us that if you’re given the option of 25 annual payments of $100,000 or a lump sum of $1,409,394.46 today, there’s no difference to you (if the discount rate you’re using is 5%). If the discount rate is 2%, then the present value climbs to $1,952,345.65.

As you can see, depending on which discount rate you use, it can have a significant impact on your present value calculations. This template will allow you to quickly change the discount rate and see how the calculation looks under different scenarios. You can also add more years to this calculation by just extending the formulas down. The amounts also don’t need to be identical, they were only set up this way purely for the purpose of comparing lottery winnings in a scenario where you earn one lump sum amount versus equal payments over multiple decades.

If you’d like to download this template to follow along, the free version is available here, which goes up to year 15. For the full and unlocked version, which has no ads and goes up to 30 years, please refer to the product page here.


If you liked this post on how to calculate discounted cash flow 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.

taccount1

Live T-Account Template

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.

Time Off Tracker Template

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.

Setting up the time off tracker

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 both numbers. 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.

calendar-309912_640

How to Do a Weekly Sales Analysis and Compare the Same Days of the Week

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:

weekly sales analysis dates

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:

weekly sales analysis dates

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:

=DATEVALUE(“Jan 1, 2019”)-(WEEKDAY(DATEVALUE(“Jan 1, 2019”),2)-1)

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:

weekly sales analysis dates

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.

reconciler1100x620

Bank Reconciliation Template With Side-by-Side Matching and Automatic Matches

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:

setting up categories and keys on the bank reconciliation template

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:

Ribbon buttons on the bank reconciliation file.

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.

person fixing a computer

This Macro Converts Your QuickBooks Export Into a Table-Friendly Format

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:

quickbooks export into excel

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:

Excel button to modify data exported from Quickbooks

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.