Chapter 5 Video: Flow Price Volume

In this Excel tutorial I'm going to be talking to you about how to find the present worth of a new product line. Say that you invested $100,000 on a new product. This product cost you $60 to make but you can sell it for $100, each. The interest rate you're choosing to analyzes is that 8 percent in B4. Now let's take a look at the first 5 years of your product line, so here I have years 0 through 5. And in year 0 you don't sell anything and that’s your initial cash flow, but in year 1 through year 5 here's your sales in units. Now let's figure out our cash flow. Remember in year 0 I simply put $100,000 into the project, so this is going to equal minus B2, to show that I invested the $100,000. Now the next year, I'm starting to make money because I've sold 5000 units. To figure out the cash flow for the 5000 units that would equal my number of units times the net profit I make off of that, which be the price per unit minus the cost to make it.

So, this would be times oops! didn't put times, times, what you could sell for which is B5 minus what it cost you to make which is B3. Now you can see I made $200,000 in year one. In order to make the remaining calculations very easy I can lock some cells and have some cells increment. They cost, the price you can sell it for B5 never changes, so I can lock that cell by pushing B4, which give me the dollar signs meaning I've completely locked it down to exactly B5. I can also do the same thing for B3. Highlight it hit F4 and I've locked the cell. I did not lock the C column because I do want it to increment my unit soul. Hit return. Now to make the remaining calculations easy, I can simply grab this corner and pull down. Notice when I got down here it incremented my C down to my C13 which is my 8000 units, but it locked down my profit there, my $100 minus my $60. Now let's figure out the present worth of this project. Now your $100,000 is already sitting at year 0, so this comes into the calculations a little bit differently than you might think.

In Excel to get your present worth this would be equal net present value, notice here it returns a net present value of an investment. And then it asks the first thing, notice it asked me in the parentheses what is my rate, that is in B4, then it asked me what are my values and I simply highlight D9 through D13 and close my parentheses. That will give me the present value of those future cash sums, but then I have… so those have been brought back to year 0, now I got to put in my initial cash flow so I add in B8 to bring everything back to the present and you can see that present worth of this new product line is about $1.6 million. In this Excel tutorial I've shown you how to do a present worth of a new product.