LadyTerp
2011-11-24, 05:41 AM
361
Hello,
I am very new to VBA and could use a little help with creating something to replace approximately 7 "if" statements that got out of control.
I have attached a spreadsheet that outlines the problem I am having.
Essentially, I am trying to forecast the production of widgets. I have only purchased 1,500 units of resources to make those widgets and once I run out I quit the business. The production inputs are the following:
Peak year - I will be increasing my levels of production until then, this needs to be able to change.
Growth rate - the rate each year I will be increasing the production until the peak year, this needs to be able to change.
Decline rate - after the peak year, my production will be declining, this needs to be able to change.
In 2000, I produced 200 widgets and that's what I'm basing the future production on (i.e. in 2001, it grows to 200*(1+growth rate)).
The problem is, I have three plants, each of which cannot produce more than a certain amount each year. So when I make the simple production profile that goes "grow at x% until peak year, then decline at y% until my resources run out" is complicated by the fact that there is a ceiling to annual production. When the production hits that ceiling, it doesn't waste the resources and instead saves them for next year, thus the production profile is somewhat extended by those excess resources.
Basically, I need it to say "grow at x% until peak year as long as it's under annual capacity, then decline at y% until my resources run out and it's under annual capacity".
I would in utmost sincerity appreciate any help and leads on this.
Thank you a bunch!
Anastasia
Hello,
I am very new to VBA and could use a little help with creating something to replace approximately 7 "if" statements that got out of control.
I have attached a spreadsheet that outlines the problem I am having.
Essentially, I am trying to forecast the production of widgets. I have only purchased 1,500 units of resources to make those widgets and once I run out I quit the business. The production inputs are the following:
Peak year - I will be increasing my levels of production until then, this needs to be able to change.
Growth rate - the rate each year I will be increasing the production until the peak year, this needs to be able to change.
Decline rate - after the peak year, my production will be declining, this needs to be able to change.
In 2000, I produced 200 widgets and that's what I'm basing the future production on (i.e. in 2001, it grows to 200*(1+growth rate)).
The problem is, I have three plants, each of which cannot produce more than a certain amount each year. So when I make the simple production profile that goes "grow at x% until peak year, then decline at y% until my resources run out" is complicated by the fact that there is a ceiling to annual production. When the production hits that ceiling, it doesn't waste the resources and instead saves them for next year, thus the production profile is somewhat extended by those excess resources.
Basically, I need it to say "grow at x% until peak year as long as it's under annual capacity, then decline at y% until my resources run out and it's under annual capacity".
I would in utmost sincerity appreciate any help and leads on this.
Thank you a bunch!
Anastasia