calendar7

Task Manager Template: Stay on top of Your Deadlines

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.

task manager template calendar

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.

month end list

For example, I’m going to change the month-end date for January 2019 to February 12:

month end dates

If I go back to the Calendar tab, even though I’ve selected January, the calendar will continue until February 12:

task manager calendar

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.

holidays

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:

task manager recurring deadlines

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:

task manager 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:

task manager recurring deadlines

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:

task manager status

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:

to do list

The last section on this tab is the tasks section.

task manager

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:

tasks

This is how the calendar now looks:

task manager calendar

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.

team calendar

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.

pay-1036469_640

Don’t Like the Look of Your Data? Use Custom Number Formats!

There are many different options for formatting data in an Excel spreadsheet. However, it isn’t all entirely flexible. I find it a bit frustrating that if you want negative numbers to show up with a dollar sign, you have to use the currency format, which does not look as polished as the accounting format does:

currency format microsoft excel

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.

How to Edit the Existing 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:

accounting format code microsoft excel

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 like 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 ( 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 semi colon that indicates the end of the positive format:

($* #,##0.00);[Red]($* (#,##0.00);($* “-“??);(@_)

Upon doing that change, my number now comes up in red:

red accounting format microsoft excel

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.

aexp

Expense Report Template for Excel

If you’re looking for a way to manage your employee expense reports and don’t want to shell out hundreds of dollars every month, then this expense report template could be a great option for you. You can set up rules and flags for categories and it will keep track of when and who a report was approved be.

Benefits of using this expense report template

The expense report template is designed to help with the following:

  • Unlike online reporting solutions, you have all the data and can store and save it as you wish.
  • Complete customization. Setup multiple approvers, categories, and flags as you need.
  • Make it easy for employees to enter expenses across multiple categories and branches
  • Allow management to easily change and setup desired limits for expenses
  • Bringing problem items to the attention of managers to force them to acknowledge and allow them to comment on individual issues.
  • An approval process that stamps the date, time and user ID of the employee who has reviewed the report and the manager who approved it.
  • Integration with the ribbon so buttons are readily available in the tab.

Setup limits for expenses

Let’s move over to the SETUP tab. This will show you the customization you can do with this report.

Under the expense categories section I can specify the dollar limit per category, the GL code, as well as if it is per attendee (PerAtt). The limit is at what amount the expense gets flagged. It won’t prevent the user from completing the report, but the limit instead acts as a way to flag expenses over the limit.

For example, in my table below if someone made a claim for $3,000 for air travel, it would get flagged for the manager to review.

In the case of meals, this is a moving target since it is largely dependent on the number of people at a given meal. If it is a company even with dozens of people then a limit of $50 or $100 will not be terribly useful. This is where the PerAtt column comes into play. For meals at $50 and an “X” marked in the PerAtt column, the $50 limit will be multiplied by the number of attendees indicated on the expense report (the data tab has this information). This will allow the limit to grow with the number of people in attendance.

The GL code column is for accounting purposes as to which account it should be booked to.

expense categories

Other setup options

In the next section of the setup tab it relates to branches and what GL suffix is to be added if necessary. If no branches are used this can be left blank. The accounting password is what will need to be entered when the accounting button is pressed at the data tab which will unlock all the tabs including setup. Also, there is a section for a list of employees and their approvers and the approver passwords.

expense report setup
If you need to set up more approvers or sections, make sure to insert a row above the last entry to ensure that formulas remain intact.
The last section in the setup relates to the GL coding for which account to credit as well as the tax account.
expense gl accounts

Using the data tab

The DATA tab is where all the data entry goes. I have filled out some expenses as below. Since both of them are over my pre-defined limits, they will both be flagged.

expense report template sample

Once the file is sent to the manager for review, he will press on the Manager Review button which will prompt for a password. If correctly entered, the Manager tab will show and it will show the following items as being flagged:

expense report template review

The information is pulled from the data tab and under the reviewable reason it explains why it has been flagged. There is also space for the manager to put their comments regarding the items so that once the report is sent to accounting for processing accounting will see any comments by the manager to confirm they have acknowledged and approved the expenses.

Finally, the accounting tab prepares the GL entry. This is triggered once the accounting button is clicked and password is entered

Get the expense report template today!

Download – Expense Report Template (Trial Version – 10 Entries)

Buy The Full Version – No limitations, coding unlocked and no ads

*default password to access the setup/accounting tabs is 5678
*passwords for approvers is listed in the setup tab

prepaid expense template

Prepaid Expenses Template

If you’ve got several prepaid expenses to track then this template can help you manage and reconcile that. You can select different start dates and durations and can easily modify the template to adjust it to your needs and add more items to it.

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 amortize prepaid expenses. If you’re looking for an amoritzation template, however, check this post out.

I have some sample data entered into it and the top section is where you can enter the details. This includes the prepaid item, the starting balance (or the total expense), the number of periods you want to expense it over, and the first period you want to start expensing it from. You can add additional items and just copy the formulas over.

The first month where I’ve entered is January 2016. However,  you can change that  to a different month and the other months will automatically increment. This spreadsheet covers 24 periods or two years as some expenses could possibly stretch longer than just one year. However, you can stretch this to more than 24 periods by again copying the formulas further down.

The section on the right allows you to enter your general ledger (GL) balance. Here you will see any variance between what that amount is and what the prepaid balance should be. This will help you to reconcile your prepaids and identify which items you still need to account for.

The prepaid expenses template is flexible and should be easy to change as needed. There is no coding in this template as it’s entirely driven by formulas. If you can copy formulas, you can easily modify this file as needed to suit your needs.

Another template that might help in your reconciliations is my  t-account template which can help plan your entries.

If you have any feedback on this template please feel free to leave a comment!