One of the most researched numbers for stock investors and traders is a moving average. What is it? The average end of day price for a stock in the past 30 days for example. Why does it matter? Because many investors consider that breaking through that average is significant. Let’s take a look at how I’d calculate that price. First, I downloaded the stock price closes from Google Finance for Apple (AAPL) as you can see here:

Then, I added a column for the 30 day moving average and simply used the “average” function:

=AVERAGE(B2:B31)

Then, I dragged the formula down as you can see here:

Then, I thought it would be interesting to determine days when the stock moves through the rolling average. How? First, I will determine if the stock is above or below the average by using an “if function”:

=IF(B2>C2,”Over”,”Under”)

Then, I can simply make it a “nested” function, by checking if the previous day had the same result. If it didn’t, that means the stock broke through its rolling average. I will simply change it as follows:

=IF(B2>C2,IF(B3>C3,””,”Stock Breaks To The Upside”),IF(B3

You can also download the spreadsheet here

One of the more interesting features in Excel 2007 is the ability to quickly get decent analysis for different scenarios. Take an interesting case where I have this data for company XYZ. I’m asked to calculate the Net Present Value of this project/company over 5 years using that data. I will create a second table below with the data. By doing this, I will be able to easily see the impact of changing my assumptions. Here is what it looks like:

Most of the data in the tableis fairly straightforward to calculate. Unit sales are given in the example and increase by the sales growth number (C6) every year. Prices are also given with growth rate. The interest rate is necessary to calculate the NPV obviously (you can get all formulas and data in the spreadsheet, link is at the end of this post).

Basically, as you can see, the NPV in my current scenario is $134K or so.

Let’s suppose that for a few of these assumptions, the estimate is very uncertain. In my example, let’s suppose that the sales growth could change quite a bit. One thing that I could do is build different scenarios where I will change both that and the cost growth. I will name the scenarios. How do I do it? I will go into “Data/What-if-analysis”

Then, I select scenario manager and I can create different outcomes. How? I simply click “add”:

Then I can name that scenario, select the cells that I will be changing (ideally the same ones as other scenarios):

Then, I will be asked for those values:

Once I’ve build all of my scenarios, I can click on “summary” and then see the results:

Here is the result that I get:

I could then create charts, etc It can be incredibly powerful.

You can download my spreadsheet here for a better look at my scenario analysis.

Are you aware of how much credit card debt you really have? Nobody wants to be in credit card debt. We all want to be free of debt. We want to be released from the payments. We want to keep more of our money.

Do you want to crush your debt?

The first step is to determine how much money you owe.

How do you do this?

• Look at your credit card balances. You need to dig up your balances to see what the totals are like. This might be the toughest part because you have to face your failures.
• Add up all of the totals. What’s the total amount of money that you owe? Is it more or less what you expected? Are you ready to take on this amount of money?
• Compare the interest rates. How much money are you spending on interest? You should consider using a credit card calculator to see where your payments are going.

Now it’s time to move on…

What can you do when you figure out how much debt you have?

Decide your next move. Will you focus on earning more money? Will you start saving more on a monthly basis? You next move is critical because it’ll determine how rapidly you become debt free. Earning more money is an excellent strategy for attacking debt. Saving up more money also helps because you don’t have to work any harder. You just have to optimize your spending.

Figure out which debt reduction tool you’ll utilize. What sort of tools will you use to assist your process? There are all sorts of apps and programs available to help you deal with your debt. What you choose to use is ultimately up to you and how serious you want to get about crushing the debt.

Start attacking your debt. Now you have to take action. Since you figured out how much money you owe, the only option is to start paying back this money. This is money that you borrowed to have what you wanted at the moment. You enjoyed the purchases. Now you have to pay this money back. This is where things get serious. This is where you have to sacrifice and get creative because every dollar counts when you’re trying to attack credit card debt.

Keep on going. You just have to remain consistent. This is key. You can’t stop until you’re out of debt. As tempting as it is to start spending money again, you can’t. Your target should be on become debt free. Once you’re debt free, you can live the life that you’ve always wanted. No more feeling strangled by your debt. No more spending your whole paycheck on debt.

How much credit card debt do you have? The second you figure this out, you can start tackling your debt!

Greetings everyone! Today, I received a question from a recent buyer of our Excel ebook: “Taking your Excel skills to the next level“. Basically, the reader was asking for more information about a specific function. Here is the question:

I would like additional explanations of the actual inputs in the formulas. If you have the formula rank(A3:C:C), what do the inputs mean or what do you put in there as I have no idea?

Very fair question. I will without a doubt be working on improving the ebook to make that more clear. I did however want to get into that question as a general because this can be very useful no matter what function you are using. Let’s use that “rank” function and use a very simple example:

If you know which function you’d like to try or have some kind of clue, you can simply start typing that function in the appropriate cell and as you type, the name of the available functions will narrow down:

You can either start typing the info or in the much more likely situation where you’re not sure how to use the fields, you can click on the top “F(x)” at the top of this screenshot:

You will then see a screen where you can get information about each field:

In this case, the number, I’m trying to “rank” is in C1, so I’ll write down and select the next field:

Ref is the range where the numbers I’d like to be ranked are so in this case it would be: C1:C5

The third field is not in bold and as you can see in the description, is not required!

Another alternative is also to click on the bottom left in the “help on this function” which gives a description and example of the function in action, see here:

I’m always more than happy to help and will certainly try to be careful but I thought this post might help of a few people:)

In some cases, I’m looking to find the exact value that would get me to a specific result. Here is one sample. Look at this sample spreadsheet. Suppose that my company expects to have sales of $1,000,000 this year. I’m trying to set targets for myself in order to reach $3,000,000 by 2021, 10 years from now. I will simply set up the spreadsheet with a bogus growth rate as you can see here:

The formula that I use is the following:

=D5*(1+$G$4)

Which I dragged. As you can see, with a growth rate of 1% annually, I would have sales of $1,093,685 in 2021 which is way under my target. How much growth do I need to reach my target? I could simply try changing the growth value until I get close to $3M but there exists a much faster method in excel. I will use the goal-seek function that you can see in the data menu:

You can see the result here:

So 12.98% is the annual growth rate required to reach my objective. As you can imagine, this function can be very useful even though it has some limitations.