In my previous post I posted about my love of Excel and we looked at JoAnna who was considering taking her favourite hobby of greeting card making and making it a small side business. Today I want to look at another type of problem that Excel can help you solve.
In my post One of My Faviourite Hobbies, I touched on the MBTI personality types and mentioned that people make personal decisions based on how they think or how they feel. Often our feelings can get in the way of making objective decisions. In this next example I’m going to teach you how to create a nifty tool called the “weighted average decisionator” and how we can use Excel to create this useful tool.
Linda is considering the decision of where she wants to go on holidays. She has narrowed her decisions to the following two options.
1. Drive to Florida to visit with family for a 2 weeks or,
2. Take a 1 week cruise with friends from Quebec to Boston via the St. Lawrence River
Both vacations are so different that it is hard for her to decide which one she prefers more. She has strong feelings about both. She hasn’t visited her Uncle Sam in Florida and he is getting older in years. Linda also loves to shop and Florida has great outlet stores. But on the other hand (she had warts J) she has always wanted to take a cruise and she has been saving up for a year now, but only has enough money to take the 1 week cruise.
Step 1 in making this decision objectively will be to come up with a set of criteria in which to make her decision. She has come up with the following 5 criteria that are most important to this decision.
1. Ability to Relax
2. Shopping Value
3. Family Visiting
4. Time with Friends
Step 2 involves giving the 5 criteria numeric weights to their importance. Here is how Linda decided the weights for her criteria.
1) Ability to Relax (Weight 30 Points)
a) Linda has had a very stressful year at work and really needs a good relaxing break so she has weighted this criterion very high.
2) Shopping Value (Weight 10 Points)
a) Linda loves to shop, but has been shopping a lot recently and is not in need of anything in particular so she has given it a lower point value.
3) Family Visiting (Weight 25 Points)
a) The thought of visiting Uncle Sam is very important to Linda so she weighted it high.
4) Time with Friends (Weight 20 Points)
a) Linda loves her friends, but feels that in comparison to visiting with Uncle Sam it should not be as important.
5) Memories (Weight 15 Points)
a) Linda loves to take pictures and loves creating scrapbooks of each of her vacations.
At this point we have enough information to set up our Excel spreadsheet. Here’s how I would set it up.
Notice that to make things simple I set up the spreadsheet to make each criterion a score out of 10. I have found that when comparing different decisions keeping the score out of 10 for each criterion makes the comparison process easier.
The final result shows that Linda should go to Boston because the Boston score was 66 while Florida only received a score of 60.
You may say, “why not just handwrite this problem instead of using Excel it’s an easy enough problem” I would agree, but I would argue that using Excel lets you change your weights and scores at the click of a button instead of rewriting the whole decision every time you want to change your weights or scores. Let Excel do the work for you! It’s a great tool and in this case reveals that the Boston trip will be the better trip in the long run.
Personal Problem Solved!