(E)Mail Merge

Blanca in Modesto, CA asked a very interesting question: Is there a way to perform a mail merge with email? In other words, Blanca was asking if it was possible for her to customize an email so that each person in her distribution list received the email customized to a level as highly personalized as she wanted?

The answer, as is usually the case—it seems almost impossible to find a business problem that the Microsoft Office Suite can’t handle, even if it involves advanced techniques such as VBA—is YES.

Here are the steps to take: Read more of this post

Money on memories!

Vacations are certainly not cheap. But the memories you create with your family are priceless!

When I was young–who am I kidding; even when I was not so young—I used to spend my (and more often than I care to admit, my parents’) money on material things. Whether it was cool jeans or the newest pair of “Jordan” sneakers when I was in high school and college or fancy rims for my car or cool electronics equipment when I was a little older; I never hesitated to spend money on “stuff”. Just “stuff”.

Well, whether it was because I was furthering my career in corporate accounting and finance and wanted to at least pretend to be responsible or because I got married, had children, and actually had to be responsible; I did a complete 180 with my approach to money, spending, and saving.  Rather than spend money as freely and carefree as I wanted and when I wanted, I began to deprive myself of almost any enjoyment or material ‘toy’, feeling that I was being selfish for spending on myself or, worse yet, that I hadn’t yet earned the right to reward myself.

Read more of this post

You fill in the blanks!

Figure 1: Neat data is not always "Analysis-Friendly" data

There are occasions when you will want to fill all empty/blank cells in a column. Often times, data is formatted in such a manner so that it is easier to read. Figure 1, for example, shows a portion of what an Accounting Report might produce for expenses over a given period of time. While it is very clean and easy to read, using this data to perform other analysis will require some work. If, for example, you wanted to use this data to show expenses by month or quarter, that would be difficult.

To make the data more “Analysis-Friendly”, then, the data needs to be converted to a “table” or “database” with records. To do so, we must make sure each row (or record) contains the expense (or similar category that is being grouped by the summary report). In our example file (attached), column B needs to contain the category for each row of data (date and amount).

Of course, we can do this the hard, manual way. We can retype the category for each subsequent row. No way! An easier way would be to copy the category heading and fill or paste in the subsequent rows. Better, but still some work.

There is actually an easier way.  Come on; of course there is! Read more of this post

Winners know how to (and that they must) push boulders!

Keep pushing life's "boulders" and it'll pay off!

Bill Parcells, the soon-to-be Hall of Fame football coach had a saying for almost every occasion and circumstance.  He was as knowledgeable about life as football and never missed an opportunity to share his wisdom and charisma.  One of his favorite expressions was used to explain the emotional fragility of young players.  “Confidence is born from demonstrated ability,” he’d say.  Of course, the thought is not original.  You may, in fact, have heard other variations of it.  “Once you’ve done it once, you can do it again.”  Or “You don’t know you can do it until you do it.”  However stated, no matter who says it or in what context, it’s as true and real as the world is round.

Confidence comes from evidence.  That’s obvious.  Or is it?  Doesn’t it take some confidence to produce the evidence in the first place?  Sure it does.  So what is it?  Does evidence lead to confidence or is it the other way around?  Indeed, like the chicken and egg quandary, the relationship between confidence and evidence is a confusing one. Read more of this post

Grouping Dates with Pivot Tables

I am not a gambling man. But if I were, I’d bet a million dollars that you will never ever grasp all that Microsoft Excel has to offer.  Fine with me because that means we can learn something new just about every day.  Pivot Tables provide a perfect example.

Pivot Table Basics

I’v raved about Pivot Tables in the past , praising the power and flexibility of this Excel feature.  In literally seconds, you can slice and dice data any way you wish to see just the data you need to make informed decisions.  (See prior post on Pivot Tables)

The journey between just learning about Pivot Tables and being able to effectively implement and use them is indeed a short one.  But take just a few more steps and you’ll find additional ways to save time, while adding helpful details to your information and analysis.  Let’s say, for example your company keeps records for each sales transaction.  A simplified table might include the name of the salesperson along with the date and amount of the transaction.

Using just the basic elements of Pivot Tables, one can easily produce a table showing the total sales for each date and each salesperson. (See Illustration).  But having data laid out in this manner is not completely helpful.  A better way to group the information might be by year, quarter, or month.  Or all three.  There are, of course, many ways to add those levels of grouping when producing this pivot table.  Those new to Pivot Tables might add columns to the raw data, converting the date to columns for the month (using the month() function), year (using the year() function), and quarter (applying the roundup() function to a simple equation: monthNumber/3- see :What Quarter is It?).  Even adding one for the week wouldn’t be that difficult. Read more of this post

Pre Workout Prep

I’m pretty good about helping my body recover and revive after a good workout.  Nothing beats a tall glass of water or an ice-cold Gatorade (G2 since it has few calories, of course) after an intense training session or a couple hours of basketball.  Psychologically as much as physically, downing a protein shake or taking some Branch Chain Amino Acids (I use http://bit.ly/nhwyky) helps me feel like I’m helping my body grow and recover after punishing it. Read more of this post

Data Tables

Performing what-if analysis in business is very common, crucial even. What if a new project is delayed by 3 months? Or what if the company is unable to secure financing at the projected cost of capital? Successfully answering questions like these often means the difference between a business finally turning the corner and becoming profitable and, well, it not. But this type of analysis is also helpful for individuals. Far from overly complicated but seldom used by consumers, tools that allow for thoughtful consideration of important personal finance decisions should be used much more often then they are. They can help one understand how much more a car will cost over time or what term and interest rate combination is required to make sure the monthly payment fits into the family budget. They can even reveal how your credit scoreis helping or hurting you.

Data Tables, one of the features in Excel's What-If-Analysis Package, is a very useful tool for comparing.

So let’s dig in. How would you perform this type of analysis? What tools would you use? Microsoft Excel, of course! Until recently, I’d approach this—or any other 2 dimensional, two variable scenario– by building a grid in Excel with all of the intersection values. For example, I might look at an auto loan with six possible interest rates, 5% to 10%, and five different possible terms, ranging from one to five years. To fill out the grid, I’d utilize the power within absolute and relative references to allow easy copy and filling across all rows and columns of the grid. Read more of this post