Archive for January, 2012

Using The Excel Payment (PMT) Function

Excel function tutorials

One of the very useful financial functions in excel is the PMT one which can calculate how much you need to set aside or invest. It can be used in a variety of ways but today I wanted to take a look at a common situation. I got the following email:

“I have to reimburse a 50K loan on January 31st 2015 and only have $21,000 saved up. How much do I need to set aside every month to make it?”

The PMT function is perfect for this.

=PMT (Rate, Nper, PV, FV)

Rate = here I’ll use the return that I’m getting on my money. Since I’m looking for a monthly number, I’ll convert the 7% into a monthly return:

Return monthly = (1+yearly)^(1/12)-1

Nper= here is a screenshot of how I’m calculating the number of months

PV= current balance..this can be linked to the portfolio value if you have it in excel

FV= what I’ll have to pay so -$50,000

Here is the entire spreadsheet:

You can also download the spreadsheet here

Whether to Hire an Employee or Not

Business & entrepreneurship, Personal finance

I’m now almost four months in to my new business.  As my last post indicated, sometimes business is slow.  But so far, the profits have increased steadily each month.  I am currently the only employee of my professional services business.  I don’t have enough work where I could afford someone full-time, but I might be able to hire a secretary 10-20 hours per week.  What worries me is that I don’t have a large enough sample size in terms of my business.  What if the work dries up?  The last thing I would want is to bring someone in and then later have to let them go because my fledgling business takes a bad turn.

There isn’t enough work yet where I’m incapable of doing everything myself, but I am starting to find it annoying coming in on the weekend to do secretarial and other administrative tasks.  The biggest reasons why I would like to hire someone, however, are as follows:

1) Sometimes people see me as less professional because I do not have a support staff/a secretary/assistant.  My profession is the type where one is expected to have a personal assistant.

2) Sometimes I feel that I could focus more on marketing my business if I didn’t have to spend so much time on administrative tasks.

3) Sometimes I’ll be out of the office for a few hours, and during that time the telephone rings straight to voicemail.  I worry that prospective clients may hang up during that time.  I also worry about not having someone at the base of operations if I need something faxed/emailed to me when I am working remotely.

Another concern for me is the extra red-tape/paperwork involved in having an employee.  It’s a definite downside to not being the sole worker.  I’m also not the best delegator so I worry how productive it would be for me to even have a secretary/assistant.

Finally, I like being solo.  I like that I can do what I want (when I want) without feeling judged.  I’ve worked in enough places to know that even if you are the boss, employees will be looking to you for guidance and direction.  There’s a certain amount of pressure that goes along with that–even though I believe it’s what’s naturally occurring anyway.

The Economy

In this economy, it would be really rewarding for me to be able to provide even a part-time job to another person.  That’s one of the ultimate goals (of mine) for owning a business.  But because the economy is so bad, I also don’t want to have to let someone go if my business slows down.  Finally, all my instincts are those of a boot-strapper, working hard to build up something from nothing.  It terrifies me to increase the expenses right now, so early in a business and when I’m trying to keep expenses to a minimum.

Conclusion

I know there’s not enough information about my specific business to ask your advice, but I would love to hear your thoughts on when a business knows it’s time to expand.  When is the time to bring in a new employee?  As always, your thoughts are greatly appreciated and personal stories are always welcomed as well.

 

 

Excel AND Function

Excel function tutorials

Today, I decided to look at the excel function AND. What does it do exactly? First, I would like to clarify that there are two main ways you would use the “AND” function. You can either use it in a nested if statement or you can use by itself. You might not understand what I mean but I will explain this much more clearly with examples.

Why You Would Use The Excel AND Function

There are many different ways to use the “AND” function. Basically, it is used to verify if a number of conditions are ALL met at once. It’s important to understand this. The objective of the function is to verify that all conditions are met. You could use 2 5 or even 10 conditions to verify. If only one of those is false, the function will return a negative value.

How Do I Use The And Function?

The first question to ask yourself is: “What type of value do you need the function to return?”

Basically you have two options:

#1-The function will return “TRUE” (if all conditions are met) or “FALSE” (if at least 1 condition is not met)

#2-You can decide what the function will return in both cases

The solution #1 only requires the use of “AND” while the solution #2 uses a nested function “IF(AND)”. If ever you are interested in reading more about nested functions in excel, you can visit a post we wrote about it here. Obviously, the second one is much more flexible and that is the one I would normally use. However, let’s start with a quick example of the “AND” function.

If I had this table of rankings and wanted to easily see which players are Russian, have played under 20 tournaments and whose name is less than 18 characters, here is what I could do:

AND (condition #1, condition #2, condition #3)

In this case, I would do:

=AND(E2=”RUS”,G2<20,LEN(C2)>18)

You can see the result here and in fact I could use conditional formatting to make it even more obvious:

Let’s imagine however that I would want to use either a formula or change the way the information is presented. Then, it would be easier to do an “IF AND” excel function as follows:

=IF(AND(E2=”RUS”,G2<20,LEN(C2)>18),”OK”,””)

As you can see, the difference is very small between the two so I think in most cases, it makes a lot more sense to use the “If And” function.

You can download the spreadsheet here.

How To Use The Find & Mid Functions In Excel

Excel function tutorials

Manipulating data in excel is a big part of what and many others end up doing on a daily basis. How can I most easily manipulate large sets of data I will take an example of something I had to do recently for a project of mine. I had a set of 200 tennis players in the following format:

I then needed to extract from this data a few informations in order to populate a table with these columns:

First and Last Name
Country
Points

You might think that it would be easy to do but how would you do it really? Remember that the current data has the last name in front rather than their first names. First off, I needed to separate each part of the first column. How? By using the “find” function. In fact, I need to find out for each line:

-Where the ranking ends (how many digits)
-Where the first and last names start and how logn they are
-Where to find the country

How will I do so? I will simply use the “find” function. First off, to find the “rank”, I will use:

=FIND(” “,A2)

The result for the first line is 2 meaning that the first space is in the 2nd character.

Then, I will determine the end of the last name by doing this:

=FIND(“,”,A2)

Then, I find the end of the first name by doing this:

=FIND(“(“,A2,D2+2)

I am using a 3rd range in this function, where I can specify where to start looking for this space. With this, I should be able to gather all of the information that I need.

Rank =LEFT(A2,C2-1)

In order to find the first name, I will be using the “mid” function. Basically, it helps me get a part of that text, starting at a point I determine, for a length that I also determine. In this case, I would get:

First Name = MID(A2,D2+2,E2-D2-2)
Last Name =MID(A2,C2+1,D2-C2-1)

Finally, to find the country, I will use the excel function “len” which returns the length of a cell. Why? I will simply do a mid that will start a bit past that last space and continue until the end of the cell (minus 1) just in case some country symbols have more or less letters than 3.

Country =MID(A2,E2+1,LEN(A2)-E2-1)

Here is the end result:

Obviously, you might find it easier to see things in action, you can download the spreadsheet here

What to do When Business Is Slow

Personal finance

As a new business owner, I find my business generation thus far to be very cyclical.  Things are always boom or bust, rather than in a happy medium.  During the boom times, I get nervous that I need to hire an employee or that I won’t be able to complete the work on time.  During the busts, I get nervous that I will never have another client and that my business is destined to fail.   I’ve written about this subject before, but I wanted to write a little bit about some action steps to take regarding what to do when business is slow.

1) Remember to Keep Your Perspective

Have you ever worked in a successful retail store?  I used to work for a movie rental chain throughout high school—back when movie rental chains were thriving.  What I noticed was that, even on a busy (rainy Saturdays in particular) Saturday night, when the business was making a killing, the customers would still come to my register in a cyclical manner.  It would be boom and then bust.  Surely more boom than bust, but still—there was a cyclical nature to the lines.

The point: don’t make rash decisions based upon the booms or busts, and don’t view your business through a microscope.  The truth is always somewhere in the middle.  As every cliche spouting professional athlete will tell you: “don’t get too high during the good times or too low during the bad times.”

That telephone likely will ring again with a new client or prospective client—and probably soon. That new customer will walk through the door, if not just this minute then eventually.  The secret is having patience, keeping perspective, and accepting that you’ll never really know where that next client or customer will come from, but, if you run a type ship good things will likely happen.

2.  Get Active

For me, if I don’t have any work to do then my job becomes finding more work.  It’s not always fun and it can indeed be at times soul-crushing, but it’s important to be active.  Maybe you can work on your marketing plan, or even do some menial jobs while you have the free time.  Write a blog post, send out a bulk mailing, contact a newspaper with a press release idea.  Just stay active and good things will likely come your way.

3. Keep Up Your Spirits 

Don’t let a little difficulty stand in your way, keep acting like the success that your company is (or will be).  Even the most famous people in the world have faced a little or a lot of adversity.  The history books aren’t lined with quitters.  For instance, most political leaders have suffered embarrassing election defeats prior to their ascendance to power.  The obituary on many a leader’s or other successes’ career has been written plenty of times.

Seth Godin’s the Dip is a great book to read if you’re concerned about whether your business is worth fighting for or not.   It states that most business owners quit just before achieving prolonged success.

4. Seek Mentors

It always helps to have the opinion of somebody who has already been through it.  Talking with a mentor (or mentors) about the problems facing you or your industry as a whole can be an invaluable way of learning and developing in your business and as a business owner.

5. Be Frugal

When times get tough, yours likely has to become a leaner business.  The important thing is to know what to cut.  If you’re cutting out client generating activities or expenses, then your frugalness will be counterintuitive.  That said, just like with your personal budget, there is always something that can afford to be cut to decrease expenses.

Conclusion

When business is slow, you have to pick up your pace, develop, and make the difficult choices.  Hopefully this list is a good starting place if you’re facing this kind of issues.  As always, it’s action that will achieve results.

But now if you’ll excuse me, I now I have to go follow my own words, and try and think of some new methods for finding new business.