Category Archive 'Corporate finance'

How to use nested IF statements in Excel with AND, OR, NOT

Corporate finance, Excel function tutorials

Following up on last week’s , here’s one on how to use Excel’s IF statement when you have multiple conditions to meet.

The original question I received from a reader, which prompted this series, was about how to use the IF statement in Excel when you had two conditions that had to be met. For example, when sales fell between a minimum and maximum number. Let’s take a look at our example again. Note that I’ve added the maximum amount of $200K into cell B3:

Before we go further, if you’d like to work through the examples yourself, here’s the raw data you can copy into an Excel worksheet. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A1 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.

Bonus 12%
Minimum amount 100000
Maximum amount 200000
Sales Bonus
Salesman A 87925
Salesman B 100000
Salesman C 145000
Salesman D 200750
Salesman E 178650
Salesman F 99555
Salesman G 147000
Salesman H 213450
Salesman I 122680
Salesman J 92500

Ok, now back to the tutorial.

Now, let’s suppose sales have to be greater than or equal to $100K and less than $200K for a salesman to receive a 12% commission rather than just be greater than $100K, as in our introductory example. How would you write that in “Excel-speak”?

Read the rest of this entry »

How to use the IF function in Excel

Corporate finance, Excel function tutorials

A couple of days ago, I received a question from a reader asking how to use the for a particular scenario he needed to do at work. I thought other people might benefit from having a short tutorial on this, so I’m providing it here, divided into a few parts.

The first part (here) will go into the basic IF statement, and subsequent parts, such as , will describe how to use it in slightly more complicated scenarios or with additional features. For example, the reader’s question had to do with using an IF statement given two conditions, e.g. if a number falls between two other numbers. I address those in the separate post in the link above.

But, let’s go over the basic IF function here, first.

Let’s say that you have a series of numbers in Excel like this that represent sales figures that 10 of your salesmen have made this quarter, in column B:

Before we go further, if you’d like to work through the examples yourself, here’s the raw data you can copy into an Excel worksheet. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A4 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.

Sales
Salesman A 87925
Salesman B 100000
Salesman C 145000
Salesman D 200750
Salesman E 178650
Salesman F 99555
Salesman G 147000
Salesman H 213450
Salesman I 122680
Salesman J 92500

Ok, now back to the tutorial.

Next, suppose that you give them a bonus commission of 12% if they’ve exceeded a threshold value, say, $100K. Rather than going through each sales figure yourself and manually checking to see if that number is greater than $100K, and if so, calculating the commissions in another column (column C in this case), you can use the IF function to do this for you.

Read the rest of this entry »

Using the Goal Seek function in Excel: a brief tutorial

Corporate finance, Excel function tutorials, Personal finance

is a really useful and easy function to use in Excel, but a surprising number of people aren’t aware of it. If you’re a regular spreadsheet user and have ever run into a situation where you wanted to figure out what number to “back into” to get a result you want, or you have a hairy equation that you don’t want to toy with, using Goal Seek is a much easier approach than using trial-and-error. Here’s a simple tutorial on how to use Goal Seek example using the famous CAGR formula.

given an initial amount, and ending amount, and a period of time. As regular readers might recall, the formula goes like this:


CAGR = (ending amount / beginning amount)(1 / # of years) – 1

If you wanted to put this into an Excel spreadsheet, it might look like what’s below (taken from the first part of my ) (click to enlarge):

cagr1.jpg

It’s pretty straightforward. Initial amount, ending amount, and # of years are all numbers you input, and CAGR is the equation above written for Excel (e.g. taking the proper cells as inputs).

But what if instead of calculating CAGR, you wanted to calculate the number of years it would take you to get to a certain return, given an initial amount and ending amount?

Read the rest of this entry »

How to use XIRR in Excel to calculate annualized returns

Corporate finance, Excel function tutorials, Personal finance

I’ve previously created a as well as an , but these aren’t always the tools you need when trying to calculate your returns.

In fact, the two CAGR tools I created really only work when you have one initial amount and one final amount, and if that’s your situation, then they work wonderfully. But how can you calculate annualized returns if you put in money to invest in an account more than once, or if you make withdrawals more than once over a period of time? (This was actually exactly the situation I faced when I calculated my stock portfolio performance recently.)

Read the rest of this entry »

A rough financial analysis of Zecco

Blogging, Business & entrepreneurship, Corporate finance

Zecco has gotten a lot of press lately, both good and bad. If you haven’t yet heard of it, it’s a site that was launched in July and currently in beta whose business model aims to “revolutionize” the financial services arena by offering free trades and offering previously unavailable investing information and opinion all in one place, a la social networking.

Much has been written about Zecco already. Most of the positive news has been related to its PR and marketing department, while the online community has mostly voiced its doubts (see Techcrunch’s thoughts here or another blogger’s experience with them). But no harm, no foul. Every new business idea is fraught with naysayers and critics, and who knows? Maybe Zecco will succeed.

“Free” business models with expenses paid for by advertising revenue is no new concept, even in bricks-and-mortar businesses. Ryan Air is considering offering free flights to over half its passengers using this model by the end of the decade. There’s even a “restaurant” in Atlanta offering free food and drink (well, bread, butter, and sweet tea) called the Butter Trough.

What I haven’t seen discussed is how their business model will work. Assuming that they’ll be paying for the free trades purely through advertising revenue, I wanted to do some rough calculations and see what sorts of numbers came up. Obviously, I’m working with limited information, but here goes:

Zecco claims it will split revenues 50/50 with bloggers. Let’s assume they’ll be approved by and use Google Adsense, a staple advertising partner among bloggers. How much revenue will that bring in?

Read the rest of this entry »