Compounding Yearly growth and Inventory Runout

[asebinop]

Hi All,

Here is my question:

I have both a current inventory balance and yearly required invetory balance for many different items. Both of these values are coming from a VLOOKUP from a different file. What I am trying to do is forecast, with an inputted yearly growth rate, how much time in years I have until I deplete inventory of each given item. I am getting caught up on the fact that I need to tie in the compounding growth on the year over year increase for the yearly required inventory balance due to growth of sales, and then apply that to an inventory balance with no growth formula.

Any help would be appreciated and I would be glad to clarify anything if unclear!

 

[asebinop]

Anyone? Still completely lost... have tried NPER calculations and Nested IF/then Statements

 

[joeu2004]

Anyone? Still completely lost... have tried NPER calculations and Nested IF/then Statements

FYI, I found your original posting too vague to offer any constructive solutions. Perhaps someone can do better. Otherwise, I would suggest that you post a concrete example that shows data, formulas, and some explanation of expected results.

In my experience, this is best accomplished by uploading an example Excel file to a file-sharing website, then posting the "shared" URL in a response here. The following is a list of some free file-sharing websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com

BTW, I did not understand what you mean by the following:

I am getting caught up on the fact that I need to tie in the compounding growth on the year over year increase for the yearly required inventory balance due to growth of sales, and then apply that to an inventory balance with no growth formula.

Hopefully, that would be clarified by "some explanation of expected results".

 

[asebinop]

I am having trouble accessing any of those websites due to my company's internet settings, but let me try to clarify further what I need help with.

I have 2 values;
1) "Current Inventory Balance" = the quantity of a particular item I have in stock
2) "Yearly Required Inventory Balance" = the quantity of aforementioned item I need in stock and then use for n year

I am adding the assumption that the number of "Yearly Required Inventory Balance" I will need from year to year will increase by 12% each year. So the for the second year, the "Yearly Required Inventory Balance" will equal the last year's "Yearly Required Inventory Balance" multiplied by 1.12 and so on for each consequent year.

My goal or expected result would be a calculation that shows how many years until my "Current Inventory Balance" will = 0, based on the projections from the "Yearly Required Inventory Balance" compounding at 12% annually.

Does this help?

 

[joeu2004]

Arrgghh! This is now the 3rd time I've tried to post this without losing it to a session time-out. Forgive me if I'm a little surly.

I am having trouble accessing any of those websites due to my company's internet settings, but let me try to clarify further what I need help with. [....] Does this help?

Somewhat. It provided more context in which to reread your initial posting. I think I understand. But I'm still not sure because of some terminology. Let me paraphrase my understanding of the problem. If I'm very wrong, at least this presentation might serve as a paradigm for any follow-up you must post to correct my misstatement of the problem. Let's have a go at it (again)....

You have an initial inventory, Inv0, which you deplete each year. The first year's usage is Use1, and you deplete 12% more each year. So Use2 = Use1*1.12; Use3 = Use2*1.12 = Use1*1.12^2; etc. You want to know in how many years the initial inventory will be depleted completely. Good so far?

Let's create a concrete example and solve it manually. I find that helps me -- and others -- understand the mechanics of the problem and the expected result.

Suppose the initial inventory is 1,000,000, and the first year's usage is 100,000. We might set up the following solution in Excel.

[TABLE="class: grid, width: 259"]
<tbody>[TR]
[TD]
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]initInventory[/TD]
[TD="align: right"]1,000,000[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]initUsage[/TD]
[TD="align: right"]-100,000[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]depleteRate[/TD]
[TD="align: right"]12.00%[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]Year[/TD]
[TD="align: right"]Use[/TD]
[TD="align: right"]Inventory[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]1,000,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-100,000.00[/TD]
[TD="align: right"]900,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]-112,000.00[/TD]
[TD="align: right"]788,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]-125,440.00[/TD]
[TD="align: right"]662,560.00[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]-140,492.80[/TD]
[TD="align: right"]522,067.20[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]-157,351.94[/TD]
[TD="align: right"]364,715.26[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]-176,234.17[/TD]
[TD="align: right"]188,481.10[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]-197,382.27[/TD]
[TD="align: right"]-8,901.17[/TD]
[/TR]
</tbody>[/TABLE]

The formulas are:

A7: 1
A8: =A7+1
B7: =B2
B8: =B7*(1+$B$3)
C6: =B1
C7: =C6+B7
C8: =C7+B8

Copy A8:C8 down until column C is negative (A13:C13). We can see the expected solution is between 6 and 7 years.

Apparently, your intuition suggested that this is an "annuity" problem, and we might be able to use NPER to solve the problem without needing a table (or many tables in your case of many inventories).

And you are right! The NPER solution is as follows, which returns about 6.96:

=NPER(B3,B2,0,B1)

You might be surprised that B1 is the 4th parameter (FV) instead of the 3rd parameter (PV). At least, I was!

Well, I cheated! I derived the NPER solution by developing an algebraic solution and determining how the algebraic elements aligned with the presentation of the "annuity" formula in the PV help page.

Some natty details....

Note that in the manual Excel solution above, usage and inventory are not always integers. Of course, they must be integers in real life.

Therefore, we must remember that the NPER solution is an approximation. My guess is: the larger the NPER result (i.e. the more years), the greater the error in the approximation.

A more accurate model might be implemented using VBA, effectively emulating the manual solution above, but taking real-world constraints into account at each iteration (each year).

Also, note that NPER does not return an integer in this example. Indeed, that will be true most of the time.

You need to decide how to handle that: round down, round up, round to nearest, or use the fractional part to determine a month (again, rounding up, down or to nearest). There is no single "right" answer. It depends on your business requirements. Do you need help with that?

these values are coming from a VLOOKUP from a different file. [....] I am getting caught up on the fact that I need to tie in the compounding growth [...] and then apply that to an inventory balance with no growth formula.

Did the above address all of your problem, even if it misses the mark due to my misunderstandings?

I do not understand what VLOOKUP has to do with it. Is that a nuance of the problem that you need help with: perhaps how to nest NPER and VLOOKUP?

And I do not understand "apply that to an inventory balance with no growth formula". Again, did I overlook some nuance of the problem?

Please respond to these questions. With all the time I invested in this response -- frustration with the UI, not your fault at all -- I would appreciate a little the feedback.

 

[asebinop]

Joeu2004,

I cannot thank you enough. This is exactly what I was looking for! The other statements I made toward the bottom are not relevant to the problem you solved, my apologies.

I really apreciate your help.

Asebinop

Arrgghh! This is now the 3rd time I've tried to post this without losing it to a session time-out. Forgive me if I'm a little surly.


Somewhat. It provided more context in which to reread your initial posting. I think I understand. But I'm still not sure because of some terminology. Let me paraphrase my understanding of the problem. If I'm very wrong, at least this presentation might serve as a paradigm for any follow-up you must post to correct my misstatement of the problem. Let's have a go at it (again)....

You have an initial inventory, Inv0, which you deplete each year. The first year's usage is Use1, and you deplete 12% more each year. So Use2 = Use1*1.12; Use3 = Use2*1.12 = Use1*1.12^2; etc. You want to know in how many years the initial inventory will be depleted completely. Good so far?

Let's create a concrete example and solve it manually. I find that helps me -- and others -- understand the mechanics of the problem and the expected result.

Suppose the initial inventory is 1,000,000, and the first year's usage is 100,000. We might set up the following solution in Excel.

[TABLE="class: grid, width: 259"]
<TBODY>[TR]
[TD]

[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]initInventory
[/TD]
[TD="align: right"]1,000,000
[/TD]
[TD]

[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]initUsage
[/TD]
[TD="align: right"]-100,000
[/TD]
[TD]

[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]depleteRate
[/TD]
[TD="align: right"]12.00%
[/TD]
[TD]

[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD]

[/TD]
[TD]

[/TD]
[TD]

[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="align: right"]Year
[/TD]
[TD="align: right"]Use
[/TD]
[TD="align: right"]Inventory
[/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD]

[/TD]
[TD]

[/TD]
[TD="align: right"]1,000,000.00
[/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]-100,000.00
[/TD]
[TD="align: right"]900,000.00
[/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]-112,000.00
[/TD]
[TD="align: right"]788,000.00
[/TD]
[/TR]
[TR]
[TD="align: right"]9
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]-125,440.00
[/TD]
[TD="align: right"]662,560.00
[/TD]
[/TR]
[TR]
[TD="align: right"]10
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]-140,492.80
[/TD]
[TD="align: right"]522,067.20
[/TD]
[/TR]
[TR]
[TD="align: right"]11
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]-157,351.94
[/TD]
[TD="align: right"]364,715.26
[/TD]
[/TR]
[TR]
[TD="align: right"]12
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]-176,234.17
[/TD]
[TD="align: right"]188,481.10
[/TD]
[/TR]
[TR]
[TD="align: right"]13
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]-197,382.27
[/TD]
[TD="align: right"]-8,901.17
[/TD]
[/TR]
</TBODY>[/TABLE]

The formulas are:

A7: 1
A8: =A7+1
B7: =B2
B8: =B7*(1+$B$3)
C6: =B1
C7: =C6+B7
C8: =C7+B8

Copy A8:C8 down until column C is negative (A13:C13). We can see the expected solution is between 6 and 7 years.

Apparently, your intuition suggested that this is an "annuity" problem, and we might be able to use NPER to solve the problem without needing a table (or many tables in your case of many inventories).

And you are right! The NPER solution is as follows, which returns about 6.96:

=NPER(B3,B2,0,B1)

You might be surprised that B1 is the 4th parameter (FV) instead of the 3rd parameter (PV). At least, I was!

Well, I cheated! I derived the NPER solution by developing an algebraic solution and determining how the algebraic elements aligned with the presentation of the "annuity" formula in the PV help page.

Some natty details....

Note that in the manual Excel solution above, usage and inventory are not always integers. Of course, they must be integers in real life.

Therefore, we must remember that the NPER solution is an approximation. My guess is: the larger the NPER result (i.e. the more years), the greater the error in the approximation.

A more accurate model might be implemented using VBA, effectively emulating the manual solution above, but taking real-world constraints into account at each iteration (each year).

Also, note that NPER does not return an integer in this example. Indeed, that will be true most of the time.

You need to decide how to handle that: round down, round up, round to nearest, or use the fractional part to determine a month (again, rounding up, down or to nearest). There is no single "right" answer. It depends on your business requirements. Do you need help with that?



Did the above address all of your problem, even if it misses the mark due to my misunderstandings?

I do not understand what VLOOKUP has to do with it. Is that a nuance of the problem that you need help with: perhaps how to nest NPER and VLOOKUP?

And I do not understand "apply that to an inventory balance with no growth formula". Again, did I overlook some nuance of the problem?

Please respond to these questions. With all the time I invested in this response -- frustration with the UI, not your fault at all -- I would appreciate a little the feedback.

 

[joeu2004]

Joeu2004, I cannot thank you enough. This is exactly what I was looking for!

Good to hear. Thanks for the feedback.