Why is an excel formula calculation not updating?

July 30, 2018 at 07:13:30
Specs: Windows 7
I'm hoping someone can help me with excel formulas not updating/calculating when I change an assumption.
I do have automatic checked under File, Options, Formulas.
I set up a simple revenue calculation for the next 5 years that consists of Units (column H, row 9) x Price (row 11), with Revenue is in row 13.
For 2018 I know Revenue is 604,000) and I'm making an assumption for Price of $35.00.
I back into Units sold by dividing Revenue by Price, which gives me 17,257 units.
Rows 10, 12 and 14 are year-over-year changes.
Going forward I want to drive Units and Price by making y-o-y assumptions for growth in rows 10 and 12, respectively.
To calculate 2019 Units I’m simply using the following formula: [last year's units * (1 + growth assumption)].
In my sheet the formula for Units in cell I9 is: [H9 * (1 + I10)], where I’m changing the growth rate in cell I10.
I have the same set up for the Price formula in cell I11, which is: [H11 * (1 + I12)], where I’m changing the growth rate in cell I12.
In cell I13 I’m just calculating Units * Price to arrive at Revenue. The formula is: I9 * I11.
In cell I14 I’m calculating the y-o-y change. The formula is (I13 / H13) -1.
I do the same thing for Units and Price going out to the year 2022 and all columns are adjacent to each other.
The problem is this: the beginning period is year 2018, in which I have Revenue hardcoded ($604,000), I’m making assumption for Price ($35.00) and dividing Revenue by Price to get to Units sold (17, 257). When I change my Price assumption in cell H11 from $35.00 to any other number, Units and Price update for the ensuing 4 years going forward, but Revenue is not changing for any of the years, including 2018.
However, if I make changes to my y-o-y growth rate assumptions for Units or Price, all the formulas going forward update, including Revenue.
I tried to use the snip function to paste it below but it doesn't seem to paste here.
Does anyone have a suggestion as to why nothing changes when I change my Price assumption in 2018?

See More: Why is an excel formula calculation not updating?

Reply ↓  Report •

#1
July 30, 2018 at 08:06:34
The easiest way to post an "image" is to post it at an image sharing site, such as imgur, and then post the link back here. Just don't use a site where we will have to register, because we probably won't.

I'm having trouble following your "text" and don't have time to set up a spreadsheet right now, trying to duplicate your data layout. However, once thing that jumped out at me is this:

You said:

"the beginning period is year 2018, in which I have Revenue hardcoded ($604,000)"

Then you said:

"Revenue is not changing for any of the years, including 2018"

My question is this: How could the Revenue for 2018 change if it is a hardcoded value?

Perhaps if you posted your example data in table format, instead in "sentences" it would be easier to follow. Please click on the following "How To" link and read the instructions on how to post example data in this forum.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
July 31, 2018 at 09:52:01
Thanks for your reply DadyDerby03.
I posted the table below.
I hardcoded 2018 revenue of 604,000 and Avg Price of 35. I divided 604,000 by 35 to arrive at the 17,257 units.
In years 2019 and beyond I'm using the y/y change in Units and Avg Price to drive the number of units sold and the Avg price per unit. I'm doing that by multiplying the prior year's number by (1 + the assumed growth rate).
In the Revenue line I'm just multiplying Units * Avg. Price.
If I change the 35.00 Avg Price in 2018, which is a hardcoded assumption, the Total Units and ASP all update going forward, which I expect them to do. However, the Revenue line for those years isn't updating, despite having cell referenced the formula for Units * Price for each year.
If I change the 2019 y/y growth assumption for Units or ASP, everything updates going forward. The issue is that Revenue for 2019, 2020, etc. doesn't update when I change the 2018 Avg Price assumption.
If I actually hard code the 2018 units of 17,257 and change 2018 Revenue to a formula for Units * Avg Price instead of the hard coded 604,000, all the years going forward will update, including Revenue.
What I can't figure out is why the Revenue for 2019 and beyond won't update when I set 2018 up with Avg Price as my driver, despite the fact that Units and Avg Price update.
Hopefully the table below makes this clear. Any help would be appreciated.

                  A          B          C          D          E 
1                 2018       2019       2020       2021       2022
2 Units           17,257     34,514     69,029     124,251    223,653
3 y/y change                 100%       100%       80%        80%
4 Avg Price       35.00      36.05      37.13      38.25      39.39 
5 y/y change                 3%         3%         3%         3%
6 Revenue         604,000    1,244,240  2,563,134  4,752,051  8,810,303
7 y/y change                 106%       106%       85.4%      85.4%   


Reply ↓  Report •
Related Solutions


Ask Question