redcar

Mileage Log Template – Track Your Travel

If you do a lot of driving and you need to expense it for tax purposes, then you know you need a good log to keep track of it. That’s what the mileage log template will help you do. Along with tracking all your trips, it will let you do the following:

  • Track both kilometers and miles and convert back to a base unit. So if you travel outside of the country and don’t want to do the conversions yourself, you can enter them as either kilometers or miles and the spreadsheet will convert it into your base unit.
  • Allow you to categorize statuses based on whether you are going to be reimbursed by your company or not.
  • Track both personal and business travel so you have a complete picture of all your mileage
  • A summary tab to help you see your mileage by month

mileage log template

How the template works

In the Log tab, simply enter the details from columns A through to J. There are drop-down options for units, type, and status. These drop downs can be changed from the Setup tab. If you need to add more lines to the table, look for the next empty row and in column A enter a date. The table will automatically expand.

The template currently is set to fit onto one page in landscape, but you can adjust the columns as you need. If you do not need to see the status and would just prefer the template goes until column I, then you can delete column J and all the conditional formatting will go away along with it.

Summary of the mileage log

On the Summary tab, you’ll see a breakdown by month of all the different statuses and km/miles traveled. This makes it easy for you to see how much mileage you still have to claim versus how much has been reimbursed for. If you delete the status column then you’ll of course not see this information and simply have the totals.

 

mileage log summary

Customizing the mileage log template in the setup tab

On the Setup tab, you can make changes to the travel type. For instance, you could put a vehicle description in addition to whether it is personal or business. There’s no limit to the number of options you can have in this field.

In the status section, in the description field I’ve indicated what color a status will be highlighted in. If you want to change the name of the status, column C is where you can rename it.

In column G, you specify whether you want your base units to be in KM or miles. This will be used to convert the mileage and determining whether a calculation is needed. If you select miles as your base unit and on the log you put in KM for the units, then it will do a conversion back to miles on the log.

mileage log setup

Download options

Feel free to test out the mileage log template by downloading the trial version here. The limitations of the trial version are that the setup tab is not available and it also has an advertisement.

If you’ve tried the trial and would like full version, please visit the product page here.

cashflow

Cash Flow Forecast Template

Download Template

cash flow forecast
 

This template allows you to monitor and forecast out cash flows for a specified number of days. The current date defaults to today’s date but you can override it manually but if you do the formula will be gone.

You can also change the number of days you want to look in advance. For instance, you may only want to look at the cash you expect to have available for the next 7 days, 14, or however long you want.

First you will want to populate the current balance for each of the accounts. Right now they are hard-coded cells but you can certainly add formulas to populate this. The input section is on the second page of the Summary tab (scroll to the right if you do not see it on your screen)

cash flow forecast table
 

The cells above in yellow are ones you can edit. The ones in grey are formulas and need to remain the same as they are used in the chart.

There are three main sections in the chart:
– Funds Available
– Upcoming Transactions
– Outstanding Checks

Funds Available is simply a formula to show what cash on hand is expected at the end of the forecasted days. It looks at the current bank balance, deducts upcoming transactions, as well as the current outstanding checks. A positive number indicates the account will have cash remaining at the end of the period. A negative amount indicates that not enough cash is in the account to accommodate all the upcoming expenses and checks to be cashed.

Upcoming Transactions are populated from the Recurring Transactions tab.

upcoming recurring transactions

You can specify if a recurring transaction recurs monthly or annually. Based on this, along with today’s date, it will calculate the next occurrence of the transaction.

Further down on the Summary tab you can see a breakdown of the largest upcoming expenses on the left-hand side for all the banks and bank-specific transactions on the right-hand side. The yellow cell indicated below can be toggled to another bank and you will see transactions just for that bank. Both of these tables will only show expenses that fall within the date range you specified (e.g. if you specify only the next 7 days, it will only show expenses up until that date).

summary of upcoming and recurring transactions
 

The Outstanding Checks are fueled by the individual bank tabs. Each tab allows you to list any checks you have outstanding along with their amounts. Note that if you change any of the bank names on the input section you will also have to rename the tab. If the tab name does not match the bank name, the checks outstanding will not populate.

amortization

Amortization Schedule and Summary Template

 
 
This template allows you to track multiple amortization / depreciation schedules and summarize them all in one tab. Above is a completed schedule with the inputs (highlighted in grey at the top) filled in. As you can see the last payment will also take care of any balloon payment required. I have made 10 different tabs but you can copy additional ones or delete ones you do not need. Each schedule accommodates up to 1,000 payments by default and assumes monthly payment intervals however this can be adjusted.
 
In the sample file I have three different amortization schedules. If I go to the Summary tab I see the following break:
amortization depreciation summary

The tab names must match to what is written in the Tab Name field otherwise they will not pull correctly. If you update the Current Month field (just enter a date value, do not enter text even though it looks like text) the formulas will update and show you what your balance currently is, how much interest has been paid to date, and how many payments are still remaining. The benefit of this template is if you are managing many different amortization schedules you can get a snapshot of all of them in one tab.

agedar

Aged Accounts Receivable Chart

Download Template

In this template you can generate a chart showing the history of a customer’s aged accounts receivable. This chart will show a breakdown by invoice age so it will be able to tell you a great deal in one picture: the customer’s total receivables by month, breakdown of the age of the receivables by month, how much sales is being done with the customer (this would be the current receivables), and whether the receivables are growing or declining. It could be a very useful tool in evaluating a customer’s credit worthiness and in helping detect potential problems.

The main input tab is the AllTransactions tab, columns A:E. Column D specifies the type of transaction and should either be PAYMENT or INVOICE. Column C (Date) relates to the date of the transaction – either  payment date or an invoice date. Columns F:H are formulas.

The other input is the Customers tab. You will need to enter all the customers onto here. The easiest way would be to copy the names from all transactions and just extracting unique value (see this post on how to do that). Note that the customer names here must match the names on the AllTransactions tab otherwise when you select a customer data may not populate correctly if the transaction data does not have a match for that customer name.

Once entered, you can go to the Aging Chart tab and select your customer from the drop-down menu and the chart will update:

It is a stacked column chart so in addition to just seeing overall receivables by month you can see their age makeup. This customer did not go past over 30 days so they don’t venture past the dark green shading. Now, my other customer, Bad Customer, has a lot more colour:

This customer has gone as high as 120+ so they have the full spectrum of the aging schedule on here. The closer the colour is to red, the older the receivable is. You can modify these colours to your liking.

The current period that I have the chart running for is from January 2016 until March 2017. You can change the starting period in cell B2 on the Summary tab and if you want to add more months then simply drag the last column’s cells from rows 1 to 8 into the next column so that the formulas will update.

Because there are no macros in this template, you will also need to update the chart range so that it includes the new months you have added. To do so, right-click on the chart and click select data and in the chart data range enter ChartData – this is a named range that will automatically select the furthest column.

 
After you hit OK the chart will update. If you delete columns you don’t need to re-size the chart, this step is only needed when adding additional columns and months

 

Vacation Accrued excel table

Accrued Vacation and Time Off Template

Tracking employee vacation and time-off requests can be a bit of a headache. This template will help track time off as well as how much vacation has been used up and how much is remaining. It also helps to prepare your recurring journal entries to expense vacation.

Download Options

Free version: Download Here

Full version: (no ads/workbook locks): see product page

About This Template

This template will help you track and reconcile vacation liability owed to employees. Both in hours accrued and taken, and also in dollar values if you need to as well.

 Vacation Accrual – Employee Data

The first tab is the EmployeeInfo tab which is where you will need to enter your employee information. All the cells in light blue are ones where you can enter information. The dark blue cells have formulas and are locked. In the below example I’ve filled in some sample employee information:

Vacation Accrued excel table
 

You can replace the data in the table and you can also add rows by just typing the employee name in the next blank row in column A and the table will automatically re-size.

If you do not want to track dollars accrued then you can simply leave the wage field blank. This template will allow you to track vacation dollars and hours even if there are wage or vacation rate changes. In the above example, Jack Smith had a wage of $10 from January 1 until May 31. And from June 1st his wage changed to $15 and vacation rate increased from 4% to 6%. I’ll go over this calculation later in the post to show you that it has calculated properly in the reconciliation tab.

If there are no rate changes you can leave the end date field blank. You only need to use the end date field if the employee has a change in vacation days or wages, or is no longer with the company. In the latter case you will also want to set their status to Inactive. Doing so will exclude them from the JE tab and calculation.

 

Also on this tab is a section to enter the holidays for the year, and this is to ensure that the vacation calculation ignores these non-working days. The next section is to ensure the calculation is done correctly and the right percentages are used – daily hours will likely stay as 8 and the annual hours to used in the vacation rate calculation. So if you are paying an employee 4% vacation that is equivalent to 10 vacation days based on 2,000 hours.

The cutoff date is up to what date you want to reconcile to. So if you wanted to know what the vacation liability looks like at the end of the year you would set it to Dec 31, as I have below.

Holidays Table
 Employee Time Off Tracker

The next tab is the TimeOff tab. This is where you can select when the employee took vacation, and I added an option for sick as well in case you wanted to track that.

Employee Time Off
 
In the above example, John Burns is away January 4th, 5th, and 6th, so I enter the 4th as the start date, and the 6th as the end date. The next business day would be the day he is back at work. The hours taken reflects the average daily hours from the previous tab. The wage information also comes from that tab.
 
On the Calendar tab you will see a year-long calendar off the time off. This isn’t part of the vacation calculation but allows you to visually track vacation and sick time taken.
 
Time Off and Vacation Calendar
 
You see above that the days for January 4, 5, and 6 are highlighted for John Burns to indicate he took vacation on these days. Sick days will show in a different colour. Weekends and holidays are also filled in with light blue. This would be more of a scheduling tool when you are dealing with many employees and wanted to minimize time off conflicts and overlaps.
 
 Accrued Vacation Reconciliation
The Reconciliation tab shows a summary of all of the time earned and vacation time taken off
 
Accrued Vacation and Time Off Reconciliation Table

You can enter a starting date (this does not affect any calculations), a starting balance if you carry forward vacation from the previous year, and adjustments for any items not captured in the hours earned or time off taken (e.g. vacation pay out). The other cells are formulas and are locked.

I will refer to my example above where Jack Smith had a change in wage and vacation rate. His wage won’t affect this calculation but his vacation rate will.

From January 1 to May 31 he will accrue at a rate of 4%. Since he works 8 hours a day x 104 working days which fall within this range once holidays are factored in, that is a total of 832 working hours. 4% of this total is 33.28 vacation hours.

The second range is from June 1 until the end of the year. During this range there are 146 working days x 8 hours for a total of 1,168 total hours worked. At his new rate of 6% this is 70.08.

If I total 33.28 and 70.08 that gives me 103.36 hours earned which is what the reconciliation shows.

If you track dollars there is a separate section for the dollars accrued which works in the same way

Accrued Vacation Dollars Reconciliation Table

Again going back to my example earlier, Jack Smith had a $10 wage from January 1 to May 31st at a 4% vacation rate. If I multiply 104 working days by his daily pay (daily hours of 8 x wage of $10 = $80 per day) then I arrive at 104 x 80 = $8,320. And 4% of this is $332.80

Now from June 1st to Dec 31st that is 146 working days x his new daily rate of $120 ($15 wage x 8 daily hours) is a total wage of 17,520. 6% of 17,520 is $1,051.20.

If I add the two amounts together, I get $1,384 (1,051.20 + 332.80). As you see this equals the dollars earned for Jack Smith.

 

Vacation Accrual Journal Entry

The last tab is the JE tab which is used to generate your vacation accrual journal entry.

Accrued Vacation Journal Entry
 

The light blue fields are ones you can enter. You will need to specify the GL accounts, pay period, start, and end dates. There is also a space where you can put an adjustment for an employee. For instance, your accrual might be for a two week period but if someone was terminated or shouldn’t have accrued for any period of time here is where you can make the adjustment.

The employees listed as active will show up in this list as well as their wage during the dates you enter – as long as you don’t have rate changes that occur in the middle of a pay period there should be no issues here.

Please note this spreadsheet should be used to help reconcile to your payroll provider’s data to ensure accuracy and completeness. Do not use this spreadsheet alone in determining your vacation liabilities as I do not offer any guarantees as to its accuracy.

2019bracket2

NHL Playoff Tree: Prediction Template

2019 NHL Playoff Tree

The NHL playoffs are coming soon and so I have setup a template for predictions that you can use.

Download Template

 

It is fairly straightforward – you select the team you expect to win in each bracket and the number of games. The page can print out easily into one page but the results are also stored in a more data-friendly version in the Results tab.

Since the season isn’t over yet obviously the standings could very well change, and that is where they can be updated on the Standings tab.

 

As you change the standings, the playoff tree will update to reflect the changes. You can change the title, as well as the standings, so as long as the format doesn’t change you could use this template for future years as well.

Create Drop Down Options in Excel Using Data Validation

You can easily create drop down options in Excel by just using data validation rules. First, what you need to do is create a list of all the values you want in the drop down list. Once completed, assign a named range to those values (on how to assign a named range, refer to this post):

Note with named ranges you cannot use spaces. If you need to, use an underscore.

Next, select the cell where you want the drop down list to be. Under the Data tab in  Excel select Data Validation. Under the allow section, select List. Then in the source section reference your named range with an = sign before it. See example below:

If you didn’t assign a named range to your options, you have to specify the cells here. Click ok and you’re done.

Now when you select that cell you will see the drop down options available. If you want to copy the drop downs to be available to adjacent cells use flash fill by dragging the bottom right corner of the cell with data validation and drag over to which cells you want it to apply to.

Drop Down Options Based on Previous Selections

What you can also do is make your drop down options dependent on what you selected in a prior drop down selection. This requires using the INDIRECT function.

In the first drop down option what I will do is create a named range for all the categories,Category1-Category5. Once a user selects an option, the next drop down will be based on the products relating to the category that they have selected. The first part is the same as the process mentioned above, only now it is relating to the selection of a category group. The key here is making sure that the categories are the same as the named ranges I have created for them. For example, if a user selects Category1, in order for my other drop down to work I need to have a named range assigned to Category1. If I do, then I can utilize the indirect formula. Below is the data validation I would use for the second drop down option:

My category selection is in cell B7. The indirect function looks at cell B7 to see what range I am referencing. If I select Category1 in cell B7, then the data validation will pull the named range for Category1. If I haven’t set up a named range for the selection, the drop down list will be empty. Otherwise, my second drop down will now reference the products in the named range belonging to Category1.

awater

How to Make a Waterfall Chart in Excel

 

 

waterfall chart
This is a chart that is useful in reviewing variances and monitoring change from one period to another. Favourable (positive) variances are green, and unfavourable (negative) variances are red. In this example I used a statement of cash flow. Increases or inflows in cash are favourable, while decreases or outflows of cash are unfavourable.

 

On the data tab all that is required is the change column (B), and the remaining formulas can stay intact.

If you were to track the changes in an income statement, you want to be careful to make sure favourable changes are positive and unfavourable ones negative. For example, if sales are up 100,000, that should be favourable since it has a positive impact on net income. However if expenses are up 100,000 that is unfavourable since it has a negative impact on net income, so although it is technically an increase, the change should be negative. This is where the cumulative change column is helpful because it shows you the running balance, and the ending figure in that column is what you are reconciling to. If that number is not correct then you know somewhere a sign is wrong or an amount is missing.

 

The remaining columns (D:H) simply have to do with the appearance of the chart. Columns D:E are positive changes, G:H are negative, and F represents the amount that is not visible or blank. The purpose for the blank values is what allows the waterfall chart to create the effect of starting from the last position and just showing the change in the cumulative value.

 

 

aproject

Project and Special Event Budget Template

 

The project budget template is designed to help track expenses that you do not need to compare against multiple time periods. That being said you could copy the template and create a separate instance for each period you want to cover. This template does not use macros so does not requiring enabling content.

 

The budget categories can easily be added by just entering a new category in the space below and the formulas will autofill and the chart adjust to contain the new category. To remove a category you can delete the cells and adjust the table by pulling on the corner in the bottom right section of the table (in the overbudget column). This will re-size the data to ensure the chart is not pulling up blank values and making the chart show blank values.

 
 
See above for the sample categories, and the chart below summarizes the data visually to show how much of a budget remains, how much has been spent, and how much is overbudget.
 

Ranks in Excel: Breaking Ties

The RANK() function in Excel is limited to a single range and if you do not have a set of unique numbers to use the rank function on it will return repeating values.
There is a workaround however. If you can afford an extra column for a ‘rank total’ then it will be easy to accommodate. Or you can use an array formula.
The easiest example in creating a rank total column is to look at standings in sports:
Team
W
Points
GF
GA
Goal Differential
Team A
5
15
20
5
15
Team B
4
12
19
18
1
Team C
6
12
12
7
5
Team D
4
11
17
15
2
In this scenario I’m going to say the rank order will first be by points, the first tiebreaker will be wins, followed by goal differential. My rank total formula will be as follows: Points + wins/100 + goal differential/10,000. I’ve broken out how the values look and after totaling them:
Team
W
Points
GF
GA
Goal Differential
Win Value
Differential Value
Rank Total
Team A
5
15
20
5
15
0.05
0.0015
15.0515
Team B
4
12
19
18
1
0.04
0.0001
12.0401
Team C
4
12
12
7
5
0.04
0.0005
12.0405
Team D
4
11
17
15
2
0.04
0.0002
11.0402
For Team A, their rank total is made up of 15 (points), .05 (wins) and .0015 for goal differential. If the factor for goal differential was only 1,000, then goal differential adds 0.015 and now it affects the decimal position for wins and has the same effect as a sixth win, which is wrong. So you want to choose your factors carefully so as not to effect the higher ranking tiebreaker. If goal differential was only ever single digits then you could have used a denominator of 1,000 instead of 10,000.
The result of this rank total tells me Team C should be ranked higher than Team B because both teams have the same points, same wins, but Team C has the higher goal differential.
Now what you can do to pull the ranks is use the following formula:
RANK(ranktotalvalue, ranktotalcolumn)
Or if you want to put the name of the teams in order of their rank rather than just saying Team A is in position 1, then you can use the index and match functions as follows. Assume the Team column is column A and the rank total is column I:
=INDEX(A:A, MATCH(LARGE(I:I,ROW(A1)),I:I,0))
Let’s break down this formula:
=INDEX(A:A
This tells the formula I want to extract the value from column A.
LARGE(I:I,ROW(A1))
This extracts the largest value in column I. The reason I use ROW(A1) instead of the number one is because now if I drag this formula down the relative reference will become ROW(A2), ROW(A3), and ROW(A4) which then looks for the second, third, and fourth largest values respectively.
MATCH(LARGE(I:I,ROW(A1)),I:I,0)
This formula looks for where the value matches the result of the large formula calculation. Where that match is made, the related value from column A is returned. And the following list is generated:
Team A
Team C
Team B
Team D
This correctly puts Team C ahead of Team B in the rankings.
WHAT IF I DON’T HAVE ANY TIEBREAKERS?

If you do not have any tiebreakers then what you can do is pull them in the order that they appear. If you want them to be in ascending or descending order, then you will first need to sort the data in such a way.
In this case, you can calculate your rank total using a value for the row the values are on. The formula for the ‘row value’ would be calculated as follows: 1/(ROW()*100).  The fraction is used to make sure the rows higher up will appear first. I multiple the denominator by 100 to push it further down the decimal location. Below is how my rank totals now look:
Team
W
Points
GF
GA
Goal Differential
Row Value
Rank Total
Team A
5
10
20
5
15
0.005
10.005
Team B
4
12
19
18
1
0.003333333
12.00333333
Team C
4
12
12
7
5
0.0025
12.0025
Team D
4
11
17
15
2
0.002
11.002
I changed Team A’s point total to 10 for the sake of this example. Now the top two ranked teams (B and C) both have 12 points. Because B is in a higher row and thus shows up before C, it has a higher row value which in turn gives it a higher total rank value. So the correct order now is Team B, Team C, Team D, and Team A.
THE FORMULA METHOD

Now if you don’t have the luxury to put an extra column in your worksheet, you can certainly do this in a formula, although it won’t be pretty. Essentially you’ll recalculate the rank total and search through the values using an array formula.
To recalculate the rank for the non-tiebreaker method:
{=INDEX($A$2:$A$5,MATCH(LARGE(($C$2:$C$5)+(1/(ROW($G$2:$G$5)*100)),ROW(A1)),$C$2:$C$5+(1/(ROW($G$2:$G$5)*100)),0),1)}
The INDEX formula again looks at the Team column while looking for the largest value when adding the points value to the row value. The calculation for the row value is the same as above just now dumped into a formula. An array formula has to be used to ensure each team’s results are looked at individually.
For the multiple tiebreaker scenario from above, the formula will be longer to accommodate for all the extra tiebreakers it has to look at:
{=INDEX($A$2:$A$5,MATCH(LARGE(($C$2:$C$5)+($B$2:$B$5/100)+($F$2:$F$5/10000),ROW(A1)),(($C$2:$C$5)+($B$2:$B$5/100)+($F$2:$F$5/10000)),0),1)}
Again, same logic and formulas are involved except without a rank total column it has to be done in an array. The results yield the same order as through adding an extra column.