Travel Business Template in Excel: Free Download

Travel Business Template in Excel: Free Download
Page content

Don’t Leave Home without Calculating These Expenses

There are certain mainstay items that you will need to account for when planning a business trip. Transportation and lodging will be your largest expenses, but little items such as meals and fares around town can add up quickly and make even the best laid budgets go awry. This is why it is important to budget for all expected expenses.

This travel business template that can be downloaded here from Bright Hub’s media gallery contains these common travel expenses, all of which are currently deductible under the IRS rules:

  1. Transportation - covers travel by airplane, train, bus, or car between an employee’s home and the business destination.
  2. Personal vehicle usage - is a separate item expense for anyone using their own vehicle during the business trip. The standard IRS mileage rate for 2011 is 51 cents per mile for each business mile driven.
  3. Fares - include taxis and other types of transportation (public or private) between the airport or train station and the hotel as well as between the hotel and places where business meetings will be held.
  4. Lodging - covers the basic room rate and all applicable occupancy taxes. To be deducible, hotel accommodations must not be extravagant or lavish.
  5. Meals - are amounts spent for food, beverages, taxes, and related tips.
  6. Tips - are gratuitous payments for services rendered for any deductible expense.
  7. Dry cleaning and laundry - are costs incurred to present or maintain a professional appearance. These expenses are more common during long business trips or when strict business attire must be observed every day. An inopportune coffee spill could also send your staff searching for a dry cleaners, so it is best to budget a small amount in this category for unexpected accidents.
  8. Business calls - includes business communications by fax machine or other communication devices.
  9. Miscellaneous - is a catchall category for any unaccounted ordinary and necessary expense related to the business travel. Examples of deductible expenses listed by the IRS include transportation to and from a business meal, public stenographer’s fees, computer rental fees, and operating and maintaining a house trailer.

While the template is set up to track IRS deductible expenses, you can always modify the spreadsheet to include non-deductible expenses. For example, you may have a category for “entertainment” to cover a non-deductible expense incurred during a meeting or social at a nightclub, theater, or sporting event.

The Template’s Framework

The template, which was created in Excel 2010, has a number of built-in features to make aggregate calculations easier by consolidating the estimated budgets of up to six employees and alerting you when the estimated budget is getting too close to the allotted budget.

  • Budget (Worksheet 1) - The main worksheet is a consolidated budget that automatically compiles the amounts entered in each of the individual employee travel budgets.

The amount or the total alloted budget is entered into cell C3 on Worksheet 1, which for our sample template is $3,000. The last column (Column C) calculates a running total as each expense is entered and the last cell in the column C shows whether and by how much the estimated costs exceeds the allotted budget.

Consolidated Budget

  • Employee Estimated Budgets (Worksheets E1-E6) - These worksheets are used to calculate the estimated costs for each itemized expense for each employee.

Each budget tracks expenses over a one week period from Sunday to Saturday and these figures are automatically fed directly into the consolidated budget. Tracking expenses in a weekly log is useful, especially when arrival and departure times vary among employees because different itineraries. Travel arrangements for VIP members may also require special treatment as upgrades for transportation and lodging may apply.

Employee Business Expenses

The Template’s Special Alerts and Reminders

Alert for Budget Short Falls

The template contains an early warning feature that uses conditional formatting to alert the travel planner when the estimates have exceeded 90 percent of travel budget. In the sample template, cells in the last column will be formatted with light red fill and bright red text red when the expenses reach 90 percent of the allotted budget. Having a cushion of 10 percent provides some leeway for unexpected expenses and gives the person making the arrangements an opportunity to shop around for discounts. However, the 90 percent level is arbitrary and can be changed by resetting the conditional formatting in the last column of the budget to: “value is greater than =$C$3*(choose a percent).”

Overbudget Alert

Reminder to Check for Discounts

The template includes an example of a pop up reminder that warns the planner to check for discounts when an amount exceeding $100 has been entered for the lodging of Employee 1.

Travel Expense Reminder

Other reminders can be added to the template to follow company policies regarding other expense limits. In Excel, reminders are set up by using the data validation tool. Simply, select the cells and then click on the Data Tab and then select Validation. From Settings choose Custom and then type in the formula that will capture your parameters. The sample template uses “>100.” After setting the formula, choose an Error alert and then type in the warning message in the Title and Description. For example, the example template includes the message: “Check for Discounts.”

Special discounts, such as frequent flyer miles and group rates for hotels, can keep a budget within its target and should be investigated thoroughly along with other saving opportunities. By starting with this travel business template, you will have a useful tool to plan your travels in advance and determine when cost cutting initiatives need to be taken.

If you’re looking for sample forms and downloadable templates, check out Bright Hub’s resource guide Over 50 Free Project Management Templates and Sample Forms.

References