Forum Discussion
Update prices based on list value
On the other hand, if all you're saying is you want to be able to select an academic year and a single annual percentage rate to apply to each of the intervening years, then a simple FV (Future Value) function will do it. Here's a reference that explains the use of the function. I use it based on a single annual period, count the number of periods (years) based on the row the year occupies in your data validation table, and use the rate specified in the drop down box.
The formula reads like this:
=FV($F$3,MATCH($F$2,Sheet1!$A$1:$A$6,0),,Table1[@[Int Price (No VAT)]],0)*-1Minus 1 at the end just to turn the number into a positive.
The basic formula could be very easy. However, the implementation of it will depend greatly on how your spreadsheet (workbook) is laid out, and maybe a more precise or comprehensive description of your goals.
Also a question: if you use this year's prices as the base, are you expecting that 5% per year increase to compound? Would you want to round prices to the nearest whole dollar (or some other generalizable figure, such as always ending in "$xxx.99"..... there are lots of ways to implement the general rule you've articulated.
Are you willing to share either the actual workbook (or a mockup that conceals any confidential or proprietary info)? Post it in your reply, dragging and dropping it to the area below the text box. OR post it on OneDrive or GoogleDrive (or equivalent) with a link pasted here that grants access.
Hi, mathetes ,
Apologies for the delay, it's been a busy week.
Ok, so in answer to your question I want to be able to have one document only that shows current prices and can do a forecast of price increase for each academic year. yes, the increase is compound. No, I don't want to round prices.
I would also like to have the option of changing the % increase, if needed, so I would be able to select for example prices for 2027/2028 with 5%, 10% or 15% increase.
Mock up attached.
Thanks in advance!
On the other hand, if all you're saying is you want to be able to select an academic year and a single annual percentage rate to apply to each of the intervening years, then a simple FV (Future Value) function will do it. Here's a reference that explains the use of the function. I use it based on a single annual period, count the number of periods (years) based on the row the year occupies in your data validation table, and use the rate specified in the drop down box.
The formula reads like this:
=FV($F$3,MATCH($F$2,Sheet1!$A$1:$A$6,0),,Table1[@[Int Price (No VAT)]],0)*-1Minus 1 at the end just to turn the number into a positive.
I am starting to look at this, and realizing we need some clearer definition.
You say this in your last post:
I would also like to have the option of changing the % increase, if needed, so I would be able to select for example prices for 2027/2028 with 5%, 10% or 15% increase.
Which in my mind raises questions like these: how much flexibility do you want to give to yourself on these variable increases running out 3, 4, 5 or more years? Are you wanting to project prices for the academic year 28/29, for example, that call that year for 10%, but for 27/28 call for 5% and 26/27 for 15%? And will you then want to create an entirely different hypothetical sequence for those same years? And so on? If so, we're not talking about those simple dropdown selections that you have, but rather a matrix that you can play with various scenarios, different sequences of increase. For example, a matrix like this which has three different sequences of increase to model over the course of six academic years.
That's just an example of an approach. It could be simpler, but in order to "simply" come up with a sequence of compounded changes over a series of years, you need to have the entire series spelled out, not just one year at a time.
- In return, I now am away from home and involved in meetings until the middle of next week, so won't be able to give any kind of reply beyond this one saying it'll be delayed. Sorry, but please be patient. And if somebody else wants to chime in, please be my guest.
