Today, I decided to do a deeper look into how corporate projects can be evaluated in excel quite easily. The reality is that there are probably 1 million different ways to do it but I will start with a few simple examples and provide the link to the excel file at the end of this post to help you play around with the data and hopefully be able to build your own spreadsheet or build on top of mine.

Net Present Value – NPV In Excel

NPV is a rather simple function that can be used in excel. Before getting started though if you’d like a quick refresher about what a net present value is, you can see an older post about it here. In short, the NPV should help you determine if a project will be profitable given a certain cost of capital. For example, imagine that company XYZ can spend $5000 now to launch a new product that will be sold for 5 years.

-The revenues in year 1 will be $7500 and will increase by $500 every year
-Costs will be of $5000 to start and then $5000 per year
-The company needs to pay 40% taxes on the profits from this product

In short, the new product’s financial impact can be seen here:

By using the NPV function as follows, I can get the expected value:

=NPV (rate, value1,value2,value3,etc)
=NPV(B3,C10,D10,E10,F10,G10)+B10

I am adding B10 at the end because the initial cost should not have any rate applied to it, it will be spent on the first day. As you can see, the NPV of this project is $2745 so it does look like a promising project.

NPV With Cash Flow Probabilities

What is more probable though is to have some uncertainty regarding the financial impact of a new activity. I will simplify the previous example by only using the cash flows as you can see here:

Then, let’s simply add a few more scenarios and odds that those will happen:

Then, I will simply use the sumproduct function to calculate the “expected cash flow” so for example:

=SUMPRODUCT(D6:D9,E6:E9)

You can now see the new NPV:

And of course, you can download my spreadsheet here!!

This example could be made much more complex but it gives you an idea! Let me know if you have any issues!

Thanks!

This blog is dedicated to using excel, improving your productivity through the use of functions, macros, charts, etc. While I personally do use both Excel 2003 and Excel 2007, I did not consider the differences between the 2 to be major. So I was very curious to see what Microsoft would announce on Monday when it previewed its Office 13 suite, due to launch later this week.

I will of course be buying it when it does launch but I thought it might be interesting to already start taking a look at the differences and what you can expect and not expect.

New Features:

Let’s be clear. There is nothing groundbreaking about what’s being released. I don’t think anyone expected such features either. Excel is by far the top spreadsheet software and there are no obvious things missing. As is the case with the rest of Office, social will become a bigger part. What will have a bigger impact though is the integration of Excel 13 with Microsoft’s cloud capabilities and its Skydrive service. I don’t think all features available with Google Docs (especially the bility to have multiple users working on a spreadsheet at the same time) will be available but it seems like saving in the clouds, retrieving and using from any location, will be possible which is clearly great news. Another nice option will be the ability to easily share a spreadsheet, a tab or specific parts of an excel document.

Specific New Functions

Obviously we don’t have the complete list but here are some of the new ones that we know about:

–Ghost Protocol: Like me, you’ve probably had to deal more than once with data that came from some other document or from the web that was in a difficult format to work with. Sure, sometimes using “text to columns” would work. Not always though. Other functions helped but in the end it always seemed to be a painful exercise. Ghost protocl will be able to recognize patterns and separate columns accordingly which will clearly be very useful.

–Quick Analysis: Building charts, pivot tables or other presentation methods is always tricky and takes longer than it should. In Excel 13, simply selecting the data will be enough for excel to present you with a list of possible ways to display the data which should be smart. The formatting including “conditional formatting” will also be much improved.

I guess the main way that Excel is improved is in how its able to interpret data automatically. It will certainly be interesting to see how this and cloud features end up working.

Do you plan on upgrading? I will certainly be writing a lot more about this along with more about the newer functions in the upcoming excel version.

In the meantime, you can try the new version here:)

One of the challenges about resolving issues in excel is that there are usually so many different ways to resolve a given problem. It’s all about understanding the exact need and trying to find an optimal solution that will be:

-As easy as possible to create and modify later on
-Quick and efficient to use
-As light as possible
-etc

In this case, I received an email from a reader that had a challenge. I will simply show you a screenshot of the file that he sent and you will get the idea:

Interestingly, there are similarities between this problem and the golf scoring problem that I discussed recently. The main difference which makes it much more difficult is that the number of possible results is unlimited. That makes it challenging on many levels and makes it impossible to use a simple vlookup. I wasn’t quite sure how to tackle this specific problem. As is always the case, using a macro was certainly an interesting option. Using a nested if statement could have worked, or even a combination of a few of those but given the number of posssible outcomes, it would be very difficult to work with, and even more tricky to modify later on. Why?

Imagine using an If(xxx,y,if(xxx,y,if(xxx,……. Etc)

This would have gone on and on. My next reflex was to ask the reader if this would be used for multiple lines at a time. If so, that would make things even more difficult. Thankfully, in this specific case, it was going to be done one at a time. This meant that I could simply verfiy (with a nested if statement for each line if that specific answer was the correct one.

In O2 I added:

=IF(AND($F$4>M2,$F$4M2,$F$4

And you can download the spreadsheet of course:)

While I have written a decent amount of content about using several excel functions, how to build a macro and various other excel tutorials, there are also alternatives that can be used. Recently I wrote about Google docs which is a cloud based alternative to using Microsoft excel. I mainly discussed using the finance functions to get live portfolio pricing. It is incredibly powerful and while the tool is relatively new, it has been a growing part of what I’ve been personally working on. Why? Cloud computing isn’t new but it continues to become better and more powerful and there is certainly a lot more flexibility when you do not rely on your data always being accessible from the computer you are using.

So yes, I will be writing a lot more about Google docs spreadsheets and how to use them for finance tools, etc.

My Wife Goes Apple!!

After the arrival of the iPod and the iPad into our house, I should have known that having an Apple computer would be that next step. That is exactly what happened when my wife decided she’d like to have a MacBook Air as her new laptop (it was well due for a change). Since we left for vacations recently, I decided that we should bring the slimmer laptop (hers!) instead of my clunky windows based laptop. I did have work to do but I figured that with the Apple Excel version, I’d be good to go right? Wrong…! In fact I turned out having some issues with functions but mostly with running some macros. Being away from home, it was quite a challenge to work on figuring out and resolving those issues but as I am now back, I’d like to tackle some of them. I figure that I’m surely not the only person running into such issues.

So before starting to write, I’d love to hear from you if you’ve had challenges adapting to the Apple excel version and if so in what ways. I’d be more than happy to try helping you out and at the same time get good examples to display on this website.

I’d love to hear from you, simply comment on this post and I will get back to you by email if I can help:)

Look forward to more about alternative spreadsheets uses such as Google Spreadsheets and Apple-based Excel:)

Thanks again!

Today, I wanted to take some time to discuss something that many of you are perhaps not familiar with. I’m guessing that I’m not the only one who uses Excel to calculate the value of my portfolio and track my investments. There are many different ways to get this done.

#1-Excel – Most people that maintain their spreadsheets in excel tend to update the prices manually which can be fine if you have only a couple of stocks. There is an even better way which is to create a macro to capture stock prices from a site such as Yahoo finance as I discussed some time ago.

#2-Software such as Mint or Microsoft Money – In the past, I also used software where I could enter my stock trades and the value of my portfolio would be updated automatically. That tended to work very well.

#3-Google Spreadsheets– This is not as known but you can also use Google Spreadsheets. Those are very similar to Excel but they are “cloud based” and there are some built in functions that can be used which are very neat. You can see a screenshot of such a document here:

Basically, you use functions that are unique to Google Spreadsheets which rely on Google Finance, those make it possible to get stock prices but also other types of information such as a company’s financials, etc. Among other things, one attractive feature is that prices are updated on a live basis. I highly encourage you to see my sample spreadsheet here:

There are many different things that can be done and I might go into more details in the near future. I do however encourage you to take a look at their tutorials here.

I would love to hear if you’ve ever tried using Google finance functions in Google spreadsheets and if so, how that has gone.