spreadsheets and suitcases

organization + travel = family fun

Travel planning for dorks- spreadsheet tutorial Part 1

on July 25, 2014

This post (and blog, really) assumes a basic knowledge of Microsoft Excel.  If the terms cell, value, formula, tab, column, and function mean nothing to you, perhaps look into one of those Office tutorials before diving in.  They’re kind of mesmerizing.

Not surprisingly, I am all about spreadsheets when it comes to organizing a trip.  Our June 2014 adventure was a trip to the East Coast, where we visited with various family members, attended an excellent Broadway show, road-tripped from NJ to PA to NY and back to NJ, attended a Yankees Game, and went to the Bronx Zoo.  On our road trip, we visited Hershey Park, went to State College (PA), and saw Niagara Falls from the Canadian side.  So…we were busy.

We stayed on course, on schedule for the most part, and mostly within budget due to diligent spreadsheetingOxford Dictionaries agrees that this is indeed a word.  Love it!  I wish it were an Olympic sport- this would be a collision of various things that I love.  I digress.

For this trip, my spreadsheet included 6 different tabs: Budget, To-Do Timeline, Details, Itinerary, Carry-on Packing List, and Packing List.  This post focuses on the first, and often most important planning tab, Budget.

Column headers include: estimated cost, actual cost, booking date, and confirmation #

Column headers include: estimated cost, actual cost, booking date, and confirmation #

I like to have a kicky font and fun name for the trip.  I can’t say this was my best work on the “trip name” side.  OK, I also need the trip dates to be nice and big at the top for easy reference.

The categories in Column B break down specific kinds of expenses.  This is the Transportation category:

Pro-Tip: I have 2 sections for Transportation, because some are Advance Expenses, and some are Variable expenses

Remembering the column headers I outlined above: in the Advance (incurred before the trip) expenses, I estimated our airfare for 4 from HNL to EWR to be $2500, and wound up paying $2831.34.  Over budget, but this is an amazing RT price for 4 people, and it was all thanks to the Alaska Airlines Visa Companion Fare benefit.  More on that in another post.  Then of course I have the booking date, and the confirmation #s.

In the Variable (incurred while on the trip) expenses, my Actual Cost for luggage fees was $100, which was $50 over my Estimated Cost of $50.  I have the spreadsheet set up to highlight the cell if the value in the Actual Cost cell is higher than in the Estimated Cost cell (via Conditional Formatting).  This helps me see where we need to adjust budget categories for the next trip.  This particular expense was annoying, because all of my other airline cards have free checked luggage as a benefit, but the Alaska card doesn’t.  I knew that going in, but it still bugs.

My other categories included Advance and Variable breakdowns for the  Pennsylvania and New York portions of the trip, with specific attractions and Misc road trip costs.  You’ll notice no other lodging cost beyond our Seattle airport hotel, our Hershey Park Hotel, and our Niagara Falls hotel.  This is because we stayed with my fantastic parents the rest of the time.  They have a great view of the NYC skyline!

Each category has a subtotal in the Estimated Cost column (highlighted a mint green), which feeds into either an Advance expense Grand Total, or a Variable expense Grand Total (highlighted in light blue for Estimated Cost, and pink for Actual Cost).  Finally, those 2 Grand Totals feed into the GRAND TOTAL at the top (highlighted in gray).  Below each GRAND TOTAL is a Variance, or the difference between the Estimated costs and the Actual costs.  Whew!

Close-up of Advance expenses section

Close-up of Advance expenses section

If you’re still reading, congratulations on your excellent attention span!  I arrived at this method of budget planning long ago, and it works for the way my brain processes information.  Feel free to customize to your heart’s content.  If there are requests, I can make a sample spreadsheet on Google Docs with the formulas, etc. all ready to go, and folks can download and enter their own categories/amounts.  Just be sure to pick a better name for a NY trip than “New York, New York.”

The next post in this series will focus on the To-Do Timeline tab.

 

Advertisements

8 responses to “Travel planning for dorks- spreadsheet tutorial Part 1

  1. […] begin a new spreadsheet and name the trip (remember to use a kicky font) […]

    Like

  2. Homepage says:

    … [Trackback]

    […] Read More here: spreadsheetsandsuitcases.com/2014/07/25/travel-planning-for-dorks-spreadsheet-tutorial-part-1/ […]

    Like

  3. […] is the final post in the spreadsheet tutorial!  See Parts 1, 2, 3, 4, and […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: