Excel Formula to Increase Unit Multiplier over Time

Copper Contributor

I would like to set up formula to use as a multiplier to apply to a number of other variables over time.  For example:

 

We will produce "x" number of widgets each month; each widget will have a constant monthly value (let's say $100 per month) for 24 full months, at which time the widget's value will revert to zero.  For any given month, we need to know how many widgets we have that are within their 24-month period of value ("y").  We will use "y" as a multiplier to a number of other constants to determine both expenses and revenues over time.  (For example, each widget within its 24-month period of value has a storage expense of $3).  By changing the value of "x" (i.e., new widgets produced each month), we will be able to look at the overall impact on income and expense.

 

This is probably a fairly standard calculation, but I'm having a little trouble getting my head around it!

 

Thank you in advance!

0 Replies