Friday, June 3, 2011

European Travel Budget Template (in Excel)

After so much delay, I finally have developed a MS Excel document that can be used for European travel budget planning. This file is definitely a work in progress, but should be sufficient to get your own budgeting started. The European Travel Budget template file can be downloaded HERE... (updated 27 June 2011 to fix bug with meal cost reporting).

Before we can get started with the travel budget, you will need to do a few things first:

1) First, contact the bank you will be using to withdraw cash from an ATM in Europe. Ask them (a) what their Foreign Transaction Fee is (%) and (b) what your maximum withdrawal per day is.

2) Contact your Credit Card company and ask them what their Foreign Transaction Fee and your credit card limit is. As a side note, Capital One, with 0% Foreign Transaction Fee, is preferable.

3) Determine an approximate maximum cost you are willing to spend for your trip.

4) Determine an approximate number of days your trip will last (sorry, but the spreadsheet only accommodates trip durations between 1 and 21).

Once you have all of these items, or rough approximation of costs, you can begin filling in information in the spreadsheet. First, download, save, and open the form. To setup the sheet perform the following activities:

1) Determine the latest conversion rate of the EUR to USD by clicking HERE... or by using the hyperlink found on line 2 of the spreadsheet. Once you have the conversion rate value, enter it into cell C2. Take note, you will need to update this value from time to time as it is not automated.

2) Enter your Foreign Transaction Fee for your credit card into cell B3 (% format).

3) Enter your Foreign Transaction Fee for your ATM bank into cell B5 (% format).

4) Enter the approximate duration of your trip into cell B7 (1 to 21).

5) Enter your approximate maximum trip cost into cell D8 ($USD format).

6) Enter your credit card limit into cell D9 ($USD format).

7) Enter your maximum ATM withdrawal (per day) amount into cell D10.

You are now ready to start researching major costs for your trip. Keep in mind, all the items you have just entered can be adjusted as you plan. Start by performing the following activities:

1) Determine your entry and exit cities for your trip. For example, flying out of Orlando (MCO) to Amsterdam (AMS) and returning from Paris (CDG) to Orlando (MCO). Use these cities and some airfare search engine sites such as Travelocity, Expedia, or Kayak to obtain an estimated airfare cost for your trip. Enter this information into cells D478 and D479.

If you have more than two airfare costs you can add additional lines by highlighting cell A479, select the Insert...option, then select the Insert->Shift cells down option. Copy cells B478 to D478 (currency cells from line above) into the newly created row. Highlight cell C478, and then type "=C4". Repeat this process as necessary for each new item. This process will be the same for adding any additional lines in the spreadsheet. It is critical that all new lines be added between the existing lines, otherwise the subtotals will not take the new lines into account.

2) Get an estimate for any travel insurance, pet boarding, and parking costs and enter this information into cells D497-D499.

3) Start identifying potential lodgings/hotels for each stop/city on your trip. I have designed the system so that you can identify a primary choice and an alternative in case the primary falls through or is unavailable. Locate the website of the primary choice and enter it into cell E412 (first line under Hotel Costs (Paid in Europe)). Enter any information, such as cost and description of the room into cell G412 (under Comments column). Find the rates and determine the total cost for your stay in this location. If a deposit is required enter the cost (in EUROs) to cell B408. Enter the total cost of your stay to cell B412, minus the cost of the deposit. Identify an alternative lodging and enter this information (minus the cost) to cells E413/G413. Repeat this process as necessary for all lodgings/stops for your itinerary. If necessary, add additional lines as previously described.

4) Determine if you will need additional pre-purchased transportation, such as train, bus, or ferry tickets. Enter this information starting with the first line under Pre-purchased Transportation Costs (starting with cell B400).

5) Start to research tours and attractions (i.e., museums, castles, etc...) you are interested in. Determine if you will pre-purchase or if you will buy in Europe. If you will be pre-purchasing, enter the information starting with cell B404 (first line under Pre-purchased Attraction/Tour/Misc Item Costs). Otherwise, if planning on buying in Europe (on credit card), enter the information starting with cell B423 (first line under Attractions/Tours/Misc Costs (Paid in Europe)-on Credit Card). If necessary, add additional lines as previously described.

6)  Continue your research and determine approximate costs for breakfast, lunch, and dinner for each day. Note, B&Bs include breakfast so do not enter a value for any days you may stay in this type of lodging. Enter your approximate costs into each respective Breakfast, Lunch, and Dinner cell for each day. I have broken these up so you can plan on paying with cash or credit. Just be mindful, which is which when entering. I find, I get a better costs when I use a credit card since my Foreign transaction fee is lower than with cash.

7) Determine what the typical tipping/gratuity and tax rate is for the area you will be in and enter this information in the Tip/Tax Rate (% format) cell for each day.

8) If you know you need to pay cash for items, enter this information under the Attractions/Tours/Misc Costs (Paid in Europe)-in Cash heading. Anything you enter under column A, will be repeated in the daily log area above.

Once you have entered all the major items, look at the Misc line for each day in the Daily Spending Log area (i.e., Day 1, 2, 3, N, etc...). This amount is calculated based on the amount of cash you have remaining to give you a slush fund that can be used to pay for incidentals or even out your budget. I prefer to keep this number between €25 to €50 per day, if possible. This is the value that will adjust to account for changes in currency conversion to keep your tally within the identified budget limit.

The Daily Spending Log contains totals for the Total Cash of the Day, the amount to withdraw from an ATM, the remaining cash at the End of Day (EOD), and text that identifies when to withdraw cash (No Withdrawal, Withdraw Before Breakfast, Withdraw After Breakfast, Withdraw After Lunch, or Withdraw After Dinner)
This instruction should be sufficient to get your started, if you have any questions drop me an email or post in the comments section.

No comments:

Post a Comment